15

The requirement is to call a web service through SSIS and calling the SSIS from a SQL Server Service Broker activated stored procedure.

Here is what I have currently doing:

Queue

   CREATE QUEUE [schema].[ProccessingQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION (  STATUS = ON , PROCEDURE_NAME = [schema].[usp_ProccessingQueueActivation] , MAX_QUEUE_READERS = 10 , EXECUTE AS N'dbo'  ), POISON_MESSAGE_HANDLING (STATUS = ON)

My stored procedure:

ALTER PROCEDURE [schema].[usp_ProccessingQueueActivation]
WITH EXECUTE AS CALLER
AS

BEGIN
    SET NOCOUNT ON;

    <snip declaration>
    BEGIN
        BEGIN TRANSACTION;

            WAITFOR
            (
                RECEIVE TOP (1)
                    @ConversationHandle = conversation_handle,
                    @MessageBody = CAST(message_body AS XML),
                    @MessageTypeName = message_type_name
                FROM [schema].[ProccessingQueue]
            ), TIMEOUT 5000;

            <snip awasome stuff>
                EXEC dbo.RunSSIS <param>

                DECLARE @ReplyMessageBody XML = @MessageBody;
                SEND ON CONVERSATION @ConversationHandle MESSAGE TYPE [type] (@ReplyMessageBody);
            END

            <handle error>

        COMMIT TRANSACTION;
    END
END

Now here is what RunSSIS stored procedure looks like

ALTER PROCEDURE [dbo].[RunSSIS]
      <params>
AS
BEGIN
       DECLARE @exec_id BIGINT

       EXEC [SSISDB].[catalog].[create_execution] 
    @package_name=N'<SSIS_package>', 
    @folder_name=N'<folder>', 
    @project_name=N'<projectName>',
    @use32bitruntime=FALSE, 
    @reference_id=NULL,             
    @execution_id=@exec_id OUTPUT   

       EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
       @exec_id, 
       @object_type=30, 
       @parameter_name=N'<param_Name>', 
       @parameter_value=<param>

       SELECT @exec_id

       EXEC [SSISDB].[catalog].[start_execution] @exec_id
END

Now this will throws the below exception in event-viewer as the Sql service broker activation security context isn't recognized in SSISDB environment.

The activated proc '[schema].[usp_ProccessingQueueActivation]' running on queue '' output the following: 'The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again.'

To resolve the problem I have tried those following approach

  • So I follow this link http://www.databasejournal.com/features/mssql/article.php/3800181/Security-Context-of-Service-Broker-Internal-Activation.htm and created a User with a self signed certificate (thinking that it is user that doesn't has permission). But it is returning same error, digging deeper I found that [internal].[prepare_execution] in SSISDB has "REVERT" statement in line no 36 that throws the error as it doesn't like Impersonation at all.

    • I tried to move the RunSSIS stored procedure to SSISDB and try to call it from activation stored procedure, it was shoot down as SSISDB it doesn't allow any user with SQL Server auth, It needs to have a Windows auth and User created by Certificate obviously doesn't has windows credential.

My question is

  • Am I on the correct path? I certainly doesn't anticipate using 2 component of SQL server together would be that difficult.
  • If not in correct approach what would be best approach to call a service from Service broker? I have seen "External Activation" for SQL Server Service broker but haven't explored is yet. But I would try to stick to something that lives inside server environment and scale-able, and don't like the idea of installing different component in prod environment (it is always a overhead for support personal,as there is one more point which can fail)

I am using Windows auth and my credential has sys_Admin access.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sukanta
  • 151
  • 6

2 Answers2

0

I think you can take out the "WITH EXECUTE AS CALLER" and everything (the proc and then the package that ends up getting called) will be run under the security context of the Service Broker. As long as that context has the permissions to do what you want to do, you should be fine.

I have not used a Service Broker in this way, but I do the same thing with jobs fired off by the SQL Agent. As long as the Agent's security context has the permissions needed in the procs/packages everything runs fine. We use network accounts for our services so it all works between servers as well.

btberry
  • 377
  • 1
  • 7
  • It is kind of late for answering it, We actually has ditched the whole calling SSIS -> webservice from Sql service broker and goes with SQL CLR route. to secure the call, We disable the anonymous calling, and force Windows login, Given that it is intranet site, and also we expect everybody calling the website should login, This is working. But I still fill this is a missed opportunity from microsoft to actually create a true push event based system from Database – Sukanta Aug 26 '16 at 02:29
  • for the record, without the EXECUTE AS clause, cross-database module signing won't work from a service broker activation procedure – Mark Sowul Jun 13 '19 at 02:09
  • And EXECUTE AS CALLER won't work for that, it has to be some specific user – Mark Sowul Jun 13 '19 at 20:36
0

This has a code smell of tight coupling and my first instinct is to decouple the queue, the DB that houses the proc, and the SSIS execution into a PowerShell script. Have the script get the messages from service broker then call SSISDB on a different connection without wrapping [catalog].[create_execution] and [catalog].[set_execution_parameter_value] in a stored proc. You can still run this script directly from Agent.

This approach gives you the most flexibility with regard to security contexts, if one of the components moves to a different server, if something is named differently in dev/QA, or technologies change (Azure ServiceBus instead of Broker for instance). You can also get creative with logging/monitoring.

Ben Campbell
  • 368
  • 3
  • 8