SSIS error: Login failed for user "NT AUTHORITY\ANONYMOUS LOGON"

Question

We converted our 2012 SSIS package to 2016 SSIS (TargetServerVersion = SQL Server 2016).

One of the database the SSIS package reads from is a SQL Server 2012 database.

Either connecting using Native OLE DB\SQL Server Native Client 11.0 or connecting using OLE DB provider for SQL Server give the same error.

Data Source=my2012SQLDBServer;Initial Catalog=my2012SQLDB;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

Data Source=my2012SQLDBServer;Initial Catalog=my2012SQLDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Application Name=myApplicationName-{....}my2012SQLDBServer.my2012SQLDB;Auto Translate=False;

When running the SSIS package from SSMS (SSMS is run using a user that has a dbOwner permission to the 2012 database, ie myDomain\myUser) , I am getting the error in the step reading a table from the 2012 database

Execute SQL Task: Error: Failed to acquire connection "my2012SQLDBServer.my2012SQLDB". 
Connection may not be configured correctly or you may not have the right permissions on this connection.
DTS_E_OLEDBERROR. An OLE DB error has occurred. Error Code:0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" HResult:0x80040E4D 
Description: "Login failed for user "NT AUTHORITY\ANONYMOUS LOGON".

On Visual Studio (run using myDomain\myUser), I am able to succesfully run the SSIS package.

myDomain\myUser also has an ssis_admin, ssis_logreader, db_owner, app_dbowner role in the SSISDB database.

How can I fix this error ? Thank you.

Answer 1

solution for me was like just changing windows authentication in the destination settings to SQL server authentication with apropriate Account that have rights to reach the server, i dont understand how it works but it does.

Answer 2

It is typical error for double-hop problem.
Simplest solution is to login using remote desktop to SSIS server and then run it using SSMS there.
Other option is to define SQL Agent job on the SSIS server and then run the package using this job.
Ultimate solution will be to configure Kerberos and allow double-hop connections on the SSIS box.

You can check following post how to configure Kerberos:
SSIS job fails when run remotely thru ssms, but runs fine on SQL Server

Answer 3

Ok I want to give a different answer in case it will help people. Obviously here you need access to runtime variables you can't see. In my message it clearly gave the userid and nothing else.

But I had a verified case of ssis getting confused by prior connection configurations and ignoring the expression overrides that were supposed to define the connection.

In my case I went from SaveSensitiveWithPassword to Windows Auth and ssis got totally confused. One indication that this is the problem is you see things in the connection string looking at the dtsx xml directly other than just the server and userid.

I'm saying if you've made a bunch of changes to your connection manager over time and suddenly your expression overrides stop working, don't kill yourself, just start over with a fresh package and connection manager.