Enable and disable xp_cmdshell

xp_cmdshell is one of those somewhat hidden gems/power-tools in SQL Server that can both make life easy and be very insecure all at the same time.

You see, this extended stored procedure allows any SQL Server administrator (any member of the sysadmin role) to run any command/program/shell-script/batch-file that the SQL Server service account has access to. It seems counter-intuitive at first, but I've seen this most often become a problem in development environments where users might have sysadmin rights on the SQL Server instance but not necessarily on the server that's running SQL Server. SQL Server is often given a fair amount of rights on the server (far greater than a standard Windows user), so anyone who can run xp_cmdshell effectively inherits those rights.

xp_cmdshell is disabled by default and must be enabled with sp_configure (see below). Once enabled, members of the sysadmin role can execute it, and permission can be given to other users by granting them execute permission. Run this in the master database, where xp_cmdshell lives, using a user that exists in master:

USE master;
GRANT EXECUTE ON xp_cmdshell TO [<some_user>];

While permission can be doled out in this way, non-sysadmins will not execute commands as the SQL Server service account. Instead, they run as the Windows account stored in the credential named ##xp_cmdshell_proxy_account##. If the proxy account has not been set, then executions by non-sysadmins will fail.

Setting the proxy account and password is as simple as:

EXEC sp_xp_cmdshell_proxy_account 'DOMAIN\user', 'SomePaSSword!';

Use an account with as few rights as possible for this, and remember that the password in a script like this is stored in plain text wherever the script lives. To remove the proxy account, pass NULL:

EXEC sp_xp_cmdshell_proxy_account NULL;

To enable xp_cmdshell:

-- We first must show SQL Server's advanced options:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
 
-- Then we can enable xp_cmdshell:
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
GO

To disable xp_cmdshell:

-- We first must show SQL Server's advanced options:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
 
-- Then we can disable xp_cmdshell:
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
GO

To check whether xp_cmdshell is enabled:

SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'xp_cmdshell';

Using xp_cmdshell:

Once it's enabled, run an operating system command by passing it as a string. Each line of the command's output comes back as a row in the result set.

EXEC xp_cmdshell 'dir C:\Temp';

A word of caution: because xp_cmdshell hands the operating system to anyone who can call it, it is a favorite target for attackers (a SQL injection hole on a server with xp_cmdshell enabled can turn into a full operating system compromise). Leave it off unless you genuinely need it, enable it only for as long as you need it, and consider alternatives such as SQL Server Agent job steps or PowerShell where they fit.

Happy executing!