How to: Enable xp_cmdshell in SQL Server 2005 and SQL Server 2008

by cafoote on January 20, 2011

There are many instance in the past where have called a bat file from a stored procedure.

The sql to create the stored procedure is pretty straight forward:

Create PROCEDURE [dbo].[sp_callingbatfile]

AS

BEGIN

execute master.sys.xp_cmdshell ‘C:\Test.bat ‘

END

Unfortunately, in SQL Server 2005 and SQL Server 2008 this does not work by default

Introduced in SQL Server 2005, the xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system. By default, the xp_cmdshell option is disabled on new installations.

If you do try this you will receive the following error:

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.
There are a couple ways to enable the extended procedure Command Shell

Via Command Line (SQL 2008 and SQL Server 2005)

The easiest way to resolve this issue is to run the following query against the Master Database:

EXECUTE SP_CONFIGURE ‘show advanced options’, 1

RECONFIGURE WITH OVERRIDE

GO

EXECUTE SP_CONFIGURE ‘xp_cmdshell’, ’1′

RECONFIGURE WITH OVERRIDE

GO

EXECUTE SP_CONFIGURE ‘show advanced options’, 0

RECONFIGURE WITH OVERRIDE

GO

Via Gui (SQL 2005 only)
1. Open the “Microsoft SQL Server 2005” program group.

2. Open the “Configuration Tools” subgroup.

3. Start the “SQL Server Surface Area Configuration” tool.

4. At the bottom of the SQL Server 2005 Surface Area Configuration task menu select: “Surface Area Configuration for Features”

5. Expand “MSSQLSERVER” and then expand “Database Engine” if needed.

6. Select “xp_cmdshell” and select the check box to “Enable xp_cmdshell” and finally click “OK” and close the window.



Comments on this entry are closed.

Previous post:

Next post: