We are upgrading our system from SQL Server 2005 to R2.
We have small module which executes XP_CmdShell through Service Broker (using the Procedure_Name option in the Queue deffinition).
In 2005 it works fine, but in R2 it fails if I don't define a Credential for the XP_CmdShell Proxy (##xp_cmdshell_proxy_account##' credential).
I tried to override it using 'Execute As' with different Logins (me and other sysadmins) but it fails, though the XP_Cmdshell work fine through the SSMS with or without 'Execute As'.
A solution exists: I should use sp_xp_cmdshell_proxy_account system procedure to create a proxy, but I still don't understand why the XP_CmdShell doesn't behave inside the Service Broker in the same way as it behave outside (in the SSMS);
and why this problem doesn't occur in 2005.
(this is a revised version of a previous question in the Security Forum)
For a login with sysadmin fixed server role, it will use the SQL Server service account to execute this command. If this case, the service account should be a login in SQL Server instance, granted execute permission on XP_CmdShell, and mapped a database
user in master database.
For a login with non-sysadmin fixed server role, you are required to create a Windows account as the proxy account, with the same configurations as the service account to execute the XP_CmdShell command.
In addition, please make sure that the Windows account has permissions on the target folders if you are trying to read or write on them.