Enable xp_cmdshell does not work

Question

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?

Answer 1

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

Answer 2

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

---