Skip to content

0400 Consistent Authentication Issue

Jump to bottom
Malcolm Stewart edited this page Oct 27, 2020 · 9 revisions

0400 Consistent Authentication Issue

0400.1 Typical Error Messages

  • Cannot generate SSPI context
  • Login failed for user '(null)'
  • Login failed for user ''
  • Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
  • Login failed for user 'JohnDoe'
  • Login failed for user 'CONTOSO\JohnDoe'
  • Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
  • SQL Server does not exist or access denied (this can also be a network error)

0400.2 Moving Parts

The initial goal is to try to isolate which of the moving parts is causing the problem.

Moving Parts

0400.3 Pre-Work

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

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

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

0400.4 Directory Services Specific Error Messages

If the SQL Server ERROLOG file contains the following messages and you have confirmed that this is the issue you are dealing with:

	Error -2146893039 (0x80090311): No authority could be contacted for authentication.
	Error -2146893052 (0x80090304): The Local Security Authority cannot be contacted.

then this is an Active Directory issue. The domain controller cannot be contacted by Windows on the SQL Server machine, or the local security service (LSASS) is having a problem.

If you need assistance, the Microsoft Active Directory team can assist in diagnosing this issue.

Clone this wiki locally