MSSQL Authentication

"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)

SSMS & Azure Data Studio

sqlcmd

VBScript

PS-MS-SQL

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...

The SQL Server service account is a domain admin
The SQL Server service account has been granted the Write servicePrincipalName permission
The SQL Server service account is NT Service/MSSQLServer

Manual SPN Registration

Service Principal Names (SPN)

Kerberos Configuration Manager for SQL Server

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.aspxhttps://learn.microsoft.com/en-us/sql/connect/jdbc/using-ntlm-authentication-to-connect-to-sql-server
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/ https://www.reddit.com/r/SQLServer/comments/oer7j4/sql_server_impact_on_disabling_ntlm_v10_protocol/

Page updated

Google Sites

Report abuse