• Article

Using Kerberos Authentication with SQL Server

To use Kerberos authentication with SQL Server requires both the following conditions to be true:

  • The client and server computers must be part of the same Windows domain, or in trusted domains.

  • A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it is registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration has not been performed or fails, the Windows security layer cannot determine the account associated with the SPN, and Kerberos authentication will not be used.

    Note

    If the server cannot automatically register the SPN, the SPN must be registered manually.

You can verify that a connection is using Kerberos by querying the sys.dm_exec_connections dynamic management view. Run the following query and check the value of the auth_scheme column, which will be "KERBEROS" if Kerberos is enabled.

SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid ;

The Role of the SPN in Authentication

When an application opens a connection and uses Windows Authentication, SQL Server Native Client passes the SQL Server server name, instance name and, optionally, an SPN. If the connection passes an SPN it is used without any changes.

If the connection does not pass an SPN, a default SPN is constructed based on the protocol used, server name, and the instance name.

In both of the preceding scenarios, the SPN is sent to the Key Distribution Center to obtain a security token for authenticating the connection. If a security token cannot be obtained, authentication uses NTLM.