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

2 Replies to “Enabling xp_cmdshell in SQL Server 2005”

Leave a Reply

Your email address will not be published. Required fields are marked *