By: Joe Gavin | Updated: 2018-08-21 | Comments (7) | Related: > 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.
Next, we import the .xml file into it to create a Job.
Action > Import Task…
Select C:\temp\AddSysadmin.xml > Open
OK
Right click on job and choose Run
Give it a couple of seconds and if it still says “Running” hit F5 to refresh and it should go back to “Ready”.
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.
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.
In Server Roles, you should see you’re in the sysadmin group.
If it’s successful we can clean up by going back to the Task Manager, right click on the job and choose Delete.
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.”
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.
Next Steps
- Here are some links related to Windows Task Scheduler and sqlcmd.exe that may be helpful:
- Check out these additional tips:
- How to Unlock a SQL Login Without Resetting the Password
- How to connect to SQL Server if you are completely locked out
- Get Back into SQL Server After You've Locked Yourself Out
- How to Unlock a SQL Login Without Resetting the Password
- Recover access to a SQL Server instance
- Locked out of Sysdmin? Regain Sysadmin on a SQL Server 2012 Cluster
- SQL Server Security
About the author
Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events. This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-08-21
