How to troubleshoot MS SQL login failure

How to troubleshoot MS SQL login failure

Applications Manager reached the SQL Server instance, but the server refused the credentials. Two different problems produce a login failure. Match the message you see, then follow that section.

Applications Manager shows Authentication failed: Incorrect username or password. above the message returned by the driver. The driver message is the one that tells you which problem you have, so read it first.

1. Login failed for user '<name>'.

Driver message: Login failed for user '<name>'.

What it means: SQL Server received the credentials and rejected them. The connection reached the server; only the login was refused. This is the common case when a SQL login is used.

Work through these steps in order:

  1. Test the credentials outside Applications Manager. Use SQL Server Management Studio and sign in with the same user name and password configured in the monitor. If that fails, verify the credentials.
  2. Check authentication mode. In SQL Server Management Studio, right-click the server, select Properties → Security, and confirm SQL Server and Windows Authentication mode is selected when using a SQL login. If the server is Windows-only, switch to mixed mode and restart the SQL Server service, or configure Windows authentication in the monitor.
  3. Check whether the login is enabled. Under Security → Logins, open the login and verify that it is not disabled and that its password has not expired.
  4. Check the SQL Server error log. Find the latest error 18456 entry under Management → SQL Server Logs. SQL Server deliberately returns a vague message to the client and writes the real reason to its own log, as a state number:
StateMeaning
2, 5The login does not exist on this server.
6A Windows login was supplied as a SQL login. See the next section.
7The login exists but is disabled, and the password is also wrong.
8The password is wrong.
11, 12The login is valid, but it has no permission to connect to this server.
18The password has expired and must be changed.
38, 40The login is valid, but its default database is unavailable.
  1. For state 38 or 40, set the login's default database to one it can access, commonly master, on the login's General page.
  2. For state 11 or 12 when using Windows authentication, grant the account Connect SQL permission or add it to a role that has this permission.
If the monitor is configured for Kerberos authentication, this article does not apply. Applications Manager links the Kerberos article instead, at SQL Server Kerberos configuration troubleshooting guide.

2. Login Failed. The login is from an untrusted domain and cannot be used with Windows authentication.

Driver message: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

What it means: the monitor is using Windows authentication, and the domain account could not be validated by the SQL Server host. The account itself may be correct; the problem is usually the trust, the local security policy or an NTLM restriction.

1. Verify the credentials

  • Ensure that the user name and password of the SQL account (domain\username) are correct.
  • Check whether the user account is locked or disabled.
  • Connect to the SQL Server with a query tool such as SQL Server Management Studio (SSMS) or DB Visualizer (with the MS JDBC driver), using the same account. If the connection succeeds, the credentials are valid.

2. Check the local security policy

Ensure the user account is added to the Access this computer from the network policy.

Refer to this page for more details.

3. Update the SQL service account, if required

If the SQL account is also used as the SQL Server service account and its password was changed recently, make sure the new password is reflected in the service configuration:

  1. Open SQL Server Configuration Manager.
  2. Click SQL Server Services.
  3. In the right pane, right-click SQL Server (<instance name>) and select Properties.
  4. Under the Log On tab, enter the new password in both the Password and Confirm Password fields.
  5. Select OK to apply the changes.

Click here for more details.

4. Check for NTLM restrictions

On the database server, check whether NTLM authentication is restricted. Navigate to Computer Configuration → Windows Settings → Security Settings → Local Policies → Security Options and look for the NTLM-related settings. If NTLM is restricted, remove the restriction or configure it to allow Windows authentication.

3. Messages that look like a login failure but are not

Two other messages are easy to mistake for a rejected login. Neither is one, and neither is fixed by changing the credentials.

Access denied: Your account does not have the required database permissions, The server principal '<name>' is not able to access the database 'master', or VIEW SERVER PERFORMANCE STATE permission was denied. The login succeeded; the account simply cannot read what Applications Manager needs. Refer to Troubleshooting issues while adding an MS SQL Monitor, section 9, Check that permission has been provided for the user.

Login timed out, or The TCP/IP connection to the host <hostname>, port 1433, has failed. The connection never got as far as checking the credentials. Refer to the same article, section 8, Connectivity issue.

4. Still facing issues?

If you have followed the steps above and still cannot log in to MS SQL, send the following to the Applications Manager support team:

  • MsSQLDebug result — click here for instructions.
  • A screenshot of a successful login using a query tool (SSMS or DB Visualizer).
  • The state number from the latest error 18456 entry in the SQL Server error log.
  • A screenshot showing the user access described in step 2 of the untrusted-domain section.
  • SIF (Support Information File).

Refer to our feature page to learn more about SQL Server monitoring in Applications Manager.