sometime we may face problem to change password of sa using sql server management studio. that becasue by default this is not set to SQL server and windows authentication mode. Here the solution to do it step by step.
1.Open the SQL Server express management studio
2.Connect to SQL Server using windows authentication
3.Right click the server name and choose properties
4.Go to security tab. Change server authentication to “SQL Server and Windows Authentication mode”
5.Click OK and restart SQL Server
6.Go to SQL Server studio management express
7.Expand the server and choose security and expand logins
8.Right click on SA, from properties modify the password and confirm password
This changed pass should work.
any solution for great vista ! ?
please send me some other method for windows 7 with sqlserver 2008
great! it work’s
Just Wanted To SAy Thank You!
Thanks – well done
(Australia)
I’m sorry to say this didn’t work for me. Annoying me showing two Errors. On first try every time it shows this one:
“A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)”
From the next following times it shows…..
“Login failed for user ‘sa’. Reason: The account is disabled. (Microsoft SQL Server, Error: 18470)”
Can anyone help me out that…
Hiii Vexed,
have you got any solution for the sa login problem ..coz im also facing the same problem.
what i observed is after setting the password for the sa login in the status tab of the properties window…if i open it agian the password is getting reset to the default one.
im not able to change the password.
Is it the same case with you ?
Kindly share the solution with me .
Thanks
BTREDDY.
Solution:
Open the properties of the user. Then click on ‘Status’. Then change where it says “Login” from Disabled->Enabled.
Hi and thanks for the your log.
I try to do that but there is a problem, when I right click the server name , Properties is disable and I can’t click it.
Could you please guide me ?
BTW : I have VS2008 SP1
It worked magic, thanks very much ashraf
For SQL Server 2008 Express
1.Open the SQL Server express management studio
2.Connect to SQL Server using windows authentication
3.Under the server name expand the Security
4.Right Click the sa username
5.Click OK on properties
6.Set the SQL Password
7.Restart SQL Management Studio
8.Login with SQL Authentication mode With username sa and your password
Hi Firnaz I had done this process but again I am having problem @ login
The error message was like this
cannot connect to WINNER-HM\SQLEXPRESS
Additional information:
Login failed for user’sa’.(Microsoft SQL Server, Eror: 18456)
Any idea about this please?
Thanks for this. This worked fine for me very well. However, next I am having problem attaching a database from asp.net web application’s ‘APP_DATA’ folder, giving a ‘access denied’ error. Any idea about this please?
if this soluton dont work, then re-install..
[…] Here Did you like this? Share […]
great ! its works .
Thanks i can login with yours instructions.
Fernando
Great, this work in my case. Thank you! Anyway, you may also try another solution to enable SQL auth mode by this http://msdn.microsoft.com/en-us/library/ms188670.aspx. Have a good time
\\Bryan
thanks alot guys, i am using sql server 2008 R2.
i just follow the instruction, thanks again…
Forgot MS SQL Server SA password? SmartKey SQL Password Recovery is a professional MS SQL Server password recovery utility that can easily help to reset lost or forgotten MS SQL Server SA password in minutes without data loss.
thanks dude.. it works !!!
Ashraf, this worked well for me – Thank You.
I did this on my Windows 7 box, running SQL Server 2008 R2
I opened SSMS, as follows:
Server Type: Database Engine
Server Name: .\SQLEXPRESS
Authentication: Windows Authentication
Clicked on CONNECT.
Then, you should be allowed into SSMS.
Right click on your server .\SQLEXPRESS and click on Properties
Select page: Security
Under Server Authentication: Change the radio button from “Windows Authentication mode” to “SQL Server and Windows Authentication mode”
Click OK
Then to restart the service, right-click on your server .\SQLEXPRESS and click on “Restart” – you will get some confirmation dialogs, accept them and wait for your server to restart.
Then follow the rest of Ashraf’s instrunctions to set the password for sa within SSMS.
Hope this helps, as it worked for me.
Thanks,
-james
thanks for the info sir, it works π
I have searched lots of articles and tricks on this topic recently. The most impressive article is this one: http://www.top-password.com/knowledge/change-sql-server-password.html
Am tried to check the all above possibilities,But still issue persist.I could not change the password.once i reset the password and restart the management studio but it will choose the default one.
First of all it gave me message : “xp_regread() returned 5,’Access is denied'” and if I click ok there it throw error :”user not authorized to perform this task”.
Any other ideas how to retrieve/reset password for SA.
By the way during installation of Visual Studio it installed SQL Express but it did not even ask me to set password for sa.
It would be nice if Microsoft put their heads together and come up with some solid way to work with their products.
I am connected as windows authenticated user at this point. In security I see 2 accounts: SA and BUILTIN\Users
Thanks a lot it worked for me π
Thank you, worked for me!
The steps described above didn’t solve my problem either. What I had to do in addition, was:
1. Right click on “sa” (logged in with Windows Authentication)
2. Click on Properties
3. Go to “Status” tab
4. Make sure “Grant” and “Enabled” are chosen
5. Restart the SQLEXPRESS service
Hope this helps.
thanks