In one of our recent migrations, we got the following error when the client tried to fire xp_cmdshell system stored procedure through some client code.
Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the ‘##SQL Server xp_cmdshell_proxy_account##’ credential exists and contains valid information.
So from Books online, this is what we got:
When xp_cmdshell is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.
The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this stored procedure takes a Windows user name and password. For example, the following command creates a proxy credential for Windows domain user SHIPPING\KobeR that has the Windows password sdfh%dkc93vcMt0.
So this is what we did:
EXEC sp_xp_cmdshell_proxy_account 'HOCBASE\admin', 'account_password'; GO EXEC sp_xp_cmdshell_proxy_account 'HOCBASE\admin', 'account_password'; GO
And things worked fine. A few more things:
How do you drop the proxy credential?
EXEC sp_xp_cmdshell_proxy_account NULL; GO
How can you find all the users (except sysadmins) who can execute or have access to xp_cmdshell?
Use master EXEC sp_helprotect 'xp_cmdshell'
And who are all the sysadmins on the box?
Use master EXEC sp_helpsrvrolemember 'sysadmin'