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

 
SQL Server Transparent Data Encryption Alternative - Free Webinar
 

Getting Sysadmin Access to SQL Server When Locked Out


By:   |   Last Updated: 2018-08-21   |   Comments (6)   |   Related Tips: More > Security

Problem

We all have probably seen this. As a SQL Server DBA, you’re expected to support anything resembling a SQL Server in your environment regardless of who installed it, or whether you have or can get sysadmin access to it.  

It happens with vendor installed instances of SQL Express, many times installed without your knowledge and stuffed on the C:\ drive of a workstation and left unpatched. I see this happen typically with keycard door systems, shipper application systems and manufacturing applications.

Is there anything you can do if SQL Authentication is enabled, but no one knows the sa password or if no one else has sysadmin access? Fortunately, the answer is, yes, there are ways.

Your first thought is probably to restart the SQL Server in single user mode, add a login and put it in the sysadmin group and you’re done. Great idea, but what if you can’t or just don’t want to disrupt a system? It may be that the system is accessed 24x7 in a manufacturing environment. Mission critical applications that run on workstation class machines using SQL Server Express Edition do exist. And sometimes you just don’t want to disrupt a system that you will now own just because you touched it once and now everything that breaks in the future gets blamed on you. This happens.

Solution

Good news, there is another way. This link will take you to a great blog post by Jason Brimhall that details how to set up a Windows Task Scheduler Job that runs as ‘NT AUTHORITY\SYSTEM’ and executes a short SQL script to add a login without disrupting anything at all. All you need is Windows admin credentials on the machine hosting the SQL Server you’re trying to access and to follow the steps.

I’ve run this successfully on a Windows 10 machine running SQL Server 2016 Developer Edition and on Windows 7 machines running SQL Server 2012 Express Edition. You could stop right here, ignore the rest of this tip and have a fantastic new tool in your toolbox, but I am hoping you will continue reading. I had a few systems I wanted to do this on and simply applying the same steps would have been more than sufficient. But sometimes I’m obsessed with making things even more easily repeatable than they are already, so I made some minor tweaks to the Task Scheduler job and saved it to an .xml file we can configure and import it in the Task Scheduler and simply run the job.

Good, you decided to read on. First, copy and paste the following XML into your .xml editor of choice, edit SERVERNAME and DOMAIN\user in both places to reflect the SQL Server you’re adding the login to and name of the AD domain account you’re adding in the highlighted places on the sqlcmd.exe command line. Next, save it as an .xml file (I chose C:\temp\AddSysadmin.xml for this example) on the Windows server you’re working with. 

<?xml version="1.0" encoding="UTF-16"?>
<Task version="1.2" xmlns="http://schemas.microsoft.com/windows/2004/02/mit/task">
<RegistrationInfo> 
<Date>2018-06-04T14:25:31.5859452</Date>
<Author>DBA</Author> 
<URI>\AddSysadmin</URI>
</RegistrationInfo> 
<Triggers /> 
<Principals> 
<Principal id="Author">
<UserId>S-1-5-18</UserId>
<RunLevel>LeastPrivilege</RunLevel>
</Principal> 
</Principals> 
<Settings> 
<MultipleInstancesPolicy>IgnoreNew</MultipleInstancesPolicy>
<DisallowStartIfOnBatteries>true</DisallowStartIfOnBatteries>
<StopIfGoingOnBatteries>true</StopIfGoingOnBatteries>
<AllowHardTerminate>true</AllowHardTerminate>
<StartWhenAvailable>false</StartWhenAvailable>
<RunOnlyIfNetworkAvailable>false</RunOnlyIfNetworkAvailable>
<IdleSettings> 
<StopOnIdleEnd>true</StopOnIdleEnd>
<RestartOnIdle>false</RestartOnIdle>
</IdleSettings> 
<AllowStartOnDemand>true</AllowStartOnDemand>
<Enabled>true</Enabled>
<Hidden>false</Hidden> 
<RunOnlyIfIdle>false</RunOnlyIfIdle>
<WakeToRun>false</WakeToRun>
<ExecutionTimeLimit>PT72H</ExecutionTimeLimit>
<Priority>7</Priority> 
</Settings> 
<Actions Context="Author">
<Exec> 
<Command>sqlcmd</Command>
<Arguments>-E -S
SERVERNAME -Q "CREATE LOGIN 
[DOMAIN\user] FROM WINDOWS EXEC sp_addsrvrolemember 'DOMAIN\user','sysadmin'"</Arguments>
</Exec> 
</Actions> 
</Task> 

Now, let’s open our Task Scheduler using the keyboard shortcut [Windows]+[R] > enter taskschd.msc > OK to open the Windows Task Scheduler.

launch task scheduler

Next, we import the .xml file into it to create a Job.

Action > Import Task…

task scheduler

Select C:\temp\AddSysadmin.xml > Open

task scheduler

OK

Right click on job and choose Run

task scheduler

Give it a couple of seconds and if it still says “Running” hit F5 to refresh and it should go back to “Ready”.

task scheduler

Now let’s confirm the login was added and it’s in the sysadmin server group by connecting to the SQL Server with Management Studio with your newly added login.

login screen

If you’re able to connect the login has been added. 

Expand Logins and find the account you added

Expand Logins, right click and select Properties.

login properties

In Server Roles, you should see you’re in the sysadmin group.

login properties

If it’s successful we can clean up by going back to the Task Manager, right click on the job and choose Delete.

task scheduler

Delete C:\temp\AddSysadmin.xml to complete the cleanup.

Troubleshooting

Here are a couple of things I ran into with my testing.

The Last Run Result for the Task Scheduler Job should be “The operation completed successfully. (0x0)”. If you get anything other than this message the likely culprit is a mistake with your SERVERNAME variable. Double check and correct if necessary, including making sure the named instance is correct if you’re using a named instance. Probably a good idea to double check DOMAIN\user in both places as well.

Another issue I encountered that was a bit trickier was due to a change in the Local Security Policy that was applied during testing, but unknown to me at the time. After executing the steps above and verifying the Task Scheduler Job result was successful the login was still not created. Adding an output file switch to sqlcmd.exe with a -o captured the error:

“Msg 15247, Level 16, State 1 User does not have permission to perform this action”. 

The next step was to attempt running it as a user that was known to be in the sysadmin group as a test. That attempt produced a rather cryptic message:

“Task Scheduler cannot apply your changes. The user account is unknown, the password is incorrect, or the user account does not have permission to modify the task.” 

task scheduler message

I found this that made sense of it all: https://blogs.technet.microsoft.com/askperf/2012/04/18/task-scheduler-error-a-specified-logon-session-does-not-exist/.

I found out “Network access: Do not allow storage of passwords and credentials for network authentication” was enabled on the machine I was testing with. Disabling it allowed everything to work. Note: this is helpful regardless if you’re manually creating the Task Scheduler Job or importing it from an .xml file.

local security policy
Next Steps


Last Updated: 2018-08-21


next webcast button


next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

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, August 22, 2018 - 1:08:04 PM - Joe Gavin Back To Top

Volker, you would still have to recycle the SQL Server with reset-dbadmin.


Wednesday, August 22, 2018 - 7:06:02 AM - Joe Gavin Back To Top

I'm happy to see this leading to even more solutions. 


Tuesday, August 21, 2018 - 10:00:31 AM - Volker Bachmann Back To Top

Maybe this is another solution?

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


Tuesday, August 21, 2018 - 9:58:19 AM - Bob Feldsien Back To Top

Fascinating article.  Frankly I'm not sure I want my developers knowing how to do this so I kind of hope they don't read it.


Tuesday, August 21, 2018 - 7:53:13 AM - Don Kolenda Back To Top

 This is a great post, Joe.  Thanks for sharing!


Tuesday, August 21, 2018 - 3:34:45 AM - Kartar Rana Back To Top

There is a similar post. This has been tweaked a bit:-

https://blogs.msdn.microsoft.com/sqlserverfaq/2012/12/18/how-to-login-to-sql-server-with-nt-authoritysystem-using-psexec-tool/


Learn more about SQL Server tools