Microsoft SQL Server

SQL Server Core Engineer Tips

The SQL Network Interface library was unable to register SPN

The SQL Network Interface library was unable to register SPN

  • Comments 26
  • Likes




The SQL Network Interface library was unable to register SPN.


Problem

In the SQL Server error log you got the following message:


The SQL 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.

To understand the error you can transcribe the error message 0x2098 in a more readable.

ERROR_DS_INSUFF_ACCESS_RIGHTS

8344 (0x2098)

Insufficient access rights to perform the operation.


This error message indicates that the service account SQL server does not have sufficient rights to register the SPN.


Cause

SPNs are used by the Kerberos authentication protocol. If the account of the proceeding is known, the Kerberos authentication can be used to provide mutual authentication by the client and server. If the account of the proceedings is not known, NTLM authentication, which provides only authentication of the client by the server is used.

If you run SQL Server under the LocalSystem account, the SPN is automatically registered as SQL registering with the machine account that has the right to create an SPN default. So Kerberos interacts successfully with the server running SQL Server.

However, if you run SQL Server under a domain account or a local account, the attempt to create the SPN may fail. When creating the service principal name fails, this means that no SPN is set for the service that is running SQL Server.



Solution


Therefore, you must implement a solution to that the SPN is created for your SQL Server instance where you want to use the Kerberos protocol.


Method 1 : The method recommended by Microsoft Support. You can give in Active Directory rights below to the service account of SQL Server:
- Read servicePrincipalName
- Write servicePrincipalName

Method 2 : You can also give him the rights manually using the tool SetSPN.(http://msdn.microsoft.com/fr-fr/library/cc280459.aspx )

For SQL server Standalone

SetSPN -A MSSQLSvc/<ComputerName>.<DomainName>:<port> <AccountName>

So for a multi-server instance you must configure the SPN for each instance, for each instance of SQL Server usefulness port TCP / IP only. For the port of the proceeding open SQL Server Configuration Manager>> Right click the instance>> TCP / IP protocol (default port)

Pour un cluster

SetSPN -A MSSQLSvc/<virtualName>.<DomainName>:<port> <AccountName>
SetSPN -A MSSQLSvc/<virtualName>.<DomainName> <AccountName>

For an instance of SQL Server cluster, you must use the FQDN of the virtual SQL server. You must also configure the SPN with a port and a second SPN without the port.


Verification

Then you can confirm that the operation went smoothly with the following command:
SetSPN -L <AccountName>

SQL server side to check, you can use the DMV sys.dm_exec_connections with the column auth_scheme :
select auth_scheme from sys.dm_exec_connections where session_id=@@spid
If kerberos is used, you should see "KERBEROS".


Documentation



How to: Enable Kerberos Authentication on a SQL Server Failover Cluster
http://msdn.microsoft.com/en-us/library/ms189585(SQL.90).aspx

Registering a Service Principal Name
http://msdn.microsoft.com/en-us/library/ms191153.aspx

How to configure SQL Server 2005 Analysis Services to use Kerberos authentication
http://support.microsoft.com/kb/917409

How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005
http://support.microsoft.com/kb/909801

You cannot start the SQL Server Agent service of a failover cluster of SQL Server 2005 if the build of SQL Server is 3179 or a later build
http://support.microsoft.com/kb/943525

Registering Kerberos Service Principal Names
http://msdn2.microsoft.com/en-us/library/ms178119.aspx

How to Configure the Service Principal Name
http://msdn.microsoft.com/en-us/library/ms942980.aspx

How to troubleshoot the "Cannot generate SSPI context" error message
http://support.microsoft.com/kb/811889

Enable Kerberos authentication for virtual servers
http://technet.microsoft.com/en-us/library/cc780918.aspx

How to: Enable Kerberos Authentication on a SQL Server Failover Cluster
http://msdn.microsoft.com/en-us/library/ms189585(SQL.90).aspx

Best practices for configuring and operating server clusters
http://technet.microsoft.com/en-us/library/cc785714.aspx

How to use Kerberos authentication in SQL Server
http://support.microsoft.com/kb/319723/en-us

Michel Degremont 
| Microsoft EMEA
Product Support Services Developer - SQL Server Core Engineer |
Comments
  • how to give the rights, is it in Active Directory or SQL server or Windows Server?

    You can give in Active Directory rights below to the service account of SQL Server:

    - Read servicePrincipalName

    - Write servicePrincipalName

  • Hi George,

    My apologies for the delay. I am sure that you have already found out how you have to do. So, I will answer for the other DBA.

    You must grant Read servicePrincipalName and Write servicePrincipalName for the SQL Server service account in the Active Directory.

    You can also perform this:

    - With  command line tool Setspn.exe that enables you to read, modify, and delete the Service Principal Names (SPN) directory property.

    - By using Adsiedit.msc tool which is included in the Windows Support Tools. Download: www.microsoft.com/.../details.aspx.

    Michel.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment