none
Differences in XP_CmdShell executions through Service Broker between 2005 and R2

    问题

  • 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.

    Any suggestions?
    (this is a revised version of a previous question in the Security Forum)

    Geri Reshef http://gerireshef.wordpress.com

    2012年4月3日 14:10

答案

  • Hi Geri,

    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.


    Stephanie Lv

    TechNet Community Support

    2012年4月6日 3:04

全部回复

  • Hi Geri,

    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.


    Stephanie Lv

    TechNet Community Support

    2012年4月6日 3:04
  • Hi Geri,

    I know this is late, but I ran into the same issue last week. After days of research, forms, experiments, I found that you should make the database trustworthy property to "ON" in order to xp_cmdshell to work properly through the Service Broker.

    Thanks and Regards,
    Shamil


    2015年4月6日 13:31
  • On behalf of all the future readers of this script - thanks for the contribution.



    2015年4月6日 13:37
  • On behalf of all the future readers of this script - thanks for the contribution.

    To bad that the solution that Shamil found is a bad one, as it opens a security hole.

    The correct solution is to use certificate signing, which I discuss in great detail in this article on my web site:
    http://www.sommarskog.se/grantperm.html
    (Although there is no example for this particular problem.) In this article, I also explain why TRUSTWORTHY is bad.

    Geri, can you make the world a service and unmark Shamil's response as an answer?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    2015年4月6日 15:28
  • Erland- thanks for your comments,
    and though the answer was unmarked - the thanks to Shamil remain..



    2015年4月6日 18:35