Wednesday, 5 August 2015

SQL Server Kerberos Authentication Issues

Problem: - Not able to create Linked Server using current Windows security contest, The Linked server fails with “Login failed for user NT AUTHORITY\ANONYMOUS LOGIN”

OR

Unable to connect to SQL Server outside of the local server, SQL Server itself was up and running fine and we were able to connect the server when we RDP to the local server and access the SQL but when we connect SQL Server outside the local server were getting the following error,

“The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server)”

Troubleshooting:-

Usually this issue occurs when Kerberos Authentication Failure, Kerberos protocol uses SPN (Service Principal Name) for the authentication with the server and client.
Usually the SPN’s are created on SQL Start-up by using the SQL Server Service Account, but most of the cases we may fail to notice the SPN did not register on SQL start-up due to permission issues,
Even though we will not see any issues connecting the SQL Server as SQL will use NTLM protocol for Authentication, all the operation using Kerberos will fail(E.g.: Linked Server, OpenRowset etc.).

We can check the SQL Error log to find if the SPN was registered successfully on SQL Server Start-up. You will the below message if the SPN is not registered on SQL Server start-up,

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/TestServer.testdomain:<InstanceName> ] for the SQL Server service. Windows return code: 0x2098, state: 20. 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.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [MSSQLSvc/TestServer.testdomain:<InstanceName>] for the SQL Server service. Windows return code: 0x2098, state: 20. 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.

Resolution:-

This is documented error in Microsoft, you need to provide READ and WRITE permissions to the SQL server domain account on the AD so that new SPN’s can be created on the AD for SQL Server.
Detailed steps to be taken for resolution of this issue is mentioned in the Microsoft article http://support.microsoft.com/kb/811889 and  http://blogs.technet.com/b/mdegre/archive/2009/11/20/the-sql-network-interface-library-was-unable-to-register-spn.aspx. The resolution task can be done only someone who has Admin access to AD.

Once the SPN’s are created and registered successfully you will able to create Linked Servers

1 comment:

  1. most affordable superior papers would agree with me that your writing skills are exemplary the sql 2014 version is such an awesome tool

    ReplyDelete