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

SQL
8 Comments

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.

Solarwinds

Here is the command I am running.

xp_cmdshell 'dir c:\Windows'

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

SQL SERVER - FIX: Msg 15281- SQL Server Blocked Access to Procedure 'sys.xp_cmdshell' of Component 'xp_cmdshell' xp_cmd-error-01-800x85

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)

Solarwinds
, , ,
Previous Post
SQL SERVER – FIX: Msg 7356, Level 16 – The OLE DB provider “ASEOLEDB” for linked server “SYBASESERVER” supplied inconsistent metadata for a column
Next Post
SQL SERVER – Cannot open backup device. Operating system error 1326 (Logon failure: unknown user name or bad password.)

Related Posts

8 Comments. Leave new

Leave a Reply

Menu