5

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

Ian Mitchell
  • 83
  • 1
  • 7
  • Do you have SQL Agent running on that particular SQL Server? You could run the package with a job instead, which will allow you to specify the proxy account, then use sp_start_job to run the job. – Jeff Lewis Aug 17 '18 at 17:28

1 Answers1

3

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/

vvvv4d
  • 3,881
  • 1
  • 14
  • 18
  • I have configured credentials, proxy, job step to run as the proxy user. I get an 'Unexpected termination' error when I run the job. Suppose I add the credential user to the computer's administrators group then it works OK. Any idea what is the minimum permission required by the credential user ? – variable Apr 05 '21 at 14:21
  • @variable did you give the credential a log in on the server and db_datareader to SSISDB database? I'm guessing it worked when you added the credential account to the administrators group because the administrators group is setup with sysadmin server role? – vvvv4d Apr 05 '21 at 15:40
  • I have given the credential login on the server. And had added read/execute permissions on the ssis package folder properties. There was no db_datareader on the SSISDB database. I have now given this permission, but the same issue persists. I think that there is no need to give this permission when permission is already assigned at the ssis catalog folder level. – variable Apr 05 '21 at 16:06
  • @variable In addition to creating the credential, create an actual login/user for the same account used in the credential. When you create the user, on the user mapping tab give it db_datareader to SSISDB. There is also a role ssis_admin you might need to give the account. – vvvv4d Apr 05 '21 at 16:27
  • It looks more like file system error - I have detailed here: https://stackoverflow.com/questions/66954755/what-are-the-minimum-permissions-to-resolve-the-unexpected-termination-error-w – variable Apr 05 '21 at 16:41