Kerberos authentication on SQL Server
RobertKaucher
Member Posts: 4,299 ■■■■■■■■■■
in Off-Topic
Today, it actually started yesterday, I had an issue with a SQL Server that was not authenticating using Kerberos. I had configured teh SPNs properly, forced AD replication, rebooted the offending SQL Server, done everything you can possibly imagine.
Everything was correct. But every time I ran the query
it returned NTLM. The reasson I am posting this is because there were no errors in the event log, there was nothing to tell me what the issue might have been. Wireshark was useless except for it's lack of Kerberos traffic, but I already knew it wasn't getting the SPN. One of my coworkers suggested powering off all of the DCs except the one on which I was using ADSI edit to configure the SPNs.
As soon as the other two DCs were powered down it worked. I Cleared my Kerberos tickets and powered on the other DCs and again, it worked. I Then powered down the third DC, the one where I was making the schema edits, and cleared my tickets and it still worked. I probably could have used nltest to see which dc was the offender, but I figured I would need to reboot anyway.
Hopefully someone who is having this issue will find this and it will save them some time.
Everything was correct. But every time I ran the query
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] auth_scheme[/SIZE] [SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]sys[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]dm_exec_connections[/COLOR][/SIZE] [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] session_id [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]@@SPID[/COLOR][/SIZE] [/COLOR][/SIZE]
it returned NTLM. The reasson I am posting this is because there were no errors in the event log, there was nothing to tell me what the issue might have been. Wireshark was useless except for it's lack of Kerberos traffic, but I already knew it wasn't getting the SPN. One of my coworkers suggested powering off all of the DCs except the one on which I was using ADSI edit to configure the SPNs.
As soon as the other two DCs were powered down it worked. I Cleared my Kerberos tickets and powered on the other DCs and again, it worked. I Then powered down the third DC, the one where I was making the schema edits, and cleared my tickets and it still worked. I probably could have used nltest to see which dc was the offender, but I figured I would need to reboot anyway.
Hopefully someone who is having this issue will find this and it will save them some time.