On this page
Why it happens
Error 18456 means SQL Server received a login request it refused to authenticate. To avoid helping attackers, the message returned to the client is intentionally generic and never says whether the user name, the password, or the permissions were the problem. The real reason is written only to the SQL Server error log, encoded as a state number.
Reading that state is the key to fixing 18456 quickly. The client always sees State 1 (a masked value), but the error log entry shows the true state. The common states are:
State 2 or State 5: the login does not exist (user name not found). State 6: a Windows login was supplied through SQL authentication. State 7: the login is disabled and the password is also wrong. State 8: the password does not match. State 38: the login is valid but has no access to the requested database. State 40: SQL Server cannot open the login default database. Match your state to this list and you know exactly what to fix.
Examples
Connecting with the wrong password (State 8)
-- Connecting as 'sa' with an incorrect password.
-- The client sees the generic message below; the error log shows State 8.
SELECT 1;Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)
SQL login used while the server is Windows-only
-- A SQL login (mixed mode) is used, but the instance only allows
-- Windows Authentication. The connection is rejected before any query runs.
SELECT SUSER_SNAME();Login failed for user 'app_user'. (Microsoft SQL Server, Error: 18456)
Login with no access to the requested database (State 38)
-- The login authenticates, but the target database has no matching user.
-- The error log records State 38 for this connection.
USE Sales;Login failed for user 'reporting'. (Microsoft SQL Server, Error: 18456)
How to fix it
Read the SQL Server error log to find the state
Start here. The state number is the whole answer. Open the error log and find the 18456 entry for your login; the State value tells you which fix below applies.
-- Read the current error log and filter for the failed login
EXEC sp_readerrorlog 0, 1, N'Login failed';Verify the user name and password (State 2, 5, 8)
State 8 is a password mismatch; State 2 and State 5 mean the login was not found at all. Confirm you are using the exact login name and correct password, then reset the password if needed.
-- Reset the password for a SQL login
ALTER LOGIN [app_user] WITH PASSWORD = 'a-strong-new-password';Enable Mixed Mode authentication (State 6)
If a SQL login is used but the instance is Windows-only, switch to Mixed Mode (SQL Server and Windows Authentication). Set it, then restart the SQL Server service for the change to take effect.
-- Set Mixed Mode in the registry (LoginMode = 2), then restart the service
EXEC xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode', REG_DWORD, 2;
-- Restart SQL Server after running this.Enable or unlock the login (State 7)
State 7 means the login is disabled (and the password was also wrong). Enable it, unlock it if it was locked out, and set a known password.
ALTER LOGIN [app_user] ENABLE;
ALTER LOGIN [app_user] WITH PASSWORD = 'a-strong-new-password' UNLOCK;Grant database access (State 38)
State 38 means the login is valid but the target database has no user for it. Create a database user mapped to the login and add it to a role so it can actually connect and work.
USE Sales;
CREATE USER [reporting] FOR LOGIN [reporting];
ALTER ROLE db_datareader ADD MEMBER [reporting];Fix the default database (State 40)
State 40 means SQL Server cannot open the login default database (it may have been dropped, taken offline, or renamed). Point the login at a database that exists, such as master.
ALTER LOGIN [app_user] WITH DEFAULT_DATABASE = master;How to prevent it
Decide on your authentication model up front. If applications use SQL logins, configure the instance for Mixed Mode (SQL Server and Windows Authentication) from the start so you never hit State 6 in production. Keep connection strings accurate: the right server name, login, password, and an explicit Initial Catalog that the login can actually open avoids State 38 and State 40.
Follow least privilege: give each application its own login mapped to a database user with only the roles it needs, rather than sharing sa. Never embed credentials in code, and validate all input to avoid SQL injection. For a broader checklist, see the SQL Server security guide.
Common questions
How do I find the real reason behind SQL Server error 18456?
The client message is deliberately generic, so open the SQL Server error log and find the 18456 entry for your login. It records a State number that reveals the cause: State 8 is a wrong password, State 5 means the login was not found, State 38 means no access to the requested database, and State 40 means the default database could not be opened.
What does State 8 mean in error 18456?
State 8 means the login exists but the password does not match. Confirm the password in your connection string, and reset it with ALTER LOGIN ... WITH PASSWORD if needed.
Why do I get error 18456 for user sa even though the password is correct?
A common cause is State 6: the instance is set to Windows Authentication only, so the sa SQL login is rejected. Enable Mixed Mode (SQL Server and Windows Authentication) and restart the SQL Server service. If the state is 7, the sa login is disabled and must be enabled first.