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
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
| 1 | Error information isn't available, usually because you lack permission to see the details. |
|---|---|
| 2, 5 | The user ID isn't valid: there is no login with that name. |
| 6 | A Windows login name was used with SQL Server authentication. |
| 7 | The login is disabled and the password is incorrect. |
| 8 | The password is incorrect. |
| 9 | The password isn't valid. |
| 11, 12 | The 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. |
| 18 | The password must be changed. |
| 38, 46 | The database requested by the user couldn't be found. |
| 58 | SQL authentication was used while the server is set to Windows authentication only, or the SIDs don't match. |
| 62 | A Windows account tried to use a contained database whose SID doesn't match. |
| 102 to 111, 132 to 133 | Azure AD (Microsoft Entra ID) authentication failure. |
| 122 to 124 | The user name or password was empty. |
| 126 | The database requested by the user doesn't exist. |
How to fix it
-
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';
-
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>';
-
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;
-
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.
-
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;
-
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
4060Cannot open database requested4064Cannot open user default database18452Login is from an untrusted domain18470Login failed, account disabled18486Login failed, account locked out18487Login failed, password expired18488Login failed, password must be changed916Login can't access the database
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.
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.