Enable / Disable xp_cmdshell on SQL Server 2005

The xp_cmdshell extended stored procedure allows a user to run OS commands from within the Database Engine. In order to execute the same we will need to enable xp_cmdshell. We can perform the same in the following two ways:

1. Using the SP_CONFIGURE Stored Procedure:

a. Open a New Query Window in SQL Server and execute the following SQL command in order to change the global configuration settings for the current server so that all the available options are displayed:
SP_CONFIGURE 'show advanced options', 1

b. Execute the following SQL Command in order to update the currently configured value of the configuration option changed with the SP_CONFIGURE system stored procedure:
RECONFIGURE

c. Execute the following SQL Command in order to display the global configuration settings for the current server:
SP_CONFIGURE
d. Check the config_value for xp_cmdshell. If the value is 0, then Execute the following SQL Command in order to enable the Stored Procedure:
SP_CONFIGURE 'xp_cmdshell', 1

e. Execute RECONFIGURE in order to update the currently configured value of the configuration Option.

2. Using SQL Server 2005 Surface Area Configuration:
Perform the following steps in order to enable xp_cmdshell from SQL Server 2005 Surface Area Configuration window:

a. Open SQL Server Surface Area Configuration :

Click On Windows Button --> All Programs --> Microsoft SQL Server 2005 --> Configuration Tools --> SQL Server Surface Area Configuration.

b. Click “Surface Area Configuration for Features”




c. Select the xp_cmdshell component on the left pane and put a check mark on the Enable xp_cmdshell checkbox on the right pane.



d. Click on the Ok Button and you have successfully enabled xp_cmdshell stored procedure on your SQL Server 2005.

Run a Batch File from an ASP.NET Webpage using xp_cmdshell

During the course of one of my assignments in an automation project, one of the requirements was to execute a batch file from an ASP.NET Web Page. The Batch File could be executed using Systems.Diagnostics.Process and Systems.Diagnostics.ProcessStartInfo. However, this lead me to impersonate a user or to elevate the privilege level of the ASP.NET Application Pool which I wanted to avoid.

After some searching in the Internet I came across a System Stored Procedure in SQL Server 2005 xp_cmdshell. However in order to execute xp_cmdshell we will need to Enable xp_cmdshell. Hope the Stored Procedure provided will come in handy to anyone facing the same issue.

GO

/****** Object: StoredProcedure [dbo].[ExecuteBatchFile] ******/

CREATE PROCEDURE [dbo].[ExecuteBatchFile]
@BatchFileName NVARCHAR (1024),
@BatchArguments NVARCHAR (256)

AS

DECLARE @V_PARAM AS NVARCHAR (1280)

SET @V_PARAM = @BatchFileName + ' ' + @BatchArguments

CREATE TABLE #TempTable
(
OUTPUT NVARCHAR (1280)
)

INSERT #TempTable exec master..xp_cmdshell @V_PARAM
SELECT OUTPUT FROM #TempTable

SET NOCOUNT ON
DROP TABLE #TempTable
SET NOCOUNT OFF