Fix it now
The client reached SQL Server and was refused. It is told nothing more on purpose, because a logon prompt that explains itself is a gift to anyone guessing passwords. The real reason is written server-side as a state number, and each published state has its own fix.
EXEC sp_readerrorlog 0, 1, N'18456';
SELECT name, is_disabled, LOGINPROPERTY(name,'IsLocked') AS locked, LOGINPROPERTY(name,'IsExpired') AS expired
FROM sys.sql_logins ORDER BY name;
- Read the SQL Server error log for the entry written at the time of the failure and note the number after the word State. In SSMS that is Management, SQL Server Logs, Current.
- Map it: 2 and 5 mean the user ID is not valid; 6 means a Windows login name was supplied with SQL authentication; 7 means the login is disabled and the password is wrong; 8 and 9 mean the password is wrong; 18 means the password must be changed.
- 38 and 46 mean the database the login asked for could not be found; 58 means SQL authentication was attempted on a Windows-only instance; 11 and 12 mean the login is valid and server access failed.
- Apply the fix for that state and retry. Do not change the authentication mode or grant sysadmin until you know which state you have.
If the error log has no 18456 entries at all, failed-login auditing may be off. Check the login auditing setting under Server Properties, Security, then reproduce the failure.
If the connection now succeeds, you are done. If the state number does not match anything published, the next section explains what that means and where to look instead.
Why it happens
Every 18456 the client sees carries State 1. That is not a bug and it is not the state you want. Microsoft publishes State 1 as error information not being available, usually because the caller does not have permission to receive the details, and SQL Server substitutes it deliberately so a client cannot tell a wrong password from a login that does not exist. The genuine state is written server-side, in the error log, on the same line as the login name and the client’s address.
So you cannot troubleshoot 18456 from the client. You need the error log, or somebody who has it; everything the client sees is identical for every cause.
Several nearby errors carry the reason in the number instead of in a state, and they are easier to read for exactly that reason. 18470 is a login refused because the account is disabled. 18486 is one refused because the account is currently locked out, and the message says an administrator can unlock it. 18461 is the instance being in single-user mode with the one permitted connection already taken. None of those needs an error log to interpret.
4064 and 4060 are the pair that confuses people most, because they are database errors dressed as login failures. 4064 is being unable to open the user default database; 4060 is being unable to open the database the login asked for. In both, authentication succeeded and the connection then failed on the database, so the fix is a database change rather than a password one.
States 2 and 5: the user ID is not valid
You have this one if The error log names a login you do not recognise, or one spelled slightly differently from the one you created.
- Confirm what exists:
SELECT name, type_desc, is_disabled FROM sys.server_principals WHERE type IN ('S','U','G'); - Check the connection string for a stale server name, a typo, or a domain prefix that no longer applies.
- If the login genuinely is missing, create it, then map it in the target database.
CREATE LOGIN [appuser] WITH PASSWORD = '<strong password>',
DEFAULT_DATABASE = [master];
USE [SalesDB];
CREATE USER [appuser] FOR LOGIN [appuser];
States 7, 8 and 9: disabled, or the password is wrong
You have this one if The login exists in sys.server_principals, and either is_disabled is 1 or the password simply does not match. State 7 is both at once.
- Enable a disabled login:
ALTER LOGIN [appuser] ENABLE; - Reset the password:
ALTER LOGIN [appuser] WITH PASSWORD = '<new password>'; - If the account is locked rather than wrong, unlock it:
ALTER LOGIN [appuser] WITH PASSWORD = '<new password>' UNLOCK; - To unlock without changing the password, toggle the policy:
ALTER LOGIN [appuser] WITH CHECK_POLICY = OFF;thenALTER LOGIN [appuser] WITH CHECK_POLICY = ON;
A service reconnecting every few seconds with an old password will re-lock the login faster than you can test it. Stop the service first, then fix the login, then start it.
States 11 and 12: valid login, server access failed
You have this one if A Windows login fails while SQL logins on the same instance work, and the account reaches SQL Server through a group rather than a login of its own.
- Microsoft’s note for state 11 is a Windows user with access through the local administrators group while Windows is not supplying administrator credentials. Start the client with Run as administrator and see whether it connects.
- Then fix it properly by adding that Windows user as a login in its own right.
- Confirm the account or a group it belongs to has a login:
SELECT * FROM sys.server_principals WHERE type IN ('U','G'); - Check for a DENY in the group hierarchy; one DENY beats every inherited GRANT.
States 38 and 46, and errors 4064 and 4060: the database is the problem
You have this one if The login succeeds against master and fails when it asks for a specific database, or the log names a database that is offline, renamed or dropped.
- Connect to master to get in at all: in SSMS use Options, Connection Properties, Connect to database, and type
master. Orsqlcmd -S server -d master. - Repoint the login:
ALTER LOGIN [appuser] WITH DEFAULT_DATABASE = [master]; - Check the database is online:
SELECT name, state_desc, user_access_desc FROM sys.databases; - Confirm the login has a user in that database; Microsoft lists a missing mapping as a cause of 4064 in its own right.
4064 is the default-database version and 4060 is the explicitly requested one. Both mean the same thing to you: the login worked, the database did not.
Full reference
The published state table
| State | What Microsoft publishes |
|---|---|
| 1 | Error information is not available, usually because you lack permission to receive the details |
| 2 and 5 | User ID is not valid |
| 6 | An attempt was made to use a Windows login name with SQL Server Authentication |
| 7 | Login is disabled, and the password is incorrect |
| 8 | The password is incorrect |
| 9 | Password is not valid |
| 11 | Login is valid, but server access failed. Often a Windows user reaching SQL Server through the local administrators group without administrator credentials |
| 12 | Login is valid, but server access failed |
| 18 | Password must be changed |
| 38 and 46 | Could not find the database requested by the user |
| 58 | Windows Authentication only, and a client tried SQL authentication. Also seen where SIDs do not match |
| 62 | A Windows account reaching a contained database that exists, where the SIDs do not match |
| 102 to 111, 132 and 133 | Microsoft Entra failure |
| 122 to 124 | Empty user name or password |
| 126 | The database requested by the user does not exist |
Microsoft adds that other states exist and signify an unexpected internal processing error. If the number in your log is not in this table, that is what you have: there is nothing published to map it to, and the useful next moves are the identity and the database rather than a search for the digits. State 40 in particular is widely quoted and is not in Microsoft’s table.
Getting at the log
| Route | Notes |
|---|---|
| SSMS, Management, SQL Server Logs, Current | The straightforward route when you can already connect |
EXEC sp_readerrorlog 0, 1, N'18456'; |
Current log, SQL Server, filtered on the number |
EXEC sp_readerrorlog 1, 1, N'18456'; |
The previous log, for a failure that predates a restart |
| The ERRORLOG file in the instance’s LOG folder | The route when the instance will not accept a connection at all |
Confirm you are reading the log of the instance the client actually reached. On a machine with more than one instance, or behind a listener, this is a more common mistake than it sounds, and it produces the impression that failed logins are not being recorded when they are simply being recorded somewhere else.
The neighbouring errors, precisely
| Error | What it says | What to do |
|---|---|---|
| 18470 | The account is disabled | ALTER LOGIN ... ENABLE; |
| 18486 | The account is currently locked out, and an administrator can unlock it | ALTER LOGIN ... UNLOCK;, or toggle CHECK_POLICY |
| 18461 | The server is in single user mode and only one administrator can connect | Find and close the connection holding it, or restart out of single-user mode |
| 4064 | Cannot open the user default database | Repoint DEFAULT_DATABASE, or fix the user mapping in that database |
| 4060 | Cannot open the database requested by the login | Fix the Initial Catalog in the connection string, or bring the database online |
What 4064 actually covers
Microsoft’s explanation for 4064 is broader than the usual reading of it. It covers permission problems in the default database, including a login with no mapped user there and a user that has been denied access, and it covers the database being unavailable for a list of reasons: suspect, dropped, misnamed, single-user with the connection taken, detached, restricted user, offline, in emergency state, or part of a mirror. Two of those are permission questions and the rest are availability questions, so check which family you are in before changing a login.
Before you widen anything
- Do not switch the instance to mixed mode to clear a state 58 unless mixed mode is what you actually want. It is a server-wide security decision taken to fix one connection string.
- Do not grant sysadmin to clear a state 11 or 12. The published remedy is a login of the account’s own, not a role membership.
- Do not create a second login with the same name in a different form. Two principals for one human is how the next person loses a day.
- Do check whether the application is retrying. A service holding an old password re-locks the account faster than you can test the reset.
Every code this article covers
| Code | What it points at | Source |
|---|---|---|
18456 |
Login failed for the named user. Severity 14. The state number in the server’s error log names the reason; the client is always told State 1 | Microsoft Learn |
18470 |
Login failed because the account is disabled. Severity 14 | Microsoft Learn |
18486 |
Login failed because the account is currently locked out; an administrator can unlock it. Severity 14 | Microsoft Learn |
18461 |
Login failed because the server is in single user mode and only one administrator can connect. Severity 14 | Microsoft Learn |
4064 |
Cannot open the user default database, so the login failed. Severity 11. Covers both permission and availability causes | Microsoft Learn |
4060 |
Cannot open the database requested by the login, so the login failed. Severity 11 | Microsoft Learn |
Confirm the fix worked
- Connect from the same client with the same credentials and confirm it succeeds.
- Re-read the error log and confirm no new 18456 entries appear for that login.
- Run
SELECT SUSER_SNAME(), DB_NAME();on the new connection to confirm both the identity and the database are what you expected. - For a Windows login, run
SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID;and confirm the authentication scheme matches your design. - Restart the application rather than testing only from SSMS, so pooled connections pick up the change.
Questions people ask about this
Why does the client always show State 1?
By design. Microsoft publishes State 1 as error information not being available, and returning the real state to an unauthenticated client would tell an attacker whether a login name exists and whether a password was close. The genuine state is written only to the server’s error log.
My state number is not in the table. What now?
Microsoft says explicitly that other states exist and signify an unexpected internal processing error, so there is nothing to look up. Work the identity and the database instead: does the login exist, is it enabled, can it open the database it asked for. State 40 is widely quoted online and is not one Microsoft publishes.
Does fixing 18456 cost anything?
No. Every fix here is a configuration or permission change using tools already in the product, in every edition including the free ones. There is no licence or add-on that changes logon behaviour.
The error log has no 18456 entries at all. Why?
Failed-login auditing may be switched off. Check the login auditing setting under Server Properties, Security, set it to record failed logins at least, then reproduce the failure. Confirm too that you are reading the log of the instance the client actually reached.
Can I stop an application locking out a login every few seconds?
Stop the application first, then reset the password, then restart it with the new one. Repeated failures from a service holding a stale password re-lock the account faster than any reset can help, which makes the reset look as though it failed.
