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

    Domanda

  • 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

    martedì 3 aprile 2012 14:10

Risposte

  • 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

    • Proposto come risposta EitanBlumin martedì 10 aprile 2012 12:36
    • Contrassegnato come risposta Stephanie Lv mercoledì 11 aprile 2012 08:16
    venerdì 6 aprile 2012 03:04

Tutte le risposte

  • 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

    • Proposto come risposta EitanBlumin martedì 10 aprile 2012 12:36
    • Contrassegnato come risposta Stephanie Lv mercoledì 11 aprile 2012 08:16
    venerdì 6 aprile 2012 03: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


    • Contrassegnato come risposta Geri_ReshefMVP lunedì 6 aprile 2015 13:35
    • Contrassegno come risposta annullato Geri_ReshefMVP lunedì 6 aprile 2015 18:33
    lunedì 6 aprile 2015 13:31
  • On behalf of all the future readers of this script - thanks for the contribution.



    lunedì 6 aprile 2015 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
    lunedì 6 aprile 2015 15:28
  • Erland- thanks for your comments,
    and though the answer was unmarked - the thanks to Shamil remain..



    lunedì 6 aprile 2015 18:35