Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday, 10 December 2013

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 Resolved issues

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

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 '##xp_cmdshell_proxy_account##' credential exists and contains valid information.


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:

1
EXEC sp_xp_cmdshell_proxy_account 'HOCBASE\admin', 'account_password';
2
GO

The effect:



And things worked fine.

 A few more things:

How do you drop the proxy credential?
1
EXEC sp_xp_cmdshell_proxy_account NULL;
2
GO

How can you find all the users (except sysadmins) who can execute or have access to xp_cmdshell?
1
Use master
2
EXEC sp_helprotect 'xp_cmdshell'

And who are all the sysadmins on the box?

1
Use master
2
EXEC sp_helpsrvrolemember 'sysadmin'