More on Recovering Access to a SQL Server Instance

By: Aaron Bertrand   |   Comments (17)   |   Related: > Security

Problem

A few years ago, I wrote a tip here showing how to recover access to an instance of SQL Server: Recover access to a SQL Server instance

The problem isn't that I wrote the article. The underlying problem is that a user can't manage a local instance of SQL Server, even if they are a local administrator. This could be for a variety of reasons, including any combination of:

• Did not add themselves as an administrator during setup
• Added themselves as an administrator, but then removed themselves (perhaps inadvertently) from the instance or from the sysadmin role
• A different Windows user, now unavailable, was the one that was added to sysadmin role during setup

If the local Windows account can't administer the SQL Server instance, you could always use sa, right? Well, not necessarily. You may have:

• Created an sa password, but forgot it, or the sa account is disabled
• Set up an sa password, but now SQL Server is configured to run with Windows authentication only
• Set it up as Windows authentication only from the start

The solution I originally published used a sysinternals tool from Microsoft called PsExec.  PsExec works by impersonating the built-in NT AUTHORITY\SYSTEM login which, in certain versions of SQL Server, has automatic sysadmin rights on the instance. In more modern versions (2012 and above), however, NT AUTHORITY\SYSTEM is no longer in the sysadmin role. This is where the problem comes in. So while PsExec will work against, say, SQL Server 2008 (or an instance upgraded from 2008), how do we solve this problem on new instances of 2012, 2014, 2016, and beyond?

Solution

Argenis Fernandez (@DBArgenis) discovered a method that takes advantage of the fact that the Windows accounts for specific services are now in the sysadmin group, even if NT AUTHORITY\SYSTEM is no longer there. And while PsExec can't be used to impersonate these other SIDs, there is another clever way to use them to pass commands into SQL Server.

Reproducing the Actual Problem

You can skip past this section if you don't want the background.

To reproduce the problem you are probably already experiencing, I installed an instance of SQL Server 2016 Express Edition, set it to use Windows authentication only, and intentionally left myself out of the sysadmin role. I initially thought you could do this inadvertently using a Next > Next > Next install, but the UI doesn't let you continue without adding at least one Windows account to the sysadmin role:

It might be possible to bypass this validation with an unattended installation, and of course that Windows account could later be evicted from the sysadmin role, the instance, or even the domain. What I did here was simply add a different account than the one I was currently using - I was installing using Gordie\Administrator, but I created a throw-away account called GORDIE\floob, and added that account to the sysadmin role instead. You can see this from my installation summary screen:

Even though I didn't add myself to the instance, I was able to connect implicitly (through Builtin\Users):

(The only logins I can see, though, are the disabled SA account, and Builtin\Users.)

And I was even able to add myself as an explicit login:

CREATE LOGIN [GORDIE\Administrator] FROM WINDOWS;

But when I tried to add myself to sysadmin, I couldn't:

ALTER SERVER ROLE sysadmin ADD MEMBER [GORDIE\Administrator];

And in fact you will quickly find that even a local or domain administrator can't get a lot done here, if they didn't add themselves to the sysadmin role during setup.

Now, The Fix

Again, with full credit to Argenis, here is how I can recover access to my instance. His solution relies on the fact that newer versions of SQL Server use service SIDs that are somewhat "vulnerable."

First, let's look at the accounts that are vulnerable (to verify this yourself, you'll have to run this on an instance where you do have elevated privileges):

SELECT p.name
  FROM sys.server_principals AS p
  INNER JOIN sys.server_role_members AS r
  ON p.principal_id = r.member_principal_id
  WHERE r.role_principal_id = 3
  AND UPPER(p.name) LIKE N'NT SERVICE\%';

On a default installation of SQL Server 2016, you should see the following four results (though the last one, belonging to SQL Server Agent, won't be present on SQL Server Express):

NT SERVICE\SQLWriter
NT SERVICE\Winmgmt
NT Service\MSSQL$<instancename>
NT SERVICE\SQLAgent$<instancename>

As a regular user, you won't see those logins in the results, but you'll have to trust me that they're there (unless they have been sabotaged).

But what can we do with this information? I'm cheating a little bit here, because I already know the answer. I'm not interested in messing with the Winmgmt service, and I obviously don't want to touch SQL Server or SQL Server Agent. This leaves SQLWriter as something to explore, so let's take a look at the properties of the service. Launch services.msc, scroll to the SQL Server VSS Writer service, and right-click for the properties:

This solution will involve stopping the service, so you may as well do that now. The important thing to note here is the folder location of the sqlwriter.exe application.

Now, about the sqlwriter.exe file: this is associated with the SQL Server VSS Writer service, which uses NT SERVICE\SQLWriter, which in turn has inherent sysadmin privileges on the local instances of SQL Server (as shown in the above query against sys.server_role_members).

Well, what if we shut down the service, and replace that sqlwriter.exe file with a file we can use to pass commands into SQL Server? Say, sqlcmd.exe? Go ahead, and stop the service. (Keep in mind that if you are using any 3rd party backup solutions that rely on the service, you should suspend their activities as well.) Now, copy sqlcmd.exe from the following location (you may have to change 140 to something else, depending on the highest version of SQL Server you have installed):

Paste that file into the folder alongside sqlwriter.exe, which should be:

Now, rename the sqlwriter.exe file to sqlwriter.exe.old, then rename sqlcmd.exe to sqlwriter.exe. You will get prompts like this one:

The final configuration change involves modifying the registry to slightly change the command that the service uses to call the executable. Open regedit.exe and navigate to the following location:

Initially, the ImagePath is specified as:

Since that exe file is actually now sqlcmd.exe, we can pass in arguments! For example, we can tell it to connect to a specific instance, using Windows authentication, and run some arbitrary command, like adding my Windows account to the sysadmin role:

So, right-click the ImagePath key, choose Modify, append the command, and click OK. Here's how it should look (enlarge):

The final step is to start the SQL Server VSS Writer service, which will fail, and that's okay. Windows can't start sqlcmd.exe as a service, but it is trivial to verify that the command was sent on to SQL Server, and that my Windows account is now part of the sysadmin server role. It will be one of the names now returned by this query (and I will suddenly be able to see other sysadmin members):

SELECT p.name
  FROM sys.server_principals AS p
  INNER JOIN sys.server_role_members AS r
  ON p.principal_id = r.member_principal_id
  WHERE r.role_principal_id = 3;

This looks on the surface to be a security hole but, in order to swap out the files in that folder, you have to be an administrator on the box where the instance resides. And in a lot of ways, if someone is a Windows administrator, you should consider them the owner of the instances and services on that machine anyway (see this other post from Argenis for more on that).

What else can I fix?

Some other commands you may want to run this way, depending on what issue you want to fix:

-- to add any login, or local administrators, to sysadmin
CREATE LOGIN [GORDIE\Administrator] FROM WINDOWS;
ALTER SERVER ROLE sysadmin ADD MEMBER [GORDIE\Administrator];

-- add local administrators as a login
CREATE LOGIN [Builtin\Administrators] FROM WINDOWS;
ALTER SERVER ROLE sysadmin ADD MEMBER [Builtin\Administrators];

-- to grant specific permissions without granting sysadmin
USE master;
GRANT VIEW SERVER STATE TO [GORDIE\Administrator];
GRANT CONTROL SERVER TO [GORDIE\Administrator];

-- to allow a SQL auth login to impersonate a higher-priv login:
GRANT IMPERSONATE ANY LOGIN TO [JUGGERNAUT\Administrator];

-- to allow remote connections or dedicated administrator connections:
EXEC sys.sp_configure @configname = 'remote access', @configvalue = 1;
EXEC sys.sp_configure @configname = 'remote admin connections', @configvalue = 1;
GO
RECONFIGURE WITH OVERRIDE;

-- to increase max server memory or disable resource governor:
EXEC sys.sp_configure @configname = 'show advanced options', @configvalue = 1;
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure @configname = 'max server memory', @configvalue = 2147483647;
EXEC sys.sp_configure @configname = 'show advanced options', @configvalue = 0;
RECONFIGURE WITH OVERRIDE;

-- to disable a logon trigger:
DISABLE TRIGGER TriggerName ON ALL SERVER;
-- if this is the problem *and* it prevents you from disabling, you could 
-- change registry entry to have the service connect as DAC, using -A
-- But that only works if DAC hasn't been disabled

-- to change the default database for any login
-- (a common problem is login issues due to an offline or removed database)
ALTER LOGIN [peon] WITH DEFAULT_DATABASE = master;

-- to set Windows auth only
-- restart of SQL Server service required
EXEC master.sys.xp_instance_regwrite 
  N'HKEY_LOCAL_MACHINE', 
  N'Software\Microsoft\MSSQLServer\MSSQLServer', 
  N'LoginMode', 
  REG_DWORD, 
  1;

-- to set Mixed mode (Windows auth + sql auth)
-- restart of SQL Server service required
EXEC master.sys.xp_instance_regwrite 
  N'HKEY_LOCAL_MACHINE', 
  N'Software\Microsoft\MSSQLServer\MSSQLServer', 
  N'LoginMode', 
  REG_DWORD, 
  2;

-- to enable the SA account
ALTER LOGIN sa ENABLE;  

-- to set a new (strong) password for SA:
ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>';

-- to set a weak password for SA (not recommended, but because you will ask):
ALTER LOGIN sa WITH PASSWORD = '<enterWeakPasswordHere>', CHECK_POLICY = OFF;

You'll just have to change the registry each time so that a new command is sent, and then try to start the service again. You could append a whole slew of commands, but that single-line entry in the registry editor is not exactly easy to work with.

Summary

Argenis has come up with a great workaround to recovering access to an instance, which only requires an outage of the VSS Writer service (other solutions require some level of SQL Server outage). I hope this comes in handy if you should find yourself locked out of an instance of SQL Server 2012, 2014, 2016, or beyond.

IMPORTANT! Please don't forget to rename the replaced sqlwriter.exe back to sqlcmd.exe, and sqlwriter.exe.old back to sqlwriter.exe, and also fix the registry, in case you need to later use the VSS Writer service.

Next Steps

• Bookmark this page so that you can easily refer back here.
• See these tips and other resources:
  • Tip #2682 : Recover access to a SQL Server instance
  • Leveraging Service SIDs to Logon to SQL Server 2012, 2014 and (new!) 2016 Instances with Sysadmin Privileges
  • Tip #2503 : How to Create Secure SQL Server Service Accounts
  • Connect to SQL Server When System Administrators Are Locked Out (TechNet)
  • Think Your Windows Administrators Don't Have Access to SQL Server 2008 by Default? Think Again.
  • All SQL Server Security Tips

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips