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 http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx. (This is a great source for the concept too.)