Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Register a SPN for SQL Server Authentication with Kerberos


By:   |   Read Comments (12)   |   Related Tips: 1 | 2 | 3 | More > Security

Problem

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.

Solution

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 
policies.

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 http://support.microsoft.com/kb/319723) 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/myhost.redmond.microsoft.com DOMAIN\SQLServiceAccount
setspn -s MSSQLSvc/myhost.redmond.microsoft.com:instancename 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


Last Update:






About the author





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

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

 setspn -s MSSQLSvc/myhost.redmond.microsoft.com:instancename 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 08, 2016 - 4:15:13 AM - Marco Back To Top

Hi Ben,

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


Wednesday, July 08, 2015 - 11:31:14 AM - Julian Blue Back To Top

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 http://support.microsoft.com/kb/319723) 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

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

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

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

Hi,

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

http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx

Thanks for reading

Ben


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

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.

SELECT
    s.session_id,
    c.connect_time,
    s.login_time,
    s.login_name,
    c.protocol_type,
    c.auth_scheme,
    s.HOST_NAME,
    s.program_name
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

AQKhan,

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

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

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 03, 2013 - 11:00:51 AM - Reinis Back To Top

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.

 


Learn more about SQL Server tools