SSIS Catalog view is not executing from Store procedure calling from a service broker

  • Hi ,

    I have a package deployed on SSISDB (the new concept in MS SQL 2012, SSIS catalogs). I have t-sql code in which i will be able to execute SSIS package in SSISDB with no problems. But if i place the same t-sql code inside of a procedure which will be called by a service broker , the code is not executing.

    I am using the following code to execute a package in the SSISDB catalog

    Declare @execution_id bigint

    EXEC [SSISDB].[catalog].[create_execution] @package_name=N'LoadToABC.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'ABC', @project_name=N'LoadToABC',

    @use32bitruntime=False, @reference_id=Null

    DECLARE @var0 NVARCHAR(200) = N'D:\MyData\SampleText20120830100001.txt'

    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'strFileName', @parameter_value=@var0

    EXEC [SSISDB].[catalog].[start_execution] @execution_id

    This code executes if run it alone or placed in a regular stored procedure , but not executes if i palce this same code inside of a procedure which is being called/executed by a service broker call like this:

    CREATE QUEUE dbo.SampleQueue WITH STATUS=ON, ACTIVATION

    (STATUS = ON, MAX_QUEUE_READERS = 1,

    PROCEDURE_NAME = spMessageProcSample, EXECUTE AS OWNER);

    The problem occurs if we call the SSIS catalogs inside a proc which will be calling through a service broker queue.

    I am running all these steps on my local instance of SQL SERVER 2012 in which i am the administrator.

    Please advice where i am doing wrong ?

    Thanks,

    Kumar

  • The service-broker procedure or executor needs to be authorized to execute these commands.

    And if it is running from a database other than your [SSISDB], then it will need either Cross-Db chaining or Certificate authorization to span both databases, and then that other database will need to be set to TRUSTWORTHY to make either of those work as well.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Hi RBarryYoung,

    Thank you for replying. Yes the PROC executing/calling by Service broker is on different database (FileTableDB).

    While i see the server log it is:

    The activated proc '[dbo].[spMessageProcSample]' running on queue 'FileTableDB.dbo.SampleQueue' output the following: 'The server principal "fl\user2012" is not able to access the database "SSISDB" under the current security context.'

    Do i need to use the password(/key) that i used when i setting up the SSISDB catalog/database to connect to FileTableDB database ?

    Can you please explain a little more.

    Thanks,

    Kumar

  • kumar-378458 (8/31/2012)


    ...

    While i see the server log it is:

    The activated proc '[dbo].[spMessageProcSample]' running on queue 'FileTableDB.dbo.SampleQueue' output the following: 'The server principal "fl\user2012" is not able to access the database "SSISDB" under the current security context.'

    Do i need to use the password(/key) that i used when i setting up the SSISDB catalog/database to connect to FileTableDB database ?

    No, the problem is exactly what I said above. You need to set it up to use either Cross-DB chaining or Certificate authorization so that it can get into [SSISDB] when running as an activated procedure. Both of those option also require that the source database ([FileTableDB]) be set to TRUSTWORTHY.

    As the setup for either of those is involved and as I have to leave for an airplane in about 10 minutes, I am going to have to let someone else explain the details.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Hi RBarryYoung,

    As you suggested I tried with Cross-db changing setup. But no luck. I got the same error. Can you please explain step by step code to do this ? I think I am doing somewhere wrong.

    Thanks,

    kumar.

  • kumar-378458 (9/4/2012)


    Hi RBarryYoung,

    As you suggested I tried with Cross-db changing setup. But no luck. I got the same error. Can you please explain step by step code to do this ? I think I am doing somewhere wrong.

    Thanks,

    kumar.

    Do both databases have the same owner? I forgot to mention it, but practically-speaking, this is a requirement also.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Yes RBarryYoung. I only (with owner "fl\user2012" as windows authenticated , admin access ) created/setup everything and i am the owner of all the objects.

    Thanks,

    kumar

  • Is CrossDB chaining set on both databases? Or is it set for the whole Sever?

    And are you still getting the same error?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • kumar-378458 (9/4/2012)


    Yes RBarryYoung. I only (with owner "fl\user2012" as windows authenticated , admin access ) created/setup everything and i am the owner of all the objects.

    Could you define "admin access"? Do you mean sysadmin on the server or ssis_admin in the SSIS Catalog? Either of these should be sufficient permissions to execute the SSIS stored procedures.

    [font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
    Business Intelligence Administrator
    MSBI Administration Blog

Viewing 9 posts - 1 through 8 (of 8 total)

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