Executing a SSIS package as different user from SSISDB

Question

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:

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

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

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.

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?

Thanks,

Ian

Answer 1

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

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.

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.

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/