You are a proud and trusted DBA of your organization who is responsible for maintaining and managing SQL Servers in the production environment. To prevent any unauthorized access to your production environment, you have decided to perform the following steps that are kind of best practices to secure your company’s SQL Servers from any unauthorized access:
- You have removed any and all built-in administrators account from SQL Server logins
- You have removed all users (Except SA) that were part of SysAdmin server role (including any Windows accounts and SQL Server logins)
- You have set an extremely complex SA password which may be hard for others to guess or remember
For day-to-day operations on SQL Server, you use your domain user account which has DBO permissions on user databases but doesn’t have SYSADMIN privileges on the system. You have not documented the SA password anywhere to prevent others from knowing the SA password. (Also, its not a good practice to document the password)
Since you have set the SA password to be complex and you have 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 Servers SA password.
You are the only person who knew the SA password in your team and now you don’t remember what it was and you need to make some server level configuration changes to your production SQL Servers.
What will you do now?
- You will try logging in as SA with all possible passwords you have in your mind.
- You will look for the SA password on your computer hard-drive or in your emails (If you had stored it in some file which is a bad practice)
- Try to restore MASTER database from database backup. However, this will not help because you will run in to the same issue as you don’t remember the SA password.
- Rebuild Master database. This may not help as you will lose all system/server level configurations and settings including logins, permissions and any server level objects.
- Re-install SQL Server and attach all user databases. This may not work as you may experience same issues that you would experience with #4.
- All your attempts to login in to the system using SA password have failed and now it is time for you to call Microsoft CSS (Microsoft Customer Support Services)
Hacking SQL Server using a Backdoor
Many users in the community and in different events have asked me about this and Id like to share with you a backdoor to SQL Server which may help you gain SYSADMIN access to your production SQL Servers. However, that would mean your Windows account will need to be a member of the local administrators group on Windows Servers where SQL Server Services are running.
Important: SQL Server allows any member of Local Administrators group to connect to SQL Server with SYSADMIN privileges.
How to take control of SQL Server when SA is lost:
Step 1: Start the SQL Server instance using single user mode from command prompt by launching the command prompt as an administrator. (Note: You can also start SQL Server 2012 using minimal configuration which will also put SQL Server in single user mode)Step 2
Step 2: From the command prompt (Run as Administrator) type: SQLServr.exe “m (or SQLServr.exe “f) and let the SQL Server database engine start. Make sure you do not close this command prompt window. You can locate SQLServr.exe in the Binn folder of your environmental path. If you don’t have SQL Server Binn folder in your environmental path, you can always navigate to the Binn folder of your SQL Server machine.
Usually the Binn folder is located @ C:\Program Files\Microsoft SQL Server\MSSQL<Ver>.MSSQLSERVER\MSSQL\Binn>
Step 3: Once SQL Server service has been started in single user mode or with minimal configuration, you can now open up another command line window as an administrator and use SQLCMD command from command prompt to connect to SQL Server instance:
-- Login from Command Prompt using SQLCMD to your SQLCMD -S SALEEMHAKANI
You will now be logged in to SQL Server. Keep in mind that you are now logged in as an Admin on SALEEMHAKANI SQL Server instance.
Step 4: Once you are logged into the SQL Server using SQLCMD from the command prompt, you have the option of creating a new account and granting server level permission. Let’s now create a new SQL login called SALEEM_SQL and then add this login to SysAdmin server role.
-- Create new SQL Login CREATE LOGIN SALEEM_SQL WITH PASSWORD='$@L649$@m' GO
Step 5: Once the new login SALEEM_SQL has been created, lets now add this login to System Admin server role on SQL Server.
-- Add Login to SYSADMIN server role in SQL Server SP_ADDSRVROLEMEMBER 'SALEEM_SQL','SYSADMIN' GO
Step 6: Once the above steps are successfully performed, the next step is to stop and start SQL Server services using regular startup options. (This time you will not need “f or “m)
Step 7: Log in to the SQL Server management studio or from the command prompt using SALEEM_SQL account and its respective password.
Step 8: You now have system admin access to your SQL Server instance. You should immediately reset the SA password and take control of your production SQL Servers.