SQL Server Kerberos and SPN Quick Reference

Article
12/05/2014

This post contains the references and methodology I use when troubleshooting SQL Kerberos issues. While there are already many SQL Kerberos articles in existence, the goal here is to provide a central and up-to-date resource(including new tools + updated tool syntax) which covers not just the configuration steps, but an understanding of why this is needed as well.

Cannot generate SSPI context.

Access Denied. (For example, when an account was not impersonated or delegated correctly and unexpected credentials were used to access a resource, such as a failed file share access during a bulk insert operation).

What is the difference between impersonation and delegation?

Impersonation flows the original caller's identity to back-end resources on the same computer. Delegation flows the original caller's identity to back-end resources on remote computers. Delegation impersonates the client without possession of the client’s password, it is a much higher privileged operation. Impersonation can be done with NTLM or Kerberos authentication. Delegation requires Kerberos authentication. Many times the terms are used interchangeably, but it is important to understand if delegation is truly taking place to identify if the problem is in fact a double-hop issue (which requires Kerberos and Active Directory delegation settings to be enabled).

Reference:

· Impersonation/Delegation https://msdn.microsoft.com/en-us/library/ff647248.aspx

What is Kerberos?

Kerberos is an industry standard authentication protocol which provides a method of initially authenticating a user to Active Directory through the logon process and then automatically authenticating the user to other remote network services, such as database, file, and web services. This provides single sign-on to network services.

Reference:

· Kerberos Authentication Overview https://technet.microsoft.com/en-us/library/hh831553.aspx

What is an SPN?

A service principal name (SPN) is an active directory object attribute. It can be an attribute of an AD domain computer object or an attribute of an AD domain user object. Every service that will use Kerberos authentication needs to have an SPN set for it so that clients can identify the service on the network. If the service is running under the LOCALSYSTEM account, the SPN attribute should be added to the AD domain computer object. If the service is running under a domain user account, the SPN attribute should be added to the AD domain user object.

The format of an SPN is

<service_class> / <host_name> : <service_listening_port|service_name>

For a default instance of SQL Server, listening on port 1433, the SPN format is as follows:

MSSQLSvc/serverxyz.your_domain.com:1433

MSSQLSvc/serverxyz:1433

Reference:

· Register a Service Principal Name for Kerberos Connections https://technet.microsoft.com/en-us/library/ms191153(v=sql.110).aspx

When do we need an SPN for SQL Server?

Kerberos is not typically required for a direct (one hop - simple client to server) connection to SQL. The default behavior for negotiated authentication in Microsoft Windows is to use Kerberos first and then failback to NTLM. Kerberos is used if it has been configured correctly, but if it is not configured the authentication will automatically use NTLM. However, a direct connection to SQL may still fail if an SPN is not present and only Kerberos is being used or if the failback to NTLM is not working correctly.

On the other hand, Kerberos is always needed when a double hop is done. For example, if you log on from LAPTOP1 to SQL2 and within SQL2 you execute a SQL linked server query to SQL3. If the SQL linked server is configured to impersonate your Windows credentials, this will require your Windows credentials to be delegated from SQL2 to SQL3, and hence requires Kerberos delegation to be configured.

Reference:

· Double-hop authentication: Why NTLM fails and Kerberos Works https://blogs.msdn.com/b/besidethepoint/archive/2010/05/09/double-hop-authentication-why-ntlm-fails-and-kerberos-works.aspx

How can you tell if an established SQL connection is using Kerberos or NTLM?

Run the following SQL DMV query:

select net_transport, client_net_address, auth_scheme from sys.dm_exec_connections where session_id=@@spid

The auth_scheme column will indicate "KERBEROS" or “NTLM” depending on how your connection was established.

Reference:

· How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005 https://support.microsoft.com/kb/909801

I’ve heard the term double-hop very often. Are more than two hops allowed?

Yes, we often see multi-hop implementations with SharePoint and Reporting Services deployments.

Figure 1: Example delegation architecture from Deployment Topologies for Reporting Services in SharePoint Integrated Mode https://technet.microsoft.com/en-US/library/bb510781(v=SQL.105).aspx

How is an SPN used?

Please note, this is a very high-level and over-simplified explanation of the Kerberos protocol, omitting many details. For full details please refer to the referenced links.

Kerberos uses “tickets” to identify users and provide a method to authenticate to services.

1. Steps (1) and (2) in the figure 2 below show the initial client to Active Directory KDC (Key Distribution Center) Authentication Service (AS) exchange.

a. The client connects to the KDC Authentication Service.

b. The KDC Authentication Service returns a ticket granting ticket (TGT) and a session key which the client can use to encrypt and authenticate communication with the KDC for ticket-granting service (TGS) requests.

2. Steps (3) and (4) in the figure below show the client to Active Directory KDC Ticket-Granting Service (TGS) exchange.

a. The client sends a request to the KDC for a ticket for the [SQL] server. To do this it sends the previously received ticket granting ticket (TGT) and the SPN. Think of the SPN as a unique name for the [SQL] service which is being connected to. When a client requests a service ticket to a network resource, the SPN host identifier will be based on how the client resolves the resource and must match an existing SPN within Active Directory. Some possible options:

i. FQDN of server running the service

ii. NetBIOS name of server running the service

b. The KDC returns a service ticket and a session key (which is used to encrypt communication with the [SQL] server). The client extracts the ticket and its own copy of the session key. Both are cached by the client allowing them to be used for the lifetime of the ticket without the need to constantly communicate with AD (for more information see Kerberos Technical Supplement for Windows https://msdn.microsoft.com/en-us/library/ff649429.aspx ) . How long a ticket is valid depends on Kerberos policy for the realm. Typically, tickets are good for no longer than eight hours, about the length of a normal logon session. It is each client's responsibility to manage the ticket for the server in its ticket cache and to present that ticket each time it accesses the [SQL] server. This provides a significant performance increase when connecting to a service.

3. Steps (5) and (6) in the figure below show the client to [SQL] server exchange.

a. The client requests access to the [SQL] server and presents the previously received service ticket. The [SQL] server decrypts the service ticket.

Figure 2: Kerberos v5 exchanges from Kerberos Network Authentication Service (V5) Synopsis https://msdn.microsoft.com/en-us/library/cc233863.aspx

Reference:

· Ticket Granting Tickets https://msdn.microsoft.com/en-us/library/aa380510.aspx.

· Session Tickets https://msdn.microsoft.com/en-us/library/aa380136.aspx.

· How the Kerberos Version 5 Authentication Protocol Works https://technet.microsoft.com/en-us/library/4a1daa3e-b45c-44ea-a0b6-fe8910f92f28

Delegation

Kerberos allows the use of delegation where the frontend service (for ex. Web App) can connect to a remote backend service (for ex. SQL Server) using the identity of the windows user who was authenticated to the frontend service. The user can authenticate to the frontend service using Kerberos and then the frontend service can authenticate using Kerberos to a backend service using the identity of the user. Kerberos also supports multi-hop scenarios where delegation is passed down to other servers.

Figure 3: Impersonation and Delegation model from The .NET Developer's Guide to Identity https://msdn.microsoft.com/en-us/library/aa480245.aspx

Two main types of delegation can be configured:

· Unconstrained delegation - Allows the service identity to delegate another user to any destination computer, service, or application.

· Constrained delegation - Allows the service identity to delegate another user only to those destination services explicitly defined. As a security best practice, Microsoft recommends constrained delegation over unconstrained delegation. Constrained delegation has two configurations; one that requires Kerberos authentication and another that works with any initial authentication protocol and protocol transition. Protocol transition enables applications to support different authentication mechanisms at the user authentication tier. They can then switch to the Kerberos protocol for security features, such as mutual authentication and constrained delegation, in the subsequent application tiers. For more information please see Delegated Authentication and Trust Relationships https://technet.microsoft.com/en-us/library/dn169022(v=ws.10).aspx.

Delegation is enabled by configuring the properties of the Active Directory account running the service which will be delegating credentials. The delegation tab is only available after an SPN attribute has been added to the active directory object.

Active Directory account properties before an SPN attribute has been added (note the delegation tab is missing):

Active Directory account properties after an SPN attribute has been added(note the Delegation tab is now present):

Active Directory account property with unconstrained (“Trust this user for delegation to any service”) delegation enabled:

Figure 4: Configuring Unconstrained Delegation

Reference:

· Kerberos Constrained Delegation Overview https://technet.microsoft.com/en-us/library/jj553400.aspx

· Configure Analysis Services for Kerberos constrained delegation https://technet.microsoft.com/en-us/library/dn194199.aspx

· How to Implement Kerberos Constrained Delegation with SQL Server 2008 https://msdn.microsoft.com/en-us/library/gg723715.aspx

Troubleshooting and Configuration Steps

Active Directory object

Active Directory object settings

Contoso\User1

1. Account option “Account is sensitive and cannot be delegated” is unchecked.

Contoso\Sql2_SvcAcct

1. SPN attributes:

MSSQLSVC/SQL2.Contoso.com:1433

MSSQLSVC/SQL2:1433

2. One of the account delegation options must be set to allow Sql2_SvcAcct to delegate a user’s credentials:

a) Unconstrained delegation (“Trust this user for delegation to any service)

-or-

b) Constrained delegation (“Trust this user for delegation to specified services only” and specify the destination service SPNs: MSSQLSVC/SQL3.contoso.com:1433 and MSSQLSVC/SQL3:1433)

Contoso\Sql3_SvcAcct

1. SPN attributes:

MSSQLSVC/SQL3.Contoso.com:1433

MSSQLSVC/SQL3:1433

Figure 5: Sample Delegation Topology and Configuration Overview

Configuration

1. Understand your topology.

a. Identify the machines, network services (such as SQL, IIS, Sharepoint, etc), Active Directory domains, number of hops, and user and service accounts involved.

2. Configure/Verify Active Directory SPN requirements.

a. Determine which services you will be connecting to via Kerberos and ensure proper SPNs exist for those services.

b. By default SQL will try to automatically register an SPN. You can either grant the SQL Service permissions to create its own SPNs or you can opt to manually register an SPN for SQL Server.

i. See Step 3: Configure the SQL Server service to create SPNs dynamically of How to use Kerberos authentication in SQL Server https://support.microsoft.com/kb/319723

c. Using the example topology above, we will need to connect to the SQL2 and SQL3 services via the Kerberos protocol. Therefore, proper SPN(s) need to be registered for both the SQL2 and SQL3 service accounts. We can manually register these SPNs via the setspn.exe tool.

C:\>setspn -S MSSQLSVC/SQL2.contoso.com:1433 contoso\Sql2_SvcAcct

Checking domain DC=CONTOSO,DC=com

Registering ServicePrincipalNames for CN=Sql2_SvcAcct,CN=Users,DC=CONTOSO,DC=com

MSSQLSVC/SQL2.contoso.com:1433

Updated object

C:\>setspn -S MSSQLSVC/SQL2:1433 contoso\Sql2_SvcAcct

Checking domain DC=CONTOSO,DC=com

Registering ServicePrincipalNames for CN=Sql2_SvcAcct,CN=Users,DC=CONTOSO,DC=com

MSSQLSVC/SQL2:1433

Updated object

C:\>setspn -S MSSQLSVC/SQL3:1433 contoso\Sql3_SvcAcct

Checking domain DC=CONTOSO,DC=com

Registering ServicePrincipalNames for CN=Sql3_SvcAcct,CN=Users,DC=CONTOSO,DC=com

MSSQLSVC/SQL3:1433

Updated object

C:\>setspn -S MSSQLSVC/SQL3.contoso.com:1433 contoso\Sql3_SvcAcct

Checking domain DC=CONTOSO,DC=com

Registering ServicePrincipalNames for CN=Sql3_SvcAcct,CN=Users,DC=CONTOSO,DC=com

MSSQLSVC/SQL3.contoso.com:1433

Updated object

C:\>setspn -L contoso\Sql2_SvcAcct

Registered ServicePrincipalNames for CN=Sql2_SvcAcct,CN=Users,DC=CONTOSO,DC=com:

MSSQLSVC/SQL2:1433

MSSQLSVC/SQL2.contoso.com:1433

C:\>setspn -L contoso\Sql3_SvcAcct

Registered ServicePrincipalNames for CN=Sql3_SvcAcct,CN=Users,DC=CONTOSO,DC=com:

MSSQLSVC/SQL3.contoso.com:1433

MSSQLSVC/SQL3:1433

3. Configure/verify local impersonation user rights.

a. Determine which service(s) will be impersonating credentials and ensure those services have impersonation rights. The impersonating service account should have the local user right (local security policy) “Impersonate a client after authentication” on the machine where the impersonation will take place.

b. In the example above, if the SQL service running on SQL2 will be impersonating the credentials of User1, you should grant it the “Impersonate a client after authentication” user right.

Figure 6: ‘Impersonate a client after authentication’ local user right

4. Configure/Verify Active Directory delegation requirements.

a. Determine which service(s) will be delegating credentials and ensure those services have delegation settings enabled within Active Directory.

i. The delegating service account should have unconstrained or constrained delegation enabled in Active Directory. See Figure 4 above for an example of how to enable unconstrained delegation.

b. In the example above, the SQL service running on SQL2 will be delegating the credentials of User1. Therefore, the Active Directory delegation settings for SQL2’s service account should be enabled. SQL3 will not be delegating credentials so you do not need to enable AD delegation settings for SQL3’s service account.

5. Verify the user account which will be delegated is allowed to be delegated (it should be by default).

a. Ensure the account being delegated does not have any delegation limitation set. The “Account is sensitive and cannot be delegated” setting should be unchecked.

b. In the example above, the Contoso\User1 account should have the “Account is sensitive and cannot be delegated setting” option unchecked.

Figure 7: AD options for account to be delegated

Troubleshooting

If the above settings are configured and Kerberos authentication is still failing, then check for common issues which can cause Kerberos authentication to fail.

1. Verify there are no duplicate SPNs. When moving or adding SPN’s it is important that a duplicate is not created. SPN’s must be unique.

a. From any server in the domain:

C:\>setspn -X

Checking domain DC=CONTOSO,DC=com

Processing entry 0

found 0 group of duplicate SPNs.

2. Verify client to domain controller network connectivity and name resolution:

a. From Laptop1 and SQL2:

C:\>nltest /sc_query:contoso.com

Flags: 30 HAS_IP HAS_TIMESERV

Trusted DC Name \\Contoso_DC.CONTOSO.com

Trusted DC Connection Status Status = 0 0x0 NERR_Success

The command completed successfully

C:\>Ping your_domain_controller (for example: ping Contoso_DC)

3. Verify remote service connectivity and host name resolution at each hop.

a. From Laptop1:

C:\>NSLOOKUP SQL2

C:\>Ping SQL2

C:\>NSLOOKUP SQL2.contoso.com

C:\>Ping SQL2.contoso.com

b. From SQL2:

C:\>NSLOOKUP Laptop1

C:\>Ping Laptop1

C:\>NSLOOKUP Laptop1.contoso.com

C:\>Ping Laptop1.contoso.com

C:\>NSLOOKUP SQL3

C:\>Ping SQL3

C:\>NSLOOKUP SQL3.contoso.com

C:\>Ping SQL3

c. From SQL3:

C:\>NSLOOKUP SQL2

C:\>Ping SQL2

C:\>NSLOOKUP SQL2.contoso.com

C:\>Ping SQL2.contoso.com

4. Verify you can make Kerberos connections at each hop

a. From LAPTOP1:

C:\>sqlcmd -S SQL2 -Q"select net_transport, client_net_address, auth_scheme from sys.dm_exec_connections where session_id=@@spid"

a. From SQL2:

C:\>sqlcmd -S SQL3 -Q"select net_transport, client_net_address, auth_scheme from sys.dm_exec_connections where session_id=@@spid"

5. Use the KLIST command to view cached SPNs at each hop. Check for any issues with the cached spn (wrong domain, port, spn name, etc)

a. From LAPTOP1 and SQL2:

C:\> klist

6. Use the KLIST command to test acquiring the necessary SPN at each hop.

a. From LAPTOP1:

C:\>setspn -L contoso\Sql2_SvcAcct (first verify the proper SPN exists in Active Directory)

Registered ServicePrincipalNames for CN=Sql2_SvcAcct,CN=Users,DC=CONTOSO,DC=com:

MSSQLSVC/SQL2:1433

MSSQLSVC/SQL2.contoso.com:1433

C:\> klist get MSSQLSVC/SQL2.contoso.com:1433

C:\> klist get MSSQLSVC/SQL2:1433

b. From SQL2:

C:\>setspn -L contoso\Sql3_SvcAcct (first verify the proper SPN exists in Active Directory)

Registered ServicePrincipalNames for CN=Sql3_SvcAcct,CN=Users,DC=CONTOSO,DC=com:

MSSQLSVC/SQL3.contoso.com:1433

MSSQLSVC/SQL3:1433

C:\> klist get MSSQLSVC/SQL3.contoso.com:1433

C:\> klist get MSSQLSVC/SQL3:1433

7. Enable Kerberos logging and examine the System event log for errors

a. How to enable Kerberos event logging https://support.microsoft.com/kb/262177

b. Security Auditing https://technet.microsoft.com/en-us/library/cc771395.aspx

8. Collect network traces at each hop. You may need to clear the locally cached SPN’s to force the client query the domain controller (and not use the cached SPN). This can be done via the KLIST purge command.

a. Kerberos errors in network captures https://blogs.technet.com/b/askds/archive/2012/07/27/kerberos-errors-in-network-captures.aspx

b. Consult whitepaper for kerberos error code reference - Troubleshooting Kerberos Errors White Paper https://www.microsoft.com/downloads/en/details.aspx?familyid=7dfeb015-6043-47db-8238-dc7af89c93f1&displaylang=en

Reference:

· Nslookup https://technet.microsoft.com/en-us/library/cc725991.aspx

· Nltest https://technet.microsoft.com/en-us/library/cc731935.aspx

· Klist https://technet.microsoft.com/en-US/library/hh134826.aspx

· Network tracing (packet sniffing) built-in to Windows Server 2008 R2 and Windows Server 2012. https://blogs.technet.com/b/yongrhee/archive/2012/12/01/network-tracing-packet-sniffing-built-in-to-windows-server-2008-r2-and-windows-server-2012.aspx

·· Audit Filtering Platform Packet Drop https://technet.microsoft.com/en-us/library/dd941625(v=WS.10).aspx

· Kerberos Authentication problems – Service Principal Name (SPN) issues - Part 1 https://blogs.technet.com/b/askds/archive/2008/05/29/kerberos-authentication-problems-service-principal-name-spn-issues-part-1.aspx

Tool: Kerberos Configuration Manager for SQL Server

The Kerberos Configuration Manager for SQL Server is a diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server, SQL Server Reporting Services, and SQL Server Analysis Services. It can perform the following functions:

· Gather information on OS and Microsoft SQL Server instances installed on a server.

· Report on all SPN and delegation configurations on the server.

· Identify potential problems in SPNs and delegations.

· Fix potential SPN problems.

Reference:

· New tool: "Microsoft Kerberos Configuration Manager for SQL Server" is ready to resolve your Kerberos/Connectivity issues https://blogs.msdn.com/b/farukcelik/archive/2013/05/21/new-tool-quot-microsoft-kerberos-configuration-manager-for-sql-server-quot-is-ready-to-resolve-your-kerberos-connectivity-issues.aspx

· Kerberos Configuration Manager for SQL Server is available https://support.microsoft.com/kb/2985455

· Download location https://www.microsoft.com/en-us/download/details.aspx?id=39046

Tool: SETSPN

Starting with Windows 2008, setspn.exe is now part of the OS. You should see the setspn.exe tool in the c:\windows\system32 directory. Be sure to use a version of Setspn.exe that is included with Windows Server 2008 or later because it includes the -S parameter for adding an SPN. Setspn -S will verify that there are no duplicate SPNs. SETSPN -A with Windows Server 2012 also does a duplicate check upfront.

List SPN. If the SQL Server service is running with credentials of a domain user account, list the SPN attribute(s) of the AD domain account.	Setspn.exe -L your_domain\sql_svc_acct_name
List SPN. If the SQL Server service is running with LOCALSYSTEM credentials, list the SPN attribute(s) of the AD computer object.	Setspn.exe -L your_sql_computer_name.your_domain.com
Add SPN. If the SQL Server service is listening to port 1433 (for ex. default instance) and is running with credentials of a domain user account, add an SPN attribute to the AD domain account.	Setspn.exe -S MSSSVSVC/your_sql_computer_name.your_domain.com:1433 your_domain\sql_svc_acct_name
Delete/Remove SPN. If the SQL Server service is listening to port 1433 (for ex. default instance) and is running with credentials of a domain user account, remove the SPN attribute of the AD domain account.	Setspn.exe -D MSSSVSVC/your_sql_computer_name.your_domain.com:1433 domain\sql_svc_acct_name
Search for any duplicate SPNs. Recall an SPN is essentially a unique name for the [SQL] service within Active Directory. Therefore, you should not have duplicate SPNs.	setspn -X