How to Configure Kerberos Delegation

The connection to SQL server should be of type Kerberos and not NTLM.

  1. Check if the client connection to SQL server is Kerberos by using the following query:

    SELECT    connection_id,    session_id,  client_net_address, auth_scheme

    FROM sys.dm_exec_connections where session_id = @@spid

  2. If the connection type is Kerberos, check the SPN entry in the active directory for the computer by using the commands explained below.
  3. SPN entries must be for the Service Account user in case SQL service account is configured under domain account and for the SQL server machine if SQL service is configured under Local System account.
    Note: Check port number for SQL server instance; use this port number in executing setspn command instead of default port number 1433.

    All examples assume a SQL server service running on port 1433 on machine named ocean11 under domain ApprovaLab.int and approvalab-net\xyz is a domain account.

    1. If SQL service is running under a domain account.
      1. Check if SPN entry exists for the SQL service domain account by executing following command.

        setspn -l approvalab-net\xyz

      2. If entry exists for the SQL server machine instead for the SQL service domain account, delete the SPN entry for the SQL server machine by executing following command.
      3. If SPN entry does not exist for the SQL service domain account, create an SPN entry by executing following command.

        setspn -A MSSQLSvc/ocean11.ApprovaLab.int:1433 approvalab-net\xyz

  4. If it is NTLM check if the connection used is of type named pipe.
    • Go to Microsoft SQL Server > Configuration Tools > SQL server surface area configuration, and check the remote connections.
    • If it is of the type named pipe change to TCP/IP only.
    • Delete the old SPN entries for computer as well as the SQL service account as described above.
    • Restart the SQL service. Now the connection should be of type Kerberos.

    Verify checks mentioned in step 2 above.

    Supporting documents: