Executing SSIS Package from a stored procedure with different user privileges

Question

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 :(

Answer 1

For posterity I got this working via the following:

• Altering the stored procedure called by the users (Admin.RunImport) to 'Execute as' the account used by the SQL Service
• The SQL Server service account (an AD Managed Service account) is modified to have permissions to execute the Admin sproc allowing the use of execute as above
• The SQL Server service account is modified to have ssisdb.ssis_admin and msdb.SQlAgentOperator roles.
• This Admin.RunImport stored procedure queues up one of 3 Agent Jobs using sp_start_job dependent on a passed parameter
  • This redirection via SQL Agent is required to bypass the ssis security context error above
  • The agent jobs are owned by 'sa', and simply execute an underlying stored procedure (Raw.hp_Execute_Import_Impl) passing in a different parameter per job.
  • This means that the Agent job runs as sa due to the ssis_admin privelege above, the same as the scheduled jobs
• The Raw.hp_Execute_Import_Impl stored procedure queues the SSIS Package as sa the same as normal.

In lieu of being able to create dedicated windows accounts for this purpose I think this as good as I'm going to get at the moment.

Thanks for the help guys!