4

I try to enable xp_cmdshell in SQL Server. So I ran:

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

The returned message says:

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.

The facet properties shows "XPCmdShellEnabled"

However, when I execute 

EXEC master..xp_cmdshell 'dir c:'

I got the error message

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
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.

What I did is from Microsoft documentation. Why does it not work?

TT.
  • 15,774
  • 6
  • 47
  • 88
Shawn
  • 5,130
  • 13
  • 66
  • 109
  • What version and edition of SQL Server are you using? It looks like you did it right, and I can't see any reason for it not to work. – jpw Sep 25 '14 at 23:34
  • Hi, Thanks for the response. It's SQL Server 2008 R2. Is there any permission require to execute xp_cmdshell? – Shawn Sep 26 '14 at 15:58
  • SQL Server security isn't my strong side, but I think you need the `CONTROL SERVER` permission. It should be granted to _sa_ and administrators by default. – jpw Sep 26 '14 at 18:58

2 Answers2

11

Lets try this: Disable it, then re-enbable it.

--Disable
Use Master

GO
EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE

GO

EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

-- Enable
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
Anuj Tripathi
  • 2,251
  • 14
  • 18
Charles Farr
  • 369
  • 3
  • 8
3

You can do this from SQL Server Management Studio as follows:

  1. Right-click the server, and choose Facets
  2. Select Facet Surface Area Configuration
  3. Set property XPCmdShellEnabled to True

enter image description here

TT.
  • 15,774
  • 6
  • 47
  • 88
Vishe
  • 3,383
  • 1
  • 24
  • 23