Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.

Why We Recommend Against xp_cmdshell

Every so often I see a post in a forum or on Twitter about the use of xp_cmdshell. Usually it is someone looking for a reference as to the security risk of doing so. So let's look at what using xp_cmdshell means.

In SQL Server 2000 we used to have a recommendation to drop the extended stored procedure if you didn't need it.  The problem is it was included as part of xplog70.dll which had other extended stored procedures which were needed. That meant you couldn't just move or delete the .DLL that was referenced. So an attacker only had to re-add the extended stored procedure. The default permissions for xp_cmdshell was that no one has it. And that meant only members of the sysadmin role could execute it (since members of the sysadmin fixed server role bypass permission checks). But members of the sysadmin role could re-add the extended stored procedure if you dropped it. So there wasn't much you could do to remediate this one.

In SQL Server 2005/2008 we have the ability to disable xp_cmdshell. Again, the default permissions is that no one has the ability to execute the stored procedure, meaning only members of the sysadmin server role can. And if you're a member of that role, you can re-enable xp_cmdshell. With SQL Server 2008 we have a bit of additional control in the form of policy-based management, but it's still do-able. Again, there's not a whole lot one can do to prevent its use by those with that level of permissions.

So what's the big deal about restricting its use? The main reason is the context in which xp_cmdshell runs and what it gives access to. First, the level of access. If xp_cmdshell is being called by a member of the sysadmin role, it is going to execute in the context of the SQL Server service account. So what ever the service account SQL Server is running under, a member of the sysadmin role can execute as through the use of xp_cmdshell. So if DBAs don't normally have administrative rights on a server, but SQL Server is running under a service account that does, then through the use of xp_cmdshell they have effectively escalated their rights to be at that level. By the way, this also means any processes which run under an account that is a member of the sysadmin role, such as a SQL Server Agent job owned by sa, runs at this level, too. And this is the first reason we are usually hesitant

Second, and finally, is what it does. The xp_cmdshell effectively drops you to a command prompt. You just have to know what command you're executing first, but you get the idea. So just about everything you can do at a command prompt, you can do through xp_cmdshell. When you couple this with the permissions that the SQL Server service account is likely to have, you're talking about the potential for some major damage. At the very least, an attacker could use xp_cmdshell to shut down key services and processes on the server (especially if coupled with something like pskill) rendering the operating system in an unstable state where it forces itself to reboot. You get the idea.

So when you couple level of access and what it can potentially do, that's the reason xp_cmdshell is disabled by default in SQL Server 2005/2008. Yes, a member of the sysadmin role can undo this, but generally you should auditing, policy management, etc., to enforce this configuration setting. Plus there's this whole idea that if it is something that needs to run from the command prompt, use a batch job or an appropriate Operating System (CmdExec) job step within a SQL Server Agent job.

 

Comments

Posted by Bill Nicolich on 13 November 2009

Good post. Thanks for the tip, Brian!

Posted by Steve Jones on 16 November 2009

Excellent post and I try to caution people as much as I can against using this. Unfortunately, so many file based items are easier with it and people want to use it.

Posted by Jack Corbett on 16 November 2009

Steve,

Best reason for learning PowerShell, IMO, is to eliminate the need for xp_cmdshell.

Posted by jay on 19 November 2009

We use xp_cmdshell for BCP in and out. We write out files in minutes using this method.  The other alternative was to use SSIS and that takes hours to do the same thing.  Why don't they come up with a command that can execute BCP without have access to the world?

Jay

Posted by terry.payne on 19 November 2009

Well said Jay. Informix isql has a simple UNLOAD command to bulk copy the results of a SQL statement to a file. Of course, that too bl**dy simple for Microsoft!

Posted by Stephen.Richardson on 19 November 2009

I can not use it, but neither can I find an alternative.  I need it to programmatically run an app for remote login then data extraction from within SSIS or SQL Job.  Any ideas?

Posted by David Benoit on 19 November 2009

Brian - great post. Thanks.

Jay, Terry, Stephen - connect.microsoft.com is the place to go. Agree with your statements. Make your voices heard.

Posted by Chris Jackson on 19 November 2009

Follow-up question. Now that you have pointed out the hole. What is your suggestion for an alternative to xp_commandshell that can be executed from SQL that does not involve a wizzard and can be ran using TSQL?

Posted by mtassin on 19 November 2009

We use it to move local drive file backups of our SQL databases to a UNC repository via a batch file.

Is there another (simpler) way to execute that batch file other than xp_cmdshell?

Posted by Martin Vrieze on 19 November 2009

Is Windows PowerShell a safe and adequate alternative to xp_commandshell?

Posted by JosephDMarsh on 19 November 2009

@mtassin: If all you're doing is moving files, why not use operating-system-level commands, rather than get SQL Server involved?  A simple batch file (or one of MANY free-/share-ware utilities) will get that job done for you.

Posted by ben.mcintyre on 19 November 2009

While it may seem a bit roundabout, I have used an asynchronous flagging scheme to avoid the kind of close coupling you are talking about (eg. BCP via xp_cmdshell).

Write and run a service on the server which checks a flag table in the database once per second.  Flag table contains details of dataset to be processed and file destination.  Service then marks the job as allocated, processes the dataset to the file (even possibly doing a remote copy), and then marks the job as complete.

The client merely needs to add the flag record with dataset and output file details and wait for the completion flag to be raised within a timeout period.

This does require non TSQL skills (.NET is perfect, but many others would do), but is a viable solution and some would even see it as elegant due to its wide application to decoupling and distributing of services.

Not saying that M$ shouldn't provide the UNLOAD equivalent, but there are ways around using xp_cmdshell.  It's a hell of a back door.

Posted by K. Brian Kelley on 20 November 2009

Jay, it sounds like you initiate the process inside SQL Server. Why for? Powershell, Perl, standard command shell commands + SQLCMD + bcp and you can accomplish the same thing. Plus you can better control user context. Always key.

Stephen, I don't follow what you mean. You can cause the process to run in a different context and then connect to SQL Server. For instance, to execute an SSIS package, you do so using dtexec. Just start the process using the right user account.

Chris, my suggestion is not to execute from within SQL Server. Look, we don't use Exchange to manage computer objects in AD, right? So you used something outside, Perl, Powershell, Python, whatever, and it connects to SQL Server as a client and makes the file manipulation calls or shells out or what has you. It means SQL Server memory isn't being taken up with that, you don't have a particular resource contention within SQL Server, and you aren't running as the SQL Server service account.

Posted by Lian Pretorius on 20 November 2009

Thanks Brian,

It also highlights the importance of having restricted service accounts.

Posted by Ken on 23 November 2009

We use it to execute RS.EXE Do you have any suggestions for alternatives here? Originally the plan was to directly edit Report Server but then it seemed all the suggestions out there said this was a bad idea and that manipulating ReportServer via RS.EXE was the way to go. So what is your suggestion for doing this if not via XP_CMDSHELL.

Posted by Eoin on 24 November 2009

What about switching it on as you need it and switching it off afterwards

Posted by Jeff Moden on 16 February 2013

If a hacker gets in with "SA" privs, then having xp_CmdShell disabled is like holding up a paper umbrella to shield you from a nuclear explosion.

The proper use of xp_CmdShell is not a security issue.  Security is the security issue. ;-)

Posted by Jeff Moden on 4 May 2013

Heh... Wow!  No one bit on that?  Ok.  Then I'll be more direct with a question because I'd really, really like to know and I've not been able to find it on the internet anywhere.  Here's the question that I don't know the answer to.

As suggested in this very fine article by Mr. Kelley, there is no way to prevent someone with "SA" privs from using xp_CmdShell.  Does anyone know of a 100% bullet proof method of preventing an "SA" from either using xp_CmdShell or executing command-line level commands through any of the dozens of hacks available to someone with "SA" privs on an SQL Server?

Posted by joao.osorio on 10 January 2014

Getting SA is not such a simple matter if you have these recommendations in mind:

1. Disable the "SA" account;

2. Don't run the database services as "system/localsystem", but eith domain accounts

3. Use mixed authentication, with personal Windows AD accounts for the DBA team members

4. Provide SQL Logins with RW privileges for the Apps

Disabling or keeping the xp_commandshell is just a cherry on top of the security cake. And still there alternatives like powershell and SQLCLR. Check a more complete insight about this matter here: blog.ozzie.eu/.../security-concern-alternative-to.html

Leave a Comment

Please register or log in to leave a comment.