Is enabling xp_cmdshell a security risk?

Download PDF

After listening to a podcast from Carlos L Chacon (twitter) at SQL Data Partners interviewing 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.

So before you read the rest of the post, please vote on this survey.

Is enabling xp_cmdshell a security risk?

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.

Posted in SQL Server Tagged with: ,
5 comments on “Is enabling xp_cmdshell a security risk?
  1. Sean McCown says:

    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.

  2. Cody Konior says:

    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.

    • SteveStedman SteveStedman says:

      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. Brian K says:

    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.

Leave a Reply

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

*

Time limit is exhausted. Please reload CAPTCHA.