Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005


You may have faced the issue of losing the SQL Server SA password. Perhaps you followed the security best-practice of removing the  builtin\Administrators from the sysadmin server role,  and no one  you can find is in the sysadmin role.   At this point you may think that your only options are to reinstall SQL Server and attach the databases, or to  directly access the master database files, which may potentially damage the data.


 


SQL Server 2005 provides a better disaster recovery option for this scenario that is non-intrusive for master DB and that will help you preserve any objects and data stored in master DB (such as logins, certificates, Service Master Key, etc.) intact. Members of the Windows Administrators group now have access to SQL Server when SQL Server is in started in single-user mode, also known as “maintenance mode “.


 


Using the single-user mode, SQL Server 2005 prevents a  Windows Administrator to abuse this privilege to act on behalf of the sysadmin without being noticed. This allows Windows Administrator accounts to perform certain maintenance tasks, such as installing patches.


 


In order to start SQL Server in single-user mode, you can add the parameter “-m” at the command line. You can also use the SQL Server Configuration Manager tool, which provides proper controls for the file access and other privileges. To use the Configuration Manager tool to recover your system, use the following steps:


 


1. Open the Configuration Manager tool from the “SQL Server 2005| Configuration” menu


2. Stop the SQL Server Instance you need to recover


3. Navigate to the “Advanced” tab, and in the Properties text box add “;–m” to the end of the list in the “Startup parameters” option


4. Click the “OK” button and restart the SQL Server Instance


 


NOTE: make sure there is no space between “;” and “-m”, the registry parameter parser is sensitive to such typos. You should see an entry in the SQL Server ERRORLOG file that says “SQL Server started in single-user mode.”


 


5. After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility using Windows authentication. You can use Transact-SQL commands such as “sp_addsrvrolemember” to add an existing login (or a newly created one) to the sysadmin server role.


The following example adds the account “Buck” in the “CONTOSO” domain to the SQL Server “sysadmin” role:


 


EXEC sp_addsrvrolemember ‘CONTOSO\Buck’, ‘sysadmin’;


GO


 


6. Once the sysadmin access has been recovered, remove the “;-m” from the startup parameters using the Configuration Manager and restart the SQL Server Instance


 


 


Important Security Notes:


This process should only be used for disaster recovery when no other method to access the system with a privileged (i.e. sysadmin or equivalent) is available.


 


This process allows a Windows Administrator account to override their privileges within SQL Server. It requires explicit and intrusive actions that can be monitored and detected, including:


·         Stop SQL Server and restart it in single use mode


·         Connecting to SQL Server using Windows credentials


 


 


  Special thanks to Buck Woody (http://blogs.msdn.com/buckwoody/) for his help in writing this article.

Comments (19)

  1. As a DBA you might have come across this situation where you have forgotten the sa password for SQL Server

  2. It’s been a while since I blogged but I had a circumstance yesterday that I thought was noteworthy. …

  3. 付博 says:

    前几天不小心将SQLServer2008中的管理员帐户给删除了(Localadministrator),由于没有开SQL认证,所以没有账户可以连接上SQL,求助了几个论坛,问题终于解决,解决方法…

  4. Kool Guy says:

    Before connecting as a single user from SQL smc,  check all the other services (like Analysis, Sql agent, reporting) are stopped. I had an error trying to connect as a single user. I figured it out I need to stop all the other services except<Instance>.

    Thanks for this. It worked for me.

  5. Just Wondering says:

    Does this trick work on SQL Server 2008?

  6. padawan says:

    Thanks very much!

    It's works for me

  7. shehan says:

    thanks! saved me a lot of trouble! 🙂

  8. raulga says:

     BTW. This also works for SQL Server 2008 & SQL Server 2008 R2

  9. sqldba says:

    Will this work in SQLServer2000?.I had similar situation of disabled windows builtin admin access and forgot sa pwd in 2000

  10. raulga says:

     This feature was introduced in SQL Server 2005, and it works for SQL Server 2005, 2008 & 2008R2.

     Unfortunately I am not sure what I can recommend for SQL Server 2000. My suggestion would be to install a new instance of SQL Server and try to recover your databases. If somebody else has a better solution for SQL Server 2000, please feel free to share it.

      BTW. I would strongly recommend migrating your SQL Server 2000 to SQL Server 2008/2008R2. SQL Server 2000 is (as far as I know) no longer supported.

     -Raul Garcia

  11. rajesh says:

    super. its working for me too…

  12. Trex says:

    Thanks a lot!

  13. John Q. Publix says:

    Exactly the solution I needed.   Thank you.

  14. daisy says:

    where i can find this  Configuration Manager tool from the "SQL Server 2005| Configuration" menu?

  15. raulga says:

     To find the SQL Configuration Manager, go to the start menu in Windows, All Programs -> Microsoft SQL Server 2005/2008/2008 R2 -> Configuration Tools.

     Or simply open the start menu in Windows and type "SQL Server Configuration" on the "search program and files", the Windows search tool should find the tool on your system.

  16. A big Thanks, this works for SQL Server 2008 R2 as well.

    Cheers

  17. Cesar says:

    I was able to just add the ";-m" to the start up parameters, log in to the SQL express instance in via management studio, and give my user the admin rights necessary.

    this worked thanks so much!

  18. Jason says:

    Maybe I'm missing something here… after starting SQL server in single user mode. I try to access the SQLSMD.. it couldn't giving an error "Server is in single user mode. Only one administrator can connect at this time."

    I couldn't access via the management studio either with the same issue.

    Please advise.

  19. raulga says:

     This is a disaster recovery mode, and a single admin connection is allowed. Most tools with a rich GUI typically open multiple connections, and they are not suitable for this task. Please try using sqlcmd, which will only open a single connection.

     If sqlcmd is also giving the error message, most likely there is another tool/service establishing a connection before you have a chance to connect. Stop all services that may connect to SQL Server during the password disaster recovery operation.