Month: March 2021

I am not getting into the discussion of pros and cons of xp_cmdshell. This command helps you to run broad level features inside your SQL server. You can run OS level commands, SSIS packages etc.

Here is how you can enable, disable and use.

To 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';

Turn on xp_cmdshell

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

Turn off xp_cmdshell

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

Here is a simple use case;

EXEC xp_cmdshell 'dir c:\'

Here is how you can enable it, use it and disable;

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

You can create a stored procedure and use;

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:\';

This is not a new feature. It came out with early release of SQL server. If you see the usage command, they are OS level command. With the super privileged access any body can run a “format c:\” OS command and leave your database server in unrecoverable state. My suggestion would be to use it with least privileged proxy account.