May 2, 2017 at 5:03 pm
Has any one else experienced Kerberos issues with SQL 2016?
We have 4 servers, dev, test, uat and prod running SQL 2016 with SP1 on Windows 2012 and they are all having the same issue. If I try and use a linked server I often get the usual 'Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' problem. The service accounts are domain accounts, are trusted for delegation and have rights over the target servers and most of the time it works OK. I have confirmed that I am authenticating against the source server using kerberos when the issue happens and multiple people are experiencing the problem so it doesn't appear to be related to one account.
While demonstrating the issue to someone yesterday I added a new linked server and all of the other ones suddenly started working again. This morning they are all broken again.
I am not sure if this is a SQL 2016 issue, a windows 2012 issue or some sort of networking issue so any suggestions on the best way to troubleshoot it?
May 8, 2017 at 3:27 pm
CC-597066 - Tuesday, May 2, 2017 5:03 PMHas any one else experienced Kerberos issues with SQL 2016?We have 4 servers, dev, test, uat and prod running SQL 2016 with SP1 on Windows 2012 and they are all having the same issue. If I try and use a linked server I often get the usual 'Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' problem. The service accounts are domain accounts, are trusted for delegation and have rights over the target servers and most of the time it works OK. I have confirmed that I am authenticating against the source server using kerberos when the issue happens and multiple people are experiencing the problem so it doesn't appear to be related to one account.
While demonstrating the issue to someone yesterday I added a new linked server and all of the other ones suddenly started working again. This morning they are all broken again.
I am not sure if this is a SQL 2016 issue, a windows 2012 issue or some sort of networking issue so any suggestions on the best way to troubleshoot it?
Just fyi, I've seen this issue on SQL Server 2008 R2 also. I have no resolution for this issue. Also this seems to only happen when I'm working in SSMS interactively. Sorry I have no resolution for you though.
May 31, 2017 at 3:18 pm
Thanks for your response. I raised a call with Microsoft and this looks like it is actually an issue in SQL 2016. We have changed the service account that is used for the SQL Server service from 'Trust this user for delegation to specific services only' to 'Trust this service for delegation to any service' and the problem seems to have gone away. The way that kerberos tickets are handled is apparently quite different between the two methods.
It was only affecting our SQL 2016 servers so MS are raising a defect with the development team.
May 31, 2017 at 3:54 pm
CC-597066 - Wednesday, May 31, 2017 3:18 PMThanks for your response. I raised a call with Microsoft and this looks like it is actually an issue in SQL 2016. We have changed the service account that is used for the SQL Server service from 'Trust this user for delegation to specific services only' to 'Trust this service for delegation to any service' and the problem seems to have gone away. The way that kerberos tickets are handled is apparently quite different between the two methods.It was only affecting our SQL 2016 servers so MS are raising a defect with the development team.
Interesting so Microsoft did confirm that it is a defect or a bug of some sort?
By chance, were you checking the tickets using klist?
Sue
June 1, 2017 at 6:51 pm
Not sure what it would be classified as but the email I received from the Microsoft engineer said that he had raised an RFC with the product team.
We did use klist to monitor and clear the kerberos tickets and one of the system engineers also created a Wireshark dump which showed the issue so I gave that to Microsoft as well.
I think that with un-constrained delegation the client ticket is passed through to the server referenced in the linked server entry but with constrained delegation the service account on the first hop server goes back to the domain controller to get a new ticket so I assume it is this process that is breaking down.
July 14, 2022 at 7:08 am
we are facing similar intermittent linked server connectivity issues
some parts of the day the linked server works, others it fails with the dreaded 'NT AUTHORITY\ANONYMOUS LOGON' problem
Netmon traces find that when it fails, it does drop back down to NTLM which isnt supported with double hop authentication and dropped down to NTLM leads to SPN issues, which we have confirmed everything is setup
On our middle server we do have constrained delegation setup, similar to the above.
In regards to Microsoft highlighting the fact that it is a bug, do you recall what you found in the logs that lead them to this conclusion ?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply