Sql-server – Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ for SSIS

This appears to be a authentication scheme problem. If my memory serves correctly, the appearance of the ANONYMOUS LOGON is indicative of NTLM being used instead of Kerberos. If your SQL Server service is running under a domain credential, you will need to ensure there is a Service Principle Name (SPN) present for SQL Server. The syntax of the SetSPN command can be found here: SetSPN In short, you must marry a protocol with a TCP Port and the service account. I believe the command will look something like this:

setspn -s MSSQLSvc/Server.Domain:TCPPort Domain\ServiceUser

To verify which authentication scheme your existing connections are using, run the following code:

SELECT
    dec.session_id,
    dec.auth_scheme
FROM sys.dm_exec_connections AS dec

If configured properly for Kerberos, you will see Kerberos in the auth_scheme column. If not, you will see NTLM.

Finally, if your setup requires multi-hop authentication, you will need to configure each subsequent hop before the SQL Server to allow delegation. Inside Active Directory, go to the properties page of the computer or user account that will need to pass through authentication to the SQL Server, go to the Delegation Tab, select "Trust this user for Delegation on Kerberos only", and then select the service that this account will be passing authentication to (search for your SQL Server service account).

Hope this helps,

Matt

Here it is: the failing login in question was a SQL Server login. Here is how we configured the SQL connection manager properties for old, new, and developer environments:

• ServerName - MyServer
• InitialCatalog - MyDB
• UserName - MyUser
• Password - ***********
• ConnectionString - Data Source=MyServer;User Id=MyUser;Initial Catalog=MyDB;Provider=SQLNCLI11;Persist Security Info=True;Auto Translate=False;

While this worked perfectly on the old SQL 2008 environment, and it was no problem from Visual Studio, it failed during simultaneous database operations from the SQL 2012 Integration Services Catalog, but started working when we added Password=****** to the connection string.

• ConnectionString - Data Source=MyServer;User Id=MyUser;Password=********;Initial Catalog=MyDB;Provider=SQLNCLI11;Persist Security Info=True;Auto Translate=False;

It would seem that if a second simultaneous connection is being opened while in the SQL 2012 Integration Services Catalog execution environment, only the connection string itself (of the initial connection?) is consulted, not Password or other constituent properties of the connection manager. Therefore, Password has to be in the connection string.