SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’

by
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell'

In this article, I will explain how I encountered the error “SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’.” Also, I will explain what I did to resolve this error.

Problem:

Recently, when I was working with SQL Server stored procedures, I wanted to create a stored procedure that creates a directory in the local file system. I searched on the web, how to create a folder in the Windows local file system.

I found that there is a system-defined stored procedure in SQL server named xp_cmdshell that we can use to execute the commands on the Windows command line. Therefore, I tried to pass the Windows command to create a folder to the stored procedure xp_cmdshell. But when I tried to create the folder, I got the following error message:

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell'
The Error Message

I was trying to execute the command as:

USE master
GO
EXEC xp_cmdshell 'MD "C:/NewFolder"'

Solution: SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’

After some research on the internet, I found that before using the xp_cmdshell stored procedure, I need to enable it. Also, it is an advanced option. You cannot enable it directly. First, you have to enable the Show Advanced Options. Only then, you can enable the xp_cmdshell.

I used the following commands to enable these options:

EXEC sp_configure 'Show Advanced Options', '1'
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', '1'
RECONFIGURE
GO

After executing the above commands, I was able to resolve the error and I was able to use the xp_cmdshell and created a folder in the local file system.

You may like the following SQL server tutorials:

This is how to fix the error, SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’.