以不同于SSISDB的用户身份执行SSIS包 [英] Executing a SSIS package as different user from SSISDB

问题描述

我们有一个要求，即用户应使用代理帐户并引用输入参数来执行SSIS程序包.

We had a requirement that a SSIS package should be executed by the user using a Proxy Account and referencing an input parameter.

以下内容演示了用于调用程序包执行的语法...

The following demonstrates the syntax used to invoke the package execution...

DECLARE @ExportID INT = 1;
DECLARE @ExecutionID INT;

EXECUTE AS [proxy_account]  

EXEC [SSISDB].[catalog].[create_execution]
    @folder_name = 'DW',  
    @project_name = 'DW_ETL',  
    @reference_id = NULL,  
    @use32bitruntime = 1,  
    @execution_id = @ExecutionID OUTPUT;

EXEC [SSISDB].[catalog].[set_execution_parameter_value]  
    @execution_id = @ExecutionID,  
    @object_type = 30,  
    @parameter_name = 'ExportID',  
    @parameter_value = @ExportID;

EXEC [SSISDB].[catalog].[start_execution]  
    @execution_id = @ExecutionID;

REVERT

这导致出现以下错误消息:

This resulted in the following error message:

无法还原当前的安全上下文.请切换到调用"Execute As"的原始数据库，然后重试.

The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again.

通过追溯代码，在SSISDB.catalog.start_execution和SSISDB.internal.prepare_execution存储过程中找到了以下代码

Having traced the code through, the following code was found within the SSISDB.catalog.start_execution and SSISDB.internal.prepare_execution stored procedures

EXECUTE AS CALLER
...  
REVERT

这导致语句失败，因为该语句将覆盖尝试指定的代理帐户.通过注释掉SSISDB.catalog.start_execution和SSISDB.internal.prepare_execution中的REVERT语句，代码成功地作为代理帐户执行了.

This resulted in the statement failing as it was overriding the proxy account that was being attempted to be specified. By commenting out the REVERT statement in SSISDB.catalog.start_execution and SSISDB.internal.prepare_execution, the code ran through successfully executing as the Proxy account.

我不希望绕开开发人员出于某种原因而放入的代码的想法，但是我需要一种方法来通过存储过程作为代理帐户执行语句，并且该方法有效.任何人都可以建议使用不引用REVERT的SSISDB.catalog.start_execution和SSISDB.internal.prepare_execution存储过程的替代版本是否有任何后果?

I'm not keen on the idea of bypassing code that a developer has put in for a reason, but I need a means to execute the statement through a stored procedure as the proxy account, and this method works. Can anyone advise if there would be any consequence to using an alternative version of the SSISDB.catalog.start_execution and SSISDB.internal.prepare_execution stored procedures that does not reference REVERT?

谢谢

伊恩

推荐答案

要以不同于SSISDB的用户身份执行SSIS包，您需要利用SQL Server代理中的某些功能.

To achieve executing an SSIS package as a different user from SSISDB you need to utilize some functionality from the SQL Server Agent.

您需要创建一个具有db_datareader访问SSIS db的凭据.您需要设置一个有权执行Execute Integration Services软件包的代理.

You need to create a credential that has db_datareader access to SSIS db. You need to setup a Proxy that has rights to Execute Integration Services Packages.

最后，完成这些先决条件设置后，您可以创建一个SQL Server代理作业，该作业以您设置的任何代理帐户身份从Integration Services目录运行您的SSIS包.

Finally, once you have those pre-requisites setup you can create a SQL Server Agent job that runs your SSIS package from the Integration Services Catalog as whatever proxy account you setup.

这里是一篇文章，提供了有关如何设置我上面描述的内容的更详细方法: https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-服务器代理使用代理帐户/

Here is an article that gives a more detail approach on how to setup what I was describing above: https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

这篇关于以不同于SSISDB的用户身份执行SSIS包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！