0420 Reasons for Consistent Auth Issues - microsoft/CSS_SQL_Networking_Tools GitHub Wiki
This page lists known issues grouped by category. Some items may appear in more than one 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 |
| Always-On Listener Loopback Protection | When connecting to the Always-On Listener from the Primary node, the connection will be NTLM. This will engage the Loopback Check and result in a Login failed error message stating that the user is from an untrusted domain. Enter the Listener NETBIOS name and fully-qualified name into the BackConnectionHostNames registry key. https://learn.microsoft.com/en-us/troubleshoot/windows-server/networking/accessing-server-locally-with-fqdn-cname-alias-denied |
| 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 |
|---|---|
| Account Disabled | You will not be able to login with this account or start a service with it. |
| Account not in Group | Access to the database is via groups rather than individually. Check the SQL logins to enumerate allowed groups and make sure the user belongs to one of them. |
| Cross-Domain Groups | Users from the remote domain should belong to a group in the SQL Server domain. If the domains lack proper trust, putting the users in a group in the remote domain may prevent the SQL Server from enumerating the group's membership. |
| Firewall Blocks the DC | Make sure the Domain Controller is accessible from the client or the SQL Server via NLTEST /SC_QUERY:CONTOSO. |
| DC Offline | See above. NLTEST can also force the computer to switch to another DC. |
| Domain Trust | The trust level between domains may cause failures in account authentication or the visibility of SPNs. You can use the SETSPN and RUNAS commands to test this independent of your application. |
| Selective Authentication | Selective Authentication is a feature of domain trusts that allows the domain administrator to limit which users have access to resources in the remote domain. Make sure the failing user is allowed to authenticate in the remote domain. |
| Account Migration | If old user accounts cannot connect to the SQL Server, but newly created accounts can, this could be due to account migration. This is an Active Directory issue. |
| Hypothesis | More Information |
|---|---|
| Missing SPN | Will cause NTLM credentials to be used. In delegation scenarios, it will result in a Login Failed for user: 'NT AUTHORITY\ANONYMOUS LOGON'. |
| SPN on Wrong Account | This will result in an SSPI Context error. Use SETSPN -Q spnName to locate the SPN and its current account. Use SETSPN -D and SETSPN -A to migrate it to the correct account. |
| Duplicate SPN | This will result in an SSPI Context error. Use SETSPN -Q spnName to locate the SPN and its current accounts. Use SETSPN -D to remove the SPN from the incorrect account. |
| Not Trusted for Delegation | In a double-hop scenario, the service account of the mid-tier service must be Trusted for Delegation in Active Directory. |
| Sensitive Account | Some accounts may be marked as Sensitive in Active Directory. These accounts cannot be delegated to another service in a double-hop scenario. |
| User Belongs to Many Groups | If using Kerberos over UDP, the entire security token must fit within a single packet. Users that belong to a lot of groups will have a larger security token than those that belong to fewer group. If using Kerberos over TCP, you can increase the MaxTokenSize setting. https://docs.microsoft.com/en-us/archive/blogs/shanecothran/maxtokensize-and-kerberos-token-bloat |
| Expired Tickets | Kerberos tickets usually have a lifetime of about 10 hours and should be automatically renewed. Using stale tokens can cause a connection to fail. Use the command KLIST purge to clear user tokens, or log off and back on or restart the machine. |
| Clock Skew | For Kerberos to work, the clocks between machines cannot be off by more than 5 minutes. |
| Not a Constrained Target | If constrained delegation is enabled for a particular service account, Kerberos will fail if the target server's SPN is not on the list of targets of constrained delegation. |
| NTLM and Constrained Delegation | If the target is a file share, the delegation type of the mid-tier service account must be Constrained-Any and not Constrained-Kerberos. |
| Per-Service-SID | This feature limits local connections to use NTLM and not Kerberos and the authentication method. The service can make a single hop to another server using NTLM credentials, but it cannot be delegated further without the use of constrained delegation. |
| Legacy Providers and Named Pipes | The legacy OLE DB Provider (SQLOLEDB) and ODBC Driver {SQL Server} that come with Windows do not support Kerberos over Named Pipes, only NTLM. Use a TCP connection to allow Kerberos. |
| Kernel Mode Authentication | Normally, the SPN must be on the App Pool account for web servers, but when using Kernel Mode Authentication, authentication is performed in the kernel and the computer's HOST SPN is used. This setting may be used if the server hosts a number of different web sites using the same host header URL, different App Pool accounts, and Windows Authentication. https://docs.microsoft.com/en-us/iis/configuration/system.webserver/security/authentication |
| NETBIOS Name | Use of the NETBIOS name, e.g. SQLPROD01, rather than the fully-qualified domain name, e.g. SQLPROD01.CONTOSO.COM, may result in the wrong DNS suffix being appended. Check the network settings for the default suffixes and make sure they are correct or use the fully qualified name to avoid issues. |
| Delegating Credentials to Access or Excel | The JET and ACE providers are like any file system destination and you must use constrained delegation to allow SQL Server to read files located on another machine. In general, the ACE provider should not be used in a linked server as this is expressly not supported if anything goes wrong. |
| Disjoint DNS Namespace | If the organizational hierarchy in Active Directory and in DNS do not match, the wrong SPN might be generated if using the NETBIOS name in the connection string. The SPN will not be found and NTLM credentials will be used instead of Kerberos credentials. Use the fully-qualified name of the server or explicitly specify the SPN name in the connection string to mitigate problems. A disjoint namespace may |
| SQL Alias | A SQL Server alias may cause an unexpected SPN to be generated. This will result in NTLM credentials if the SPN is not found, or an SSPI failure, if it inadvertently matches the SPN of another server. |
| Web Site Host Header | If the web site has a host header name, the HOSTS SPN cannot be used. An explicit HTTP SPN must be used. If absent, NTLM will be used and cannot be delegated to a back-end SQL Server or other service. |
| HOSTS File | The hosts file overrides DNS lookups and may cause and unexpected SPN name to be generated. This will cause NTLM credentials to be used. If an unexpected IP address is in the hosts file, the SPN generated may not match the back-end pointed to. |
| Delegating to a File Share | Constrained delegation (to Any) must be used in this scenario. |
| HTTP Ports | Normally, HTTP SPNs do not use port numbers, e.g. HTTP/WEB01.CONTOSO.COM, but you can enable this via policy on the clients. The SPN would then have to be in this format, HTTP/WEB01.CONTOSO.COM:88, to enable Kerberos to work. Otherwise, NTLM credentials are used. The following article discusses why enabling this is not a good idea: http://blog.michelbarneveld.nl/michel/archive/2009/11/14/the-reason-why-kb911149-and-kb908209-are-not-the-soluton.aspx |
| Hypothesis | More Information |
|---|---|
| Integrated Authentication is not Enabled | In Internet Explorer, make sure the Integrated Windows Authentication is enabled. |
| Wrong Internet Zone | The web site needs to be in the Local Intranet zone or credentials will not be automatically passed. |
| IIS Authentication | The web site needs to be configured to allow Windows Authentication and the web.config file needs to have <identity impersonate="true" /> set. |