Stored procedure with EXECUTE AS cannot run xp_cmdshell

Question

I have a sql server user, proxyAccount, which I configured as xp_cmdshell_proxy_account

In a stored procedure I use xp_cmdshell, and when I execute the stored procedure with this account, everything works fine.

However, if I add:

WITH EXECUTE AS 'proxyAccount'

to the stored procedure, I get the following error when I execute it:

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.

What might be the problem? Why can't proxyAccount run xp_cmdshell when set to EXECUTE AS, but being able to run it otherwise?

Answer 1

The problem was solved by the following steps:

1. Creating a new account that uses Windows Authentication (not using SQL Server Authentication)
2. Set this new account to xp_cmdshell proxy
3. Grant xp_cmdshell permission to the old proxyAccount