You get an error says “Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON'” when you run an SSIS 2012 package from a stored procedure

You heard that finally Microsoft provided an easy way to run an SSIS package from a stored procedure with SQL server 2012 release when you use the new project deployment model, you deploy your project and call the right stored procedures and as far as you are calling them from the same database server where they are deployed and with a windows account that has the needed permissions everything works great.

Then you decide to create a stored procedure and send it to the web developers or execute form anywhere outside of the database server and you are surprised that the package is throwing the error in the subject Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ or any other error implies that the user executing the package has insufficient permissions.

The problem is that integration services as of the 2012 release does not support credentials delegation which means even though the SSIs package is running under the right windows account it does not pass that account when it tries to access the database or file system as they say at the end of this topic.

This is really a problem because in most of the cases your application that needs to call SSIS will reside on a different server and this method is useless, the issue was reported to Microsoft and they said it will be fixed in the next release but you need a solution now! There are few workarounds but none of them is pretty but you might find them helpful in your case:

1. SQL Authentication: If your problem is only with the database access then you can use SQL authentication instead of windows authentication – although it’s not recommended, and to secure the password you need to parameterize the connection manager and make the password a parameter and check the sensitive checkbox.
2. Call the execution statement through a SQL job: the only command that matters when it comes to authentication in the call to the start_execution function, which is good because you don’t have to change the whole stored procedure you created and you only do it once and you use it for all packages, this is the option i ended up using as my package needs to access the file system, here is what you need to do:
   • remove the call to start execution from you procedure, which is EXEC [SSISDB].[catalog].[start_execution] @execution_id
   •  Replace it with an insert statement to a queue table; the table will have the following fields:
     • ExecutionID: bigint to hold theid for the execution instance you started.
     • Processed: a bit field to indicate if the instance was executed.
     • any other fields like table id or time stamp would be helpful for you.
   • your stored procedure will look something like this:   

     Create Procedure [dbo].[usp_GenerateReport]
     (
      @DateTo datetime,
      @ExecutionID bigint output
     )
     AS
     BEGIN
     
     Declare @execution_id bigint
     EXEC [SSISDB].[catalog].[create_execution] @package_name=N'PackagName.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'Folder1', @project_name=N'Folder1', @use32bitruntime=True, @reference_id=2
     
     --set parameters in case you have any
     EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'DateTo', @parameter_value=@DateTo
     
     --EXEC [SSISDB].[catalog].[start_execution] @execution_id
     
     --insert into my queue table
     Insert into SSISProcessRequest values (@execution_id, 0)
     
     END
     

   • now create an SQL agent job that runs an SQL command under the windows account that you would like to execute the package, it will check for packages with processed flag equals false, execute them then set the flag to true, my service runs every 15 seconds and has the following SQL code:  

     Declare @ExecutionID bigint, @SSISProcessRequestID int
     WHILE EXISTS (SELECT 1 FROM SSISProcessRequest WHERE Processed = 0)
     BEGIN
     
      SELECT TOP 1 @ExecutionID = ExecutionID FROM SSISProcessRequest WHERE Processed = 0 ORDER BY DateRequested;
     
      EXEC [SSISDB].[catalog].[start_execution] @ExecutionID;
     
      UPDATE SSISProcessRequest SET Processed = 1 WHERE ExecutionID = @ExecutionID;
     
     END
     
     

1. Web Service: create a web service on the SSIS server to call the package, I didn’t try this option myself and usually network admins do not prefer to open web ports on the database servers for security reasons, but it could be an option for you.

 

• Database
• Microsoft SQL Server
• NT AUTHORITY\ANONYMOUS LOGON
• SQL
• SQL Server Integration Services
• SSIS
• SSIS 2012
• SSISDB
• start_execution
• Stored procedure