How to enable XP_CMDSHELL in SQL Server?

Issue

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.

Solution

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

Reference

• More about XP_CMDSHELL at Microsoft Docs.