Execute SSIS procedure from stored procedure using non-privileged SQL login

Question

I have a web app running on a different server that uses a SQL based login (not a windows based login) to connect to the SQL server. That app is calling a stored procedure that is located in a database other than SSISDB and the stored procedure is attempting to call the following SSIS procedures:

[SSISDB].[catalog].[set_environment_variable_value]
[SSISDB].[catalog].[create_execution]
[SSISDB].[catalog].[set_execution_parameter_value]
[SSISDB].[catalog].[start_execution] @execution_id

When the attempt is made we get the following error:

The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Integrated Authentication.

My challenge is how to work around this problem within the confines I have:

• I cannot change the code on the web app or the authentication method.
• Only minimal, non-impactful changes can be made to the stored procedures such as using "Execute As"
• I cannot make changes that require additional code changes - such as creating a wrapper procedure or job.

I came across this thread which describes the problem perfectly but ends in "I'll try something else." The thread also leads to this thread which has great information but does not address starting the procedure as a non-windows user.

I've been working with impersonation attempts, and various combinations of execute as and have been unsuccessful. All of the information I find gives me clues to what I need to do, but none of them directly address this exact scenario and I am hitting a wall.

Can someone please describe how to work around this specific scenario? I don't need exact step by step details (although that would be great), but I need information on specifically how to get from point A (The web app calling a stored procedure with an SQL account) to point B (A SSIS package being executed).

I feel the solution is to make the stored procedure impersonate a windows login that can run the SSIS procedures. But how to do that properly?

Other suggestions that can be used in the interim are also appreciated, but they have to fit in with my confines listed above.

Other resources I have used are here: https://blogs.msdn.microsoft.com/sqlserverfaq/2009/07/24/how-to-impersonate-the-privileges-to-create-a-login-using-the-stored-procedures-using-execute-as-clause/

https://www.sqlservercentral.com/articles/using-certificates-to-sign-stored-procedures

http://rusanu.com/2006/03/07/call-a-procedure-in-another-database-from-an-activated-procedure/

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/79391dd7-9afb-4bfb-aa90-1fc1cc82b305/impersonation-on-server-level-doesnt-work?forum=sqlsecurity

http://blog.sqlxdetails.com/procedure-with-execute-as-login/

Answer 1

As far as I know session started with SQL Server authentication will not let you create security context outside of SQL Server (because the only external context available is SQL Server service account and EXECUTE AS will not let you impersonate SQL Server service account for external access).
SSISDB procedures has to have context outside of SQL Server, so session has to be started using Windows Auth.
You have few options, but all needs more changes that you mentioned in the email:
1. I think easiest: create SQL Agent job wrapper.
2. Create SQLCLR stored procedure creating new callback session (it will use service account = sysadmin, I would say too privileged).
3. Enable Service broker service and use activation to start SSIS.
4. Publish SSIS package as a view and let your app consume the view.
5. I didn't try this, but worth trying to use a loopback linked server.