When I was trying to execute an .exe file in my PC using xp_cmdshell command, I got an error which says the sys.xp_cmdshell component is turned off.
Ans Here is the error message…
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.
By Default, xp_cmdshell is Disabled in SQL Server. So We have to enable it to use the feature.
What is xp_cmdshell ?
Spawns a Windows command shell and passes in a string for execution. Any output is returned as rows of text.
In another way xp_cmdshell is a alternative of DOS prompt for SQL Server.
To Learn more use below link…
Reference : docs.microsoft.com
How to Fix the issue ?
Then what I did is Enable the xp_cmdshell using SQL Server configure option.
Here are Steps to do it…
3. Step 2 – Run RECONFIGURE
In here you have top add “1” if you want to Enable the Feature. If you want to Disable the feature, then add “0”.
Then the quarry will run properly.
-- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1; GO -- To update the currently configured value for advanced options. RECONFIGURE; GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1; GO -- To update the currently configured value for this feature. RECONFIGURE; GO