none
SSISDB create_execution error: The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again.

    Question

  • I'm trying to execute SSIS package from service broker activation procedure.

    When SP is located in my database, I get following error:

    "The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again."

    When the same SP is located in SSISDB, it works fine, but for consistency reasons I would prefer to have this procedure in my database.

    To emulate this error we we can use following SP:

    ALTER PROCEDURE [dbo].[ExecutePackage]
    WITH EXECUTE AS 'dbo'
    AS
    BEGIN
    
    DECLARE
    	 @execution_id BIGINT;
    
    EXEC [SSISDB].[catalog].[create_execution] 
    	@package_name=N'Loader_DynamicPool.dtsx',
    	@execution_id=@execution_id OUTPUT,
    	@folder_name=N'SSIS Concurent',
    	@project_name=N'SSIS Concurent',
    	@use32bitruntime=False,
    	@reference_id=Null;
    
    DECLARE @var0 SMALLINT = 1;
    
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
    	@execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0;
    
    exec [SSISDB].[catalog].[set_execution_parameter_value]
    	@execution_id,  @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1;
    
    EXEC [SSISDB].[catalog].[start_execution] @execution_id;
    
    END;

    and then call:

    EXEC [dbo].[ExecutePackage]

    Works in SSISDB, fails in user database.


    Sunday, May 18, 2014 9:30 PM

Answers

  • Error is not about accessing the database, but reverting security context, so I suspect some problem with the call stack.

    I ran a trace of SP:StmtStarting events with your proc and see the SSISB catalog procs execute a REVERT in the SSISDB database context.  That explains the error. 

    As a workaround, you could run you proc via a SQL Agent stat-up job instead of with SB queue activation, with an infinite loop.  That would avoid the issues with the security context revert.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, May 25, 2014 1:07 AM

All replies

  • Hi Piotr,

    According to the error message, it seems that the database role dbo cannot access the SSISDB database under your database context. Could you modify the procedure to change the Execute As account from dbo to a Windows account that has sufficient permissions on both the SSISDB database and your own database?

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    Tuesday, May 20, 2014 4:27 PM
    Moderator
  • "dbo" is already mapped to Windows account and this account is a sysadmin.

    It is also mapped in both databases.

    Error is not about accessing the database, but reverting security context, so I suspect some problem with the call stack.

    Saturday, May 24, 2014 8:27 PM
  • Error is not about accessing the database, but reverting security context, so I suspect some problem with the call stack.

    I ran a trace of SP:StmtStarting events with your proc and see the SSISB catalog procs execute a REVERT in the SSISDB database context.  That explains the error. 

    As a workaround, you could run you proc via a SQL Agent stat-up job instead of with SB queue activation, with an infinite loop.  That would avoid the issues with the security context revert.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, May 25, 2014 1:07 AM
  • Thanks for error explanation.

    I was trying to avoid SQL Agent jobs - with activation stored procedure I can ran multiple threads in parallel and easily manage maximum number of concurrent packages.

    With agent jobs I should have multiple jobs or start new jobs on demand with some custom code to manage threads count.

    So I wonder if there is any simple workaround, which would allow to execute these commands in synchronous mode within context of SSISDB database with minimal overhead.

    Sunday, May 25, 2014 1:51 AM
  • Have you found a resolution to this that does NOT involve SQL Agent?

    I'm in the same boat as you, seeing the same errors and logs about "cannot revert current security context".

    Thanks in advance


    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

    Monday, June 30, 2014 9:23 PM
  • How do you pass parameters to a agent job from an application? Workaround is only good for static SSIS packages.  

    Thursday, February 16, 2017 11:27 PM
  • There is many options, you can include them in the script creating a job, in a permanent table or send in a broker message.

    But question is off topic, you will get better answers if you will start a new tread.

    Friday, February 17, 2017 5:54 AM
  • I've come across the exact same issue.

    We were looking to run a SSIS package as a specific account from a stored procedure.

    The reason that we found for this issue was because in [SSISDB].[catalog].[start_execution] and subsequently [SSISDB].[internal].[prepare_execution], there is a reference to EXECUTE AS CALLER and REVERT.  This overrides the original EXECUTE AS statement in the parent stored procedure.

    I'm normally very reluctant to make changes to provided stored procedures, but we could not see any benefit to the EXECUTE AS and REVERT code in [SSISDB].[catalog].[start_execution] or [SSISDB].[internal].[prepare_execution], so commented out the code.  Now the solution works as expected.

    Does anyone know if this will result in any complications?

    Thanks,

    Ian

    Monday, April 03, 2017 2:34 PM