Enabling xp_cmdshell in SQL Server 2005

xp_cmdshell is used to spawn a Windows command shell and executes a command line process by the operating system.

In order to minimize the security risk of executing malicious code outside SQL Server 2005, xp_cmdshell is disabled by default, and the following error message will be shown when xp_cmdshell is being executed.

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.

(*Only* when there is an absolute need to enable the xp_cmdshell option) xp_cmdshell can be enabled by either one of the following methods.

[1] Using the Surface Area Configuration tool (or using the "sac" utility for surface area configuration)

Surface Area Configuration application can be found on the SQL Server
--> Start
  --> Programs
    --> Microsoft SQL Server 2005
      --> Configuration Tools
        --> SQL Server Surface Area Configuration
          --> click on Surface Area Configuration for Features
            --> SQL Sever instance /Database Engine
              --> xp_cmdshell (check the "Enable xp_cmdshell" checkbox)

[2] Running the sp_configure system stored procedure (administrative login required).

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

-- To disallow advanced options to be changed.
EXEC sp_configure 'show advanced options', 0
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO

===== For more information =====

- xp_cmdshell (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms175046.aspx

- Surface Area Configuration for Features (xp_cmdshell) - Database Engine
http://msdn2.microsoft.com/en-us/library/ms177290.aspx

- xp_cmdshell Option
http://msdn2.microsoft.com/en-us/library/ms190693.aspx

- sac Utility
http://msdn2.microsoft.com/en-us/library/ms162800.aspx

===== Other posts that point here =====

~ xp_cmdshell

http://sql-server-performance.com/community/forums/p/25032/139207.aspx