Problem

-> One of the SSIS package was failing with below error when validated from SSMS,

Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. Reason: Could not find a login matching the name provided.

-> I tried validating the package and it succeeded. Spoke to the developer who was experiencing this issue and understood below details.

-> SSIS package is stored in “Integration Services Catalog” on SQL Server instance JBSERVER1\SSIS.

-> The SSIS package has a “Data Flow” Task that connects to SQL Server instance JBAG1.

-> Developer has a RDP session to Database Server JBSERVER3. Opens up a SSMS in database server JBSERVER3 and connects to SQL Server Instance JBSERVER1\SSIS and is validating the SSIS package and encounters this error.

-> When the developer validates the package connected from Database Server JBSERVER1, there are no issues.

-> This error is due to double-hop authentication issue. Please refer articles “Understanding Kerberos Double Hop” and “Double-hop authentication: Why NTLM fails and Kerberos works” for more details on Double-hop authentication.

Analysis

-> Connected to SQL Server instance JBAG1, queried SQL Server errorlog and found below errors, which is same when SSIS package validation fails,

2019-07-19 12:15:16.500 Logon        Error: 18456, Severity: 14, State: 5.
2019-07-19 12:15:16.500 Logon        Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. Reason: Could not find a login matching the name provided. [CLIENT: 192.168.15.12]

2019-07-19 13:07:27.200 Logon        Error: 18456, Severity: 14, State: 5.
2019-07-19 13:07:27.200 Logon        Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. Reason: Could not find a login matching the name provided. [CLIENT: 192.168.15.12]

2019-07-19 13:17:08.790 Logon        Error: 18456, Severity: 14, State: 5.
2019-07-19 13:17:08.790 Logon        Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. Reason: Could not find a login matching the name provided. [CLIENT: 192.168.15.12]

-> “ping -a 192.168.15.12” resolves to SSIS server JBSERVER1.

-> Connected to SQL Server instance JBAG1 again, queried SQL Server errorlog and searched for keyword “Service Principal Name (SPN)” and found below details,

2019-07-18 14:49:03.810 Server SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

2019-07-18 14:49:03.810 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/JBAG1.JBS.COM ] for the SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

2019-07-18 14:49:03.810 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/JBAG1.JBS.COM:1433] for the SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

-> SPN is not properly registered. Execute below code on server JBAG1 remotely (RDP to a different server other than JBAG1 and then use SSMS to connect to JBAG1. This is just to use TCP) and look for “net_transport” and “auth_scheme” in your output,

select @@servername as ServerName, session_id,net_transport, auth_scheme from sys.dm_exec_connections where session_id= @@spid

-> Ideally, you should see net_transport as TCP and auth_scheme as KERBEROS. Since SPN is not configured properly in SQL Server instance JBAG1, auth_scheme is NTLM and this is the reason for the error message. Configuring proper SPN should solve this problem.

SPN

-> SPNs are objects registered within AD which allow Kerberos to work. On startup SQL Server attempts to register 2 SPNs against the service account. On shutdown, it attempts to re-register these.

-> These operations can fail, so SPNs are either not registered, or deleted. Both of these can cause problems.

Checking SPNs exist against the service account

-> There should be 2 SPNs configured per instance. One for instance name and one for port number. These need to be against the servername listed in ‘select @@servername’.

-> You can query the SQL Server port using below query,

SELECT MAX(CONVERT(VARCHAR(15),value_data)) as Port FROM sys.dm_server_registry WHERE registry_key LIKE '%MSSQLServer\SuperSocketNetLib\Tcp\%' AND value_name LIKE N'%TcpPort%' AND CONVERT(float,value_data) < 0;

Add SPN manually

-> To query the SPNs registered against a service account run the following from an administrative command prompt:

setspn -L JBS\JBSQLServer

-> You will see output similar to below,

Registered ServicePrincipalNames for CN=JBSQLServer,OU=Service,OU=Generic Accounts,OU=User Environment,DC=JBS,DC=com:

    MSSQLSvc/JB1.JBS.COM:15734
MSSQLSvc/JB1.JBS.COM:IN01
    MSSQLSvc/JBAG1:15734
    MSSQLSvc/JBAG1
    MSSQLSvc/JBSERVER1.JBS.COM:15734
    MSSQLSvc/JBSERVER1.JBS.COM:SSIS
    MSSQLSvc/JBSERVER2.JBS.COM:15734
    MSSQLSvc/JBSERVER3.JBS.COM:IN01

-> SPN entries for database server JBAG1 is not created using FQDN (Fully qualified domain name) and has a wrong port number.

-> Correct SPN can be added using below command from administrative command prompt,

setspn -A "MSSQLSvc/JBAG1.JBS.COM:1433" JBS\JBSQLServer
setspn -A "MSSQLSvc/JBAG1.JBS.COM" JBS\JBSQLServer

-> If it is a named instance, Example : JBSERVER123\IN2017 with port 15223. Below command can be used,

setspn -A "MSSQLSvc/JBSERVER123.JBS.COM:15223" JBS\JBSQLServer
setspn -A "MSSQLSvc/JBSERVER123.JBS.COM:IN2017" JBS\JBSQLServer

-> Delete the bad SPNs using below command,

setspn -D "MSSQLSvc/JBAG1:15734" JBS\JBSQLServer
setspn -D "MSSQLSvc/JBAG1" JBS\JBSQLServer

-> It can take some time for the changes to take effect, but you should be able to reconnect to SQL Server, and get a KERBEROS connection.

-> Execute below code on server JBAG1 remotely (RDP to a different server other than JBS and then use SSMS to connect to JBS\IN2017. This is just to use TCP) and look for “net_transport” and “auth_scheme” in your output. You should see net_transport as TCP and auth_scheme as KERBEROS.

select @@servername as ServerName, session_id,net_transport, auth_scheme from sys.dm_exec_connections where session_id= @@spid

Add SPN using “Microsoft® Kerberos Configuration Manager for SQL Server®”

-> Download the tool and install it.

-> Double click “C:\Program Files\Microsoft\Kerberos Configuration Manager for SQL Server\KerberosConfigMgr.exe”

-> To connect to a local server, leave all fields blank and click connect.

-> To connect to a remote server, enter the Server name, username and password as below,

-> After connecting to the server, you will see all SPNs. Scroll to right and check the status of the SPN. In our case it is Missing.

-> Click on “Fix All” button, and confirm YES.

-> Once created, the status should change to Good.

-> Testing SSIS package should be fine now and you shouldn’t be seeing the issue any more. In my case the issue got resolved after I have added the SPNs.

-> In case the issue is still not resolved after adding SPNs. Check below things,

Checking for multiple SPNs for an instance

-> Sometimes the same SPN is registered against multiple Service Accounts. This will break KERBEROS, as it expects the SPN to be unique.

-> To check for multiple SPNs, run the following,

setspn -Q "MSSQLSvc/JBAG1.JBS.COM:1433"
setspn -Q "MSSQLSvc/JBAG1.JBS.COM"

-> Output,

Checking domain DC=JBS,DC=com

CN=JBSQLServer,OU=Service,OU=Generic Accounts,OU=User Environment,DC=JBS,DC=com

MSSQLSvc/JBS1.JBS.COM:15734
MSSQLSvc/JBAG1.JBS.COM:1433
MSSQLSvc/JBAG1.JBS.COM

CN=svc-Prod-SQLServer,OU=Service,OU=Generic Accounts,OU=User Environment,DC=JBS,DC=com

MSSQLSvc/JBAG1.JBS.COM:1433      
MSSQLSvc/JBAG1.JBS.COM
MSSQLSvc/JBS09903.JBS.COM:15734
MSSQLSvc/JBS09900.JBS.COM:15734
MSSQLSvc/JBS09909.JBS.COM:15734
MSSQLSvc/JBS09909.JBS.COM:INSQL01
MSSQLSvc/JBS09911.JBS.COM:INSQL01

CN=svc-Prod-build,OU=Service,OU=Generic Accounts,OU=User Environment,DC=JBS,DC=com

MSSQLSvc/JBS09982.JBS.COM:15734
MSSQLSvc/JBS09982.JBS.COM:PMGBVSQL01
MSSQLSvc/JBS19609.JBS.COM:DMGBVSQL01
MSSQLSvc/JBS09612.JBS.COM.net:64363
MSSQLSvc/JBAG1.JBS.COM:1433       
MSSQLSvc/JBAG1.JBS.COM

Existing SPN found!

-> In this case I am able to see SPNs created also for service account JB\svc-Prod-SQLServer and JB\svc-Prod-build. May e the service account was changed multiple times. Currently Service account for SQL Server instance JBS is JB\svc-JBS-SQLServer. So in this case we will delete the SPN’s created for servcie account JB\svc-Prod-SQLServer and JB\svc-Prod-build using below command,

setspn -D "MSSQLSvc/JBAG1.JBS.COM:1433" JB\svc-Prod-SQLServer
setspn -D "MSSQLSvc/JBAG1.JBS.COM" JB\svc-Prod-SQLServer
setspn -D "MSSQLSvc/JBAG1.JBS.COM:1433" JB\svc-Prod-build
setspn -D "MSSQLSvc/JBAG1.JBS.COM" JB\svc-Prod-build

Delegation

If instances shows KERBEROS connections, and it still doesn’t work, check that the service account is set to allow delegation.

Similar Issues

-> Linked Servers configured may also fail with this error, since Linked Server needs a ‘double-hop’ which requires you to be connected to the instance using Kerberos. For example, On SQL Server instance SERVER1\IN01, you have configured a linked server to SERVER2\IN02. Everything works fine when connected to Server1. But connection fails with message “Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ ” when You have a RDP session to SERVER3 (Any other server other than SERVER1, SERVER2) and connected to SERVER1\IN01 using SSMS from SERVER3 and test a connection for the configured linked server which points to SERVER2\IN02. You can solve this by configuring proper SPN as mentioned in this post.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.