Objective

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.

1 : Error messgge

Reason

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…

2 : Step 1 – Run  SP_Configure ‘Show advanced options’

3. Step 2 – Run RECONFIGURE

4. Step 3 – Run CONFIGURE ‘xp_cmdshell’ to Enable

In here you have top add “1” if you want to Enable the Feature. If you want to Disable the feature, then add “0”.

5. Step 4 – Again Run RECONFIGURE

6. Step 5 : Then Run the quarry you want to execute

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

Thanks !

References :

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/xp-cmdshell-server-configuration-option