martedì 3 aprile 2012 14:10We 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)
Geri Reshef http://gerireshef.wordpress.com
Tutte le risposte
venerdì 6 aprile 2012 03:04Moderatore
It can be the permission issue of XP_CmdShell.
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.
For more information, please pay attention to Tibor's blog: Xp_cmdshell and permissions.
TechNet Community Support