Why did the SPN's disappear?
-
Ran into a very puzzling issue this morning: a 2017 SE server suddenly lost all its SPN's, and I can't figure out why or how.
Background:
The service account was changed on 7/18, and the SPN's under the old account were deleted and new SPN's under the new account created. All was well.
On 9/27, I applied CU16 to the server and restarted it. Again, no problems.
Beginning just after midnight today (9/30), the log shows repeated errors involving the NT ANONYMOUS login - all related to linked servers. Reports began failing. Since linked server connection failures are often related to SPN issues, I checked the dm_exec_connections view, and sure enough, none of the remote connections were being made through Kerberos. A check of the SPN's revealed - nothing. There were no SPN's. I recreated them, and all is working well again, but knowing how this happened would be helpful. I found nothing in any of the Windows logs, and the SQL Server log showed only the failures beginning just after midnight.
Any clues, additional questions to ask, things to look into, would be greatly appreciated.
Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer -
Roland are you creating the SPN's manually?
typically, the service account running the SQL service creates the SPN on restart of the SQL service.
That means it needs the Trusted for Delegation in Active Directory.
that wahy whenever patches, reboots etc happen, the SPN's get recreated correctly.
if the service account did NOT have that setting, every time the SQL service restarts, someone might be creating the SPN manually to fix the Kerberos issue.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi Lowell,
The accounts are trusted for delegation, as are the servers. The SPN's were created automatically when the service account was changed (did not mean to imply the originals were created manually) - I explicitly checked for them at the time. Everything was functioning as expected until midnight on 9/30, at which point (or some time prior to that point) the SPN's simply disappeared. I recreated the SPN's and things are back to normal, but I'd really like to figure out what happened and why. There are a VERY limited number of people in our company with sufficient permissions to delete SPN's and I know none of them deleted anything from this box.
Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer -
did you check SQL Server logs if he attempted (unsuccessfully) to register SPNs around that time? I could imagine if there was some sort of service restart and the service was unable to query AD right away, SPN registration has been skipped.
-
There was nothing in the logs other than the login failures related to the anonymous login. The service did not restart prior to that (had been running since July 18).
Still a freakin' mystery.
Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply