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 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.