SQL SERVER – FIX: Msg 15281- SQL Server Blocked Access to Procedure ‘sys.xp_cmdshell’ of Component ‘xp_cmdshell’

One of my blog readers pinged me on Skype and asked a simple question. I always thought I had a blog written about this error, but I was wrong. So, this blog is the outcome of a short interaction with my blog reader. Let us learn about how to fix blocked access error.

Hi Pinal,
I am getting below error in SQL.

Msg 15281, Level 16, State 1, Procedure xp_cmdshell
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’, search for ‘xp_cmdshell’ in SQL Server Books Online.

Here is the command I am running.

xp_cmdshell 'dir c:\Windows'

We can easily reproduce the error if the setting is OFF.

As we can see that the error message is useful and tells what exactly needs to be done. But for someone who is new to SQL Server, above may not be enough.

SOLUTION / WORKAROUND

Here is the query we need to run in SQL Server Management Studio.

-- We need to have this ON because xp_cmdshell is an advanced option.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured values for sp_configure
RECONFIGURE WITH OVERRIDE
GO
-- Now, enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured values for sp_configure
RECONFIGURE WITH OVERRIDE
GO

After completing above query, we should be able to use xp_cmdshell without above error.

Reference: Pinal Dave (https://blog.sqlauthority.com)