By: K. Brian Kelley | Updated: 2017-09-06 | Comments (2) | Related: More > Security
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.
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.
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.
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.
- 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.
Last Updated: 2017-09-06
About the author
View all my tips