Is enabling xp_cmdshell a security risk?

Download PDF

After listening to an interview of Sean McCown (twitter) and some lively discussion on xp_cmdshell, I decided it would be a worthy enough discussion to create a blog post. At a minimum it might stir up some opinions.

There are many varying opinions out there, including some really good opinions that applied to SQL Server 2000, but don’t really apply any more.

Warning: This post may contain information disturbing to those who think they are safe if xp_cmdshell is disabled.

Let’s start by looking at some facts:

The facts with xp_cmdshell on SQL Server 2005 or newer, including SQL Server 2016.

  • xp_cmdshell is disabled by default on install.
  • Only those users with sysadmin (sa) permissions can use it.
  • Only those users with sysadmin (sa) permissions can enable it for use, or disable it.
  • Those users with sysadmin (sa) permissions can do so much other damage (if they wanted to) like DROP DATABASE, even if xp_cmdshell is not enabled.

What I someone was to do the following:

formatC

Here is how you check if xp_cmdshell is enabled and available to use.

SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value
FROM sys.configurations
WHERE name = 'xp_cmdshell' ;

Here is how you turn xp_cmdshell on.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

Here is how you turn xp_cmdshell off.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

Here is how you use xp_cmdshell:

EXEC xp_cmdshell 'dir c:\';

Here is how you use xp_cmdshell when it is disabled.

DECLARE @originalSetting INTEGER = 0;
SELECT @originalSetting = CONVERT(INT, ISNULL(value, value_in_use))
FROM  sys.configurations
WHERE  name = 'xp_cmdshell' ;

IF @originalSetting = 0
BEGIN
	EXEC sp_configure 'show advanced options', 1;
	RECONFIGURE;
	EXEC sp_configure 'xp_cmdshell', 1;
	RECONFIGURE;
END

EXEC xp_cmdshell 'dir c:\';

IF @originalSetting = 0
BEGIN
	EXEC sp_configure 'show advanced options', 1;
	RECONFIGURE;
	EXEC sp_configure 'xp_cmdshell', 0;
	RECONFIGURE;
END

Now make it easy by putting xp_cmdshell into a stored procedure (a temp stored procedure in this case). Some people will probably hate me for doing this. This stored procedure give unlimited access to run xp_cmdshell by anyone with sysadmin (sa) privileges.

CREATE PROCEDURE #sp_SuperCmdShell
(
    @commandToRun VARCHAR(1000)
)
AS
BEGIN
    -- check to see if xp_cmdshell is enabled, if its not
    --   then enable it and run the command then set it back
    --   to not enabled.
    DECLARE @originalSetting INTEGER = 0;
    SELECT @originalSetting = CONVERT(INT, ISNULL(value, value_in_use))
    FROM  sys.configurations
    WHERE  name = 'xp_cmdshell' ;

    IF @originalSetting = 0
    BEGIN
	    EXEC sp_configure 'show advanced options', 1;
	    RECONFIGURE;
	    EXEC sp_configure 'xp_cmdshell', 1;
	    RECONFIGURE;
    END

    EXEC xp_cmdshell @commandToRun;

    IF @originalSetting = 0
    BEGIN
	    EXEC sp_configure 'show advanced options', 1;
	    RECONFIGURE;
	    EXEC sp_configure 'xp_cmdshell', 0;
	    RECONFIGURE;
    END

END
GO
exec #sp_SuperCmdShell 'dir c:\';
exec #sp_SuperCmdShell 'dir d:\';

Keeping in mind that this will log to the error log messages that look something like this.

spid63 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
spid63 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
spid63 Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.
spid63 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
spid63 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
spid63 Configuration option 'xp_cmdshell' changed from 1 to 0. Run the RECONFIGURE statement to install.
spid63 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
spid63 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
spid63 Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.
spid63 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
spid63 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
spid63 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
spid63 Configuration option 'xp_cmdshell' changed from 1 to 0. Run the RECONFIGURE statement to install.
spid63 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
spid63 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
spid63 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
spid63 Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.
spid63 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.

So the simple fact is that the xp_cmdshell setting being enabled or disabled does not prevent anything. Additionally if you see the error messages listed above in the log, then it doesn’t tell you who did it, it just tells when it happened.

Questions:
Q: All my users have sysadmin (sa) permissions, how can I prevent them from running xp_cmdshell.
A: Well you can’t. Perhaps the better question would be how to convert all those sysadmin users to not be sysadmins.

What are your thoughts on xp_cmdshell?  Post a response below, I would love to hear from people on this one.

 

More from Stedman Solutions:

SteveStedman5
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!

7 Comments on “Is enabling xp_cmdshell a security risk?

  1. Hey Steve, thanks for writing this. I get so angry listening to DBAs argue that cmdshell is evil when they’re clearly not coming from a logical standpoint. When I started reading this I was really scared you were going to try to make some ridiculous argument about how dangerous it is.
    Anyway, good job on this.

    • Thanks for the feedback Sean. Good luck with your Minionware. It looks like you have done some really cool things with it.

      -Steve Stedman

  2. Don’t forget that you can go a step further by removing xp_cmdshell completely (not just the extended procedure but also the DLLs behind it) This breaks stuff pretty severely but it’s documented since 2000 https://support.microsoft.com/en-us/kb/891984

    Of course then service packs can add it back, so… people need to learn to live with it.

    • Cody,
      Thanks for the info. Good points. I guess if you really want to get rid of xp_cmdshell, removing the DLLs behind it would be one way of doing it.

      -Steve Stedman

  3. Agree with you re: xp_cmdshell. It’s locked down properly and any security issues with it are more symptoms of a more generic security issue.

    Here’s a somewhat-related question: is there a best practice regarding the “show advanced options” feature? I see all kinds of links saying you need to turn it on in order to get to the good stuff, but nobody ever says you should turn it back off again. I know turning it on and off is trivial (as long as you’re sysadmin or serveradmin) but I’m wondering if people typically keep it off and only turn it on when needed.

  4. Heh,,, I shared Sean’s initial reaction when I saw the title. I was greatly relieved when I saw the “Warning” in Red Letters.

Leave a Reply

Your email address will not be published. Required fields are marked *

*