The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and con

Error Message:
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

Root Cause:
If the SQL Server login who is executing xp_cmdshell is sysadmin, then SQL Server will use the service account (it will not "pretend to be somebody else"). But if the login isn't sysadmin, then we need to configure what Windows account to be used (using sp_xp_cmdshell_proxy_account). Note that this configuration is the same for all non-sysadmins!

Solution:
1. xp_cmdshell has to be enabled (easiest way is in Surface Area Config Tool)
2. The login has to be granted access to the master database and the user it maps in as must have EXECUTE rights against xp_cmdshell.
3. The SQL Server Agent proxy account must be set correctly.

Quick T-SQL:
Here's the TSQL script that does all above:
--1, allow xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

--2, grant permission to xp_cmdshell
USE master
CREATE LOGIN JohnDoe WITH PASSWORD = 'jlkw#.6('

--Note, we are in the master database
CREATE USER JohnDoe FROM LOGIN JohnDoe

--Run as login x
EXECUTE AS login = 'JohnDoe'

--Below fails, no execute permission on xp_cmdshell
EXEC xp_cmdshell 'DIR C:\*.*'
REVERT
GO

--Note, we are in the master database!!!
GRANT EXECUTE ON xp_cmdshell TO JohnDoe

--Try again
EXECUTE AS login = 'JohnDoe'

--Execution of xp_cmdshell is allowed.
--But I haven't configured the proxy account...
EXEC xp_cmdshell 'DIR C:\*.*'
REVERT
GO

--3, specify the proxy account for non-syadmins
--Replace obvious parts!
EXEC sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd'
EXECUTE AS login = 'JohnDoe'

--Execution of xp_cmdshell is allowed.
--And executes successfully!!!
EXEC xp_cmdshell 'DIR C:\*.*'
REVERT

--Cleanup (Drop the proxy account)
EXEC sp_xp_cmdshell_proxy_account null

DROP USER JohnDoe
DROP LOGIN JohnDoe
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE

Links:
Enabling xp_cmdshell Option
Setting up SQL Server Agent proxy account
Advanced options in SQL Server to avoid setting up Proxy Account