SQL Server xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the ‘##xp_cmdshell_proxy_account##’ credential exists and contains valid information

Hi Friends,

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'