xp_cmdshell: Baby did a bad, bad thing

My syntax highlighter is a bit broken.  Until I fix it, the TSQL shown below will be full of crud.  Fix it before trying it on your system.  Or just wait until I fix up the highlighter.  Your choice, really.

Much has been written about why SQL’s xp_cmdshell is a bad thing.  It has its uses, of course, and sometimes we just have to enable it for our apps to work.

In my most recent post about SQL, you would’ve detected a certain.. disdain.. for applications developers.  It’s not their fault, though.  They’re just trying to write something that works.  In my workplace, there is a “just make it work” mentality, which can lead to some awful misconfigurations.  That’s why this post is dedicated to just how bad xp_cmdshell can be.

By default, xp_cmdshell is disabled, and with good reason.  If it’s not set up just so, it can be exploited.  The gist of it is this:  if you can run a command from a DOS shell, non-interactively, xp_cmdshell will run it, too.  So it stands to reason that you should lock xp_cmdshell down as much as possible.  But this is at odds with the “just make it work” mentality, and sometimes security is compromised as a result.

If you’ve set up your SQL server per best practice, your SQL Server service account is an unprivileged domain account.  That means it’s just a regular account, not a member of any special domain or local groups.  When you tell the SQL installer to use this account, it sets up file permissions, registry permissions and local policies so that your service account has all the things it needs to run SQL properly.  But by default, the SQL installer doesn’t prompt you for a domain account.  No.  Left to its own devices, it will let you choose from a drop-down:

<insert screenshot here that Crayon syntax highlighter doesn’t like 🙁 >

 

 

Interesting.  The local system account is here, as well as the option to browse.  Hey, Local System looks good.  So, for this demonstration, let’s assume the naive person installing SQL selects this account.  For those of you not in the know, the Local System account has the keys to the city over the local machine.  It has no rights on any other system.  But for this demo, that doesn’t matter.  Let’s then assume the “Use the same account for all SQL Server services” is clicked.

So we carry on installing, and voila, we have a running SQL instance.  Let’s say the apps developer asks for xp_cmdshell to be enabled.  That’s easy enough to do:

 

And we get a successful result!

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘xp_cmdshell’ changed from 0 to 1. Run the RECONFIGURE statement to install.

Let’s try a command:

 

 

index

Hey, this is great!  It WORKS!!!!!!!!!!!!!!!!!!!!!!

Let’s try another one:

 

2_dir_c

Yay!  More working xp_cmdshell goodness.

We can even use it to write to a text file:

And the result is, funnily enough, a text file C:\FILE.TXT:

hello
appended data
more data

 

Let’s try something a little more interesting.

 

3_format_c

We should all breathe a sigh of relief here.  The format command needs to be run interactively.  It wants you to answer yes or no.  For once, Microsoft’s efforts to save us from ourselves have been useful.

And thankfully, by default, diskpart also runs interactively:

 

4_diskpart

Here’s where it can get interesting.  Diskpart runs interactively by default, but can also run in scripted mode.  Let’s jump to a DOS box for a minute to see how that works:

 

5_dosbox

I’ve just deleted the contents of disk 1.  It didn’t take much effort, did it?  Let’s see if we can script that.

First, create a text file somewhere that contains this:

Now, let’s run diskpart scripted:

 

6_diskpartscripted

Hmm.

I wonder if we can do the same thing with xp_cmdshell?

First, let’s create the command file:

Then, let’s run diskpart scripted:

 

7_diskpartscripted

If you don’t think a misconfigured xp_cmdshell is a bad thing, try this on a production system.  See how long you stay employed.

Some notes on the demo above.  I ran these commands using an account that was a member of the Administrators group on the local machine.  That’s why I didn’t have to do anything else special in order for xp_cmdshell to work.

Let’s say I have an unprivileged user that I want to be able to run xp_cmdshell commands.  I need to give them access to the command:

Let’s try this again, this time running it under the context of a standard user account:

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the ‘##xp_cmdshell_proxy_account##’ credential exists and contains valid information.

SQL is thoughtful enough to protect us from ourselves.  But we want to JUST MAKE IT WORK!!!!!!!!!!!!!!

So, let’s do what every app developer I’ve ever met would do.  Let’s configure the proxy account using a privileged account – maybe even the app developer’s own account (which is, of course, a member of the Administrators group).  This can be done via the GUI or TSQL, but for this demo, I’ll show the TSQL:

Now when we run the whoami command, we get this result:

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
The EXECUTE permission was denied on the object ‘xp_cmdshell’, database ‘mssqlsystemresource’, schema ‘sys’.

OK, fine.  Let’s grant this unprivileged user the rights to use xp_cmdshell:

Let’s run the whoami command again:

 

8_whoami

Yay!  It works!  Great, huh?

Now, let’s try that diskpart stuff again:

You’ll recall those commands created the diskpart script file.  Let’s then run the script file:

9_ohdear

 

Oh dear.

Those of you in the know might be thinking we could do “select disk 0” in our diskpart script and run it to trash the system volume.  Fortunately, diskpart protects us from ourselves:

10_protectusfromourselves

 

If there’s a saving grace, it’s that the system volume can’t be trashed using this simple method.  But hey, we can still wreak havoc.  Let’s say we have a database called SuckedIn, whose files live in F: drive:

 

11_suckedin

You know where I’m going with this, right?  If we don’t know the disk number, we can still use diskpart to identify the volume number, by changing the script to “list volume” to identify where F: drive lives:

 

12_suckedin

Then we just change the diskpart script to:

And the result is data destruction:

 

13_datadestruction

Thankfully, we still can’t trash the system volume, but if someone trashed your database volumes, you’d be just as screwed.  Don’t let this happen to you!

In case you passed out from boredom while reading my post, here’s the important bits that led to this disaster:

  1. The SQL service account is set as Local System
  2. The SQL proxy account is a privileged account, i.e. one that has Administrator rights over the local machine
  3. xp_cmdshell is enabled under these conditions.

That is all it takes to allow an otherwise regular, unprivileged user to HOSE YOUR SQL BOX!  All of this could have been prevented by using best practice, which in case you fell asleep again, is very simple:

  1. Configure your SQL instance to use an unprivileged account for its service account
  2. For the love of all that’s holy, use an unprivileged account for the SQL proxy account
  3. Don’t enable xp_cmdshell unless you absolutely, positively have to.

To change the SQL Server service account, use the SQL Server Configuration Manager tool.  You’ll need to restart the SQL Server service, so schedule an outage window to do this.

Once you’ve done this, use this script to configure the SQL Proxy account.  Make sure this is an unprivileged account:

This change takes effect straight away.  No outage required.

Now when you run the whoami account as an administrator, you’ll get this result:

 

14_whoami

If you run it as an unprivileged user, you’ll get this result:

 

15_whoami

Now try running the diskpart script as the unprivileged user.  It won’t work.  YAY for system security!

 

 

2 thoughts on “xp_cmdshell: Baby did a bad, bad thing

  1. “use an unprivileged account for the SQL proxy account” — What do you consider an unprivileged acocunt. I am using a domain user account and still can run xp_cmdshell and create/delete files. I’ve also tried a local user on the server and it can also do that.

  2. Hi Alan,

    That’s a good question, and yes I guess it depends on definitions. My own definition of an unprivileged account is one that a) is not a member of the local Administrators group, b) is not a member of Domain Admins (or Enterprise Admins or some other special group) and c) does not have any special rights conferred upon it eg through local policy.

    In the examples above, I’ve used a domain account that is a member of Domain Users and nothing more. This allows the proxy account to traverse the network to reach shared folders. The account could just as easily be a local account on the server. So it would still be able to perform tasks on the local file system, but not on remote shares.

    It is possible to configure xp_cmdshell to run under the context of an unprivileged account and still be able to create and delete files, provided the underlying NTFS permissions allow this. I obviously don’t know the specifics of your configuration, so I can’t give you specific guidance unless you fill in the blanks for me. I suspect the NTFS permissions on the files/folder in question are sufficiently loose to allow file manipulation. In short, if you can log in with the account interactively and are able to touch files with it, you would also be able to do this via xp_cmdshell (assuming there are command-line equivalents to do whatever you’re doing).

    If there’s a specific scenario you want me to address, let me know. Send me screenshots or details of your config so I can see if I can reproduce the unwanted behaviour here. I’m always up for finding out why things work the way they do. And if I’ve published something that’s flat-out wrong, I want to know about it!

Leave a Reply