SQL Server Error 18456 Login Failed for User

Quick Answer

Check SQL Server logs for the state number (e.g., 18456, state 8 means bad password, state 1 means user doesn't exist). Fix the root cause: reset password, enable account, or switch to mixed mode.

Why This Happens

I've seen error 18456 in every SQL Server version from 2008 to 2022. The culprit is almost always one of three things: wrong password, disabled login, or SQL Server running in Windows-only authentication when you're trying to use a SQL login. The error message in Management Studio is vague — it just says "Login failed for user". The real info lives in the SQL Server error log, where you get a state number that tells you exactly what's wrong. Here's a real-world example: someone sets up a new app, uses the sa account with a password from the documentation, but the sa account is disabled by default in SQL Server 2019+. You get 18456, state 7 or 8.

Step-by-Step Fix

1. Check the SQL Server error log. Open SQL Server Management Studio (SSMS). Go to Management → SQL Server Logs → Current. Look for the last 18456 entry. Note the state number. Common ones: state 1 (user doesn't exist), state 5 (wrong login type), state 7 (disabled account), state 8 (bad password).
2. Verify the login exists. In SSMS, go to Security → Logins. Right-click, select Properties. If the user isn't there, create it with CREATE LOGIN.
3. Reset the password. Right-click the login → Properties → Password. Type a new one. Make sure it meets any domain password policies. Apply.
4. Enable the login. In the same Properties window, check the Status page. Set "Permission to connect to Database Engine" to Grant. Set "Login" to Enabled. Apply.
5. Check authentication mode. Right-click the server in SSMS → Properties → Security. If it's set to "Windows Authentication Mode", change it to "SQL Server and Windows Authentication Mode". Restart the SQL Server service. This is required for SQL logins (like sa) to work.

Alternative Fixes

If the steps above don't work:

• Try connecting from a different machine or via ODBC. Sometimes SSMS caches bad credentials. Use sqlcmd -S yourserver -U sa -P yourpassword from command line. If that works, the issue is SSMS.
• Check if the login is orphaned. If you restored a database from another server, the user exists in the database but not in the server instance. Run ALTER USER userName WITH LOGIN = loginName in the database.
• Verify TCP/IP is enabled. In SQL Server Configuration Manager, go to SQL Server Network Configuration → Protocols for MSSQLSERVER. Enable TCP/IP if it's disabled. Restart SQL Server. This matters if you're connecting from an app on a different port.

Prevention Tip

Set SQL Server to mixed mode during installation. Otherwise, you'll hit this every time you install an app that uses SQL authentication. Also, keep your sa password in a password manager — don't rely on the default. I also set up alerts in SQL Agent to log failed logins to a table so I catch this early.

Real talk: I've seen this error waste entire afternoons because people didn't check the error log state number. Don't be that person. Look at the state number first. It saves hours.