Error 18456 – Login failed for user ‘xxx’

2025.09.28

Error 18456 – Meaning of “Login failed for user” + How to Fix

Introduction

When using SQL Server, you will likely encounter Error 18456: “Login failed for user” at least once. This error is not just a simple “login failure”; the actual cause differs depending on the State code. In this post, we outline how to check the SQL Server error logs, what the key States mean, and how to resolve the issue.

Table of Contents

Error message and the meaning of State

When connecting with SSMS, you may see an error message like the following.

SSMS dialog showing Error 18456

By selecting “Show details,” you can check the specific State. Login-related errors are also recorded in the SQL Server Error Log on the server side.

Error 18456 entry in SQL Server error log


The meanings of representative States are as follows (partial list):

  • State 1 – Login failed, but you don’t have permission to view the State details
  • State 2 / 5 – Invalid user name (login does not exist)
  • State 6 – A Windows login name was used with SQL Server Authentication
  • State 7 – Login is disabled
  • State 8 – Incorrect password
  • State 11 / 12 – Login is correct but has no server-level access
  • State 18 – Password expired / must be changed
  • State 38 / 46 – Default database does not exist
  • State 58 – SQL Server is configured to accept only Windows Authentication
  • State 126 – The specified database does not exist

For the complete list of States, see the official Microsoft documentation.

How to check using SQL Server logs

In SSMS, open Management > SQL Server Logs to check the error details.

Also, by running the following query, you can verify the path of the error log file. By opening the ERRORLOG file at the confirmed path in a text editor, you can also review the error.

EXEC xp_readerrorlog 0, 1, N'Logging SQL Server messages in file';
Query result showing the error log file path

How to fix by State

State 1 — Login failed but you don’t have permission to view the State

This State is recorded on the application side. A different State is actually recorded in the SQL Server error log, so first check the correct State from the SQL Server logs and then proceed with the fix.

State 2 / 5 – Invalid user name (login does not exist)

This State is recorded when a non-existent login is used. For example, if you try to connect to SQL Server with a login named “User100” that does not exist, you can confirm a message like the following is recorded.

errorlog output example

Error: 18456, Severity: 14, State: 5.
Login failed for user 'user100'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

How to fix

State 6 – A Windows login name was used with SQL Server Authentication

This message is recorded when a Windows-authenticated login attempts to authenticate using SQL Server Authentication.

errorlog output example

Error: 18456, Severity: 14, State: 6.
Login failed for user 'domain\userName'. Reason: Attempting to use an NT account name with SQL Server Authentication. [CLIENT: <local machine>]

How to fix

  • Log in using Windows Authentication.

State 7 – Login is disabled

This State is recorded when the login itself exists, but the login is not enabled.

errorlog output example

Error: 18456, Severity: 14, State: 7.
Login failed for user 'sa'. Reason: An error occurred while evaluating the password. [CLIENT: <local machine>]

How to fix

  • Enable the login. In SSMS, right-click the login, open Properties, and under Status switch Login to Enabled.
SSMS screenshot: enable a disabled login

State 8 — Password mismatch

This State is recorded when the password is incorrect.

errorlog output example

Error: 18456, Severity: 14, State: 8.
Login failed for user 'xxx'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]

How to fix

  • Use the correct password.
  • If unknown, use another login and change the password for the target login.

State 38 — Incorrect target database at connect time

In SQL Server you can specify which database to connect to at login. You can set it in advance per login, or include it in the connection string. If the database you attempt to connect to is offline or otherwise inaccessible, this message is recorded.

errorlog output example

Error: 18456, Severity: 14, State: 38.
Login failed for user 'user1'. Reason: Failed to open the explicitly specified database 'xxx'. [CLIENT: <local machine>]

How to fix

  • Connect to SQL Server and check the status of the target database.
  • When connecting to SQL Server, make sure an appropriate database is specified.

State 58 — SQL Server is configured to accept only Windows Authentication

This State is recorded when SQL Server cannot use SQL Server Authentication. By default, SQL Server permits only Windows Authentication; to use SQL Server Authentication, you must configure Mixed Mode during installation or change the setting after installation.

errorlog output example

Error: 18456, Severity: 14, State: 58.
Login failed for user 'user1'. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only. [CLIENT: <local machine>]

How to fix