In a recent article, I took you on a trip through how to hack (ethically) a SQL Server to regain sysadmin access. In that article, I made quick mention of restarting SQL Server into single-user mode. It only makes sense to show quickly how to get into single-user mode.
Before getting into that, I do hope that there is something you will be able to learn from this basics article. If you are curious, there are more basics articles on my blog – here.
Single-User
So, what exactly is this single-user mode thing? Single-user mode is basically the official back-door into SQL Server for various reasons such as:
- Somebody deleted all of the logins that were in the sysadmin role.
- The sa account is disabled or the password has been forgotten.
- Somebody deleted any Windows groups that were members of the sysadmin role.
- All members of the sysadmin role are no longer with the company.
- You need to restore the master database
- You want to keep SQL Server all to yourself because you are greedy!
These are some pretty solid reasons to need to be able to use the back door. But how exactly do we get to the back door?
Two Paths
As luck would have it, there are two ways to enable single-user mode. You can either get there by making some changes for the SQL Server service in Configuration Manager, or you can utilize a command prompt. I won’t cover the gui path beyond the gentle reminder that you must remember to undo your change when using that method.
My preferred method is through the command line. Using my SQL Server 2017 as the experiment, I would navigate to the Binn directory for that instance. In this case, as shown in the next image.
Before getting too far ahead of myself, I am going to stop my SQL Server.
Notice, I also queried to find all of my services related to SQL before stopping the MSSQLServer service via the net stop mssqlserver command. We will come back to some net start and net stop commands later.
With the service successfully stopped, I can now restart the service in single-user mode.
And then the validation that we are indeed starting in single-user mode…
But wait, did you notice that bit of trickery on the startup command?
sqlservr.exe -m"SQLCMD"
This is a pro-tip for when you must use single-user mode. Inevitably, somebody will steal the single-user connection and you will be locked out of the session. By using an app name after the single-user switch, you are telling SQL Server to only accept connections for that specific application. Since most apps will not be using sqlcmd, you will have far less contention to gain that connection and you will be able to complete your task much easier.
You could also pass something like this instead…
sqlservr.exe -m"Microsoft SQL Server Management Studio - Query"
In this case, I would be limiting the connections to a query from SSMS (and not object explorer).
Now that I have a single-user connection, I can add a sysadmin or restore the master database or just sit on it and play devious. It all depends on what your objective for the single-user session happens to be.
More Command Line
Remember that reference to the NET commands? Well, it turns out we can also start SQL Server in single-user via net start. Let’s check it out.
The command is pretty simple:
net start mssqlserver -m"SQLCMD"
The effect here is the same as navigating to the Binn directory and starting SQL Server with the sqlservr.exe executable. The big difference is considerably less typing and less verbose output of the service startup.
When using the net start method, you do need to know the service name of the SQL Server instance. To get that, I do recommend the following powershell script.
get-service -name *sql* |format-table -auto
This will produce results similar to the following.
From the results, I can pick the SQL Server service and then pass that to the net start command fairly easily.
The Wrap
Starting SQL Server in single-user mode should be a tool every data professional holds in the bag. This is an essential tool that can be used in multiple scenarios and ensure you are able to fully maintain and control your server. I have shown how to get to single-user mode via two command line methods and mentioned a GUI method. The nice thing about the command line methods is that you don’t have to remember to undo the startup switch like you do with the GUI method.
If you feel the need to read more about single-user mode, here is an article and another on the topic.
This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.
