Register a SPN for SQL Server Authentication with Kerberos

By:   |   Comments (14)   |   Related: 1 | 2 | 3 | > Security


After querying the SQL Server sys.dm_exec_connections DMV I noticed that all my currently connected sessions using Windows Authentication had used NTLM and not Kerberos. How do I get them to use Kerberos?  Check out this tip to learn more.


When it comes to configuring your SQL Servers to use Kerberos authentication there are a couple of prerequisites that must be met. First, the clients and servers must be joined to a domain. If they are joined, but they are in different domains then a two-way trust must be setup between these domains. Secondly an SPN must be successfully registered for the SQL Server service so that it can be identified on the network. The first requirement is pretty easy to validate so let's concentrate on the second one.

There are a few ways that we can check if the SPN has been registered successfully. If your SQL Server instance is running under a domain account (which is recommended) you can run the following command to see the services that are registered. If there are no services registered for this account you will get the error message below the command.

C:\Users\test>setspn -l DOMAIN\SQLServiceAccount
FindDomainForAccount: Call to DsGetDcNameWithAccountW failed with return value 0x00000525
Could not find account SQLServiceAccount

Alternatively, you can also use the SQL Server error log to validate if the SPN has been registered successfully or not. By either filtering within the SSMS GUI or using xp_read_errorlog we can search for the string below. If found we know the SPN did not register successfully.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the 
SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated 
authentication to fall back to NTLM instead of Kerberos. This is an informational message. 
Further action is only required if Kerberos authentication is required by authentication 

Finally, you can contact your system administrator and have them use the ADSIEdit MMC console to manually check if the service is registered.

Now that we've identified the issue we can go through a couple of different options that will allow us to successfully register the SPN and use Kerberos authentication.

Option 1 - Register SPN automatically

To enable the SPN to be registered automatically on SQL Server startup the service must be running under the "Local System" or "Network Service" accounts (not recommended), under a domain administrator account, or under an account that has permissions to register an SPN. The permissions required for this are the "Read servicePrincipalName" and "Write servicePrincipalName" access control settings in the Active Directory service. One thing that should be noted is granting these rights is not recommended (see if SQL Server is clustered or if you have multiple domain controllers as latency in Active Directory replication can cause connectivity issues with your SQL Server instance.

Option 2 - Register SPN manually

To register an SPN manually we can use the Microsoft provided Setspn.exe utility. To be able to run this tool and register an SPN you need to be a domain admin or have the appropriate privileges (defined above). One other thing to note is that the -s option ensures that the SPN you are trying to create is not already defined. Here are a couple examples. The first one is for a default instance and the second is for a named instance.

setspn -s MSSQLSvc/ DOMAIN\SQLServiceAccount
setspn -s MSSQLSvc/ DOMAIN\SQLServiceAccount

Once you've picked and implemented one of these options and if necessary restarted SQL Server you can establish a new connection and run the following TSQL to check that you are now using Kerberos authentication.

select session_id,net_transport,client_net_address,auth_scheme from sys.dm_exec_connections
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, March 4, 2024 - 3:15:10 PM - Paresh Motiwala Back To Top (92033)
Hi, thanks for this meaningful article.
However, we did an in-place OS(2012-2016) upgrade for a sql server x. with an instance \y running on it.
The service used to run on service account "nt server\mssql$y". After upgrade we find that the account doesn't exist on the windows server. Solarwinds cannot discover this sql instance nor can couple of people in the organization(getting sspi error). Everything else works.
what can I do ?

Tuesday, January 8, 2019 - 4:04:22 AM - Shashi Kant Back To Top (78659)

 Please make sure there is no data and time difference from AD server. SQL server and AD server time should be in sync. If there is a difference in time more then 5 to 10 min then these error should come even your SPN registered. AD won't allow for Kerbros authentication. 

Wednesday, August 24, 2016 - 10:34:16 AM - harish Back To Top (43179)

 The command in the article (given Below) needs to be modified

 setspn -s MSSQLSvc/ DOMAIN\SQLServiceAccount

The FQDN is preceded by the '/' before the InstanceName and ':' before the port number. The article says use ':' prior to Instance name.



Wednesday, June 8, 2016 - 4:15:13 AM - Marco Back To Top (41636)

Hi Ben,

thanks a lot for this helpfull information. Issues with clusters and multiple domcons were fixed in SQL Server 2012

Wednesday, July 8, 2015 - 11:31:14 AM - Julian Blue Back To Top (38152)

I think the restriction on granting write serviceprincipal name make sense when both conditions are true:

One thing that should be noted is granting these rights is not recommended (see if SQL Server is clustered *** AND  *** you have multiple domain controllers as latency in Active Directory replication can cause connectivity issues with your SQL Server instance.

Monday, September 16, 2013 - 9:59:16 AM - John Langston Back To Top (26811)

I have run into a feature of NETBIOS relative to server names that impacts proper SPN registration for Kerberos.  There is a fifteen (15) character name limitation.  Anything beyond 15 characters is truncated, so manual registration will be required (and the truncated server name that is registered will need to be removed). 

Tuesday, August 27, 2013 - 2:54:08 PM - AJ Back To Top (26496)

I am havin gthe issue specified above but I am in a workgroup setting on a Windows server 2003 and SQL 2008.

This is what I am seeing in the SQL error log:.

"The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3."


"Error: 18456, Severity: 14, State: 38."


Any help would be appreciated.




Thursday, June 27, 2013 - 9:47:23 AM - zzx375 Back To Top (25598)

Server names that exceed the NetBios name length of 16 characters will need to have their SPN explicitly created.  We discovered this after the corner office wanted server names exceeding the NetBios limit.  Microsoft has an SSPI executable (got my copy after a PSS call) that helps diagnose SPN issues. 

Thursday, May 16, 2013 - 9:30:45 AM - Ben Snaidero Back To Top (23987)


I think you've found you're issue, you're connecting with NTLM....Here is a good blog post that explains

Thanks for reading


Wednesday, May 15, 2013 - 3:24:02 PM - B Ford Back To Top (23969)

Others can query from linked servers, but I can't. (Error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.) Any ideas why? In the results of query below - BFORD has auth_scheme of NTLM.

FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id
order by login_name

session_id connect_time login_time login_name protocol_type auth_scheme HOST_NAME program_name
51 05:27.4 05:27.4 domain\bcampbell TSQL KERBEROS BCAMPBELL-WS Microsoft SQL Server Management Studio
117 14:51.2 14:51.2 domain\bford TSQL NTLM BFORD Microsoft SQL Server Management Studio - Query
120 55:33.9 55:34.0 domain\lwright TSQL KERBEROS LWRIGHT-WS Microsoft SQL Server Management Studio - Query
148 35:36.7 35:36.8 domain\mmichels TSQL KERBEROS MMICHELS-WS Microsoft SQL Server Management Studio
88 13:04.0 13:04.0 domain\sjohnson TSQL KERBEROS SJOHNSON-WS Microsoft SQL Server Management Studio - Query
134 25:09.0 25:09.2 domain\ttrisler TSQL KERBEROS TTRISLER-WS Microsoft SQL Server Management Studio - Query
102 46:16.5 46:16.5 domain\twilliams TSQL KERBEROS TWILLIAMS Microsoft SQL Server Management Studio - Query

Monday, May 13, 2013 - 4:41:42 AM - RFK Back To Top (23914)


The problem you see is due to Kerberos chain, and the serviceaccount that runs the sql 2005 service needs to be allowed to be enabled for delegation to forward the user credentials, an option within the AD on the user/computer, and there need to be set SPN for both servers.

hope this helps

Friday, May 10, 2013 - 8:57:12 AM - Scott Back To Top (23872)

My #1 interview question for any new hire for any IT position: What is Kerberos? Their answer tells me everything I need to know. I'm not saying the person needs to know but they do need to admit they don't know and how they would find out.


Friday, May 10, 2013 - 7:52:05 AM - AQKhan Back To Top (23871)

I have a linked server on one Server (SQL Server 2005) which points to other Server (SQL Server 2008), I get NT\ANONYMOUS LOGIN failure error when connect from management studio on my PC, while it works fine when connecting from the SQL 2005 Server. 

Even I have created SPN for the SQL Service Account.

Friday, May 3, 2013 - 11:00:51 AM - Reinis Back To Top (23705)

If you are testing the connection security protocol on MSSQL2005 but you have SSMS open on the same machine that SQL is running on, the query against sys.dm_exec_connections will return NTLM because you will be using Named Pipes (unless it is disabled).  To test it make sure you connect from a different machine to the SQL Server.


get free sql tips
agree to terms