By: K. Brian Kelley | Updated: 2017-09-06 | Comments (2) | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > Security
Problem
I’ve recently been told by my auditors that we need to make sure xp_cmdshell is disabled on our SQL Servers. However, when I look on-line, I see conflicting arguments for and against disabling xp_cmdshell. I need to understand the pros and cons of doing so and whether or not it’s worth it.
Solution
In security, there’s a general principle to turn off what you’re not using. If we take that principle without further consideration, the recommendation we’ll develop is to disable xp_cmdshell. This is where the recommendation to disable xp_cmdshell originally came from. However, once we look at the security on xp_cmdshell, the ease with which it can be enabled, and the fact that Microsoft does enabling and disabling in its own code, we’ll see that disabling xp_cmdshell is not an effective security control.
Why We Block xp_cmdshell
The main reason we block xp_cmdshell is it is a method of running operating system commands in the context of the SQL Server account. For most database setups, this isn’t needed. As a result, we disable it (or leave it disabled on the newer versions of SQL Server). If you don’t need it, then leave it disabled. There’s no reason to spend the extra work to turn on something you don’t need.
However, if you do need it, then you may find yourself at odds with auditors and security personnel. Because the guidance has been to disable it for so long, often without any explanation, they’re likely to insist on it being disabled. However, security is about trade-offs, especially trade-offs between protecting an asset and providing functionality and access to it. In this case, it behooves us to consider whether we can truly keep xp_cmdshell disabled and what we get from doing so. Let’s consider some additional factors.
Default Security on xp_cmdshell
By default, no account has access to execute xp_cmdshell explicitly. When xp_cmdshell is enabled, that means someone needs to have CONTROL SERVER permissions. Being a member of the sysadmin fixed server role grants such permissions implicitly. Therefore, by default, only the highest level of privileges can call xp_cmdshell. A regular logon does not have access. In order for a regular logon to have access, someone has to grant such privileges.
Re-enabling xp_cmdshell
Given that the highest privileges have access to xp_cmdshell, we need to note that should someone log on with this level of rights within SQL Server, they can re-enable xp_cmdshell by using sp_configure. Therefore, even if we disable xp_cmdshell, the folks who would be able to execute it also have the permissions to enable it, should they find it disabled.
There are ways to try to handle this. We can put DDL triggers in place. We can set up Policy-Based Management. However, every such attempt fails to the fact that someone with those rights can overcome any such monitoring / blocking mechanisms we enable. That’s because folks with this level of rights should be considered trusted. At this level it’s more about monitoring, because outright blocking isn’t possible. The phrase, “trust but verify,” comes to mind.
Using SQL Server Agent to accomplish the same thing
If it’s a normal (not Express) edition of SQL Server installed, that also means SQL Server Agent is also installed. By default, the SQL Server Agent extended stored procedures and the SQL Server Agent service doesn’t run. However, many organizations enable SQL Server Agent to run backups and database maintenance jobs. If you support SQL Server Reporting Services with subscriptions, those subscriptions run in the form of SQL Server Agent jobs. So in most shops, SQL Server Agent is up and running.
Even if it’s not, the same highest privileges which can enable xp_cmdshell using sp_configure can also enable to the SQL Server Agent extended stored procedures using sp_configure. If we’re monitoring xp_cmdshell and not the SQL Server Agent stored procedures, that’s an effective way around the control. Then the only challenge is getting the SQL Server Agent service to start up. That may require more creativity, especially if the person isn’t also an administrator at the OS level. However, in quite a few shops, the DBAs are administrators at the OS level or at least have the ability to stop and start services. As a result, such an individual can enable SQL Server Agent and start it up.
Once SQL Server Agent is up and running, jobs become the method of executing OS level commands. After all, SQL Server Agent job steps include CmdExec and PowerShell options. Therefore, if the xp_cmdshell path is blocked and too heavily monitored, then this provides another vector to do the same thing.
Microsoft code uses xp_cmdshell
Not surprisingly, some of Microsoft’s code uses xp_cmdshell as well. For instance, execute the following T-SQL command in the master database as a member of the sysadmin fixed server role:
SELECT definition FROM sys.system_sql_modules WHERE definition LIKE ‘%xp_cmdshell%’;
If you have replication installed, you’ll get multiple rows returned. Replication isn’t the only feature which uses xp_cmdshell. There are other components which also use it. Some are smart enough to check if xp_cmdshell is enabled. If it isn’t, the code enables it, then execute its, then disables it. This reinforces what we’ve looked at thus far: re-enabling xp_cmdshell is trivial.
Concluding Thoughts
By default, xp_cmdshell is disabled. If you don’t have a reason to enable it, then the best option is to leave it as is: disabled. However, if you do need it for a solution, then realize that the call to disable xp_cmdshell doesn’t do much at all to increase the security of your SQL Servers. The folks who have permission to execute xp_cmdshell by default also have the ability to re-enable it. In addition, if you have SQL Server Agent running, those same individuals can execute the same types of commands using a job. Therefore, from an audit and control perspective, we can’t conclude that disabling xp_cmdshell is an effective control. In order for it to work, there have to be compensating controls to try and address all the limitations we have in securing it.
As a result, I suggest leaving xp_cmdshell disabled as a nice to have when looking at SQL Server security. This is simply to avoid the question from the auditors and security analysts, which takes up our valuable time. However, I also make sure folks understand that there isn’t a real security benefit to doing so. In other words, we leave it off because we’re “lazy:” there’s work to enable it in the first place and then there’s more work explaining why xp_cmdshell being enabled doesn’t effectively put our SQL Servers at any greater risk.
Next Steps
- See why you can’t stop an administrator from re-enabling xp_cmdshell.
- Understand how to make SQL Server Agent do the same thing as xp_cmdshell.
- Learn how you can execute an OS command if SQLCLR is enabled.
About the author
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-09-06
