THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Argenis Fernandez

Think Your Windows Administrators Don’t Have Access to SQL Server 2008 by Default? Think Again.

 

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.

interesting_lock

(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)

image

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:

image

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):

image

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:

image

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.

Cheers!

 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.

Published Sunday, July 10, 2011 7:56 PM by Argenis
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Robert L Davis said:

Interesting. I never considered trying something like this.

July 10, 2011 10:15 PM
 

Paul Timmerman said:

Well that's not scary at all, LOL!

July 10, 2011 10:20 PM
 

Mark Shay said:

You can also schedule an AT Job to open a command prompt.. Basically, the same thing to get NT Authority/SYSTEM session going.

July 10, 2011 10:31 PM
 

Robert Miller said:

Some mad hacker skills there.  

Seriously, like Robert Davis, I never thought of this approach.

July 10, 2011 11:01 PM
 

Meher said:

Nice demo Argenis.

Meher

July 11, 2011 9:07 AM
 

Brent Ozar said:

Wow.  Nice find, man.

July 11, 2011 9:10 AM
 

K. Brian Kelley said:

Another thing is if you look at the SQL Server VSS Writer service, the service account is System. In SQL Server 2000 System was required because of Full Text. In 2005/2008 it's required not only for updates, but also for this service.

The one disadvantage of using AT is you can't get an interactive session in newer versions of the OS. This is to prevent the case where you schedule a job using AT to start explorer and then intentionally kill explorer. And you can prevent the AT issue by setting the service account that is used for these jobs. This should be a best practice because it's also a security exploit on Windows systems where you've been stripped of admin rights.

July 11, 2011 9:31 AM
 

Jim Murphy said:

Nice job!  Nice combined use of tools and 'features'.

July 11, 2011 9:54 AM
 

Chris Wood said:

Argenis,

The KB article says that this account is used for Microsoft Update. I have had problems with others using Microsoft Update on SQL Server so that would be a good reason not to give it sysadmin authority. Do we know what authority the SQL VSS Writer service needs because I would want to remove sysadmin role from it?

Thanks

Chris

July 11, 2011 11:50 AM
 

Amit Banerjee said:

If you are using VSS backups for your SQL Server instance, then remove the Local System account from the sysadmin list will cause these backups to fail.

From http://support.microsoft.com/kb/919023

Additionally, because of the types of operations that the writer must perform, we recommend that you do not remove the NT AUTHORITY\SYSTEM login from the sysadmin server role.

July 11, 2011 12:10 PM
 

Chris Wood said:

Amit,

If you are only using the Maintenance Plan Backup DB thru an SQL agent job then you do not need this service right? We also use Red Gate SQLBackup so I know that this uses the Writer service but what elso would use it?

Chris

July 11, 2011 1:51 PM
 

Argenis said:

Chris,

For regular native backups or Red Gate backups you do not need the VSS Writer service. You would need it if you used DPM, for example.

-Argenis

July 11, 2011 2:10 PM
 

Chris Wood said:

Argenis,

If that's the case then we can certaily try to remove syadmin authority.

Thanks

Chris

July 11, 2011 3:30 PM
 

Leo Miller said:

Thinking it through you will see he doesn't even need to add himself as a Sysadmin. Under the NT Authotity account he is already a sysadmin, and effectivly transparent to most monitoring.

Our system monitors Sysadmins added or removed, but we wouldn't see the first logon.

July 11, 2011 5:36 PM
 

Argenis said:

Leo,

If you're only monitoring 'sa' logins, then yes, you will miss the login by 'NT AUTHORITY\SYSTEM'. In my opinion any system that is properly audited should monitor all logins (failed and successful) - this is especially true of member of the sysadmin server role.

July 11, 2011 6:15 PM
 

GrumpyOldDBA said:

ah well this account NT AUTHORITY\SYSTEM  does not need sysadmin rights to run, even on a cluster. I change this to public only, add as a user to master database and grant datareader. I forget exactly what it needs to call, think it might be checking version info.

Sorry but a DBA should understand security on his or her system fully with regard to what logins are created by default on install - it could be that I spent many years working in a SOX environment - that tends to sharpen your outlook ( paranoia ) on things.

I see to remember ms kbs covering this - something like "impeding admins ona  cluster" or something.

Good post though - I wonder how many DBAs suddenly had a panic attack?

July 13, 2011 9:56 AM
 

Dale Hirt said:

I had heard of the running SQL Server in single-user mode hack, but this one is truly a wonder of simplicity and a gaping hole.

Thanks for the great article on it.

July 18, 2011 4:22 PM
 

Aaron Sentell said:

At least they can't use this to hack in using a GUI like SSMS ... or can they? Not that I feel any better either way.

July 20, 2011 4:43 PM
 

Jorge Segarra said:

Again, awesome find. Good news for security folks though, looks like this loophole might be "closed" in Denali as NT\System is no longer sysadmin by default: http://msdn.microsoft.com/en-us/library/bb500459(SQL.110).aspx

"BUILTIN\administrators and Local System (NT AUTHORITY\SYSTEM) are not automatically provisioned in the sysadmin fixed server role."

July 23, 2011 8:05 PM
 

Jason S said:

This isn't a security hole or loophole by any means. If you are a local admin on a system, all bets are off to begin with. Local admins by definition can do anything they want. If someone isn't trusted, then they shouldn't be a local admin. If an untrusted user is on a system as local admin, you've got big trouble whether they try to log into SQL Server or not.

Also, the task scheduler was completely redesigned in Vista/2008 so the AT trick (mentioned way above) hasn't worked in a while.

October 14, 2011 3:47 PM
 

Argenis Fernandez said:

  If you recall one of my previous blog posts, titled Think Your Windows Administrators Don’t Have

January 12, 2012 6:34 PM
 

Sudhanshu said:

Excelelnt one, this saves my time...

hurrayyyyy

March 21, 2012 3:40 AM
 

Neil Simmons said:

Actually as long as you use the -i switch in psexec then you can just as easily run the SSMS from here.

August 13, 2012 9:14 AM
 

Nik Edmiidz said:

Got the following error:

C:\Windows\system32>sqlcmd -S MAGMA\R2

HResult 0xFFFFFFFF, Level 16, State 1

SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFF

FF].

Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or in

stance-specific error has occurred while establishing a connection to SQL Server

. Server is not found or not accessible. Check if instance name is correct and i

f SQL Server is configured to allow remote connections. For more information see

SQL Server Books Online..

Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

C:\Windows\system32>

September 7, 2012 12:07 PM
 

Argenis said:

@Neil: indeed you can - the cmd window shows a what I would have needed several SSMS screenshots to demonstrate.

September 7, 2012 12:18 PM
 

Argenis said:

@Nik: MAGMA\R2 is the name of the SQL Server instance that I used in this example. You'll have to replace it with the name of the instance you're trying to connect to.

September 7, 2012 12:19 PM
 

PJ said:

THANK YOU!!!

November 15, 2012 10:00 AM
 

Ankur Arora said:

This article is indeed very interesting and knowledgable. Thanks a lot Argenis

!!

November 22, 2012 3:17 PM
 

Josh M. said:

Great workaround!

January 18, 2013 10:57 AM
 

DL said:

Great tool!  Thanks!

February 12, 2013 12:08 PM
 

Es said:

Amazing. Thanks!

May 30, 2013 10:15 PM
 

David said:

I frickin' love you!

October 31, 2013 11:15 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement