I'm having issues with allowing my users to execute SSIS Packages in a reasonable manner due to varying levels of privilege required.

The scenario: we've created a data warehouse, with two different SSIS packages responsible for loading it with data, one is to be run automatically (via a SQL Agent job, and is working fine), and another that must be run on-demand by users once upstream data is finalised and cleansed etc.

This package performs very privileged operations including backing up the database at the start of the run (to be sure, to be sure), dropping and recreating calculated tables etc.

I've written a stored procedure to execute this job via the [SSISDB].[catalog].[create_execution] and [SSISDB].[catalog].[start_execution] stored procedures…. this works fine when run under my account (I'm a sysadmin).

The stored procedure failed when run by a normal user due the higher level of permissions required in SSISDB and MSDB to enqueue the execution, and the package itself failed because it is running under their (lowly) security context.

What I've tried:

I attempted to resolve the issue using 'Execute As' in the stored procedure, however this failed due to the cross-database chaining issues, Trustworthy flag etc.

I've also attempted to solve the issue by having Agent jobs to run the package, and just running the agent job from the stored procedure, however I rapidly entered a world of pain involving:

• The inability to set execution permissions on a per-job basis
• The hope to configure this access via a central Server Role to cater for changing staff over time, and jobs can only have a single user as owner
• The dark world of Proxy accounts, Credentials in combination with sql-auth logins etc

Plans C and D

The only options I can think of remaining to me are to create a dedicated SQL Server Login with elevated permissions, and trust the users not to pass the credentials around/lose auditability of who scheduled the import (how this problem is solved in other areas of the organisation), or custom build a web front end purely to allow the users to authenticate as their 'Server Role' account, and then let the web app run the stored procedure under a second (privileged) connection.

So….

Is there any advice out there on how to:

• have an SSIS package perform privileged operations
• executed by a lowly privileged user (using an AD windows account)
• preferably where the access to run the job is managed via a central Server Role (I don't have an easy ability to create a new windows group for them)
• and where any new, intermediate/proxy accounts are SQL Server Auth accounts (again, very limited ability to make changes to the AD)

I understand there's a lot of moving parts here (and some feel like spinning blades) so let me know if there's any other info you feel I've missed.

Cheers,
Tim

Edit….

So today I created a dedicated SQL Server login with ssis_admin permissions, created three SQL Server Agent jobs owned by that user, and updated the stored procedure my end users call to execute as that user. This failed due to the inability to call create execution as a SQL Server login, it requires a windows account.

I updated the users stored procedure to execute as the Windows account SQL Server is running as (an AD service account), granted it ssis_admin and it fails with the error

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

This isn't going anywhere fast 🙁