Recently, when I tried to run a BCP command using XP_CMDSHELL on a newly setup SQL Server, I got the below specified error saying that the xp_cmdshell is turned off.
Msg 15281, Level 16, State 1, Procedure sys.XP_CMDSHELL, Line 1
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.
To solve this problem, you just need to enable XP_CMDSHELL in the configuration. Here are the T-SQL statements to enable the command shell using sp_configure. You can run these commands either on SSMS or Azure Data Studio.
/* Enable XP_CMDSHELL if it is disabled */ EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'xp_cmdshell', 1; GO RECONFIGURE; GO
- More about XP_CMDSHELL at Microsoft Docs.