Tuesday, August 12, 2014

Reset sa password of SQL server

I did some steps in order to reset sa password of SQL server ; HERE is detail for reference.

RDC Login to machine IP = xx.xx.xx.xx with ‘Domain Admin’ account.
UPDATE: If all else fails, you can run SQL Server in Single User Mode, which allows a single connection to the server, and enable/change the sa password. To do this you can:
1. Open the command prompt (Right-Click on and select "Run As Administrator")
2. From the command prompt type net stop mssql$SQL2008R2
3. Next type net start mssql$SQL2008R2 /m
4. Open SQL Server Management Studio. Do not login, cancel the login dialog.
5. From the file Menu select New->Database engine query, and login (Make sure you use the host name and not localhost).
6. Execute the query ALTER LOGIN sa WITH PASSWORD = ''; to reset the password (if the sa is not enabled then type ALTER LOGIN sa ENABLE to do so)
OR -- As indicated by @jimasp in the comments, for step 6 you may have to do ALTER LOGIN sa WITH PASSWORD = '' UNLOCK;, because the sa account may be locked from too many login attempts.
7. From the command prompt type net stop mssql$SQL2008R2
8. Next type net start mssql$SQL2008R2
9. Login with the sa user and add the Administrator user or do what ever.