sql-server - 从 SQL Server Service Broker 调用带有 SSISDB 实现的 SSIS

Question

要求是通过 SSIS 调用 Web 服务，并从 SQL Server Service Broker 激活的存储过程调用 SSIS。

这是我目前正在做的事情:

队列

   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) 

我的存储过程:

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

现在这是 RunSSIS 存储过程的样子

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

现在，这将在事件查看器中引发以下异常，因为 SSISDB 环境中无法识别 Sql 服务代理激活安全上下文。

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.'

为了解决这个问题，我尝试了以下方法

• 所以我点击这个链接 http://www.databasejournal.com/features/mssql/article.php/3800181/Security-Context-of-Service-Broker-Internal-Activation.htm并创建了一个具有自签名证书的用户(认为它是没有权限的用户)。但它返回相同的错误，深入挖掘，我发现 [internal].[prepare_execution] 在SSISDB 在第 36 行有“REVERT”语句，抛出错误为它根本不喜欢模仿。

  • 我尝试将 RunSSIS 存储过程移动到 SSISDB 并尝试从激活存储过程中调用它，它被击落为 SSISDB，它不允许任何具有 SQL Server 身份验证的用户，它需要具有 Windows 身份验证和用户由证书创建的显然没有 Windows 凭据。

我的问题是

• 我走的路正确吗？我当然不认为同时使用 SQL Server 的 2 个组件会那么困难。
• 如果方法不正确，从服务代理调用服务的最佳方法是什么？我已经看到 SQL Server Service Broker 的“外部激活”，但还没有探索过。但我会尝试坚持使用服务器环境内且可扩展的东西，并且不喜欢在产品环境中安装不同组件的想法(这始终是支持个人的开销，因为还有一点可以失败)

我正在使用 Windows 身份验证，并且我的凭据具有 sys_Admin 访问权限。

Answer 1

我认为您可以取出“WITH EXECUTE AS CALLER”，所有内容(过程以及最终被调用的包)都将在 Service Broker 的安全上下文下运行。只要该上下文有权执行您想做的事情，就应该没问题。

我没有以这种方式使用 Service Broker，但我对 SQL 代理触发的作业做了同样的事情。只要代理的安全上下文具有 procs/packages 中所需的权限，一切就可以正常运行。我们的服务使用网络帐户，因此服务器之间也能正常工作。