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

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.

sql-serverssissql-server-2012integrated-security


Solution 1:[1]

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

Solution 2:[2]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 ?????? ?????????

Related Questions