-
Notifications
You must be signed in to change notification settings - Fork 46
0420 Reasons for Consistent Auth Issues
Malcolm Stewart edited this page Nov 3, 2020
·
32 revisions
This page lists known issues grouped by category.
| Hypothesis | More Information |
|---|---|
| Bad password | All providers will return some variation of this message: Login failed for user 'userx'. The SQL ERRORLOG will contain: Login failed for user 'userx'. Reason: Password did not match that for the login provided. Use the correct password in the connection string or use a different account if you cannot remember the password. If the application is SQL Server Integration Services (SSIS), there may be multiple levels of Configuration file for the job, which may override the Connection Manager settings for the package. If the application was written by your company and the connection string is programmatically generated, then engage the development team to resolve the issue. As a temporary work-around, hard-code the connection string and test. Use a UDL file (Test a Connection via a UDL File) or a script to prove a connection is possible with a hard-coded connection string. |
| Invalid user name | All providers will return some variation of this message: Login failed for user 'userx'. The SQL ERRORLOG will contain: Login failed for user 'userx'. Reason: Could not find a login matching the name provided. You will need to (a) use a valid login or (b) in SQL Server Management Studio, create a new login of the desired name and password. |
| SQL logins are not enabled | All providers will return some variation of this message: Login failed for user 'userx'. The SQL ERRORLOG will contain: Login failed for user 'userx'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. This can be resolved in one of two ways: 1. Use an Integrated login, e.g. for OLE DB Providers: add INTEGRATED SECURITY=SSPI to the connection string, and for ODBC drivers: TRUSTED_CONNECTION=YES. The .NET Provider accepts either syntax. Note: this may lead to other issues if they are not configured correctly to allow integrated authentication and will need to be troubleshot as a separate issue. 2. Enable SQL logins on the server. In SQL Server Management Studio, right-click on the SQL Server name in the Object Explorer and select Properties. In the Security pane, select "SQL Server and Windows Authentication mode" and click OK. Restart SQL Server for the change to take place. Note: This may lead to other issues, such as needing to define a SQL login. Another caveat is trying to specify a local Windows account or a Domain account for the username. Only SQL logins are allowed. The application should be using Integrated security if that is what you are attempting. |
| Named Pipes connections fail because the user does not have permissions to log into Windows. | The SQLOLEDB Provider gives: SQL Server does not exist or access denied. Most other providers give both messages below in some order, regardless of using Integrated Security or a SQL login: 1. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. 2. Could not open a connection to SQL Server. Some also include "Login timeout expired" as part of the message. Add the failing user to the Users group on the SQL Server machine. If SQL is mirrored or clustered, perform on all machines. |
| Hypothesis | More Information |
|---|---|
| Database offline | All providers will return some variation of this message: Cannot open database "test" requested by the login. The login failed. Most will also return: Login failed for user 'userx'. - if using a SQL login or: Login failed for user 'contoso\user1'. - if using an Integrated login The SQL ERRORLOG will contain: Login failed for user 'userx'. Reason: Failed to open the explicitly specified database 'test'. In many cases, this error is logged right after the server is rebooting or after a cluster has failed-over. In this case, the error is benign, as the server will start accepting logins before all databases are on-line. If the issue persists, and the you cannot bring it on-line in SQL Server Management Studio, then you can engage the SQL Core team to perform further troubleshooting. Note: All users should fail to connect to the database and it should appear Offline or in some recovery mode in SQL Server Management Studio. If some users can connect, then try the Database Permissions hypothesis below. |
| Database Permissions | This will return exactly the same error as above. However, in this case, the database will not appear off-line in SQL Server Management Studio, and other users, e.g. the DBA will be able to connect to it. The user account in question will need to be granted explicit access to the database, or be added to a SQL Server Role or a local Windows group or Domain group that has access to the database . |
| No Login | The typical error is: 2017-08-24 16:46:34.96 Logon Error: 18456, Severity: 14, State: 11. 2017-08-24 16:46:34.96 Logon Login failed for user 'CONTOSO\JohnDoe'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ] Various causes have been related to this and all but #1 below should be troubleshot by SQL Core. See 0116 Known Errors. 1. About 30% of cases are for the Anonymous Logon account. This is a Kerberos issue and troubleshoot as such. 2. One issue was a bad manual entry in the HOSTS file - the wrong server name was given. 3. The remainder of the issues appear to fall into the following categories: 3.1. The named account (one case it was the SQL Service account) need to be granted sysadmin rights. 3.2. Logins were denied (or not granted) for an end-point. 3.3. The account had access via the Administrators group, but needed UAC elevation in order to be able to get access to the server. Turn off UAC, run the app "As Administrator", or grant the account direct access to resolve. Not required for service applications, only the interactive user. 3.4. In one case, a group the user belonged to had DENY permissions in SQL Server. |
| Linked Server Account Mapping | See 0650 SQL Server Linked Server Delegation Issues. If the Linked Server security dialog is set to "Be made without using a security context," this will result in a Login Failed for user 'NT AUTHORITY\ANONYMOUS LOGON". If it is set to "Be made with this security context", then it needs to be a SQL login. From inside Management Studio connecting to the mid-tier server, you may get the following: Msg 233, Level 20, State 0, Line 0 A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) Note the Shared memory provider is from SSMS to the mid-tier server, not to the linked server. From a .NET application, you may get the following: The OLE DB provider "MSDASQL" for linked server "SQLPROD02" reported an error. Authentication failed. Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SQLPROD02". If you also see the below message, it indicates you are using Named Pipes for the linked server connection and a SQL login, and the mid-tier SQL Server service account/machine account does not have login rights to Windows on the back-end server: OLE DB provider "MSDASQL" for linked server "SQLPROD02" returned message "[Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'CONTOSO\SQLPROD01$'.". Correct by forcing TCP/IP or granting the appropriate permissions on the back-end server. In addition to the above settings, there are individual account mappings that can be made in the upper portion of the dialog, which override the main mapping settings. |
| Proxy Account | An SSIS job run by SQL Agent may need permissions other than the SQL Agent service account can provide. Check whether a Proxy account needs to be created or used and that if one is being used, it is the right account. |
| Bad Metadata | A View or stored procedure gets login failures to a Linked Server whereas a distributed SELECT statement copied from them does not. This is likely due to the View having been created and then the linked server was recreated or a remote table was altered without rebuilding the View. |
| Hypothesis | More Information |
|---|---|
| Bad Server Name in Connection String | SQLOLEDB and SQLNCLI11 OLE DB Providers over TCP or Named Pipes return: Login failed for user 'userx'. - when using a SQL login Login failed for user 'CONTOSO\user1'. - when using a Windows login The SQL Server and ODBC Driver 13 ODBC Drivers return: Login failed for user 'CONTOSO\user1'. The SqlClient .NET Provider returns: Login failed for user ''. -when using TCP and a SQL login or a Windows login Login failed for user 'CONTOSO\user1'. - when using Named Pipes and a SQL login or a Windows login In the ERRORLOG, you should see the reason: Login failed for user 'userx'. Reason: Could not find a login matching the name provided. or Login failed for user 'CONTOSO\USER1'. Reason: Could not find a login matching the name provided. This can be a common issue if deploying an application that used a DEV or QA server into production and failed to update the connection string. To resolve this issue, validate the server is the intended server. If not, correct the connection string. If it is, then add the login to the database or, if a windows login, add to a local group or domain group that is allowed to connect to the database. |
| Wrong Database Name in Connection String | The driver will return: Cannot open database "northwind" requested by the login. The login failed. Some may also return: Login failed for user CONTOSO\user1. The ERRORLOG file will contain: Login failed for user 'CONTOSO\User1'. Reason: Failed to open the explicitly specified database 'northwind'. The database name should be obvious in the error message and the ERRORLOG entry. Change the connection string, if incorrect, or grant the user permissions. |
| Wrong Explicit SPN Account | If the application specifies the SQL Server service account in the ServerSPN property of the connection string, e.g. Provider=SQLNCLI11;Data Source=SQLProd01;initial catalog=northwind;integrated security=sspi;server spn=contoso\sql_svc_01 If the account name is correct, the the connection will be Kerberos, if not found, it will be NTLM, and if the account exists but is not the SQL Server service acount, it will cause an SSPI Context error. You can use one of the methods in Determine If I Am Connected to SQL Server using Kerberos Authentication to test independent of the application. Please test from a remote machine. Local connections on Windows 2008 R2 and later will be NTLM in order to support the per-service SID security feature to prevent one service from spoofing another. |
| Explicit SPN is Missing | If you specify a non-existent SPN explicitly in the ServerSPN property of the connection string, the the connection will be made using NTLM authentication. Use SETSPN -L domain\serviceacct to list all SPNs for the SQL Server service account. Add the missing SPN or change the connection string to use an existing one. Test from a remote machine as local connections will always use NTLM even if Kerberos is configured correctly. |
| Explicit Misplaced SPN | If the SPN you specify in the connection string exists on a service account that is not used by SQL Server, you will get an SSPI Context error message. Use SETSPN -L domain\svcacct to list SPNs on the SQL Server service account. Use SETSPN -Q spnName to find what account the SPN is on. You can move the SPN via SETSPN -D and SETSPN -A or choose an SPN already on the correct account. |
| Explicit SPN is Duplicated | You you recently changed the SQL Server service account from LocalSystem to a domain account, it is easy to forget to remove the SPN from the computer account and just create a new SPN on the new service account. This will cause an SSPI Context error. Use SETSPN -Q spnName to search for all service accounts on which the SPN is attached. If on more than one, use SETSPN -D to remove the bad copy. |
| Hypothesis | More Information |
|---|---|
| Access via Group | The user does not belong to a local group that is used to grant access to the server. The provider should reutrn the error: Login failed for user 'contoso/user1' The DBA can double-check this by looking at the Security\Logins in SSMS. If it is a contained database, check under databasename\Security. Also by running the following stored procedure: xp_logininfo 'contoso/user1' If you get an error, SQL cannot resolve the user name at all. Suspect a name that is not in the active directory or issues connecting to the DC. Try with another name to see if the issue is specific to that one account. If no rows get returned, then there is no group that provides access to the server. If one or more rows are returned, then the user belongs to a group that gives access. |
| Network Login Disallowed | The user account is not allowed the Network Login type. This will show in an event on the SQL Server that the user does not have the allowed login type. Check in secpol.msc that the user account (or a group they might belong to) does not exist in Local Policies, User Rights Assignment, Deny access to this computer from the network. |
| Service Account not Trusted for Delegation | If not a delegation scenario, check in the SQL Server SECPOL.MSC that the SQL Server service account is listed under Local Policies, User Rights Assignment, Impersonate a client after authentication. |
| Only Admins can Login | Check whether HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa!crashonauditfail is set to 2 and the security event log requires manual clearing. This is the bad state. Resolve by setting to 0 and then reboot the server. You may also want to change the security event log to allow events to roll over. See this KB for more information - the setting affects all services SQL, IIS, file share, login, etc.: https://support.microsoft.com/en-us/help/832981/users-cannot-access-web-sites-when-the-security-event-log-is-full Note: this only affects integrated logins. A Named Pipe connection will also be affected with a SQL Login because Named Pipes first logs into Windows' Admin pipe before connecting to SQL Server. |
| Local Security Subsystem Issues | The driver returns: The login is from an untrusted domain and cannot be used with Windows authentication. In the SQL Server ERRORLOG, you see one of the following: SSPI handshake failed with error code 0x80090311, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. SSPI handshake failed with error code 0x80090304, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. You may also note Kerberos errors in the System event log on the SQL Server machine for the same time range.These error codes have specific meanings: Error -2146893039 (0x80090311): No authority could be contacted for authentication. Error -2146893052 (0x80090304): The Local Security Authority cannot be contacted. This is an Active Directory issue. |
| Corrupt User Profile | If you log in as the affected user, they will get a temporary Profile. This is a good indication the profile is corrupt or they are a guest user. If a Guest user, then this needs to be addressed by adding them to the appropriate groups. If they are not a guest user, then use the steps in the following support article to either (a) repair the profile (Method 1) or (b) delete and recreate the profile (Method 3). https://support.microsoft.com/en-us/windows/fix-a-corrupted-user-profile-in-windows-1cf41c18-7ce3-12f9-8e1d-95896661c5c9 |
| Credential Guard is Enabled | A common symptom is that Windows 7/8 users can delegate credentials in a linked server or from IIS/SSRS to SQL but Windows 10 clients cannot. Windows 10 users will get Logon failed for user: 'NT AUTHORITY\ANONYMOUS LOGON'. If the client is Windows 10 Enterprise Edition and the Credential Guard feature is turned on, then you will not be able to use full delegation (Trust this user for delegation to any service). You can only use constrained delegation. In Windows 10 Enterprise there is a new feature called Credential Guard: https://docs.microsoft.com/en-us/windows/access-protection/credential-guard/credential-guard As per documentation https://docs.microsoft.com/en-us/windows/access-protection/credential-guard/credential-guard-requirements: Applications will break if they require: * Kerberos DES encryption support * Kerberos unconstrained delegation * Extracting the Kerberos TGT * NTLMv1 |
| Hypothesis | More Information |
|---|---|
| NTLM Peer Login | When communicating between computers that are either both workstations or in domains that don't trust each other, you can set up identical accounts on both machines and use NTLM peer authentication. Logins only work if both the user account and the password match on both machines. |
| Loopback Protection | Loopback protect is designed to prohibit applications from calling other services on the same machine. You can either set the DisableLoopbackCheck or BackConnectionHostNames (preferred) registry keys to allow this. https://support.microsoft.com/en-us/help/926642/error-message-when-you-try-to-access-a-server-locally-by-using-its-fqd |
| Double Hop | Performing a double-hop will fail using NTLM credentials. Kerberos credentials are required. |
| Lanman Compatibility Level | This usually happens between older computers (pre Windows 2008) and newer computers. Switching to Kerberos avoids this issue. |
| Hypothesis | More Information |
|---|---|
| Hypothesis | More Information |
|---|---|
| Hypothesis | More Information |
|---|---|