Skip to main content

Authenticated mechanism used to connect to SQL Server in Active Directory network - Kerberos and NTLM

While we use Integrated Authentication or trusted connection, NTLM and/or Kerberos used to get a client authenticated with a remote SQL Server. Here remote SQL Server is actually a network resource (service) for the client who uses these standard practices in Active Directory to communicate securely.

First let us understand little more about these authentication mechanisms first:

NTLM: In this case, client sends the user name to the server. Then the server generates and sends a challenge to the client. Then client encrypts that challenge using the user’s password and the sends a response to the server. If it is a local user account, server validates user's response by looking into the Security Account Manager. If domain user account, server forward the response to domain controller for validating and to retrieve group policy of the user account. If client is authorized, server constructs an access token and establishes a session for the use of client.

Kerberos:  This authentication provides a mechanism for mutual authentication between a client and a server on an open network using a third party. The three heads of Kerberos comprise the Key Distribution Center (KDC), the client user and the server offering the desired service to access. The KDC is installed as part of the domain controller and performs the Authentication Service (AS) and the Ticket-Granting Service (TGS) functions. When the client user log on to the network, it request a Ticket Grant Ticket(TGT) from the AS in the user's domain. Then when client want to access the network resources (in our case, SQL Server), it presents the TGT, an authenticator and Server Principal Name (SPN) of the target server to TGS in the domain to retrieve a session ticket for future communication with the network service. Once the target server (SQL Server) validates the authenticator, it creates an access token for the client user.

So to use Kerberos, below requirements must be satisfied:

SQL Service (instance) must have a valid registered SPN in the domain.
Client, server and third party (KDC) must be in same domain. If client and server are in different domain for some special reason, these two domains must be configured as two-way trust.

Now, when a client attempt to connect to a SQL Server in a network, below factors play a commanding role to decide if Kerberos or NTLM will be used as Authentication Mechanism:
1)  Kerberos is used when making remote connection over TCP/IP if SPN presents for the SQL Server.
2) Kerberos is used when making local tcp connection on XP if
SPN presents for the SQL Server.
3) NTLM is used when making local connection on
WIN 2003.
4) NTLM is used over NP connection.
5) NTLM is used over
TCP connection if SPN is not found/properly registered.
This concept can impact a database professional’s life very closely and s/he needs to clearly understand to effectively troubleshoot login failures/security issues faced by client using integrated authentication.

So, if you are in a situation where your client is having trouble to connect to your SQL Server using intergraded security, follow below steps to troubleshoot the situation:

Double check if client really have access (classical methods of verifying login etc and by reading logs)
Double check if SPN is properly registered for your server if SQL Server is using Kerberos
Analyze the log and do further troubleshooting as per (This is a great source for the concept too.)


Popular posts from this blog

How to kill a negative SPID (like SPID -2) in SQL Server?

Rarely this scenario will arise when most likely you see this negative SPID (most likely SPID -2) is blocking other transaction causing issues. If you try to kill it using normal KILL command, it will fail reporting below error: Msg 6101, Level 16, State 1, Line 1 Process ID <SPID Number> is not a valid process ID. Choose a number between 1 and 2048 This is because of an orphaned distributed transaction ID.  Please follow below steps to kill it: Step 1: -- Find the UOW Number select req_transactionUOW from master..syslockinfo where req_spid = <SPID Number> --  <SPID Number>  is -2 most likely. Step 2: -- Copy the UOW number from Step one KILL ‘<UOW Number>’ This will kill the negative SPID resolving the issue.  However please note following points: 1. For SPID -2, you may find multiple UOW numbers. Please start killing them one by one. Typically killing first UOW will resolve the issues. (ie. will kill all UOW and release

DMV/TSQL to find out basic hardware information of the SQL Server including when SQL Server started.

Please use below code: However, please be advised that it can not tell correct information around virtualization.  For example, it will show Hypervisor even if SQL runs on a physical OS where Hyper-V is on. So use this query only when you do not have sufficient access on underlying Windows Operating system to get these information directly. -- Basic hardware information for SQL Server (sys.dm_os_sys_info) /* This query is courtesy of All credits goes to original author. */ SELECT cpu_count AS [Logical CPU Count] , scheduler_count , hyperthread_ratio AS [Hyperthread Ratio] , cpu_count / hyperthread_ratio AS [Physical CPU Count] , physical_memory_kb / 1024 AS [Physical Memory (MB)] , committed_kb / 1024 AS [Committed Memory (MB)] , committed_target_kb / 1024 AS [Committed Target Memory (MB)] , max_workers_count AS [Max Workers Count] , affinity_type_desc AS [Affinity Type] , sqlserver_start_time AS [

‘Trace Skipped Records’ – What is this and how to resolve it while using SQL Server Profiles?

In some very rare case, you may experience a very weired message in profiler’s output as ‘Trace Skipped Records’ while you trace something on SQL Server. Screenshot of similer situation is as below: This is not an error but it comes by design of SQL Server (I believe so). When you are using SQL profiler and return data is too big to fit in the GUI (for me, it is an enormous xml), SQL Server simply prints this message and proceed to next step. Mostlikely this is to save server’s memory and performance. Although not suggested and guranteed, you can try to run a server side trace and dump data in a file which should capture all the data. However, it is strongly not recommended to run a trace on your production server from server side. Microsoft will probally document this limitation in future. More details may be found at