Unable to change the password of "sa" in SQL Server 2008

Question

I am trying to change the password of the sa account via SQL Server Management Studio, but it's not changing it, i tried all the techniques describe here http://blog.sqlauthority.com/2009/08/04/sql-server-forgot-the-password-of-username-sa/

WhenI tried to enter the management studio by sa it gave this error

Login failed for user 'sa'. (.Net SqlClient Data Provider)

---

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

---

Server Name: MY-PC\SQLEXPRESS Error Number: 18456 Severity: 14 State: 1 Line Number: 65536

after changing password I got this error

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.) (.Net SqlClient Data Provider)

---

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476

---

Server Name: MY-PC\SQLEXPRESS Error Number: 233 Severity: 20 State: 0

Answer 1

I first logged into SQL Server Management Studio(SSMS) via windows authentication mode. SSMS automatically picks the account with which you've logged into the PC. Your Winodws account is already added into SQL Server logins during installation. Then, I took below mentioned steps to rectify the issue:

1. Ensure mixed mode authentication is enabled. Go to SQL Server instance properties and check below. This step requires SQL Server service restart if you are making any changes to accomplish this.

2. Reset password of 'sa' user. Go to SQL Server Instance -> Security -> Logins -> sa -> right click sa node and go to properties. Provide a new password here. You might have to uncheck "Enforce password policy" check box if you are using a weak password just for your development purposes.

3. Go to status page in properties window of 'sa' user which you opened in step 2 above and ensure that 'sa' user is enabled as shown below:

4. Make sure Named pipes is enabled in SQL Server configuration manager as shown below. You might require SQL Server service restart in case you change any settings to accomplish this.

Answer 2

I got it done , the password changes here http://web.archive.org/web/20180115133134/http://www.rickwargo.com/2010/09/14/installing-dotnetnuke-community-edition-on-microsoft-webmatrix/

then open sql server from windows account and set remote connection to true and set security both "windows and sql server security"

it also needs to first allow TCP/IP and pipes in sql server configuration to be set as Enabled , then you must restart SQL server ad restart the SQLEXPRESS services

Answer 3

Please, check your password for "sa".May be your password is wrong what you entered in login.
Because , i checked on my SQL-Serever Managment studio , I getting same error message, was
same as your error message When i am enter wrong password.

So,Please try again with carefully change password, and also check status of "sa" and restart
sql-server.Then try to login.

Answer 4

Please follow below step :

1) First Open SQL Server Managment Studio.
2) Now you are in Login Scrren. Here you enter your "Server Name" : ".\SQLEXPRESS",
select "Auhentication" : "Windows Authentication". After this click on "Connect".
3) Now you are open "Object Explorer" is left side.
4) Expand "Security" ,
5) Expand "Login" ,
6) Right click on "sa" and select "property"
7) Select "General" from left site "select page".
8) Now you select "SQL Server Authentivation"
9) Enter "Password" then click on "ok" button.
10) Please check also "status" from left site "select page".There check is "Login" is enabled.