0420 Reasons for Consistent Auth Issues

0420 Reasons for Consistent Auth Issues

This page lists known issues grouped by category.

0420.1 Issues with a SQL Login

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 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 the customer trying to specify a local Windows account or a Domain account for the username. Only SQL logins are allowed. The customer should be using Integrated security if that is what they 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.