Security – a topic that is even more dear to us in the last few months after witnessing so many major flaws with huge, huge organizations. There are quite some things that worried and continue to worry me when we talk about securing data in SQL Server, but one of them really stands out in my mind. Let me tell you which one.
Have you ever worked in a company where a piece of software(in my case a backup solution) requires both local admin and sysadmin permissions inside SQL Server in order to work? Hope you haven’t. And I hope that even if you do, you will be able to fight and change “the situation”.
The software that we were “forced” to use because of “how that company works” was using the NT Authority\SYSTEM account as its login inside SQL Server. Actually it’s Windows service was running under the LocalSystem account and that was not something we were able to change(again – don’t ask why). So up until SQL Server 2012 NT Authority\SYSTEM is by default part of the sysadmin role and if you don’t know that – go check it out for yourself and decide whether or not it should be the case in your environment. As of 2012, Microsoft decided to revoke the sysadmin role from this login and of course that lead to some problems for us, but in general it was a great decision and here is just one more reason why. Have you ever heard about psexec? That’s a simple .exe file(that’s also free) that you can use to actually impersonate NT Authority\SYSTEM locally on the OS level. Now that means that if I am able to run the SQL Server Management Studio via psexec, I will be sysadmin inside of that instance! That technique is used very often whenever you want to recover your access to SQL Server or you just don’t want to wait for anyone to give you those permissions(OK, I said it. I admit I have done that). A complete article on the topic can be found here.
So do you think that leaving the sysadmin role to NT Authority\SYSTEM is right? Do you think that the fact that there is even a small chance of someone seeing that article and trying that on your production SQL Server box is secure? And what about the fact that such an “operation” does not actually require any reboots of the instance/machine? Yeap. Leaving you here hopefully thinking…
Thanks for hosting T-SQL Tuesday #63, Kenneth! Looking forward to the summary post already!
What do you think?