locked
Using XP_CmdShell: are there differences between 2005 and R2? RRS feed

  • Question

  • We are upgrading now our system from SQL Server 2005 to 2008 R2. 

    We are using in our code XP_Cmdshell. 

    In the new server we enabled it using XP_Configure. 

    When I use it directly from the SSMS it works fine (I'm sa). 

    When I use it through Service Broker (it is executed in a different session under different identity) I receive the following error message: 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. 

    I created a credential according the error message and so far it works fine. 

    My question: in SQL Server 2005 I didn't have to create a credential or any other configuration appart of SP_Configure (as long as I remmember). 
    Is there any difference in the Security deffinitions of R2 which cause this behaviour? 
    Can I override this problem and execute XP_CmdShell through Service Broker? 
    Why is there such a difference? In both situations the XP_CmdShell should run under the same identity and with the same privileges!

    Geri Reshef http://gerireshef.wordpress.com

    Monday, April 2, 2012 11:45 AM

Answers

  • Geri shalom

    AFAIK , there is no difference. I have not used Service Broker, but same in SS2005 if we want that none authorized users run xp_cmdshell we need to create a proxy account exact as you did in SS2008R2

    BTW, SS2012 is already here why not upgrading to a newer version ? :-)


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/


    Monday, April 2, 2012 12:02 PM
  • Hi Geri,

    I believe you can define the user under which the reader procedure will be executed.

    If I remember correctly, when you create a SB queue and define a procedure, you can also define something like "EXECUTE AS" and then define any user you wish.


    Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

    • Marked as answer by Geri_Reshef Monday, April 2, 2012 6:53 PM
    Monday, April 2, 2012 2:02 PM

All replies

  • Geri shalom

    AFAIK , there is no difference. I have not used Service Broker, but same in SS2005 if we want that none authorized users run xp_cmdshell we need to create a proxy account exact as you did in SS2008R2

    BTW, SS2012 is already here why not upgrading to a newer version ? :-)


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/


    Monday, April 2, 2012 12:02 PM
  • Hi Geri,

    I believe you can define the user under which the reader procedure will be executed.

    If I remember correctly, when you create a SB queue and define a procedure, you can also define something like "EXECUTE AS" and then define any user you wish.


    Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

    • Marked as answer by Geri_Reshef Monday, April 2, 2012 6:53 PM
    Monday, April 2, 2012 2:02 PM