SQL Server error 18456 (Login failed), explained and fixed
Login failed for user '…'. (Microsoft SQL Server, Error: 18456) tells the client almost nothing on purpose; detailed answers would help attackers probe accounts. The diagnostic gold is on the server: the SQL Server error log records the same failed login with a State number, and the state is what separates a wrong password from a disabled account, a SQL login attempted on a Windows-authentication-only server, or a user whose default database is unavailable. Never troubleshoot 18456 from the client message alone.
Get the state, then act on it
- Open the SQL Server error log (in Management Studio under Management, SQL Server Logs, or the ERRORLOG file) and find the entry at the failure's timestamp: “Login failed for user 'x'. Reason: … [CLIENT: …]” followed in the data by the state. Microsoft documents the states; the ones met most often:
The states encountered most, and their fixes
- Password mismatch (commonly state 8): the account exists, the password sent is wrong for it. Reset the login's password (as an admin:
ALTER LOGIN [name] WITH PASSWORD = '...';) and update the stored credential wherever the application keeps it.
- Invalid user ID (states 5 and related): no login by that exact name exists on this server; check for the right instance (logins are per-instance), spelling, and whether the account was ever migrated to this server.
- SQL login on a Windows-only server: the server's security is set to Windows Authentication mode, so SQL logins are refused wholesale. Either use a Windows/domain account, or switch the server to mixed mode (a server-level change in SSMS under Security, followed by a service restart, to be made deliberately).
- Login disabled: the login exists but is disabled; enable it in its properties or with
ALTER LOGIN [name] ENABLE;, and find out why it was disabled before doing so.
- Default database problems (commonly state 38): the login authenticated but its default database is missing, dropped, or not accessible to it, and the connection fails. Point the login's default database at one it can use (
ALTER LOGIN [name] WITH DEFAULT_DATABASE = [master]; unblocks diagnosis) and restore the missing database or grant access.
If the state points somewhere else
- Failed logins after a server move: logins migrated between instances keep their names but their internal SIDs can mismatch the database users (orphaned users), producing failures inside a specific database while server-level login works;
ALTER USER [name] WITH LOGIN = [name]; relinks them.
- Password policy rejections on creation or reset are a different error family (15128 and policy messages); 18456 is about authentication at connect time.
Common questions
Why does SSMS show the same 18456 for every cause? Client-facing uniformity is the security design. The server log holds the reason and the state because administrators, not remote callers, are meant to see it. Any guide that diagnoses 18456 without the log is guessing.
It works from the server itself but not from the application server, why? Likely Windows authentication context: the connection succeeds with your interactive domain account and fails with the application's service account (or the reverse), or the app is using a SQL login the server is not in mixed mode to accept. The error log entry's user and reason settle it.
Where exactly is the state printed? In the error log entry's data portion and in the extended detail SSMS shows under the failed login in its log viewer; it also appears in the Windows application event log copy of the entry. Search the log for the timestamp and username.
Related errors