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

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

Robert L Davis — Mon, 11 Jul 2011 02:15:26 GMT

Interesting. I never considered trying something like this.

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

Paul Timmerman — Mon, 11 Jul 2011 02:20:01 GMT

Well that's not scary at all, LOL!

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

Mark Shay — Mon, 11 Jul 2011 02:31:28 GMT

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

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

Robert Miller — Mon, 11 Jul 2011 03:01:05 GMT

Some mad hacker skills there.

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

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

Meher — Mon, 11 Jul 2011 13:07:54 GMT

Nice demo Argenis.

Meher

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

Brent Ozar — Mon, 11 Jul 2011 13:10:01 GMT

Wow. Nice find, man.

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

K. Brian Kelley — Mon, 11 Jul 2011 13:31:25 GMT

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.

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

Jim Murphy — Mon, 11 Jul 2011 13:54:19 GMT

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

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

Chris Wood — Mon, 11 Jul 2011 15:50:12 GMT

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

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

Amit Banerjee — Mon, 11 Jul 2011 16:10:12 GMT

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.

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

Chris Wood — Mon, 11 Jul 2011 17:51:41 GMT

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

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

Argenis — Mon, 11 Jul 2011 18:10:24 GMT

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

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

Chris Wood — Mon, 11 Jul 2011 19:30:36 GMT

Argenis,

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

Thanks

Chris

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

Leo Miller — Mon, 11 Jul 2011 21:36:52 GMT

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.

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

Argenis — Mon, 11 Jul 2011 22:15:48 GMT

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.

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

GrumpyOldDBA — Wed, 13 Jul 2011 13:56:54 GMT

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?

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

Dale Hirt — Mon, 18 Jul 2011 20:22:16 GMT

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.

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

Aaron Sentell — Wed, 20 Jul 2011 20:43:17 GMT

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.

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

Jorge Segarra — Sun, 24 Jul 2011 00:05:24 GMT

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."

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

Jason S — Fri, 14 Oct 2011 19:47:13 GMT

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.

Leveraging Service SIDs to Logon to SQL Server 2012 Instances with Sysadmin Privileges

Argenis Fernandez — Thu, 12 Jan 2012 23:34:44 GMT

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

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

Sudhanshu — Wed, 21 Mar 2012 07:40:05 GMT

Excelelnt one, this saves my time...

hurrayyyyy

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

Neil Simmons — Mon, 13 Aug 2012 13:14:40 GMT

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

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

Nik Edmiidz — Fri, 07 Sep 2012 16:07:09 GMT

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>

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

Argenis — Fri, 07 Sep 2012 16:18:36 GMT

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

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

Argenis — Fri, 07 Sep 2012 16:19:18 GMT

@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.

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

PJ — Thu, 15 Nov 2012 15:00:53 GMT

THANK YOU!!!

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

Ankur Arora — Thu, 22 Nov 2012 20:17:18 GMT

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

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

Josh M. — Fri, 18 Jan 2013 15:57:04 GMT

Great workaround!

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

DL — Tue, 12 Feb 2013 17:08:23 GMT

Great tool! Thanks!