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.