none
Unable to Execute SSIS Package from SQL Server Login RRS feed

  • Question

  • Hi,

    The front end app calls my stored procedure which in turn calls a SSIS package (project deployment mode) but I get an error saying that cannot execute with a SQL Server Login. Below are the details:

    The app invokes the s.p using a sql server login.

    my s.p calls the package by EXEC [SSISDB].[catalog].[create_execution] etc.

    I get error  | Error Number: 27123, Error Severity: 16, Error State: 1, Error Procedure: create_execution, Error Line: 38, Error Message The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.

    Fine but I cannot have the application changed to windows authentication now!

    I try EXECUTE AS USER = 'user name'   call ssis_package revert

    and get

     | Error Number: 15199, Error Severity: 16, Error State: 1, Error Procedure: start_execution, Error Line: 35, Error Message The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again.

    I tried GRANT IMPERSONATE ON all to sqluser

    and keep getting Incorrect syntax near 'IMPERSONATE'. Weird because that seems to be the syntax.

    I don't want to use an intermediate sql agent job and cannot change the app to use windows credentials, at least for now. So

    Is there a solution around this? Any ideas?

    Any help would be much appreciated,

    Thanks,

    Waldemar



    Friday, December 2, 2016 3:11 PM

Answers

  • http://dba.stackexchange.com/questions/80507/issues-with-module-signing-and-ssis-catalog-internal-procedures

    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Friday, December 2, 2016 4:29 PM

All replies

  • hi,

    Use proxy account to run SSIS job 


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Friday, December 2, 2016 4:04 PM
  • Hi Vishe,

    A proxy is a SQL Server Agent concept and I don't want to involve the SQL Agent here. I am calling the package directly from a sql server stored procedure.

    Using the agent will bring many problems.

    - One additional point of failure

    - Problems passing the parameters, which I have to pass

    - Concurrency: what happens when the job is executed multiple times.

    etc. I know it is a possible workaround but I will do my best not to go there.

    Thanks for the answer,

    Waldemar


    Friday, December 2, 2016 4:14 PM
  • http://dba.stackexchange.com/questions/80507/issues-with-module-signing-and-ssis-catalog-internal-procedures

    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Friday, December 2, 2016 4:29 PM
  • Thank you Vishe for your help. Nice reference that convinced me the context change is not viable. I will go the CLR way if the connection cannot be changed to windows authentication.

    Waldemar


    Saturday, December 3, 2016 3:47 AM