Recover SA Password on Microsoft SQL Server

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.

 

  • 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

 

 

27 thoughts on “Recover SA Password on Microsoft SQL Server

  1. Pingback: SQL Server DBA’s Everyday Life « Maurice Ray Consulting

  2. 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?

    • 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!

    • 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. 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 ;-(((

  4. I have experienced the same issue with Sergiy. But I resolved my problem using a software called “SQL Server Password Changer”.

  5. 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.

  6. Pingback: Recover SA Password on Microsoft SQL Server | Korobchinskiy's blog

  7. 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.

  8. 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

  9. I got this response while following you procedure to resolve my sql problem.
    ….is not a valid login or you do not have permission

  10. Pingback: Adding yourself to a SQL instance when you have no clue what the SA password is | thesql

  11. Pingback: Khôi phục password của sa trong SQL Server – dangphongvanthanh

  12. 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.

    • 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.

Leave a Reply

Your email address will not be published. Required fields are marked *