Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Is disabling xp_cmdshell in SQL Server really secure?


By:   |   Read Comments (2)   |   Related Tips: More > Security

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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


Last Update:


signup button

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, September 15, 2017 - 3:19:13 PM - K. Brian Kelley Back To Top

OK, but let's take your vector. What's to stop someone from adding the sp_configure and RECONFIGURE commands to the same stored procedure in the event that you leave xp_cmdshell disabled? Absolutely nothing.

And that's the point. Anything that can run as sysadmin level privs (or CONTROL SERVER) can re-enable. Therefore, it's not a real security control by itself.


Monday, September 11, 2017 - 11:24:48 AM - Robert Eder Back To Top

Consider the senario where a user database has a stored procedure that calls xp_cmdshell that is executing on a schedule by the SQL Agent.  If that user database has non-SA accounts with the ability to alter the stored procedure, a non-SA could elevate their priviledges to SA by modifying the stored procedure.  If xp_cmdshell calls a batch file, any non-SA that has the ability to modify the batch file would have the ability to elevate their priviledges.

Even with this senario possibility, I do not beleive it justifies the blanket policy of not allowing xp_cmdshell to be enabled.  I would not go so far as to state that enabling it is trivial in all situations.  It enabling xp_cmdshell does not create the possibility of priviledge elevation, then enabling it is trivial.  However, if it does, then you must determine if it is acceptable risk.


Learn more about SQL Server tools