I’ve an online app operating on a unique server that makes use of a SQL primarily based login (not a home windows primarily based login) to hook up with the SQL server. That app is asking a saved process that’s positioned in a database apart from SSISDB and the saved process is making an attempt to name the next 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 try is made we get the next error:
The operation can’t be began by an account that makes use of SQL Server
Authentication. Begin the operation with an account that makes use of
My problem is learn how to work round this downside inside the confines I’ve:
- I can not change the code on the net app or the authentication technique.
- Solely minimal, non-impactful adjustments may be made to the saved procedures equivalent to utilizing “Execute As”
- I can not make adjustments that require extra code adjustments – equivalent to making a wrapper process or job.
I got here throughout this thread which describes the issue completely however ends in “I will attempt one thing else.” The thread additionally results in this thread which has nice info however doesn’t handle beginning the process as a non-windows consumer.
I have been working with impersonation makes an attempt, and numerous combos of
execute as and have been unsuccessful. The entire info I discover offers me clues to what I have to do, however none of them straight handle this actual state of affairs and I’m hitting a wall.
Can somebody please describe learn how to work round this particular state of affairs? I do not want actual step-by-step particulars (though that might be nice), however I would like info on particularly learn how to get from level A (The net app calling a saved process with an SQL account) to level B (A SSIS package deal being executed).
I really feel the answer is to make the saved process impersonate a home windows login that may run the SSIS procedures. However how to try this correctly?
Different solutions that can be utilized within the interim are additionally appreciated, however they’ve to slot in with my confines listed above.
Different assets I’ve used are right here: