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?

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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).

Answer 5

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.

Answer 6

It's 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

Answer 7

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**