Sql-server – Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. (MsDtsSrvr)

authenticationsql server

I have a clustered environment which has many instances in it. I have modified the xml file to allow several instances to use ssis. Everything works great when the instances are on the same node as the clustered ssis. However if they are on different nodes there seems to be an aunthentication problem and it loses my credentials. For some reason it tries to use Anonymous Logon instead. Here is the full error:

The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in Server 2005 Books Online.

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (MsDtsSrvr)

I would appreciate anyones help on this problem.

Thanks,

Best Answer

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

Related Question