Get access to SQL Server when you don’t have the SA password

In this post I’ll cover a quick way to get access to SQL Server when you don’t have a login and don’t know the password for the SA account.

I’ve used this trick many times in my years working with SQL Server, generally when “inheriting” an instance built by another team that no longer knew the SA password or the person who set up the instance left the company a while ago and didn’t pass down any info about credentials with sysadmin permissions on the instance.

This also works if the instance was initially configured to work with Windows authentication only.

Prerequisites

For this to work, your Windows/AD account needs to be a member of the local Administrators group on the instance’s host.

The instance will be unavailable for general use during the time required to create your administrative login on it, so, if it’s a production instance, you’ll need to schedule some downtime with the business/application owners.

Ideally sqlcmd (which is usually installed together with SQL Server) or any other command line tool that allows you to connect to the instance should also be present on the host.
SSMS is fine too, but you’ll need to get the timing right to make sure that you’re able to connect to the instance and that slot isn’t taken by someone else since SQL Server needs to be in single-user mode for this to work.

How to get access to SQL Server

Note, all the steps from this point onward are carried out on the VM hosting the instance that I’m trying to get access to.

First, I need to get the service name of the instance, this can be done from both Windows Services Manager (services.msc) as well as from SQL Server Configuration Manager.
The service name is usually SQL Server (MSSQLSERVER) for a default instance or SQL Server ([Instance Name]) for a named instance.

In my case, the service name is SQL Server (SQL2022).

Next, I open PowerShell as Administrator and stop the instance, this also works from Command Prompt.

Then, I start SQL Server in single user mode by providing the /m flag and also specify that only connections from sqlcmd are allowed, hence the /m"SQLCMD".
In the same line I also use sqlcmd to connect to the instance using Windows authentication (yes, even if my account doesn’t yet have a matching login on the instance).

To have the commands execute in close succession I separate them using a semicolon instead of having them executed as two separate commands (each on their own line).

To run the same command in Command Prompt, you have to replace the semicolon with &&, like so:

Now, since I am connected to the instance, I can check again if my login really isn’t there.

Indeed, it is not.

After that, I can proceed to create my login (winsrv2k22\vlad) and add it to the sysadmin fixed server role.

Since my login is now created and I also have sysadmin membership, i can close sqlcmd proceed to restart the service back in multi-user mode.

From this point onward I can connect normally to the instance using Windows authentication.

Get access to SQL Server using dbatools

If there are no restrictions on installing PowerShell modules in your environment and/or if PowerShell Remoting is enabled, another way to do this is by using dbatools‘s Reset-DbaAdmin.

dbatools does all the steps described previously in one simple command – Reset-DbaAdmin.

Conclusion

It’s straight-forward to gain access to a SQL Server instance with minimal downtime as long as you have admin permissions on its host and you schedule the downtime appropriately.

Similarly, these steps can be used for creating a SQL Login or updating the password of the sa account, the difference is the T-SQL that you have to run to achieve that.

If you’re interested in more security-related posts, check out how to leverage the trustworthy database option to gain privilege escalation.

Or maybe you want to migrate the sa password from one instance to another without knowing the password.