EXECUTE XP_CMDSHELL error - SQL Server blocked access to procedure 'sys.xp_cmdshell'

I am in the middle of migrating our data warehouse to a new server and came across this issue when migrating one of our existing SQL jobs which had been setup by our DBA (who has since left the company).

The SQL job was a simple maintenance script which ran weekly and deleted old archived CSV files from a specified folder which were over a certain age.

The script was throwing the following error:

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.

xp_cmdshell is a server configuration option introduced in SQL Server 2005 which enables system administrators to control wehter the xp_cmdshell extended stored procedure can be executed on a system. By default, it is disabled on new installations but can be enabled by running the sp_configue system stored procedure as shown in the script below:

-- 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

This has resolved the initial issue with the SQL job failing.

In addition, I had to provide SQL DB engine service account access to the archive folder where the CSV files were stored in order for the script to have sufficient access to delete the files within.

I have done some other brief research on the subject and it appears that enabling xp_cmdshell access is generally regarded as somewhat of a security risk, but only as far as access to system administrative privileges is a security risk. The risk can be mitigated by maintaining good security protocols and processes within the organisation.