Today I had a very cool idea, which I like to share. A customer of mine had trouble with his SQL Server 2008 R2. Because of that, he needed to open SQL Server Management Studio for troubleshooting and then he realized that he was not able to login into SQL server neither with his domain accounts nor with service accounts. The company which installed the SQL Server has simply forgotten to set permission for the SQL Server administrator group correctly and additionally they didn’t write down any of the passwords for service nor SA accounts. There was no way of accessing the SQL Server application at all.
Well, not a very comfortable situation at all, unless you are THE SCOM GUY. What? Yes, THE SCOM GUY. Why? Ok, let me explain. As SCOM guy you probably have already installed the SQL Server management pack and if you remember how the things needed to be setup, then you might know, that up to SQL Server 2008 R2, Local System has full SQL Server access per default. This was needed by the SCOM agent, which runs unsually under Local System to be able, to fully monitor the SQL Server. This information you must know when you need to decided how to setup your RunAs accounts. Check the post from Kevin Holman about the SQL Server management pack about this topic. Because Local System includes the NT AUTHORITY\SYSTEM token, which has sysadmin permission per default, allows you to have unrestricted access to the SQL Server right out of the box.
How does this help? Ok, let’s find a way to get Local System respectively NT AUTHORITY\SYSTEM access. I remembered a tool called psexec from Sysinternals and after some binging I found the correct syntax.
Update: I was just wondering after publishing this quick post during my train ride back from work, if someone else already figured out this kind of “security hole”. Surprisingly I found many blogs about this kind of solution I was not aware of. This of course takes away some coolness of this post 🙁 .
Start an elevated CMD and type the following command. This will start an interactive
(-i) command prompt under the system account (-s)…
PsExec.exe –i –s cmd.exe
If you check using whoami you see that the new command prompt is started as NT Authority\System account…
Next, change to the directory where the SQL Server Management Studio (SSMS.exe) is installed. Run SSMS.exe…
The SQL Server Management Studio starts using the NT Authority\System Account. Press Connect…
Now, tataaaa, we are having full access to the SQL Server without any regular user /administrator account…
Now check the sysadmin group and its members. As you can see, the NT Authority\System has sysadmin permissions per default…
At this very moment you are able to add any account to the sysadmin role and fix any misconfiguration.
I hope this helps you recover your databases and accessing your data!
Hi Stefan, just want to let you know, by default, LocalSystem has sysadmin access when installing SQL 2008, but in SQL 2012, LocalSystem does not have such rights anymore by default. so this would not work in SQL 2012.
Hi
Yes, you are absolutely right.
Thank you for your comment,
Stefan