Scenario
For some reason you need to gain sysadmin access to a SQL Server instance. Maybe you have inherited it from a DBA that was eaten by a sleepy tiger, or more likely, the SQL Server instance was installed by a consultant and the sysadmin password was forgotten a long time ago. Oh, and you want to do it while the SQL Server is running. No matter why, here is one possible solution, provided that you have local admin access. There are probably ways to escalate access without local admin as well. And of course there are tons of ways to escalate a normal user to an admin user, but that is not the focus of this post.
This procedure has been tested on SQL Server 2014 and 2016. On previous versions, simply running SQL Server Management Studio as admin locally on the server was enough, but on never versions that path has been blocked by default.
To make the process easier, we will utilize the PowerUpSQL PowerShell module from NetSPI.
Procedure
Note: One of the SQL Server instances used in the example is the default instance of the server IM01. As such, it has no instance specification on the form Server\instance. If your server has on or more named instances, you will have to specify the instance name.
You need to obtain local or remote console access to the server. The user has to be a local admin on the server, and should be a user on the SQL Server. Application service accounts are nice starting point if no one has any access to the SQL Server at all. That is, you may not have access to the server, but you have an application using it, and that application accesses the SQL Server using a service account in Active Directory. You can give that account temporary local admin access on the SQL Server. Then you can escalate it to sysadmin access, and then use that login to grant yourself access. You should of course remove sysadmin access from the service account when you are done.
As a sidenote, you will often find that the service account already has sysadmin access, or that the application has a hard-coded SQL Server login account (not in AD but a SQL Server specific account) that is called sa (sysadmin) or is a member of the sysadmin group.
- Download the module from https://github.com/NetSPI/PowerUpSQL. There are ways to launch the module remotely, but in this example we are copying them to the server.
- Copy the files to a local folder, we use C:\Temp in our example.
- Get a copy of psexec.exe from sysinternals.
- Open an administrative PowerShell session running as the local system.
- PsExec.exe -i -s powershell.exe
- Run the following commands in the black PowerShell console.
- Verify that you are running as the nt authority\system account by running whoami.
- Import-Module c:\Temp\PowerUpSQL.psd1
- Enumerate local SQL Server instances
- Get-SQLInstanceLocal
- Check the current access level
- Get-SQLServerInfo -Verbose -Instance Server\instance
- Look for IsSysadmin in the output
- Escalate access
- Invoke-SQLEscalatePriv –Verbose –Instance “SQLServer1\Instance1”
- Invoke-SQLImpersonateService -Verbose -Instance SQLServer1\Instance1
Note: Sadly, some of the screenshots were lost. I will add some new ones later if I remember.
- If you are successful, you may execute TSQL code to grant yourself access.
- Get-SQLServerInfo -Verbose -Instance Server\instance
- Look for IsSysadmin in the output
- Import SQLPS
- Import-Module -Name Sqlps
- Test that you are able to execute arbitrary TSQL
- Invoke-Sqlcmd -Query “Select @@version” -serverinstance SQLServer1\Instance1
- Run the TSQL necessary to grant yourself access. Sample:
- Invoke-Sqlcmd -Query “ALTER SERVER ROLE [sysadmin] ADD MEMBER [Domain\user]” -serverinstance
- De-escalate access
- Invoke-SQLImpersonateService -Verbose -Rev2Self
- Connect to the server using your chosen account.
If that did not work
There is another method, based on shutting the SQL Server down and starting it in single user mode.
- Log in to the SQL Server as a local admin.
- Shut down SQL Server and the SQL Server Agent.
- Start an administrative command prompt.
- Navigate to the SQL Server instance directory, usually something like this: “C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn”
- If you are using a default instance, execute sqlservr.exe –m –c
- For a named instance, use sqlservr.exe –m –c –s Instancename
- If someone else keep nabbing your connection, use -m”SQLCMD” to only allow sqlcmd connections.
- Open a new command prompt and navigate to the instance directory.
- Execute sqlcmd.exe
- You should get a 1> prompt.
- Input “@@SERVERVERSION” on line 1 without the quotes and press enter.
- Input “GO” at line 2 and again press enter. The server should respond with the current version.
- Add yourself to the sysadmin role by entering the following lines:
USE [master]
GO
CREATE LOGIN [DOMAIN\user] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [DOMAIN\user]
GO
- There will be no response, but you can verify by executing the following lines.
EXEC sp_helpsrvrolemember 'sysadmin'
GO
- It will return a list of sysadmin group members.
- When you are done, return to the first cmd window and press [Ctrl]+C to shut down the SQL Server.
- Restart the SQL Server and SQL Server Agent services in the normal mode.
