// SQL Server error reference

SQL Server error 18456: Login failed for user

SQL Server rejected the login. The client always sees state 1; the real reason (bad password, unknown login, no database access) is in the server's error log.

Error number
18456
Severity
14
Category
Connection and login
Error message
Login failed for user '%.*ls'.%.*ls%.*ls

What it means

18456 is the generic login failure. SQL Server deliberately tells the client nothing more than Login failed for user '...', so an attacker can't tell a wrong password from a missing login. The specific reason is written to the SQL Server error log as the error's state number, together with the client's IP address.

So the first step is always the same: find the matching entry in the error log and read its state.

Common causes

  • Wrong password, or a SQL login typed with the wrong name.
  • The login doesn't exist on this instance, which is common after restoring a database to a new server: the database user exists but the server login doesn't.
  • The server allows Windows authentication only, and you connected with a SQL login.
  • The login's default database is offline, was dropped, or the login has no user in it.
  • The login is disabled, locked out, or its password expired or must be changed (these have their own messages: 18470, 18486, 18487, 18488).
  • Connecting to an Azure SQL database without naming the database, when the login exists only as a contained user in that database.

What the state number in the error log means

1Error information isn't available, usually because you lack permission to see the details.
2, 5The user ID isn't valid: there is no login with that name.
6A Windows login name was used with SQL Server authentication.
7The login is disabled and the password is incorrect.
8The password is incorrect.
9The password isn't valid.
11, 12The login is valid, but server access failed. A common case is a Windows user who reaches SQL Server only through the local Administrators group without running the program as administrator.
18The password must be changed.
38, 46The database requested by the user couldn't be found.
58SQL authentication was used while the server is set to Windows authentication only, or the SIDs don't match.
62A Windows account tried to use a contained database whose SID doesn't match.
102 to 111, 132 to 133Azure AD (Microsoft Entra ID) authentication failure.
122 to 124The user name or password was empty.
126The database requested by the user doesn't exist.

How to fix it

  1. Find the failure in the error log and read the state. In SSMS it's under Management, SQL Server Logs; or query it:

    EXEC sys.xp_readerrorlog 0, 1, N'Login failed';
  2. State 8 or 9: the password is wrong. Reset it, or connect with the right one.

    ALTER LOGIN app_user WITH PASSWORD = N'<new strong password>';
  3. State 2 or 5: the login doesn't exist. Create it, and if the database was restored from another server, map the existing database user back to it.

    CREATE LOGIN app_user WITH PASSWORD = N'<strong password>';
    USE SalesDb;
    ALTER USER app_user WITH LOGIN = app_user;
  4. State 58: the server is in Windows-only mode. Enable SQL Server and Windows authentication mode in the server properties (Security page), then restart the SQL Server service.

  5. State 38 or 46: name a database the login can open in the connection, or fix its default database.

    ALTER LOGIN app_user WITH DEFAULT_DATABASE = master;
  6. Disabled or locked login: re-enable or unlock it.

    ALTER LOGIN app_user ENABLE;
    ALTER LOGIN app_user WITH PASSWORD = N'<password>' UNLOCK;

Questions

Why does the error always say State 1?

SQL Server sends state 1 to every client on purpose, to avoid telling an attacker why a login failed. The real state is in the SQL Server error log entry for the same login attempt.

What does error 18456 state 38 mean?

The login succeeded, but the database it asked for could not be opened: it doesn't exist, is offline, or the login has no user in it. The error log entry names the database.

Why does sa fail with state 7 after I enabled mixed mode?

When SQL Server is installed with Windows authentication only and later switched to SQL Server and Windows authentication, the sa login starts out disabled. Enable it with ALTER LOGIN sa ENABLE; and give it a strong password.

Related errors

More connection and login errors

The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.

// fewer errors, faster fixes

Work on SQL Server in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.