1

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?

6
  • Are you connecting from a remote client? Jul 22 at 17:36
  • Yes. Question revised with more detail.
    – JoeNahmias
    Jul 22 at 17:39
  • Did you run the Microsoft® Kerberos Configuration Manager for SQL Server® microsoft.com/en-us/download/details.aspx?id=39046? Jul 22 at 17:45
  • 1
    @DavidBrowne-Microsoft - I tried running from the remote workstation, but I get An error (1332) occurred while enumerating the group membership. The member's SID could not be resolved.
    – JoeNahmias
    Jul 22 at 17:54
  • Are you using FQDN to connect to your instance? If not you have to register SPN for netbios names too.
    – MBuschi
    Jul 23 at 7:36
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.