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.

0400.5 Login Error Codes

If you are troubleshooting a Login Failed error message, the SQL Server ERRORLOG file can give more information in the SQL State value with Error 184456 (Login Failed).

State Description
1 Error information is not available. This state usually means you do not have permission to receive the error details. Contact your SQL Server administrator for more information.
2 User ID is not valid.
5 User ID is not valid.
6 An attempt was made to use a Windows login name with SQL Server Authentication.
7 Login is disabled, and the password is incorrect.
8 The password is incorrect.
9 Password must be changed.
11 Login is valid, but server access failed. One possible cause of this error is when the Windows user has access to SQL Server as a member of the local administrators group, but Windows is not providing administrator credentials. To connect, start the connecting program using the Run as administrator option, and then add the Windows user to SQL Server as a specific login.
12 Login is valid login, but server access failed.
18 Password must be changed.

These errors should be relatively easy to correct.

Clone this wiki locally