Kerberos vs. NTLM for SQL Server Login and Multi-Hop Authentication 

Introduction 

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? 

1. The client sends a login request to the server. 

2. The server responds with a challenge (a random value). 

3. The client encrypts this challenge with its hashed password and sends it back. 

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

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? 

1. The user logs in and requests a Ticket-Granting Ticket (TGT) from the Key Distribution Center (KDC). 

2. The KDC validates the credentials and issues a TGT. 

3. When the user accesses SQL Server, a Service Ticket is generated. 

4. The SQL Server validates the ticket and grants access. 

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

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: 

1. Client → Web Server (First Hop  Works) 

2. Web Server → SQL Server 1 (Second Hop ❌ Fails with NTLM,  Works with Kerberos) 

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

1. Open Active Directory Users and Computers (ADUC). 

2. Find the SQL Server service account (e.g., CONTOSO\sqlservice). 

3. Go to Properties → Delegation Tab. 

4. Select Trust this computer for delegation to any service (Kerberos only). 

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

1. Open SQL Server Management Studio (SSMS). 

2. Connect to SQL Server 1. 

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

Afroz Labbai  

Data Engineering  

Addend Analytics  

Addend Analytics is a leading Power BI consulting services provider and Microsoft Power BI partner based in Mumbai, India. In addition to Power BI implementations, we specialize in providing end-to-end solutions like Business Central with Power BI to unlock actionable insights. Our expertise also extends to Microsoft Fabric consulting, offering competitive Microsoft Fabric pricing to meet your business needs. 

We have successfully delivered Power BI for Manufacturing industry, with real-time Power BI manufacturing dashboards. Having successfully completed over 100 projects across industries such as financial services, banking, insurance, retail, sales, real estate, logistics, and healthcare. Whether you’re exploring Business Central implementation cost or seeking advanced data analytics, Addend Analytics is here to help. Get a free consultation now by emailing us at kamal.sharma@addendanalytics.com.