September 10, 2018 at 3:38 pm
Dear Colleagues,
I am having a kerberos-related problem. I have a network of two SQL Servers that reference each other via Linked Servers. The linked servers use integrated kerberos authentication (that is, authentication is "made using the login's current security context" in the linked server properties window).
When testing the linked server connections, I get this error: Login failed for user 'NT AUTHORITY/ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456), but I am able to connect directly to both servers in SSMS. I also verified that each server can connect directly to the other server with the service account credentials.
This was working as of Friday. We made some updates to DNS settings in the network over the weekend, but rolled those changes back when we ran into this problem. The DNS changes and rollback have propagated throughout Active Directory.
Both servers are on the same domain and both are a part of the same subnet. The linked servers use the SQL Server Native Client 11.0. I verified that the linked servers specify the correct NetBIOS name of each target server, and I verified that DNS resolves both NetBIOS names to the correct IP Addresses.
I rebooted both SQL severs and verified that each properly registered an SPN on the domain using the correct NetBIOS name. Both SQL Servers use the same service account for the SQL Server service. The service account has sysadmin rights on both servers and domain admin rights on the domain.
The service account is Trusted for General Delegation on the domain, and I used setspn to verify that the service account has both of the SQL servers' SPN's registered to it.
In desperation, I also tried forcing the linked servers to connect via TCP and via Named Pipes, using port 1433. I also verified that port 1433 is open on the Network firewall.
I've run out of things to check. Does anyone have any suggestions for me?
"Help me, Obi-Wan Kenobi. You're my only hope!"
Thank you for your help. As always, it is much appreciated.
🙂
September 10, 2018 at 5:51 pm
You must register the DNS as an SPN under the SQL Server Service account for Kerberos delegation to work to pass the token to the linked server.
September 10, 2018 at 6:07 pm
Joe Torre - Monday, September 10, 2018 5:51 PMYou must register the DNS as an SPN under the SQL Server Service account for Kerberos delegation to work to pass the token to the linked server.
Both server 1 and server 2 successfully registered an SPN when the SQL Service started:
"The SQL Server Network Interface library successfully registerd the Service Principal Name (SPN) [MSSQLSvc/Server.Domain.com:1433] for the SQL Server service."
Is this what you meant?
September 10, 2018 at 7:16 pm
Additional note:
I enabled Kerberos Logging on SQL Server 1, and received KDC_ERR_BADOPTION errors when I tried to connect to the linked server, which indicates that it is definitely a Delegation problem. However, everything seems to be set up correctly. What am I missing?
Thank you, again. 🙂
September 11, 2018 at 10:19 am
If you only connect using the machine name that should be working. If you are connecting using a DNS that isn't Server.Domain.com you need to resister that name as an SPN too.
September 13, 2018 at 11:10 am
There is one additional fact about our environment that is significant, but I did not realize that it was significant. We use aliases for our SQL Servers (via DNS CNames). The problem appears to be that the initial connection to the SQL Server (the first "hop") is using NTLM authentication if we reference the alias.
To fix this, I added a job to the Windows Task Scheduler which registers an SPN for the alias when the server starts.
SetSPN -s "MSSQLSvc/ServerAlias.Domain.com:1433" "Domain/ServiceAccount"
Thank you for your help, Joe. Kerberos is a really deep rabbit-hole. 😀
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply