Is enabling xp_cmdshell a security risk?

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:

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:



  

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!