When a user that is not a member of the sysadmin fixed server role tries to use xp_cmeshell command, we need to set up a credential ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.
We use this system stored procedure to set up the credential:
EXEC sp_xp_cmdshell_proxy_account 'DomainName\AccountName', 'Password';
While you are trying to set up the XP_CMDShell proxy account in SQL SSMS on Windows Server 2008 R2, you might get the error message below:
Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1
An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: '5'.
This error message indeed didn't tell you what is the cause of the problem, as you might use the valid windows account and password but you were still not able to create the credential.
From what I experienced, the problem comes from the windows server UAC. It is because the user account you are running SSMS doesn't have the permission to create the credential, you need to run SSMS as Administrator as below:
After SSMS is running at as administrator, I rerun the same stored procedure. it is successful. I hope this helps you as well.