How can I enable xp-cmdshell on a SQL Server 2005 instance?

(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.