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 :(
create_execution
i.e. do they need to specify parameters on execution for their "data's ready scenario?" 2) Safe to assume that you're not interested in putting them in the ssis_admin role? – billinkc Jan 12 '16 at 13:22create_execution
because I do need to pass a parameter (one of three values) from the sproc to the job. I'm happy to have three sprocs/jobs etc if that solves it. 2) If ssis_admin is the lowest privilege role that gets me there, I'm open to it... it's better than sysadmin at least and solves them accidentally dropping/nuking the warehouse tables in general use. – Wokket Jan 12 '16 at 21:19ssis_admin
role would allow them to run the SSIS packages (the procs check for membership in sysadmin or ssis_admin roles) but I think it's going to run as them and therefore not able to take backups and such. (I'd have to test that for sure, can never remember if it runs as them or the SQL Server service account). However, being a member of ssis_admin allows them deploy packages and muddle with configurations which may or may not be a good thing. The 2016 gives us more granular roles but obviously not much use here – billinkc Jan 12 '16 at 21:27EXECUTE AS
to allow them to run the specific jobs viasp_start_job
. Says the random internet guy who's terrible at security – billinkc Jan 12 '16 at 21:30