How to Enable xp_cmdshell in SQL Server 2005 using sp_configure
This SQL tutorial shows how to enable xp_cmdshell execution in SQL Server using sp_configure system configuration procedure.
xp_cmdshell extended stored procedure executes a given operating system command shell.
The xp_cmdshell stored procedure is capable of extending a database users ability over operating system. This feature makes a xp_cmdshell a very powerful tool in SQL Server 2005 and in other SQL Server versions.
To make the SQL Server more secure, xp_cmdshell is disabled by default on SQL Server 2005 or SQL Server 2008 and later.
In order to prevent SQL Server data platform secure the xp_cmdshell permissions should be well configured and the use of xp_cmdshell in SQL Server t-sql codes should be strictly controlled.
For example, if you run the below t-sql xp_cmdshell in SQL Server 2005 to see how it works, the below error message will be returned by the sql engine.
EXEC xp_cmdshell 'dir *.exe';
GO
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server.
A system administrator can enable the use of 'xp_cmdshell' by using sp_configure.
For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
By the way, I realized that with the removal of Surface Area Configuration tool from Microsoft SQL Server components, the warning message for displabled xp_cmdshell is altered as following text.
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.
Above warning message is thrown from a Microsoft SQL Server 2012 database instance
To enable xp_cmdshell stored procedure in SQL Server 2008 or Denali, SQL Server administrators can run the below sql code as with the sa account priviledges or using another SQL Server administrator account.
To summarize the below t-sql script enable xp_cmdshell using sp_configure system configuration tool.
First, enable SQL Server advanced options with sp_configure.
Reconfigure to apply changes
Then, enable xp_cmdshell with sp_configure xp_cmdshell
Reconfigure to apply changes
USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
-- Returns the following message after a successfull execution
-- Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
-- Returns the following message after a successfull execution
-- Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO
-- Returns the following message after a successfull execution
-- Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
If you only run the below sp_configure sql statement without enabling "show advanced options", SQL engine will throw the following error.
EXEC sp_configure 'xp_cmdshell', 1
-- You will get the following error message
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.
After you enable xp_cmdshell execution with sp_configure, you can run the below sample scripts without any error.
EXEC xp_cmdshell 'ping 127.0.0.1';
EXEC xp_cmdshell 'dir *.exe';
An other xp_cmdshell example in real life time saving application for database administrators is to list SQL Servers in network using xp_cmdshell and sqlcmd tools together.
After you enable xp_cmdshell stored procedure using sp_configure, the output of the sample xp_cmdshell command in SQL Server 2005 will result as follows.
SQL developers can continue reading on this xp_cmdshell tutorial at list directory files using SQL and xp_cmdshell utility.