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
No comments:
Post a Comment