May 4, 2017 at 7:55 am
I've still seen it both service account and/or machine as was well but I would guess it's just on the service account and needing it for the machine is something else.
Maybe get rid of the spns you have now, flush the cached tickets and then monitor what's going on with the tickets using klist.
This article is better than some of the others in terms of troubleshooting - might be worth working through it:
SQL Server Kerberos and SPN Quick Reference
Sue
May 4, 2017 at 11:58 am
Adam Bean - Thursday, May 4, 2017 7:16 AMadb2303 - Thursday, May 4, 2017 6:05 AMThis doesn't sound right. Permitting delegation on the machine account suggests that the service is/was running as a local system account. I would use setspn to delete all the SPN's registered against the new account, old account and machine... then restart SQL Server and let SQL register the SPN's again cleanly. You can then go back into AD, find the service account and permit delegation to those services.I removed the service accounts delegation permissions yesterday and it continued to work. Yet this morning, it is now broken. Perhaps a caching issue.
So currently:
- SQL running as a domain account
- Domain account can create/modify SPN's
- Machines can delegateSo I'm back to the question from earlier, do I really need delegation on BOTH the account and machine?
No, if the SQL Server service is running under a domain account, you don't need the machine SPN's... in fact, they're likely interfering with it all.
- I would remove the SPN's associated with the machine accounts. You can do this using setspn -d command against the SPN's that are listed for the server... you can get this my doing setspn -l hostname
- Check that delegation for the service account is set in AD... this is for the double hop, which is the error you're getting regarding anonymous logons...
it should be okay after that. If not, delete the SPN's for the service account and restart SQL Server (if possible), then check the SQL Server error log that it's successfully created the SPN's. Recheck delegation in AD
May 17, 2017 at 9:35 am
OK ... back at this. Started clean.
- Validated service account has proper SPN permissions
- Removed existing SPN's
- Waited 15 minutes for replication, validated gone
- Granted Keberos delegation to service accounts
- Waited 15 minutes for replication
- Restarted SQL on both instances
- Waited 15 minutes for replication
- Validated that SPN's created without issue
- Setup a linked server between the two, using current logins security context "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."
The Linked Server works fine when I go server to server, it's when I introduce the double hop (my local/client machine) that this scenario presents itself.
So, this is unfortunate. Does this mean that we have to set the delegation at the machine level then? I'm going to let it sit for a bit longer to ensure it's not something additional with cache/replication/etc.
Thanks
May 17, 2017 at 11:20 am
Well, after waiting it out and coming back ... it's all working. So moral of the story, the service account needs delegation permissions.
Thanks all!
May 17, 2017 at 11:25 am
Adam Bean - Wednesday, May 17, 2017 11:20 AMWell, after waiting it out and coming back ... it's all working. So moral of the story, the service account needs delegation permissions.Thanks all!
Glad it's finally working.
What's odd is that before you had sporadic issues which usually indicates means it's not configuration issues but something else - generally something with the network infrastructure when it's Kerberos. So if it comes back, I'd typically start checking the tickets using klist.
If you don't want to use klist then look at enabling Kerberos logging on the servers - it does to the system log, doesn't require a reboot to enable.
But I hope it just stays as it is for now and that it was just the delegation being messed up.
Sue
May 18, 2017 at 3:52 am
Adam Bean - Wednesday, May 17, 2017 11:20 AMWell, after waiting it out and coming back ... it's all working. So moral of the story, the service account needs delegation permissions.Thanks all!
This is correct, it's the service account that delegates a users security context, not the machine. The clue here is the SPN is bound to the user account that the service is using 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply