Enabling xp_cmdshell in SQL Server 2005

 

If you don't enable xp_cmdshell and you issue a command such as the following to get a directory listing of the C: drive:

 

xp_cmdshell 'dir c:\'

 

 

you get the following error message:

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.

 

Solution 
So in order to use xp_cmdshell whether you are a sysadmin or a regular user you need to first enable the use of xp_cmdshell.  Doing this is similar to what was done when enabling the dedicated administrator connection.  This can be done by using sp_configure:

• sp_configure 'xp_cmdshell', '1'

or by using the SQL Server Surface Area Configuration as follows:

• launch the SQL Server Surface Area Configuration.tool which can be found under SQL Server 2005 Configuration Tools in the SQL Server 2005 menus
• select "Surface Configuration Area for Features".
• check the box for "xp_cmdshell"
• select "Apply"

Next Steps

• Check whether or not you are using xp_cmdshell for previous versions of SQL Server and enable it if it is still needed when you make the upgrade to SQL 2005
• With a new installation of SQL Server 2005 check to see if you really need this option enabled.  From a security perspective it is best to minimize any risks, so keep this option disabled makes the most sense unless there is a real need.