-
Notifications
You must be signed in to change notification settings - Fork 46
0420 Reasons for Consistent Auth Issues
Malcolm Stewart edited this page Nov 2, 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 2000 Linked Server Workflow. 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 incorrect, it will be NTLM. 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. |
| Hypothesis | More Information |
|---|---|
| Hypothesis | More Information |
|---|---|
| Hypothesis | More Information |
|---|---|
| Hypothesis | More Information |
|---|---|
| Hypothesis | More Information |
|---|---|