TL;DR
This guide compares Kerberos and NTLM for SQL Server authentication, explaining how each protocol works and why NTLM fails in multi-hop scenarios like linked servers. It covers the double-hop issue, step-by-step Kerberos configuration (SPN registration, Active Directory delegation), verification queries, and troubleshooting tips, helping you choose and correctly set up the right authentication method for secure, scalable SQL Server environments.
When setting up SQL Server authentication, especially in a multi-tiered environment, choosing the right authentication protocol is critical for security and performance. Two commonly used authentication mechanisms in Windows-based environments are:
- NTLM (New Technology LAN Manager)
- Kerberos Authentication
Understanding how these protocols work, their limitations, and their impact on multi-hop authentication (double-hop scenarios) is crucial for securing SQL Server logins.
Understanding NTLM Authentication
What is NTLM?
NTLM is a challenge-response authentication protocol used in Windows environments. It does not support delegation, which makes it less suitable for multi-hop authentication scenarios.
How NTLM Authentication Works?
- The client sends a login request to the server.
- The server responds with a challenge (a random value).
- The client encrypts this challenge with its hashed password and sends it back.
- The server validates the response with the Active Directory domain controller (DC).
Key Features of NTLM:
Does not require additional configuration in SQL Server.
Works without Kerberos delegation.
No support for multi-hop authentication (prevents access to resources beyond the first server).
Struggling with SQL Server authentication issues? Talk to our Data Engineering experts →
Limitations of NTLM in SQL Server Authentication
- NTLM cannot pass user credentials beyond the first hop.
- If you try to connect from one SQL Server to another (e.g., SQL Server → Web Server → SQL Server), NTLM fails because it does not support delegation.
- NTLM is less secure than Kerberos.
Understanding Kerberos Authentication
What is Kerberos?
Kerberos is a ticket-based authentication protocol that allows secure authentication and supports multi-hop delegation.
How Kerberos Authentication Works?
- The user logs in and requests a Ticket-Granting Ticket (TGT) from the Key Distribution Center (KDC).
- The KDC validates the credentials and issues a TGT.
- When the user accesses SQL Server, a Service Ticket is generated.
- The SQL Server validates the ticket and grants access.
- In multi-hop scenarios, Kerberos allows delegation of the authentication token.
Key Features of Kerberos:
- Supports multi-hop authentication (e.g., SQL Server → Linked Server).
- More secure than NTLM.
- Works with Active Directory authentication.
- Requires SPNs (Service Principal Names) and Delegation to be configured.
Need help configuring secure, scalable SQL Server access? Explore our Data Engineering services →
Kerberos vs. NTLM: Key Differences
| Feature | NTLM | Kerberos |
| Authentication Type | Challenge-Response | Ticket-Based |
| Security Level | Weaker | Stronger |
| Supports Multi-Hop | No | Yes |
| Performance | Slower | Faster |
| Requires SPN Configuration | No | Yes |
| Works Without Active Directory | Yes | No |
Multi-Hop Authentication in SQL Server: The Double-Hop Issue
What is the Double-Hop Issue?
In a multi-hop authentication scenario, a client connects to Server A, which then needs to pass authentication to Server B (e.g., using Linked Servers or Remote Queries).
- With NTLM, authentication fails because credentials cannot be passed beyond the first server.
- With Kerberos, authentication succeeds if delegation is configured correctly.
Example Scenario: Linked Server with Multi-Hop Authentication
Consider the following:
- Client → Web Server (First Hop Works)
- Web Server → SQL Server 1 (Second Hop ❌ Fails with NTLM, Works with Kerberos)
- SQL Server 1 → SQL Server 2 (Third Hop Works with Kerberos Delegation)
Configuring Kerberos Authentication for SQL Server
Step 1: Register Service Principal Name (SPN)
To use Kerberos with SQL Server, register an SPN for the SQL Server service account.
setspn -S MSSQLSvc/sqlserver1.contoso.com:1433 CONTOSO\sqlservice
- MSSQLSvc → SQL Server service
- sqlserver1.contoso.com → SQL Server FQDN
- 1433 → SQL Server port
- CONTOSO\sqlservice → Service account
Verify SPN registration:
setspn -L CONTOSO\sqlservice
Step 2: Configure Kerberos Delegation in Active Directory
- Open Active Directory Users and Computers (ADUC).
- Find the SQL Server service account (e.g., CONTOSO\sqlservice).
- Go to Properties → Delegation Tab.
- Select Trust this computer for delegation to any service (Kerberos only).
- Restart the SQL Server service.
Step 3: Verify Kerberos Authentication in SQL Server
Run the following command in SQL Server:
SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID;
- Returns KERBEROS → Authentication is working correctly.
- Returns NTLM → SPN or delegation is misconfigured.
Testing Multi-Hop Authentication
Check Authentication Type from a Remote Server
- Open SQL Server Management Studio (SSMS).
- Connect to SQL Server 1.
- Execute the following query on a linked server:
EXEC (‘SELECT SYSTEM_USER, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID;’) AT [SQLServer2]
- If Kerberos is used, you will see KERBEROS.
- If NTLM is used, multi-hop authentication will fail.
Troubleshooting Kerberos Authentication Issues
1. Check SPN Registration
setspn -L CONTOSO\sqlservice
Ensure no duplicate SPNs exist.
2. Ensure Delegation is Enabled in Active Directory
- Open Active Directory Users and Computers → Check Delegation settings.
3. Verify Authentication Mode in SQL Server
SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID;
Conclusion
For SQL Server authentication, Kerberos is the preferred authentication protocol due to its security, speed, and support for multi-hop authentication.
- Use NTLM only if you don’t require multi-hop authentication.
- Use Kerberos if you need secure delegation across multiple servers.
- Register SPNs and configure delegation properly to avoid the double-hop issue.
By implementing Kerberos authentication, you ensure a secure and scalable SQL Server environment.
Get expert support setting up Kerberos delegation the right way. See how we can help →
Frequently Asked Questions
Common questions about Kerberos, NTLM, and the double-hop authentication issue in SQL Server.
SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID
If it returns KERBEROS, authentication is working correctly. If it returns NTLM, your SPN or delegation setup needs to be fixed.
Author By
Afroz Labbai
Afroz is a Data Engineer and Team Lead with 4+ years of experience in building scalable data platforms using Azure technologies such as Azure Data Factory, Azure SQL, Databricks, and Synapse. He specialises in ETL development, SQL optimisation, and data modelling, delivering reliable and cost-efficient solutions for enterprise analytics. He is passionate about continuous learning, mentoring teams, and driving data-driven decision-making.
