Enable xp cmdshell SQL Server

Question

I want to execute EXEC master  xp cmdshell  bcpquery   But I am getting 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    Is there any way to activate this  or execute something before enabling the feature    How to solve it

User · Answer

Right click server -- Facets-- Surface Area Configuration -- XPCmshellEnbled -- true

User · Answer

While the accepted answer will work most of the times  I have encountered  still do not know why  some cases that is does not  A slight modification of the query by using the WITH OVERRIDE in RECONFIGURE gives the solution  Use Master GO  EXEC master dbo sp configure  show advanced options   1 RECONFIGURE WITH OVERRIDE GO  EXEC master dbo sp configure  xp cmdshell   1 RECONFIGURE WITH OVERRIDE GO   The expected output is      Configuration option  show advanced options  changed from 0 to 1  Run the RECONFIGURE statement to install    Configuration option  xp cmdshell  changed from 0 to 1  Run the RECONFIGURE statement to install

User · Answer

Even if this question has resolved  I want to add my advice about that     since as developer I ignored   Is important to know that we re talking about MSSQL xp cmdshell enabled is critical to security  as indicated in the message warning      Blockquote   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          Leaving the service enabled is a kind of weakness  that for example in a web-app could reflect and execute commands SQL from an attacker  The popular CWE-89  SQL Injection it could be weakness in the our software  and therefore these type of scenarios could pave the way to possible attacks  such as CAPEC-108  Command Line Execution through SQL Injection  I hope to have done something pleasant  we Developers and Engineer do things with awareness and we will be safer

User · Answer

You need to enable it  Check out the Permission section of the xp cmdshell MSDN docs    http   msdn microsoft com en-us library ms190693 aspx   -- 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

User · Answer

You can also hide again advanced option after reconfigure   -- show advanced options EXEC sp configure  show advanced options   1 GO RECONFIGURE GO -- enable xp cmdshell EXEC sp configure  xp cmdshell   1 GO RECONFIGURE GO -- hide advanced options EXEC sp configure  show advanced options   0 GO RECONFIGURE GO

User · Answer

As listed in other answers  the trick  in SQL 2005 or later  is to change the  global configuration settings for show advanced options and xp cmdshell to 1  in that order   Adding to this  if you want to preserve the previous values  you can read them from sys configurations first  then apply them in reverse order at the end   We can also avoid unnecessary reconfigure calls   declare  prevAdvancedOptions int declare  prevXpCmdshell int  select  prevAdvancedOptions   cast value in use as int  from sys configurations where name    show advanced options  select  prevXpCmdshell   cast value in use as int  from sys configurations where name    xp cmdshell   if   prevAdvancedOptions   0  begin     exec sp configure  show advanced options   1     reconfigure end  if   prevXpCmdshell   0  begin     exec sp configure  xp cmdshell   1     reconfigure end     do work     if   prevXpCmdshell   0  begin     exec sp configure  xp cmdshell   0     reconfigure end  if   prevAdvancedOptions   0  begin     exec sp configure  show advanced options   0     reconfigure end   Note that this relies on SQL Server version 2005 or later  original question was for 2008

User · Answer

For me  the only way on SQL 2008 R2 was this    EXEC sp configure  Show Advanced Options   1     RECONFIGURE   WITH OVERRIDE       EXEC sp configure  xp cmdshell   1     RECONFIGURE   WITH OVERRIDE

[sql-server-2008] Enable 'xp_cmdshell' SQL Server

Examples related to sql-server-2008