MSSQL Authentication

Check

To determine the authentication method of your connection use...

SELECT net_transport,

       auth_scheme,

       encrypt_option,

       last_read,

       client_net_address,

       local_net_address

  FROM sys.dm_exec_connections 

 WHERE session_id = @@SPID;

"You must run this on a client machine and not on the SQL Server you are testing, otherwise it will come back as NTLM even if Kerberos is properly configured. This is due to per-service SID security hardening added in Windows 2008, which makes all local connections use NTLM regardless of whether Kerberos is available."(1)

For info on all connected sessions use...

SELECT a.session_id,

       b.connect_time,

       a.login_time,

       a.login_name,

       b.protocol_type,

       b.client_net_address,

       b.auth_scheme,

       a.HOST_NAME,

       a.program_name

  FROM sys.dm_exec_sessions a

  JOIN sys.dm_exec_connections b

    ON a.session_id = b.session_id

 ORDER BY b.client_net_address

Or...

SELECT s.session_id,

       s.original_login_name,

       c.net_transport,

       c.auth_scheme,

       c.local_net_address,

       c.local_tcp_port,

       s.program_name

  FROM sys.dm_exec_sessions s

  LEFT OUTER JOIN sys.dm_exec_connections c

    ON (s.session_id = c.session_id)

 WHERE s.is_user_process = 1

Scenario: The SPN maps to the correct domain account, virtual account, Managed Service Account (MSA), or built-in account. For example, Local System or NETWORK SERVICE.Authentication Method: Local connections use NTLM, remote connections use Kerberos.
Scenario: The SPN is the correct domain account, virtual account, MSA, or built-in account.Authentication Method: Local connections use NTLM, remote connections use Kerberos.
Scenario: The SPN maps to an incorrect domain account, virtual account, MSA, or built-in account.Authentication Method: Authentication fails.
Scenario: The SPN lookup fails or doesn't map to a correct domain account, virtual account, MSA, or built-in account, or isn't a correct domain account, virtual account, MSA, or built-in account.Authentication Method: Local and remote connections use NTLM.

NTLM

NOTE: NTLM is not considered secure. NTLMv2 has some security improvements around the strength of cryptography, but flaws remain.

Kerberos

Automatic SPN Registration

SQL Server will attempt to register a Service Principal Name on startup of the SQL Server service but will fail to do so unless any one of the following is true...

Manual SPN Registration

Bibliography & References



Kerberoshttps://stackoverflow.com/questions/60595463/microsoft-sql-server-auth-scheme-do-not-show-kerberoshttps://support.microsoft.com/en-us/topic/how-to-troubleshoot-the-cannot-generate-sspi-context-error-message-03d15ff2-e062-e023-272c-d5cd413c37b3https://learn.microsoft.com/en-US/troubleshoot/sql/database-engine/connect/cannot-generate-sspi-context-errorhttps://documentation.red-gate.com/sm/troubleshooting/error-messages/sql-monitor-connection-error-cannot-generate-sspi-contexthttps://techcommunity.microsoft.com/t5/sql-server-support-blog/getting-cross-domain-kerberos-and-delegation-working-with-ssis/ba-p/318361https://techcommunity.microsoft.com/t5/sql-server-support-blog/my-kerberos-checklist-8230/ba-p/316160(1) https://github.com/microsoft/CSS_SQL_Networking_Tools/wiki/Determine-If-I-Am-Connected-to-SQL-Server-using-Kerberos-Authentication
NTLMhttps://www.preempt.com/blog/ntlm-security-risks/ http://web.archive.org/web/20160201034029/http://blogs.msdn.com/b/openspecification/archive/2010/05/03/ntlm-v1-no-excuse-me-ntlm-v2-oh-no-you-were-right-it-s-v1.aspx
Register a Service Principal Name for Kerberos Connectionshttps://docs.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections?view=sql-server-ver15 
Introducing the Restriction of NTLM Authenticationhttps://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/dd560653(v=ws.10)?redirectedfrom=MSDN 
Supported scenarios for restricting NTLM in a domainhttps://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/jj865677(v=ws.10) 
Disable NTLMhttps://www.top-password.com/blog/prevent-ntlm-credentials-from-being-sent-to-remote-servers/