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.