How to Resolve Microsoft SQL Server Error 18456 (Login Failed)

What is Microsoft SQL Server Error 18456?

Error 18456 is the generic “login failed” message returned by the SQL Server database engine whenever authentication fails. Each failure records a State code that clarifies the root cause, such as an incorrect password, disabled login, missing database mapping, or mixed-mode issues.Because the error masks sensitive details, users only see the number 18456 while admins can read the exact State in the SQL Server error log. Fixing the problem requires matching the State code to its meaning and correcting the underlying login or configuration problem.

What Causes This Error?

Incorrect passwords trigger State 8, the most common cause. Password mismatches occur when users type the wrong password, the password has expired, or Windows group policies forced a reset.Disabled, locked, or deleted logins raise States 2, 5, and 7. SQL Server prevents logins that are explicitly disabled or locked after too many failed attempts.Wrong default database settings produce State 16. If the default database was dropped or renamed, SQL Server cannot connect the user during login.Mismatched authentication mode (Windows vs. SQL) raises State 58. Servers running Windows-only mode reject SQL logins, while mixed-mode servers accept both.

How to Fix Microsoft SQL Server Error 18456

Match the State code in the SQL error log to choose the right fix. For State 8, reset the password with ALTER LOGIN WITH PASSWORD and confirm the user types it correctly.If the login is disabled (State 2 or 5), re-enable it with ALTER LOGIN ENABLE. For locked accounts (State 7), unlock with ALTER LOGIN WITH PASSWORD = 'newPW' UNLOCK.For State 16, set an existing default database using ALTER LOGIN WITH DEFAULT_DATABASE = . Ensure the database is online and accessible.For State 58, switch the server to mixed-mode or use Windows Authentication. In SQL Server Management Studio, right-click the server → Properties → Security → select “SQL Server and Windows Authentication mode.” Restart the service.

Common Scenarios and Solutions

Azure SQL Database often returns Error 18456 State 1 when the contained user has an expired password. Use ALTER USER WITH PASSWORD = 'newPW'.After restoring a database, orphaned users cause State 11 or 12. Fix by running sp_change_users_login 'auto_fix', 'user', 'password' or ALTER USER WITH LOGIN.High-availability failovers may reset default databases, causing State 16. Update logins in the primary replica and let synchronization propagate.

Best Practices to Avoid This Error

Enforce strong but memorable passwords and set reminders before expiration dates. Educate users on Windows Authentication where possible.Automate login health checks with scripts querying sys.sql_logins for disabled or locked accounts. Alert on State codes in the error log.When using Galaxy’s AI Copilot, enable credential linting so developers get inline warnings if they reference disabled logins in scripts.

Related Errors and Solutions

Error 4064 “Cannot open user default database” appears when Error 18456 State 16 is unaddressed. Set a valid default database.Error 233 “No process is on the other end of the pipe” can accompany 18456 when the server rejects the connection at the network layer. Verify TCP/IP and Named Pipes settings.Error 18452 “Login failed. The login is from an untrusted domain” occurs when mixed-mode is disabled. Enable mixed-mode or use Windows logins.

Wrong or expired password (State 8)

Disabled, locked, or deleted login (State 2, 5, 7)

Default database unavailable (State 16)

Authentication mode mismatch (State 58)

Orphaned users after restore (State 11, 12)

Error 4064 – Cannot open user default database; Error 18452 – Login from untrusted domain; Error 233 – No process on the other end of the pipe; Error 15151 – Cannot alter the login; Error 18470 – Login login disabled;

Why does Error 18456 not show details in SSMS?

For security, SQL Server hides the exact reason from the client. Check the SQL Server error log to find the State code that explains the failure.

How do I find the State code for Error 18456?

Open SQL Server Error Log (xp_readerrorlog) and locate the 18456 entry. The message includes “State: x” where x pinpoints the cause.

Can I fix Error 18456 without restarting SQL Server?

Yes. Most fixes—resetting passwords, enabling logins, changing default databases—apply instantly. A restart is only needed when switching authentication modes.

How does Galaxy help prevent Error 18456?

Galaxy’s context-aware AI Copilot flags disabled or expired logins during query authoring and suggests valid credentials, reducing production login failures.