Jan 02 2013

Recover SA Password on Microsoft SQL Server

Filed under SQL, SQL

Applies to:

Microsoft SQL Server 2005, Microsoft SQL Server 2008

Overview

If you ever lost a SA password, you may have thought your only option is to reinstall SQL and re-attach to the DB’s. However, SQL server provides a much better disaster recovery method which preserves objects and data in the master DB. Members of the server’s Local Administrator’s group can access SQL server by starting it in single-user mode.

How to Recover SA Password

Open SQL Server Configuration Manager
Stop the SQL Server Instance you need to recover the SA password
Open the properties on the SQL Server Instance and click on the Advanced tab
- Change the Startup parameter by adding -m; at the begging of the line and click OK
Start the SQL Service Instance
Open the command prompt
Run sqlcmd and press enter
Run a Transact-SQL command to add an existing account or a new account to the sysadmin server role. Replace DOMAIN\Username with the account you want to add. *NOTE: if you receive errors, I’ve listed some common issues at the end of this article.

EXEC sp_addsrvrolemember 'DOMAIN\Username', 'sysadmin';
GO

Open SQL Server Configuration Manager
Stop the SQL Server Instance
Open the properties on the SQL Server Instance and click on the Advanced tab
- Change the Startup parameter by removing the -m; at the begging of the line and click OK
Start the SQL Service Instance
Open Microsoft SQL Server Management Studio and login with the account you added
- Under the DB, expand Security, then Logins
- Open the properties for the sa account, and reset the password

Common Error: Running SQLCMD HResult 0x2, Level 16, State 1

HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

Typically, this error is caused by the SQL Browser service not running or the named pipes not being enabled or incorrect. If the SQL Browser Service isn’t started, start it. If there is a specific instance name, try connecting to that instance (ie: sqlcmd -S tcp:localhost\instancename). SQLCMD is connecting through named pipes and the pipe name for the default instance is:

\\.\pipe\sql\query

To connect to a named instance, it connects to

\\.\pipe\<instancename>\sql\query

To make sure the named pipe is enabled and to change it to the default:

Open SQL Server Configuration Manager
Expand SQL Server Network Configuration
Click on Protocols for <InstanceName>, the default is MSSQLSERVER
Open the properties on Named Pipes

Passwords, sa, SQL, sqlcmd.exe

admin

37 comments

4 pings

Skip to comment form

- Sergiy on March 1, 2013 at 4:43 pm
- #
C:\Users\sergiy>sqlcmd
Msg 18461, Level 14, State 1, Server WIN-4NBGJC9LV1X, Line 1
Login failed for user ‘WIN-4NBGJC9LV1X\sergiy’. Reason: Server is in single user
mode. Only one administrator can connect at this time.

Any ideas?
1. - admin on March 1, 2013 at 5:20 pm
    Author
  - #
  Check if you have other SQL services running. Shut down FTS, SSIS and RptSvcs, leaving only SQL Srvr and SQL Srvr Browser services. Let me know if that resolves for you and I’ll make a note in the directions!
  1. Prabhu on July 18, 2014 at 6:37 am
    
    #
    
    After Shutdown everything apart from SQL Server and Sql Server Browser i am facing this same error.. any idea
    1. philip on February 26, 2018 at 4:13 pm
      
      #
      
      if you get this error it’s most likly you are not getting in fast enough. a better way to do it, is stop SQL in config manager then in a CMD prompt type
      net start “SQL Server (MSSQLSERVER)” /f /m
      the /m will set single user mode it and then you can type the
      sqlcmd -Sservername/instancename and you should get in make sure its capital S thats for default instance if you have named swap mssqlserver for your instance name.
      1. Mithun on November 8, 2018 at 3:23 pm
        
        #
        
        I got the same error, but it disappeared when I ran command prompt as administrator.
2. - ankitkumar.sparks2017 on January 25, 2018 at 2:41 am
  - #
  RUN > Type SSMS>
  Connect to Server> Click Cancel button.
  View (Main Menu)>Registered Servres> Database Engine>Local Server Groups>Select you server i.e. LAB-AK01- >Right click >Go To “Service Control” and click “Restart”.
  
  post the above steps run the sqlcmd:
  
  C:\Users\sergiy>sqlcmd
  Tested with SQL Server 2008 to 2012.
3. - Vojtan on September 4, 2020 at 4:57 am
  - #
  Someone else has already connected to the server – most likely SQL Server Agent of some process running under a domain/local account. Make sure everything else is disabled or cannot connect to the server bedore you do.
  
  Good luck, my man
- Sergiy on March 1, 2013 at 8:36 pm
- #
I guess it’s more complicated ;-(

C:\Users\sergiy>sqlcmd
Msg 18456, Level 14, State 1, Server VCENT, Line 1
Login failed for user ‘WIN-4NBGJC9LV1X\sergiy’.

Here is the problem. Our BDA is gone. We have no password at all ;-(
SQLServer and SQLServer Agent are running under local user which is sqluser.
I don’t have the password for it.

I created a local user Sergiy and gave him a password(this user has local admin privileges).

.. still can’t reset SA password ;-(((
- Sergiy on March 1, 2013 at 9:34 pm
- #
this solution worked like a charm!!!!
I hope this will help.
http://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/
- David on October 23, 2013 at 10:04 am
- #
I have experienced the same issue with Sergiy. But I resolved my problem using a software called “SQL Server Password Changer”.
- golond on December 10, 2013 at 4:21 pm
- #
You, sir, are a hero.
I even had the “the default settings SQL Server does not allow remote connections” error and your solution worked like a charm. A huge thank you from me to you.
1. - admin on December 10, 2013 at 4:53 pm
    Author
  - #
  Glad it worked for you!
- Pankaj Rajput on March 13, 2014 at 7:06 am
- #
Thank you so much, You are my superhero, i was struggling from 5-6 hours and finally your thread solved my problem completely. Thanks Again. Keep posting such more good stuff.
- Srinivas Reddy on May 15, 2014 at 1:52 am
- #
Thank you for the information, we can able to create the windows login in sql server with the above command when we lost sql sa password and don’t have any windows logins
- Prince on May 20, 2014 at 6:25 am
- #
I got this response while following you procedure to resolve my sql problem.
….is not a valid login or you do not have permission
- Lea on July 21, 2014 at 12:00 am
- #
Wonderful – this worked for us – thank you for posting!
- tell on December 19, 2014 at 11:04 pm
- #
Msg 15247, Level 16, State 1, Procedure sp_addsrvrolemember, Line 29
User does not have permission to perform this action.
- Eric Chappuis on December 22, 2014 at 11:15 am
- #
Thank you so much, you saved my installation !
- Xavier Carrero on January 22, 2016 at 10:08 am
- #
Smart approach. Kudos!
- Johnson on April 19, 2016 at 4:41 am
- #
i have tried but getting error “The sa password must meet SQL Server password policy requirements. For strong password guidelines, see Authentication Mode, in SQL Server Books Online.” then i tried free SQL Password recovery tool from here http://www.mdfviewer.com/recovery/password/ and recovered my SA account password successfully.
1. - juan on November 11, 2016 at 2:03 pm
  - #
  Crap, i have to pay, its not free
- Jithender on October 20, 2016 at 9:44 am
- #
Excellent solution ! thankyou
- Steve Windle on January 30, 2017 at 4:11 pm
- #
Spectacular success – thanks for your help
- resetisnotrecovery on February 2, 2017 at 7:28 am
- #
reset != recovery
please change the tittle of this article
1. - Jeff on March 23, 2017 at 6:26 pm
  - #
  What you want is completely ill advised, and no secure system built in the last 20 years will let you do it. If somebody says their system is secure, but they let you recover passwords in the way you expect here, then they are lying to you.
  
  Reset is the only reasonable kind of recovery for passwords. If it is possible to actually recover passwords in any system you use, please stop using it. If it has any kind of private information locked away by that absurdly insecure password system, then you’re sitting on a lawsuit landmine just waiting to blow up in your face. It’s negligence, in this day and age, to store recoverable passwords.
  
  Passwords in SQL Server, like ANY good secure system, are not recoverable in the sense that you can find out what the password is and keep using it. The recovery is in recovering control over the password and the account itself.
  
  It’s cyber security 101, no system should ever store passwords for later retrieval. What should be stored is a sufficiently strong hash of the password that cannot be converted back into the original password. The only place passwords should be stored is in the user’s brain, or maybe the user’s personal secure password storage.
  
  If a password isn’t stored by the machine, then the password itself can never be recovered.
  
  The use of the account can be recovered, however, by overriding the old password with a new one. This is right and proper and in line with good cyber security practices provided only people with the proper authority can do so.
- David on June 12, 2017 at 8:19 pm
- #
i cant’ change the Startup Parameter. My user es domain admin ..
- Shyam on September 6, 2018 at 9:24 am
- #
Once you have started the ssms in dac mode in CMD (net start “SQL Server (MSSQLSERVER)” /f /m), just type SSMS and hit enter.
This will launch SSMS in admin mode and logged in user can now update SA password without knowing the original password or can create new SysAdmin user.
- Sandeep on March 14, 2019 at 2:14 pm
- #
Watch it here:
https://youtu.be/DJjS4FCXyjk
- Fahim Thanawala on April 1, 2019 at 12:13 pm
- #
I am following the steps to enable my named SQL instance in single mode, but its not working.
We have multipe named instance in our SQL Server.
I stopped SQL Server, SQL Server Agent and Reporting SQL Service in SQL Configuration Manager.
Open the command prompt in admin mode and ran the below 2 commands
net start “SQL Server (instancename)” /f /m
sqlcmd -S Servername\instancename,
But getting the error as Login Failed for the user. Reason: Server is in single mode. Only One administrator can connect at this time
- Jack on June 16, 2019 at 10:11 am
- #
You didn’t recover the password. You made a new one. The problem is you have software that uses that password and you don’t know where all it uses that password.
- Mr Poopy Butthole on July 10, 2019 at 12:05 pm
- #
Thank you this worked a treat
- Copperskull on October 22, 2019 at 6:00 pm
- #
This did exactly what I needed. Thank you so much for this article!
- Junior_DBA on January 3, 2020 at 8:01 am
- #
Sergiy’s link worked for me as well. You need to download and extract the files on the same location as your ssms.exe file is.
- Jessica on February 25, 2020 at 10:45 am
- #
I am getting the error that only one user can be logged in. So I changed the login user on the LOG ON tab in properties. Ran the exec command without error but it doesn’t add that sysadmin role to my user.
Any thoughts?
- Slider on March 4, 2020 at 12:24 am
- #
UPVOTE -Sandeep Gupta

https://www.youtube.com/watch?v=DJjS4FCXyjk&feature=youtu.be

This solution is application in following cases:

1) “sa” users passowrd is lost/forgotten/locked and no other sysadmin

user is available.

2) Windows admin user has not been assigned sysadmin role in SQL server.

Steps to follow to login as system administrator in SQL Server :

1) Stop SQL Server Service

2) Start SQL Server in single user mode

-mSQLCMD parameter

3) Connect to SQL Server through sqlcmd

Open command prompt as admin : sqlcmd -S.

4) Add Windows local admin as sysadmin

USE [master]

GO

CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH

DEFAULT_DATABASE=[master]

GO

EXEC master..sp_addsrvrolemember @loginame =

N’BUILTIN\Administrators’, @rolename = N’sysadmin’

GO

5) Stop SQL Server Service

6) Start SQL Server Service in multi user mode

7) change “sa” password
- Jan on April 24, 2020 at 9:32 am
- #
Thx a lot. Work like a charm
- R. A. Gustavson on May 15, 2020 at 12:57 pm
- #
OUTSTANDING !!

THANK YOU .. !!!!