SQL Server Error 18456: Login Failed for User

Cause #1: Wrong Password or Username — State 1, 2, or 5

The most common cause of error 18456 is simply typing the wrong password or username. I've seen this countless times — someone's caps lock is on, or they're using a password from an old sticky note. SQL Server gives you state codes in the error log that tell you exactly what's wrong. State 1 means generic failure, state 2 means bad user ID, and state 5 means bad password. The error message itself won't say "wrong password" — it'll just say "Login failed for user" — so you have to check the SQL Server error log.

To see the actual state, open SQL Server Management Studio (SSMS) and connect with Windows Authentication (if you can). Then run this query against the master database:

SELECT * FROM sys.dm_exec_sessions WHERE session_id = @@SPID;
-- Or check the error log directly:
EXEC xp_readerrorlog 0, 1, N'18456';

If you can't connect at all, look in the Windows Event Viewer under Applications and Services Logs > Microsoft > Windows > MSSQLSERVER. The state number is in the details. For state 5, the fix is simple: reset the password. Don't bother with the GUI — use a script:

ALTER LOGIN [YourUserName] WITH PASSWORD = 'NewStrongPassword';
GO

The reason step 3 works is that SQL Server hashes passwords using SHA-512, and a typo means the hash won't match. There's no lockout by default in SQL Server (unless you set one), so you can try as many times as you want.

Cause #2: SQL Server is in Windows Authentication Mode Only — State 1

Here's a trap: you install SQL Server and choose "Windows Authentication Mode" during setup. Later, you try to log in with a SQL Server username and password (like 'sa'), and you get error 18456 with state 1. What's actually happening here is that SQL Server won't even attempt to validate the password because the server is configured to only accept Windows logins. The login attempt is rejected at the protocol level before any password check happens.

The fix is to switch to Mixed Mode (Windows + SQL Server Authentication). You can do this through SQL Server Configuration Manager or by editing the registry. I prefer the SQL Server Management Studio approach, but if you can't get in at all, use the registry:

-- If you can connect via Windows Auth:
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2;
GO
-- Then restart the SQL Server service.

-- Registry path if you need to do it manually:
-- HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\MSSQLServer\LoginMode
-- Set to 2 (1 = Windows only, 2 = Mixed)

After restarting, you'll be able to use SQL Server logins. This is the most common "I just installed SQL Server and can't log in" scenario on Windows Server 2019 and 2022.

Cause #3: Disabled or Locked Out Login — State 6 or 8

State 8 means the login is disabled. State 6 means the Windows login mapping is broken (rare, but happens with domain migrations). The 'sa' account is disabled by default in SQL Server 2016 and later — that's by design, and it catches people off guard. You might have forgotten that you disabled a login after a security audit, or a DBA script ran overnight and turned it off.

To re-enable a disabled login, connect with Windows Authentication (you still need a sysadmin account) and run:

ALTER LOGIN [YourUserName] ENABLE;
GO
-- For the 'sa' account specifically:
ALTER LOGIN [sa] ENABLE;
GO
-- Then set a strong password if it was never set:
ALTER LOGIN [sa] WITH PASSWORD = 'YourStrong!Passw0rd';
GO

If you can't get in with any account at all — maybe the only sysadmin account was the disabled one — you'll need to start SQL Server in single-user mode. Stop the SQL Server service, then start it from the command line with the -m flag. This lets you connect as a local admin to fix the logins. It's a last resort, but it works.

One more thing: state 8 can also appear if the password has expired and the server enforces expiration. SQL Server 2019 and later can enforce password policies if you configured them. Check if the login has CHECK_EXPIRATION set to ON:

SELECT name, is_expiration_checked FROM sys.sql_logins WHERE name = 'YourUserName';

If it's ON and the password expired, reset it with CHECK_POLICY = OFF temporarily, then set a new one and turn policies back on.

Quick-Reference Summary Table

If you're still stuck after checking these three causes, look at the SQL Server error log for the exact state number. The state is the first clue. Don't just Google "error 18456" — you need to know which state you're dealing with. The fix for state 5 is useless if you're dealing with state 8.