As consultants we are sometimes asked the following question: “We have an old version of SQL that we can’t turn off and no one has access to it. Is there a way to create an account on a server you don’t have access to?” The answer is YES, and it isn’t as difficult as you would expect. In this post, I will walk you through the steps needed to gain access to an instance of SQL Server when no one else can access it.
You will need two things before we get started:
- Local administrator access to the server
- An outage window because you will have to stop the instance of SQL and run it in a minimal configuration mode for a couple of minutes.
Once you have local admin and you are within your outage window, you will need to stop the current instance of SQL server. There are multiple ways you can perform the following steps. For this post, I am choosing to use PowerShell but I will address using SQL Server configuration manager. If you are following along and going the PowerShell route you will need to open a Administrator PowerShell window. In this post I am working with a named instance of SQL and will use the following command to turn it off:
Get-service -include ‘mssql$tester’ | stop-service
Once SQL has stopped, you will need to start it again but with a specific startup parameter “-m“:
-m Indicates to start an instance of SQL Server in single-user mode. Only a single user can connect when SQL Server is started in single-user mode.
You can learn more about the different parameters available to start SQL Server here: https://learn.microsoft.com/en-us/sql/tools/sqlservr-application?view=sql-server-ver16
I am choosing to start SQL Server from the command line and pass the “-m” parameter at startup. You can also add this startup parameter using SQL Server Configuration Manager but I prefer to use the command line so I don’t forget to remove it later when restarting SQL. If you want to add “-m” using configuration manager you need to right-click on the instance of SQL Server and select properties. When the properties window opens go to the “Startup Parameters” tab, and you can add it there.
One thing to note is that after the -m, you will see I have “SQLCMD” listed since we are starting SQL Server in single-user mode and I’m telling SQL to only allow connections from the SQLCMD command line tool. If I don’t specify this, anyone from anywhere could connect and steal the connection and I’d be stuck waiting for them to finish or forced to restart the service again.
To start SQL from the command line, you will need to navigate to the instance’s Binn directory; in my example below, that is “C:\Program Files\Microsoft SQL Server\MSSQL15.TESTER\MSSQL\Binn”.
To start SQL, I will use the following command:
.\sqlservr.exe -sTESTER -mSQLCMD The -s is the name of the instance I want to start and the -m will start SQL in single-user mode as we read above. You will also notice the command starts with a “.\”, this is telling PowerShell to start in the current directory to execute the sqlservr.exe file.
If everything goes correctly you will start to see SQL Server error log output in the command window. Just like you would if you were viewing the SQL Server error log in Management Studio.
The next thing you need to do is open a second PowerShell or command line window that has administrator permissions. From that second window, connect to the instance of SQL server using the sqlcmd utility. You can find out more about sqlcmd from the following Microsoft Learn article: sqlcmd utility – SQL Server | Microsoft Learn
To connect to the instance of SQL, you’ll use something similar to the following command:
sqlcmd -S server_name\instance_name -E Once connected, you can use this window to create a new SQL auth login that you can later use with a GUI-based tool like Management Studio to modify the server as needed. You could also add a Windows login or Windows group to the server at this point if you don’t want to create a new login on your server.
After you’ve created your new account, you will need to stop the instance of SQL Server that is running from the first command window by pressing the keys CTRL+C and then confirming you want to shutdown SQL Server.
After SQL has stopped you will need to start it back up but this time without the added -m parameter. If you did all this work from the command line, you can start SQL server again using a similar command to how we stopped SQL in the beginning:
Get-service -include ‘mssql$tester’ | start-service If you used the SQL Server Configuration Manager for this task, you will need to remove the -m startup flag and then restart the instance of SQL server through that tool. After SQL has been restarted you can connect with whichever tool you like to complete the necessary work.
In conclusion, gaining access to an old SQL Server instance when no one has the credentials may initially sound daunting, but with the right steps, it’s achievable. The process primarily involves leveraging local administrator privileges and operating in an outage window to run the SQL server in minimal configuration mode. By understanding and utilizing specific commands, both in PowerShell and the SQLCMD utility, you can create a new account or add an existing one for later access using tools like SQL Server Management Studio. If you are surprised by how easy it is to hijack a SQL server with a few scripts, remembering that this is only possible with local administrator permissions should be a cautionary tale for who gets to be a local admin on your SQL server hosts!
Featured image by Chauncey Sims on Unsplash
There is a simpler way to gain access to a SQL engine using PowerShell. Try DBATools.io module with the Reset-DbaAdmin commandlet (http://docs.dbatools.io/Reset-DbaAdmin). This is a simple one-line command that does everything for you to include stopping and starting the SQL engine both times and providing your account or a provided account access, plus adding to the SysAdmin role. You still need to be a local admin on the server.
I have used this commandlet couple of times in the last two years! DbaTools is a great open source community developed PowerShell module that has tons of great uses all around SQL server.
Thank you for the comment. I also love DBATools and use it almost daily, but I was not aware of this particular commandlet. Very cool what they are doing and thank you for sharing.