December 21, 2018 at 5:37 am
Hi all,
I'm hoping someone can help me. A few weeks ago I replaced a SQL 2012 Server with a brand new SQL 2017 server, and migrated all the databases across. This caused no major issues, once I had determined the correct procedure for migrating the SSISDB., unfortunately after a couple of weeks an issue came to light with one single process . I've looked at this a couple of times since and it's left me scratching my head.
The user has an application which allows him to import CSV fies into a da5tabase, and it does this via a DTSX task. Running the DTSX task from SSMS or SSDT works fine, however the application calls the DTSX task from a stored procedure and to do this does so using an SSIS Proxy account. when you run the stored procedure it fails with the following error.
Msg 27123, Level 16, State 1, Line 13
The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Integrated Authentication.
Msg 6522, Level 16, State 1, Procedure internal.start_execution_internal, Line 0 [Batch Start Line 12]
The only issue is that the proxy account is a Windows Account. I've powered on the old server, and compared the code and permissions all the way down the line, and they are identical. The procedure works fine on the old server, but fails either running it from the application of from within SSMS as the proxy user.
It's got me puzzled.
Tony
December 21, 2018 at 5:53 am
How was it being run old SQL Server? Using something like EXECUTE AS with an SSISDB task doesn't work; the task must be run under the context of the user logged in, and that user must be a window user. I suspect that either you're using EXEUCTE AS, or a SQL Authentication Login.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 23, 2018 at 11:16 pm
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply