Post reply

SP with xp_cmdshell gives an error in the MessageQueue, but executes successfully in SSMS!!

  • April 2, 2015 at 1:42 pm

    #295637

    Hi All,

    I have a SP, which calls xp_cmdshell in it. This SP is being used in a MessageQueue.

    The SQL server and Agent services are configured using a Domain service account, and this service account has a login under the sysadmin server role as well.

    When the MessageQueue is invoked, the SP returns the following exception :

    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.

    But when I execute the SP in SSSMS, under the service account login (which is a sysadmin), it successfully executes.

    I even created the ##xp_cmdshell_proxy_account## credential, but the Message Queue never stops giving this error.

    This is how the queue looks like :

    CREATE QUEUE [dbo].[TestxmCmdshellQueue]

    WITH STATUS = ON ,

    RETENTION = OFF ,

    ACTIVATION ( STATUS = ON ,

    PROCEDURE_NAME = [dbo].[SpWithxp_cmdshell_Init] ,

    MAX_QUEUE_READERS = 5 ,

    EXECUTE AS N'dbo' ),

    POISON_MESSAGE_HANDLING (STATUS = OFF)

    The Service account is configured as a Local Administrator in the Local Server as well.

    I am running SQL Server 2008 R2 Sp2 on a Windows Server 2008 R2 Enterprise Machine.

    Please let me know what I am missing here.

  • April 2, 2015 at 6:02 pm

    #1788450

    Is the owner of the database a member of the sysadmin role?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 2, 2015 at 9:37 pm

    #1788455

    Yes Jeff.

    The owner of the database is "sa"

  • April 6, 2015 at 7:22 am

    #1788903

    Hi All,

    I Made TRUSTWORTHY = ON in the database and the SP executed without any problem by the Message Queue.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply