February 22, 2017 Leave a comment
Problem: When trying to create a xp_cmdshell proxy account in SQL Server 2014 using the sp_xp_cmdshell_proxy_account procedure, you may get an error.
sp_xp_cmdshell_proxy_account ‘Domain\DomainUser’, ‘password’
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’.
There are several explanations for the error but in my case it was down to UAC permissions. If you launch SQL Enterprise Manager as an Administrator the command runs. If you don’t run as administrator internally the API call to LsaOpenPolicy fails.
However, there is also a workaround command that works regardless of being logged into SSMS as an administrator:
create credential ##xp_cmdshell_proxy_account## with identity = ‘Domain\DomainUser’, secret = ‘password’