0600 Credential Delegation Issue
Does the issue only affect database connections, or does it affect web and file share connections, too?
0600.1.1 Typical Error Messages
- Cannot generate SSPI Context
- Login failed for user '(null)'
- Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
0600.1.1 Is there a better workflow?
- If the issue is intermittent and not consistent, use 0300 Intermittent or Periodic Network Issue, including connection timeouts.
- Does the issue happen with a simple client-server connection? If yes, use 0400 Consistent Authentication Issue.
- Is the error a connection timeout or other network failure? If yes, use one of the networking workflows.
The initial goal is to try to isolate whether the web server or the SQL Server is not accepting Kerberos credentials. If there are problems after that, we have additional troubleshooting steps.
Here is the conceptual diagram of Kerberos delegation.
0600.3.1 Please perform the initial data collection and narrowing steps:
0100 Initial Data Collection and Scoping Questions
This will help get a macro perspective of the scope of an issue, such as whether the issue affects multiple computers or just one, or whether only those computers in a specific data center are facing issues. This can help focus the troubleshooting steps. It will also make you prepared for discussing the issue with Microsoft Support should you choose to do so.
0600.3.2 Make sure you understand the application architecture. Make a summary in a succinct form, similar to the below description:
- There are two domains involved: CONTOSO and FABRIKAM.
- The client (SPARKY.CONTOSO.COM) is Windows 2012.
- The user (CONTOSO\JOHNDOE) runs EDGE and connects to a web server (
HTTP://WEB01.CONTOSO.COM/Accounting) using Integrated security. - The IIS app pool runs as (CONTOSO\WEB_SVC).
- The web server connects to SQL Server 2014 (SQLProd01.FABRIKAM.COM\Accounting on port 1433) using the SqlClient .NET 4.6.2 Provider and delegates the user credentials to SQL Server via integrated security.
- The SQL Server service account is FABRIKAM\SQL_SVC_01.
0600.3.3 Collect the SPN information based on the service accounts identified in the description. e.g.
SETSPN -L CONTOSO\WEB_SVC > c:\temp\spns.txt > creates a new file
SETSPN -L FABRIKAM\SQL_SVC_01 >> c:\temp\spns.txt >> appends to a file
0600.3.4 View ASPX/ASMX Web Site Credentials
A number of sections below will have reference to the DIAGINFO.ASPX page, which can be placed in most ASP.NET .ASPX-page-based web sites, and may also work with .ASMX web services. But it will probably not work with MVC web sites. Read the topic linked below and see if you can place it in your web site. It does not require any DLLs and is just an in-line scripted page. Restarting IIS is required.
Determine If I have Delegatable Credentials in my ASPX Web Site
0600.4.1 IIS and SQL Server on the same machine
As long as DIAGINFO.ASPX (see 0600.3.4 above) shows either NTLM or Kerberos credentials with an Impersonation Level of either Impersonation or Delegatable, this scenario should work. If not, make sure the user can connect directly to the SQL Server machine. If not, troubleshoot as a regular Authentication issue. If the user can connect to the SQL Server, then you may be running into an issue that can be resolved by the DisableLoopbackChecks or BackConnectionHostNames registry keys. See https://support.microsoft.com/en-us/help/926642/error-message-when-you-try-to-access-a-server-locally-by-using-its-fqd
0600.4.2 Constrained Delegation
Normal Kerberos delegation is "unconstrained" or "full" since you can delegate credentials to any machine or service that has the appropriate SPNs defined and the service account is Trusted for Delegation. You can also set up Constrained delegation, where credentials can only be delegated to named back-end servers. Constrained delegation can force Kerberos authentication, or when connecting to a back end resource, such as a file share, where Kerberos cannot be used, can be configured to use "Any" authentication.
0600.4.3 Multiple Domains
If the web server, SQL Server, or user machine/account are not all in the same domain, first validate delegation on systems that are all in the same domain. e.g. if the user and IIS are in Domain A and SQL Server is in domain B, can the web site delegate credentials to a SQL Server in Domain A? Can a user and IIS machine in Domain B delegate to the SQL Server in Domain B?
If same-domain delegation works but cross-domain delegation fails, then the issue is likely domain trusts or selective authentication or similar.
0600.4.4 IIS Account Impersonation Deconstructed
Which IIS account is being used can be somewhat confusing. Fortunately, if using DIAGINFO.ASPX, the "Running as User" and "Name" user accounts give this information. These values should be the same. If not, you should check the web site authentication mode and the web.config impersonation mode.
The topics below show how the different web site settings affect which account will be used.
0600.4.4.1 ASP.NET Impersonation
This is governed by the <identity impersonate=.../> tag in the web.config file.
If FALSE, then the w3wp.exe service account (App Pool account) is used. NTLM or Kerberos can work, but cannot delegate user credentials.
If TRUE and userName and password attributes are present in the tag, then the account named by userName is used. NTLM or Kerberos can work.
If TRUE and a userName and password are not supplied, then we are in a true delegation scenario and continue with this workflow.
0600.4.4.2 Anonymous Authentication / Forms Authentication
If TRUE, then the IIS Anonymous account (IUSR) is used. This is a local account and cannot reach off-box resources.
If FALSE, then continue to the next section.
0600.4.4.3 Basic Authentication / Digest Authentication
The user is logged in locally, no delegation, no double-hop. You can connect using either NTLM or Kerberos to SQL Server.
0600.4.4.4 Windows Authentication
The client's user account will be delegated. Continue with this workflow.
0600.4.4.4 Claims Authentication
Claims authentication can use Forms or Windows credentials to get a cookie to the web site, which basically uses Anonymous authentication. This is rare in IIS but more common in SharePoint 2010 and later. In order to delegate credentials, the application must call the Claims to Windows Token Service (C2WTS) in order to get a Windows token (credentials) with which to call and external server. This is necessary when connecting to SQL Server, which does not support Claims Authentication connections. The Name in DIAGINFO.ASPX may look like +w|username|0 in a Claims-enabled web site. You should engage SharePoint or an appropriate resource to assist with this case.
With a Claims-Enabled web site, the web application could also use a SQL login using the TCP/IP protocol, or explicitly impersonate a Windows account, e.g. Reporting Services Data Source using Stored Credentials, or use the ASP.NET Impersonation settings to impersonate a Windows account or the App Pool account, all of which do not involve delegating the user credentials.
This technique tests the web site and SQL Server independently to see whether Kerberos credentials are being used and delegatable credentials are available. It starts with IIS as the mid-tier application.
0600.5.1 Does the browser user have delegatable credentials in IIS?
Try to implement this technique if at all possible. It gives direct evidence that is difficult to obtain otherwise. If you cannot implement this, then consult your web app developer as to the best way to determine this.
Determine If I have Delegatable Credentials in my ASPX Web Site
Here are some additional output examples:
In the example below, the impersonation level is Impersonation. This means that the web application does not have delegatable credentials and connecting to a remote SQL Server using Windows authentication will likely result in a login failure for the anonymous user.
Running as user: CONTOSO\userx
Browser User: CONTOSO\userx
Authentication Type: NEGOTIATE (Authenticated)
Name: CONTOSO\userx
...
Auth type: Kerberos
Impersonation Level: Impersonation
The server process can impersonate the client's security context on its local system. The server cannot impersonate the client on remote systems.
If the Auth Type is NTLM or None, then delegation will also not succeed.
If Form's or Anonymous authentication are enabled, you may see an entry similar to the one below. You cannot connect to SQL Server using the IUSR account.
Running as user: NT AUTHORITY\IUSR
Browser User:
Authentication Type: (Not authenticated)
Name: NT AUTHORITY\IUSR
...
Auth type: Negotiate
Impersonation Level: Impersonation
The server process can impersonate the client's security context on its local system. The server cannot impersonate the client on remote systems.
In the example below, ASP.NET impersonation is turned off and the page is impersonating the App Pool account: <identity impersonate="false" />
Running as user: IIS APPPOOL\Classic .NET AppPool
Browser User:
Authentication Type: (Not authenticated)
Name: IIS APPPOOL\Classic .NET AppPool
...
Auth type: Negotiate
Impersonation Level: None
An impersonation level is not assigned..
0600.5.2 IIS/ASP Failure Actions
Follow this procedure if the credentials are not Kerberos or are Kerberos but are not delegatable.
0600.5.2.1 Run SETSPN -L using the IIS app pool account. This will list all the SPNs associated with that site. Make sure there is an SPN for each way a user might type the web site name, e.g. both HTTP/WEB01 and HTTP/WEB01.CONTOSO.COM. The browser does not fix up the domain name and add the DNS suffix if missing, so you need an entry per name variant.
0600.5.2.2 If the app pool is running a local account, such as Network Service, make sure the name variants are on the HOSTS SPN and that there is not an HTTP SPN that might be on a different service account. The browser searches for the HTTP SPN first and then falls back to the HOSTS SPN.
0600.5.2.3 Check whether the web site allows Kernel Mode authentication. If it does, then all SPNs need to be on the machine account and not on the App Pool account. This is off by default.
0600.5.2.4 Make sure the browser user is typing the web site name and not the IP address.
0600.5.2.5 This article goes over more scenarios regarding delegation from a web site: https://docs.microsoft.com/en-us/troubleshoot/iis/troubleshoot-kerberos-failures-ie
600.5.2.6 If the credentials are Kerberos but are not delegatable, check whether the user account is marked as Sensitive in Active Directory. Make sure the app pool account is marked as Trusted for Delegation in Active Directory.
0600.5.2.7 If other users can delegate their credentials but one user cannot, you may want to engage the IIS support team or the Active Directory team regarding this one account.
0600.5.2.8 If the above does not help you get delegatable credentials in the web application, engage the IIS support team or the Active Directory team to assist. Having the DiagInfo.ASPX page results will help.
0600.5.3 Can the user connect to SQL Server using Kerberos credentials?
Use one of the techniques in this article to determine whether you can connect to the SQL Server machine with Kerberos credentials. Make sure the client and server are on different machines. If possible, run the test from the web server.
Determine If I Am Connected to SQL Server using Kerberos Authentication
0600.5.4 SQL Failure Actions
0600.5.4.1 If the Auth Scheme is NTLM rather than Kerberos, troubleshoot using the 0400 Consistent Authentication Issue workflow.
0600.5.4.2 If there is a login failure, troubleshoot using the 0400 Consistent Authentication Issue workflow.
0600.5.4.3 If there is a timeout or networking issue, use the 0200 Consistent Network Issue workflow.
0600.5.5 Delegation Testing
Once both IIS and SQL show Kerberos individually, it is time to test delegation.
**0600.5.6 Delegation Failure Actions
This section is when both 0600.5.1 and 0600.5.3 are working, but 0600.5.5 fails. If 0600.5.1 or 0600.5.3 fail, see sections 0600.5.2 and 0600.5.4 respectively and then move to 0600.5.5.
Check the following items:
0600.5.6.1. Is the user account marked Sensitive? If so, it cannot be delegated. Typically, this is for domain administrators, but can be set by Group Policy.
0600.5.6.2. Can other accounts be delegated?
0600.5.6.3. If more than one domain is involved, can credentials for accounts from the web server / SQL Server domain be delegated?
0600.5.6.4. You may want to engage Microsoft support for assistance.