SQL Server 2012: Password emergency
If the unthinkable happens and you forget your SQL Server password, there are ways to get back into the system.
You’re a proud and a trusted DBA at your organization. You’re responsible for maintaining and updating several important services running on SQL Servers in your production environment. You’ve decided to perform the following steps—which are the kind of best practices any solid DBA would take—to secure your company’s SQL Servers from any unauthorized access:
- You’ve removed any and all built-in administrator accounts from SQL Server logins.
- You’ve removed all users (except System Administrator, or SA) that were part of the SYSADMIN server role (including any Windows Accounts and SQL Server logins).
- You’ve set the SA account password to something extremely complex that would be hard for anyone to guess or remember.
- For day-to-day operations on SQL Server, you use your domain user account, which has Database Owner (DBO) permissions on user databases but doesn’t have SYSADMIN privileges on the system.
- You haven’t documented the SA password anywhere to prevent others from knowing it. After all, it’s not a good practice to document the password.
Because you’ve set the SA password to be so complex—and you’ve been using your domain account and not the SA account for all your daily database-related activity on SQL Server—the unthinkable has happened. You forgot your SQL Server SA password.
You were the only person who knew the SA password in your team. Now you don’t remember what it was and you need to make some server-level configuration changes to your production SQL Server boxes. What are you going to do now? Here are some options:
- Try logging in as SA with all possible passwords you have in your mind.
- Look for the SA password on your computer hard drive or in your e-mails (you may have stored it in some file, which is a bad practice, but can be helpful).
- Try to restore the Master database from database backup. This won’t help in the long run because you’ll still encounter the same issue if you don’t remember the SA password.
- Rebuild the Master database. This is only marginally helpful, as you’ll lose all system- and server-level configurations and settings including logins, permissions and any server-level objects.
- Re-install SQL Server 2012 and attach all user databases. This may not work, as you may experience the same issues that you would experience with rebuilding the Master database.
Assume all your attempts to log in to the system using the SA password have failed. Now it’s time for you to call for reinforcements: the Microsoft product support services team. Here’s what they might tell you to do:
This is a backdoor to SQL Server 2012 that will help you gain SYSADMIN access to your production SQL Servers. However, this means your Windows account will have to be a member of the local administrators group on Windows Servers where SQL Server 2012 services are running.
SQL Server lets any member of a Local Administrators group connect to SQL Server with SYSADMIN privileges.
Here are the steps to take control of your SQL Server 2012 as an SA:
|1.||Start the SQL Server 2012 instance using single-user mode from the command prompt by launching the command prompt as an administrator. You can also start SQL Server 2012 using minimal configuration, which will also put SQL Server in single-user mode.|
|2.||From the command prompt (Run as Administrator), type: SQLServr.Exe –m (or SQLServr.exe –f) and start the SQL Server 2012 database engine. Make sure you don’t close this command prompt window. You can locate SQLServr.exe in the Binn folder of your environmental path. If you don’t have a SQL Server 2012 Binn folder in your environmental path, you can always navigate to the Binn folder of your SQL Server 2012 machine. Usually the Binn folder is located at C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn>.|
|3.||Once the SQL Server 2012 service has started in single-user mode or with minimal configuration, you can now open up another command-line window as an administrator and use the SQLCMD command from the command prompt to connect to the SQL Server 2012 instance:|
SQLCMD –S <Server_Name\Instance_Name> Example: SQLCMD –S "SALEEMHAKANI"
|You will now be logged in to SQL Server. Keep in mind that you’re logged in as an Admin on the SALEEMHAKANI SQL Server 2012 instance.|
|4.||Once you’re logged in to SQL Server 2012 using SQLCMD from the command prompt, you have the option of creating a new account and granting it any server-level permission. Create a new login in SQL Server 2012 called “Saleem_SQL” and then add this account to the SA server role. To create a new login from the command prompt after performing Step 3, use the following:|
1> CREATE LOGIN '<Login_Name>' with PASSWORD='<Password>' 2> GO
|Here’s an example:|
1> CREATE LOGIN SQL_SALEEM WITH PASSWORD='$@L649$@m' 2> GO
|Once you’ve created the new login “SQL_SALEEM,” add this login to the SA server role on the SQL Server 2012 instance. From the same command-prompt window, execute the following statement:|
1> SP_ADDSRVROLEMEMBER '<Login_Name>','SYSADMIN' 2>go
|To add an existing login to the SYSADMIN server role, execute the following:|
1> SP_ADDSRVROLEMEMBER '<LOGIN_NAME>','SYSADMIN' 2> GO
|Here’s an example:|
1> SP_ADDSRVROLEMEMBER SQL_SALEEM,'SYSADMIN' 2> GO
|The previous operation will take care of granting SYSADMIN privileges to the “SQL_SALEEM” login.|
|5.||Once you’ve successfully performed these steps, the next step is to stop and start SQL Server services using regular startup options. This time you won’t need –f or –m.|
|6.||Log in to the SQL Server 2012 management studio. You could also log in from the command prompt using the “SQL_SALEEM” account and its respective password. You now have SA access to your SQL Server 2012 instance. You may now reset the SA password and take control of your production SQL Server boxes.
It’s perfectly normal to forget your password from time to time, but that doesn’t mean it’s any less of a hassle. These emergency steps should get you up and running without too much scrambling or downtime.
Saleem Hakani is a principal architect at Microsoft with more than 18 years of experience. He has worked with SQL Server since 1992 and has worked on numerous large-scale Microsoft services as an engineer and architect over the last seven years, including Hotmail, Bing and MSN. Hakani leads the worldwide SQL Server Community for Microsoft employees, and is a technical presenter for various Microsoft events including TechReady, SQLFEST, SQL-SCHOOL and SQLPASS.