Sql-server – SSIS DB installaion

The SSIS intallation is working as a charm, the only problem was that we wanted to connect to SSIS 2014 from SSMS 2012.

At the end we agreed to upgrade the developers machines to sql server 2014 developer edition, and migrated the packages to sql 2014, and now everything is working fine.

I will leave the question here as a reference, for the community and for myself, because at the end I had so many problems, and they all have been solved.

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!