Have you encountered the dreaded SQL Server error “Login failed for user NT AUTHORITY\ANONYMOUS LOGON“? That’s because of the infamous “double hop” issue. It often manifests in the context of linked servers, where you are logged onto your PC, and accessing “SQL Server 2”, using a linked server created on “SQL Server 1”, using Windows authentication. The first hop is from your PC to “SQL Server 1”, and second (double) hop is from “SQL Server 1” to “SQL Server 2”. In this case, if “SQL Server 1” fails to pass on your Active Directory identity to “SQL Server 2”, the authentication fails for the second (double) hop, and it results in the above error.
First, I am going to briefly cover what is Kerberos, why its needed, what role SPNs play in this context – BUT if you are already familiar with the concepts and just want to see the script, jump straight to the code below:
Script to generate the setspn commands required to register the SPNs for SQL Server service account
DBAs often get around the double hop issue by using SQL authentication, instead of the more secure Windows authentication. This is just a workaround to overcome the issue – but its very much possible to get Windows authentication to work in a double hop scenario.
The double hop failure is due to the inability of “SQL Server 1” to delegate/pass on the user’s credentials to “SQL Server 2”. This is due to lack of Service Principal Names (SPNs) on the SQL Server service account or due to the Delegation feature not being enabled on the service account. An SPN in Active Directory (AD) is a unique identifier that links a specific network service to the specific Active Directory account running that service – this facilitates Kerberos delegation. If a service does not have the correct SPN registered in AD, the authentication will fail or fall back to NTLM mode, which is slower and less secure.
To summarise – in order to get Kerberos authentication to work, and also to avoid double hop authentication issues (especially for linked servers), you need to register SPNs for the service accounts of all SQL Servers involved and also configure the SQL Server service accounts to allow Kerberos delegation.
At server startup, SQL Server automatically tries to register two SPNs (one for the port number its listening on, and the other for the instance name) – but this might not always work, especially if the SQL Server service account hasn’t got the necessary permissions to register an SPN in the AD. In the SQL Server error log, you will see messages like below at startup if the SPN registration is successful. It will also log a message if it fails to register the SPNs. Its a good idea to register these SPNs yourself using setspn command, covering all necessary port, server name and listener name combinations, to make sure Kerberos works in all cases (like connecting using server name, or listener name)
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.
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/CHMSQLS1.MyDomain.net:SQLInst1 ] for the SQL Server service.
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/CHMSQLS1.MyDomain.net:15000 ] for the SQL Server service.Once SPNs are registered, Use the Windows application “Active Directory Users & Computers” (dsa.msc) to look up the service accounts of the SQL Servers involved, and update the Kerberos delegation settings under the Delegation tab of the service account properties dialog box. This can also be achieved using PowerShell scripting.
Script to generate the setspn commands to register the SPNs for SQL Server service account
This script queries Always On related Dynamic Management Views (DMVs) to get the listener information. It calls various system functions as well as an OS query via xp_cmdshell to get the full domain name. Here are the instructions on how to use the setspn command generation script:
- Connect to your SQL Server in SSMS using host name (NOT THE LISTENER name). This helps the code work better
- Execute the script. It outputs the necessary setspn commands
- Copy each of the generated commands, and paste them into your command prompt (cmd.exe) window
- Run them in command prompt to get the SPNs registered
- Check the output of each command to make sure it succeeded. Investigate and resolve any errors
- REPEAT the process on your other AG replicas/nodes, to make sure SPNs are registered for all nodes/replicas
- To check all the SPNs are registered successfully, run: setspn -L “DomainName\SQLServerSeviceAccount”
- To display all SPNs associated with your SQL Server: setspn -Q “MSSQLSvc/SQLHostName:InstanceName”
--Created by Vyas Kondreddi
--https://dutyDBA.com
SET NOCOUNT ON
DECLARE
@ServiceAccount sysname, @Domain sysname, @SQLServerPort nvarchar(5), @PingCMD nvarchar(128),
@InstanceName sysname = @@SERVICENAME,
@MachineName sysname = UPPER(CAST(SERVERPROPERTY('Machinename') AS sysname)),
@SPNService nvarchar(24) = 'setspn -s MSSQLSvc/'
DECLARE @PingOutput table (PingOutput nvarchar(128) NULL)
DECLARE @SPNCommands table (SPNCommand nvarchar(512))
--Retrieve the SQL Server service account name
SELECT @ServiceAccount = service_account
FROM sys.dm_server_services
WHERE servicename LIKE 'SQL Server (%'
--Make sure you are connected using server name using TCP/IP, not the listener name
--Getting the TCP port the SQL Server service is listening on
SET @SQLServerPort =
(
SELECT CAST(local_tcp_port AS varchar(5))
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
)
--Retrieving the fully qualified domain name
--NOTE: This requires xp_cmdshell access
IF DEFAULT_DOMAIN() <> 'WORKGROUP'
BEGIN
SET @PingCMD = 'ping -n 1 ' + @MachineName
INSERT INTO @PingOutput (PingOutput)
EXEC master..xp_cmdshell @PingCMD
DELETE @PingOutput WHERE (PingOutput NOT LIKE 'Pinging%') OR (PingOutput IS NULL)
SET @Domain =
(
SELECT STUFF(PingOutput, 1, CHARINDEX('.', PingOutput), '')
FROM @PingOutput
)
SET @Domain = '.' + LEFT(@Domain, (CHARINDEX(' ', @Domain) - 1))
END
ELSE
BEGIN
SET @Domain = ''
END
--SPNs for "Listener Name + Listener Port Number and "Fully Qualified Listener Name + Listener Port Number"
INSERT INTO @SPNCommands (SPNCommand)
SELECT @SPNService + dns_name + ':' + CAST(port AS varchar(5)) + ' ' + @ServiceAccount
FROM sys.availability_group_listeners
UNION ALL
SELECT @SPNService + dns_name + @Domain + ':' + CAST(port AS varchar(10)) + ' ' + @ServiceAccount
FROM sys.availability_group_listeners
--SPNs for "Listener Name + Instance Name" and "Fully Qualified Listener Name + Instance Name"
IF @InstanceName <> 'MSSQLSERVER'
BEGIN
INSERT INTO @SPNCommands (SPNCommand)
SELECT @SPNService + dns_name + ':' + @InstanceName + ' ' + @ServiceAccount
FROM sys.availability_group_listeners
UNION ALL
SELECT @SPNService + dns_name + @Domain + ':' + @InstanceName + ' ' + @ServiceAccount
FROM sys.availability_group_listeners
END
--SPNs for "Listener Name + Default Port Number" and "Fully Qualified Listener Name + Default Port Number"
INSERT INTO @SPNCommands (SPNCommand)
SELECT @SPNService + dns_name + ':' + @SQLServerPort + ' ' + @ServiceAccount
FROM sys.availability_group_listeners
UNION ALL
SELECT @SPNService + dns_name + @Domain + ':' + @SQLServerPort + ' ' + @ServiceAccount
FROM sys.availability_group_listeners
--SPNs for "SQL Server Host Name + Port Number" and "Fully Qualified SQL Server Host Name + Port Number"
INSERT INTO @SPNCommands (SPNCommand)
SELECT @SPNService + @MachineName + ':' + @SQLServerPort + ' ' + @ServiceAccount
UNION ALL
SELECT @SPNService + @MachineName + @Domain + ':' + @SQLServerPort + ' ' + @ServiceAccount
--SPNs for "SQL Server Host Name" and "Fully Qualified SQL Server Host Name" when the port is 1433
IF @SQLServerPort = '1433'
BEGIN
INSERT INTO @SPNCommands (SPNCommand)
SELECT @SPNService + @MachineName + ' ' + @ServiceAccount
UNION ALL
SELECT @SPNService + @MachineName + @Domain + ' ' + @ServiceAccount
END
--SPNs for "SQL Server Host Name + Instance Name" and "Fully Qualified SQL Server Host Name + Instance Name"
IF @InstanceName <> 'MSSQLSERVER'
BEGIN
INSERT INTO @SPNCommands (SPNCommand)
SELECT @SPNService + @MachineName + ':' + @InstanceName + ' ' + @ServiceAccount
UNION ALL
SELECT @SPNService + @MachineName + @Domain + ':' + @InstanceName + ' ' + @ServiceAccount
END
SELECT DISTINCT SPNCommand
FROM @SPNCommands
ORDER BY SPNCommandThe output would look like below. You need to copy these commands one by one, paste them into your command prompt (cmd.exe) and run them to register the SPNs. If you get permission errors, ask someone with necessary permissions (typically from your Active Directory team) to run these commands for you.
SPNCommand
-------------------------------------------------------------------------------------------
setspn -S MSSQLSvc/CHMSQLSvcListener.MyDomain.net:15000 MyDomain\svc-SQL-CHMSQLS1
setspn -S MSSQLSvc/CHMSQLSvcListener.MyDomain.net:15001 MyDomain\svc-SQL-CHMSQLS1
setspn -S MSSQLSvc/CHMSQLSvcListener.MyDomain.net:SQLInst1 MyDomain\svc-SQL-CHMSQLS1
setspn -S MSSQLSvc/CHMSQLSvcListener:15000 MyDomain\svc-SQL-CHMSQLS1
setspn -S MSSQLSvc/CHMSQLSvcListener:15001 MyDomain\svc-SQL-CHMSQLS1
setspn -S MSSQLSvc/CHMSQLSvcListener:SQLInst1 MyDomain\svc-SQL-CHMSQLS1
setspn -S MSSQLSvc/CHMSQLS1.MyDomain.net:15000 MyDomain\svc-SQL-CHMSQLS1
setspn -S MSSQLSvc/CHMSQLS1.MyDomain.net:SQLInst1 MyDomain\svc-SQL-CHMSQLS1
setspn -S MSSQLSvc/CHMSQLS1:15000 MyDomain\svc-SQL-CHMSQLS1
setspn -S MSSQLSvc/CHMSQLS1:SQLInst1 MyDomain\svc-SQL-CHMSQLS1The above output is from an SQL Server replica that is part of an Always On availability group.
You might wonder, are all these SPNs really needed. Yes they are. You need to register SPNs for the host name and port number, instance name combinations, as well as listener name and port number combinations – including the hostname/listerner with FQDN variation.
Basically, Kerberos authentication succeeds only if the SPN exactly matches the hostname/listener/port used in the connection string the client uses.
DBAs are often unfamiliar with SPN registration, and find it difficult to work out the syntax required for all the above combinations. I hope the above script makes your life easier. I recommend running this script as soon as a new SQL Server is built/deployed. In fact, sorting out the SPNs should be one of the steps in your SQL Server build and deployment checklist or ‘fit for production’ checklist. Please share your thoughts by leaving a comment below.
