I have two SQL Server 2016 Standard Edition servers on the same domain. ServerA has two linked server connections setup to ServerB.
The first linked server connection is using a dedicated remote SQL Login security context. That one always was and still is working fine.
The second connection uses the "login's current security context" which is a Windows Authentication (AD) Login on ServerA. This was also working fine up until today.
Today out of nowhere, everyone who connects to ServerA and then tries to run a query that uses the second linked server connection, ends up getting hit with this error:
OLE DB provider "SQLNCLI11" for linked server "LinkedServerName" returned message "Cannot generate SSPI context". Msg -2146893044, Level 16, State 1, Line 0 SQL Server Network Interfaces: The logon attempt failed
Also the users are able to directly connect to both ServerA and ServerB fine currently, and even execute queries against objects on ServerB directly, but when they try to run queries referencing the same objects on ServerB via the linked server on ServerA is the only time they get the above error, currently.
This is what we currently see for SPNs and logs (sorry for all the obfuscation):
ServerB Successful SPN Registration SQL Logs:
ServerB Successful SPN Registration SQL Logs:
- Notice both servers are successfully registering SPNs for themselves for both port 1433 and no port specified.
ServerA Registered SPNs:
- I'm not sure what the second "CN=" line (second to last line) is but it literally says "CN=ServerASvc", but that's not the name of the SQL Service account. The SQL Service Account is "SQLServiceAccount1".
- I'm also not sure why the last line has an SPN for just the server name "ServerA" (which we do have a Host / CName for in our DC), but seems redundant to the one of the previous lines.
ServerB Registered SPNs:
To recap my SPNs:
- My ServerA's SQL Server Instance's Service Account (let's call it ServiceAccountA) has an SPN to trust its own server.
- My ServerB's SQL Server Instance's Service Account (ServiceAccountB) has an SPN to trust its own server.
- Delegation is setup for ServiceAccountA to be able to access the SPN for ServiceAccountB.
ServerA's service account is SQLServiceAccount1 and is also the same service account used on our DevSqlServer1.
ServerB's service account is SQLServiceAccount2 and is also the same service account used on our DevSqlServer2.
Running the query
SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID returns
KERBEROS on both ServerA and ServerB for me.
Also, different SQL Server Instance Service Accounts are running for each server. ServiceAccountA for ServerA, and ServiceAccountB for ServerB.
The night before this issue started happening, Windows updates were installed on ServerB (and a few of our other servers). Specifically this update November 10, 2020—KB4586830 (OS Build 14393.4046) which apparently states this:
After installing this update on domain controllers (DCs) and read-only domain controllers (RODCs) in your environment, you might encounter Kerberos authentication and ticket renewal issues. This is caused by an issue in how CVE-2020-17049 was addressed in these updates.
We tried rolling back the update, which invoked a restart of the server and things seemed to start working again, but then started breaking again for just our web server (a third / separate server), which hits our ServerA to query the linked server to ServerB.
Originally ServerA and ServerB were part of separate Windows Failover Clusters (ServerA1 & ServerA2, and ServerB1 & ServerB2). When that was the case, this DBA.StackExchange answer by Hannah Vernon sounded similar to what we experienced.
The only difference is we previously weren't letting the Service Accounts register / deregister the SPNs, when the issue started. Now we are letting them do so, and there haven't been any failover events, and we triple checked all SPNs are setup correctly currently. So I don't think that's our issue here.
At this point in time, both clusters are destroyed, and the secondary servers removed, yet the issue still persists with the individual servers (ServerA and ServerB).
Things We've Tried / Issue Progression
Logging out and logging back in after changing my AD password did not fix the issue, but physically restarting my machine temporarily did fix it for just me.
setspn -l ServerA_SQLServiceAccountName and after I did that, I did see the SPN that was in my SQL Error Log. So I think Pinal Dave was right in my case.
Today I woke up and everyone on my domain is hosed again, myself included. I tried flushing the DNS on ServerA, and now we're receiving the error "Linked Server error: Login Failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" instead of my original error, when querying the linked server from ServerA.
It appears I am facing some sort of SPN / double-hop issue because when I RDP directly to ServerA and connect to my SQL Instance on ServerA (from within the RDP session - same account as I'm connecting to the instance when not RDP'ed in) and try querying the linked server, everything is all good. The question is, are my SPNs actually correct? (See current SPNs in the "Relevant Information" section above.)
We triple checked the SPNs, and the AD delegation properties of the Service Accounts. We're fairly confident all ducks are in order. We're fully down again: web server, SSMS, etc, no matter how we try to query the linked server, which I think is due to the default 10 hour ticket time of Kerberos lapsing, and it not correctly renewing a new instance of the same ticket. We're currently facing this error:
Msg 18456, Level 14, State 1, Line 1 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
We've been trying to troubleshoot using the Kerberos Configuration Manager and the only issue it found (this entire time) is a warning saying "Kerberos Configuration Manager reports that "TCP must be enabled to use Kerberos Authentication":
We've checked TCP is enabled in both of our servers, so I'm not sure why it's saying this warning or how to troubleshoot it, and if it's our root issue or not.
Another thing worth noting is we've only been intermittently down for most of the time. That is, some users are able to successfully query the linked server when using our applications (web, mobile, etc) and some aren't. So if it was a true SPN issue, I would think no one would be able to query the linked server - an all or nothing situation. This definitely feels more like a random Kerberos issue (possibly related to the Windows Update mentioned earlier).
At this point, we've tried
purge on our KLIST Kerberos tickets,
ipconfig /flushdns both on the client machine and servers (SQL Servers, DNS Servers, and Web Server), and have tried restarting the client machines and server machines, rolling back the aforementioned Windows Update on whatever machines we were reliably sure had it, in addition to everything else previously mentioned, to no avail. At best we've gotten very intermittent mixed results with sometimes restarting the client machine sort of works, temporarily.
Currently everything is truly a single server for ServerA and a single server for ServerB (these were Windows Failover Clusters at the start of this issue, but have seen been removed and the clusters destroyed). We also have dev copies of ServerA (ServerADev) and ServerB (ServerBDev) which share the same SQL Service accounts.
We've since setup replication from ServerB to ServerA to side-step the issue in production for now, but we encountered the same issue on our dev environment this week as well.
I tried using
xp_cmdshell to run
klist purge and I cycled the SQL Services for both ServerADev and ServerBDev, but the issue still persisted.
At this point, I'm pretty sure the issue is due to the botched Windows update November 10, 2020—KB4586830 (OS Build 14393.4046) that specifically states "you might encounter Kerberos authentication and ticket renewal issues". Unfortunately this was installed on a multitude of our servers (both SQL Servers and Domain Controllers) and subsequent Windows updates that have ran, remove the botched update from the Windows Update History, so I have no reliable way to tell which servers even originally received the botched update.