How to Enable / Disable Xp_cmdShell in SQL SERVER? TIP #82

Friends,

Sometimes it might be possible that you have to run dos command from SQL SERVER. In such situation you have to enable the xp_cmdShell option of  sql configuration.

To enable this we can write following statement

EXEC sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
EXEC sp_configure ‘xp_cmdshell’, 1;
GO
RECONFIGURE;
GO

Just wanted to share that it can be a security thread as well.  So enabling the xp_cmdshell option might be sometime dangerous if we did not handle the SQL injections.

Now to disable this we can write following commands

EXEC sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
EXEC sp_configure ‘xp_cmdshell’, 0;
GO
RECONFIGURE;
GO

We can enable / disable this by following steps as well (Below steps will work with SQL SERVER 2008 or higher version )

Step 1: Select facets  option by right clicking SQL database as shown below

Facet

Step 2:-  When you go with option one you will get a new screen. In that new screen select facet “Surface Area Configuration”

Surfacearea

Step 3:- Now all the advance option will be available of Surface area Configuration as shown in below figure you can enable disable Xp_CmdShell.

xp_cmd_shell

 

Thanks

RJ

Where is “SQL server Surface Area configuration” in SQL Server 2008 R2 ?

Hello friends,

Many times you need to do SQL Server related configuration like, CLR enable, Xp_cmdShell, Remote Access etc.

 I am able to set this configuration in SQL server 2005 using “SQL server Surface Area configuration”. But I was surprised when I did not find this exe in SQL Server 2008 R2.

After little research I found how to get this “SQL Server Surface Area Configuration” in SQL server 2008 R2.

Step 1:- Right click the SQL Server 2008 Server in SQL Server Management Studio.

 Step 2:- Choose option “Facets

Image

 Step 3:- You will get below screen. You just need to select “Surface Area Configuration” option from dropdown.

Step 4:- Once you have selected the “Surface Area Configuration “ then you will get following screen.

 

So now it is good to go and configure your setting.

Thanks & Best Regards

Rajat Jaiswal