(Note: Please replace the – [hyphen] with an _ [underscore] wherever xp-cmdshell is mentioned in this FAQ.)
As part of Microsoft’s desire to increase the out-of-the-box security of SQL Server 2005, it has turned off the xp-cmdshell extended stored procedure by default. If you try to use xp-cmdshell without manually enabling it, you will get an error message like this:
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.
or like this:
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 50
The configuration option ‘xp-cmdshell’ does not exist, or it may be an advanced option.
If you don’t have a need to use the xp-cmdshell, then don’t enable it. But if you have a need for it, then you must manually enable it. There are two different ways to enable xp-cmdshell in SQL Server 2005. Use can turn it on using the SQL Serve 2005 Surface Area Configuration tool, or you can use the sp_configure command.
To turn on the xp-cmdshell using the SQL Serve 2005 Surface Area Configuration tool, follow these steps:
1) From Start –> Programs –>, select the Microsoft SQL Server 2005 program group and launch the Surface Area Configuration tool, which is found under the SQL Server 2005 Configuration Tools in the SQL Server 2005 menus.
2) Once the Surface Area Configuration tool has launched, select “Surface Area Configuration for Features,” and the following screen appears.
3) Next, click on xp-cmdshell in the tree menu at the left, and then click on “Enable xp-cmdshell”, and then click on “Apply.” Xp-cmdshell is now enabled.
If you want to enable xp-cmdshell using sp_configure instead of using the Surface Area Configuration tool, run the following script in Management Studio:
EXEC sp_configure ‘show advanced options’, 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure ‘xp-cmdshell’, 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure ‘show advanced options’, 0 *
GO
If you are upgrading from SQL Server 2000 to SQL Server 2005, the Upgrade Advisor will advise you to check whether or not you are using xp-cmdshell for SQL Server 2000, and then suggest that you enable it if it is still needed after the upgrade to SQL Server 2005.
]]>