14

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

| improve this question | |
  • 1) Must they launch the packages via 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:22
  • 1) I'm using create_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:19
  • The ssis_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:27
  • I think the least amount of risk would be have 3 hard coded jobs that use the correct combination of credentialed users/account to achieve a least privileged state. I'd then look at either granting those users the ability to run jobs or baring that, create three procs that use EXECUTE AS to allow them to run the specific jobs via sp_start_job. Says the random internet guy who's terrible at security – billinkc Jan 12 '16 at 21:30
  • @billinkc I think ssis_operator would do – Tom V - try topanswers.xyz Jan 13 '16 at 4:19
2

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!

| improve this answer | |
  • 2
    Thanks for coming back and posting the solution. Now when you have this problem again and forget what you did, you can search on the web and you'll find your own solution! – Nick.McDermaid Jan 18 '16 at 4:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.