We are testing a 3rd party tool to schedule/execute jobs in our environment so trying to test launching SSIS packages within the integration service catalog. I have found examples of the code to run the procedures to execute the SSIS jobs which works fine but I don’t want to grant the 3rd party’s user account full access so like to wrap a procedure around the SSISDB.catalog procedures necessary to run the SSIS job using ‘execute as’.
On a dev server I created the test proc within the SSISDB DB (will change this to a separate db later, just trying make this work) using ‘execute as’ referencing a sql account but got error saying it must run as an AD account. I then tried using a full AD admin account on the box as the ‘execute as’ but get errors like: Msg 27146, Level 16, State 1, Procedure create_execution, Line 180
Cannot access the package or the package does not exist. Verify that the package exists and that the user has permissions to it.
Hoping to find out if someone has done something similar and the steps they have taken as I’m concerned I’ll over complicate this or grant more permissions to accounts than necessary.
I appreciate any help or being pointed in the right direction!
Thanks,
Jeff
Best Answer
figured out the issue. didn't know the ssisdb procedures already were doing 'execute as' and didn't know you needed to use an AD account for impersonation to work.