Kerberos and NTLM authentication methods in SQL Server.
 

Kerberos Authentication:

Kerberos is a network authentication protocol that is used to provide secure authentication between clients and servers in a Windows environment. In SQL Server, Kerberos authentication can be used to authenticate users without the need to provide a username and password each time they connect to the server.

Kerberos authentication works by using a ticket-based authentication system. When a user logs into a Windows machine, their credentials are verified by the Domain Controller (DC) and a Kerberos ticket is issued. This ticket is then used to authenticate the user to other network resources, including SQL Server.

 

To use Kerberos authentication with SQL Server, the following requirements must be met:

 

Ø  The SQL Server instance must be running on a Windows Server operating system.

Ø  The SQL Server service account must be a domain account.

Ø  The client machine and SQL Server must be members of the same domain.

Ø  The client machine must be configured to use Kerberos authentication.

 

Example:

Suppose a user named "Arshad" wants to connect to a SQL Server instance named "SQLSERVER" using Kerberos authentication. Arshad logs into his Windows machine and requests a Kerberos ticket from the Domain Controller. The Domain Controller verifies Arshad's credentials and issues a Kerberos ticket.

When Arshad tries to connect to SQLSERVER, SQL Server requests a Kerberos ticket from the client machine. Arshad's client machine sends the Kerberos ticket to SQL Server, and SQL Server uses it to authenticate Arshad without requiring him to provide a separate username and password.

NTLM Authentication:

 

NTLM (NT LAN Manager) is a Microsoft authentication protocol that is used to provide secure authentication in a Windows environment. In SQL Server, NTLM authentication can be used to authenticate users without the need to provide a username and password each time they connect to the server.

NTLM authentication works by using a challenge-response mechanism. When a user logs into a Windows machine, their credentials are verified by the Domain Controller (DC). When the user tries to connect to a network resource, such as SQL Server, the server sends a challenge to the client machine. The client machine responds with a hashed version of the user's credentials, which the server verifies to authenticate the user.

To use NTLM authentication with SQL Server, the following requirements must be met:

Ø  The SQL Server instance must be running on a Windows Server operating system.

Ø  The client machine and SQL Server must be members of the same domain or have a trust relationship.

Ø  The client machine must be configured to use NTLM authentication.

Example: 

Suppose a user named "Siri" wants to connect to a SQL Server instance named "SQLSERVER" using NTLM authentication. Siri logs into her Windows machine, and when she tries to connect to SQLSERVER, the server sends a challenge to the client machine.

Siri's client machine responds with a hashed version of her credentials, and SQL Server verifies the credentials to authenticate Siri without requiring her to provide a separate username and password.

It's worth noting that while both Kerberos and NTLM authentication methods provide secure authentication in a Windows environment, Kerberos is generally considered more secure and is the recommended authentication method for SQL Server.

Feature	NTLM	Kerberos
Password Transmission	Password is hashed and sent over the network	Password is never sent over the network
Authentication Method	Challenge-response authentication	Ticket-based authentication
Mutual Authentication	Not required	Required
Security	Vulnerable to certain attacks such as replay attacks and man-in-the-middle attacks	Resistant to certain types of attacks
Compatibility	Compatible with older versions of Windows	Requires Active Directory and newer versions of Windows
Performance	Can be slower than Kerberos due to the need for hashing and response generation	Generally faster than NTLM due to the use of tickets

Configure SQL Server to use Kerberos authentication: Use the setspn utility to create the SPN for the SQL Server instance. This command should be run on the domain controller, and should look something like this:

setspn -A MSSQLSvc/<servername>.<domainname>:<port> <SQL Server service account>

Note that the <servername> and <domainname> should be replaced with the appropriate values, and <port> should be the port number used by SQL Server (usually 1433).