How to enable xp_cmdshell extended stored procedure for Microsoft SQL Server 2005

xp_cmdshell extended stored procedure executes a given operating system command shell. The xp_cmdshell is capable of extending a database users ability over operating system it is a very powerful tool. To make the SQL Server more secure, xp_cmdshell is disabled by default on SQL Server 2005.

For example, if you run the below sql command to see how it works on SQL Server 2005 the below error message will be returned

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.
 

To enable xp_cmdshell extended stored procedure, you can run the below code as the sa account or another administrator account;

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

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 enabling the xp_cmdshell functionality, you can run the below sample scripts

EXEC xp_cmdshell 'ping 127.0.0.1';

EXEC xp_cmdshell 'dir *.exe';

BlinkList   Del.icio.us   Digg   Furl   Simpy   Spurl   DZone   ma.gnolia   Shadows