Cannot connect to MSSQL using kerberos auth

All we need is an easy explanation of the problem, so here it is.

We have a standalone SQL server [Microsoft SQL Server 2019 (RTM) – 15.0.2000.5 (X64)] that we’d like to use kerberos authentication with, to enable querying of other databases via linked servers. We’ve set up our MSSQL instance to run using an AD service account and given that account access to the private key for the SSL cert that the instance uses for connection encryption.

After restarting, I see in the ERRORLOG that MSSQL is able to register the appropriate SPNs:

Server      The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/mssql-server.example.org ] for the SQL Server service.
Server      The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/mssql-server.example.org:1433 ] for the SQL Server service.

However, when connecting from a remote Windows 10 machine, which is joined to the same domain as the server, using Windows Authentication in SSMS [v18.9.2] and then checking the auth_scheme I see:

/*------------------------
SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID;
------------------------*/
net_transport                            auth_scheme
---------------------------------------- ----------------------------------------
TCP                                      NTLM

(1 row affected)

Looking at the documentation I’ve found on the web, I believe I’ve done everything necessary to make this work. Any ideas what I’ve missed?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I kept trying this from another remote machine and noticed that it used Kerberos properly without any trouble.
Digging deeper, it seems there were some DNS changes related to this server which I surmise may have caused/impacted this. Back on the original remote machine that was connecting via NTLM, I ran ipconfig /flushdns, restarted SSMS, and then it connected successfully using Kerberos.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply