Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

More on Recovering Access to a SQL Server Instance


By:   |   Last Updated: 2017-01-27   |   Comments (12)   |   Related Tips: More > 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:

Database Engine Configuration Screen

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:

Administrators group only contains GORDIE\floob

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

Object Explorer - Implicit connection allowed 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];

Msg 15151, Level 16, State 1
Cannot alter the server role 'sysadmin', because it does not exist or you do not have permission.

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:

SQL Server VSS Writer Service 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):

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\140\Tools\Binn\

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

C:\Program Files\Microsoft SQL Server\90\Shared

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

Rename Prompt for sqlwriter.exe

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:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLWriter

Initially, the ImagePath is specified as:

"C:\Program Files\Microsoft SQL Server\90\Shared\sqlwriter.exe"

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:

-S .\instancename -E -Q "ALTER ROLE sysadmin ADD MEMBER [GORDIE\Administrator];"

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

Registry Command to send arbitrary code to SQL Server

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


Last Updated: 2017-01-27


next webcast button


next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, 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 serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, February 01, 2017 - 4:45:43 PM - Diane Robey Back To Top

 Thank you Aaron and Argenis! I added a book mark for the next time I need this.

 


Monday, January 30, 2017 - 12:28:19 PM - Aaron Bertrand Back To Top

 

Just as a follow-up from Argenis on twitter, you don't have to rename sqlwriter.exe, you can just change the registry path to point to the latest sqlcmd.exe on your system, simplifying this solution quite a bit.


Sunday, January 29, 2017 - 10:32:13 PM - Tim Plas Back To Top

Although I think I'll request the folks at DBATOOLS.IO to look into incorporating Argenis / Aaron's methodology, perhaps as just one other option, into Reset-SqlAdmin. Because sometimes it would be _SO_ handy to do an access reset without taking the SQL service down. (Security implications bother me a little, but the capability is there.)


Sunday, January 29, 2017 - 9:40:44 AM - Rob Back To Top

 

Of course you can use the Reset-SQLAdmin command in the dbatools module as well to make life easier by autoimating those steps ;-)

https://dbatools.io/functions/other/reset-sqladmin/


Saturday, January 28, 2017 - 2:00:19 PM - TIm Plas Back To Top

 If you're willing to take downtime (SQL restart), & have Windows local-admin access, the Reset-SqlAdmin command from  dbatools.io  provides a very user-friendly way to get admin access to a SQL instance.   --but I really like having the option to accomplish this with no SQL downtime, with your approach.

 


Friday, January 27, 2017 - 12:06:41 PM - Aaron Bertrand Back To Top

 

@Saravanan I disagree. As I stated in the article, this method is only viable for people who already have local administrator access on the physical machine where SQL Server resides. If you don't want non-DBAs to perform this type of operation, don't make them administrators. You can take this method away all you like, but there will be others. Like I repeat over and over again to customers, assume that a local administrator owns everything on the box.


Friday, January 27, 2017 - 12:02:19 PM - SQLPRODDBA Back To Top

 Excellent work Aaron and Argenis!

 


Friday, January 27, 2017 - 10:49:30 AM - Saravanan Thambidurai Back To Top

 

Its hacking.MS should fix this. Its a serious bug.Non DBA's can follow this steps to login to the SQL server.

Need to follow the MS recommened steps "https://msdn.microsoft.com/en-us/library/dd207004.aspx" to recover access as stated by Jean.

My fingers crossed for MS to fix this.


Friday, January 27, 2017 - 9:38:38 AM - Jean-Philippe George Back To Top

Aaron, I read to quicly, and missed the "don't need to restart the service" part. Which is very, very nice. Thanks !


Friday, January 27, 2017 - 9:02:10 AM - Aaron Bertrand Back To Top

Jean-Philippe, yes, that's true, and works fine for local or non-critical instances of SQL Server. However, as I explained in the original tip, we don't always have the luxury of taking the service down.


Friday, January 27, 2017 - 7:44:46 AM - Hiren Patel Back To Top

 Awesome article Aaron, thank you and Argenis.

 


Friday, January 27, 2017 - 5:37:51 AM - Jean-Philippe George Back To Top

Thanks for the tip.

You can also restart Sql Server in single user mode. Then any member of the computer's local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role.

Explained here : https://msdn.microsoft.com/en-us/library/dd207004.aspx


Learn more about SQL Server tools