We had an email thread at work where someone was asking for assistance with an unknown sa password on a SQL instance on a new laptop. “No big deal” – I said to myself – “You can reset the sa password by starting SQL in single-user mode” (If you haven’t heard about this, have a look here).
And then I started thinking – but what if this happen to be a production server with tons of users and suddenly we found ourselves locked out of SQL Server? Starting with SQL Server 2008, the BUILTIN\Administrators group is not made a member of the sysadmin server role unless you add it explicitly. You’d have to bring the server down to reset the sa password. Downtime is not good eats.
Yeah, well – don’t get a false sense of security. Members of the local Administrators group can still login to SQL Server. With full sysadmin privileges.
(Taken from http://www.flickr.com/photos/librarianinblack/5898803951/in/photostream)
How? By impersonating the NT AUTHORITY\SYSTEM login that is created by Setup and granted sysadmin privileges by default. You can easily do this with PsExec (using the –s switch) or creating a Windows Scheduled Task running under the SYSTEM account.
Let me show you what I mean:
By default, things look like this on SSMS on a default installation (this particular instance just happens to be named, other than that it’s all default values for setup – MAGMA is the name of my test VM and SQLPS is the name of the domain)
As you see, the Administrators group is not listed as a login. Which means that if you try to login using the Administrator account, you’ll get a nice error window that says:
I happen to have a copy of PsExec on my C:\bin directory, so I will use it to illustrate this example. Let’s kickoff a cmd window (running as Administrator) that will run PsExec and launch another cmd window running as cmd – the parameters I used for PsExec are: Interactive (-i) and Run as System (-s):
I ran “whoami” on the child cmd window to show who’s running it. That was easy, eh?
Now, I just launch sqlcmd and run a couple of statements:
And voilà – I’ve created a new login for the Administrator user and made it a member of the sysadmin server role, all while running under NT AUTHORITY\SYSTEM.
So what is Microsoft’s guidance in regards to this NT AUTHORITY\SYSTEM login? To leave it as is – here is the KB article that states it. It was written for SQL Server 2005 and it hasn’t been updated since. I asked some of my contacts at CSS and they confirmed that the article is still considered valid for 2008 and 2008 R2. And that would be my humble recommendation as well. On a heavily secured installation though, you will definitely want to remove that login and perform careful testing of the side-effects of removing it.
Edit: As Jorge Segarra pointed out below, NT AUTHORITY\SYSTEM is no longer provisioned as member of the sysadmin server role by setup in SQL 11. I confirmed the behavior on a Denali CTP3 instance I setup from scratch.