About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.
Error 18456 means SQL Server rejected a sign-in. The client sees only "Login failed for user" because Microsoft says the message returned to the client "deliberately hides the nature of the authentication error". The real reason is in the SQL Server error log, in an entry that carries a state number and, for some failures, a line starting "Reason:". Read that entry first, then match the state number to the table below.
Changing passwords, logins or authentication mode before reading the log is guesswork, and some of those changes cannot be undone cleanly. This page gives read-only checks first, then a fix for each cause.
What the error means
Microsoft Learn lists the event as 18456, source MSSQLSERVER, symbolic name LOGON_FAILED, message text "Login failed for user '%.*ls'.%.*ls". Microsoft describes it as a connection attempt rejected because of an authentication failure, with causes such as invalid credentials, password expiry and the wrong authentication mode.
Two details matter. First, whether the user name in the message has a domain in front of it. Microsoft says that without a domain name the problem is a failing SQL Server login, and with a domain name it is a failing Windows account login. Second, the state number in the error log. Microsoft publishes this list of states:
| State | Meaning on the Microsoft page |
|---|---|
| 1 | Error information isn't available; you may lack permission to receive the details |
| 2, 5 | User ID isn't valid |
| 6 | A Windows login name was used with SQL Server Authentication |
| 7 | Login is disabled, and the password is incorrect |
| 8 | The password is incorrect |
| 9 | Password isn't valid |
| 11, 12 | Login is valid, but server access failed |
| 18 | Password must be changed |
| 38, 46 | Couldn't find database requested by user |
| 58 | SQL Server is set to Windows Authentication only and a client used SQL authentication; another cause is SIDs not matching |
| 62 | Windows account tried a contained database whose SIDs do not match |
| 126 | Database requested by user doesn't exist |
Microsoft adds that "Other error states exist and signify an unexpected internal processing error." The page covers SQL Server 2017 and later on Windows and Linux.
Likely causes
| Cause | What you see | How to confirm |
|---|---|---|
| Wrong or expired password, or login disabled | State 7, 8, 9 or 18; reason "Password did not match that for the login provided" | Error log entry beside the 18456 line |
| Login does not exist | State 2 or 5; reason "Could not find a login matching the name provided" | Compare the name in the log with the logins on the instance |
| Server is in Windows Authentication mode | State 58; reason "An attempt to login using SQL authentication failed. Server is configured for Windows authentication only." | Instance Properties, Security page |
| Windows login name sent as a SQL Server login | State 6 | Connection string uses a user name and password for what is a Windows account |
| Login valid but cannot reach the server | State 11 or 12; a Windows user that gets in through the local Administrators group | Application not started with Run as administrator; a deny on a group |
| Default or named database unavailable | State 38, 46 or 126; reason "Failed to open the database" or "Failed to open the explicitly specified database" | Database offline, misspelled in the connection string, or login default database dropped |
| Group-based access not resolving | Windows user fails although a group has a login | Run xp_logininfo for the user; cross-domain groups must live in the SQL Server's domain |
| Wrong server | Works in test, fails in production | Connection string still names the DEV or QA server |
Prove the cause before changing anything
All of these checks only read.
- Open the current SQL Server error log. In SSMS, expand Management, then SQL Server Logs, and double-click the current log. Microsoft's connectivity page describes using Filter with the text to search for. Search for "Login failed". You can also open the ERRORLOG file in a text editor; Microsoft gives the default path pattern as Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG.
- Find the pair of lines for the failure. Microsoft shows the format with an example:
Error: 18456, Severity: 14, State: 8.followed byLogin failed for user '<user_name>'. [CLIENT: <ip address>]. Read the state number, then the Reason text if present. - Note the CLIENT address. If it is not the machine you expect, the application is pointing at the wrong place or a different host is retrying with old credentials.
- For Windows accounts reached through a group, run
xp_logininfowith the account name. Microsoft's description of the result: an error means SQL Server cannot resolve the name at all; no rows means no group provides access; one or more rows means the user belongs to a group that provides access. - In SSMS, check Security, then Logins, for the login, and note whether it is disabled and what its default database is.
If the log shows only state 1, Microsoft's description is that error information isn't available because you may not have permission to receive the details. Ask an administrator to read the log for you rather than guessing.
Fix
Bad password, expired password, state 7, 8, 9 or 18. Use the correct password in the application or ask the administrator to set a new one.
Risk: setting a new password changes the credential for every application that uses the login, and any of them still holding the old password will start failing with state 8. There is no way to restore the old password from SQL Server. To back out, set the password back to the previous value, if the person who held it still has it. Change the saved password in each application in the same window.
If the application is SQL Server Integration Services, Microsoft notes that a configuration file may override the Connection Manager settings. For state 7 on sa after switching modes, Microsoft explains that when SQL Server was installed in Windows Authentication mode and later changed to mixed mode, the sa login is initially disabled.
Login missing, state 2 or 5. Check the spelling in the connection string first. If the login should exist, create it. For a Windows account, you can instead add it to a local or domain group that is allowed to connect.
Disruptive step: creating a login or adding an account to a group grants access. Grant only what the application needs. To back out, drop the login you created or remove the account from the group; the reason a login existed or was removed may be in change records, so check before dropping one you did not create.
Windows Authentication only (state 58) or a Windows login sent as a SQL login (state 6). Either change the application to use integrated security (for ODBC, add TRUSTED_CONNECTION=YES; for OLE DB, INTEGRATED SECURITY=SSPI) or, for state 58, enable SQL Server and Windows Authentication mode on the instance.
Risk: changing the connection string to integrated security changes which account the application signs in as, and Microsoft warns that integrated security may expose other issues if it is not configured to allow integrated authentication. To back out, restore the previous connection string.
Disruptive step: changing the authentication mode needs a restart of the SQL Server service, which drops every connection and interrupts every application. Schedule it. To back out, set the mode back and restart again.
Database missing, state 38, 46 or 126. Fix the database name in the connection string, noting case if the server uses a case-sensitive collation. If the login's default database is offline or gone, either specify a database that is available or ask the administrator to change the default database with ALTER LOGIN or SSMS. If the login has no user in the database it needs, Microsoft shows CREATE USER [<UserName>] FOR LOGIN [UserName] as the way to map it.
Disruptive step: ALTER LOGIN changes the login for every application that uses it, and CREATE USER grants database access. To back out, set the default database back and drop the user you added.
State 11 or 12 with an administrator account. Microsoft's example is a Windows user who has access as a member of the local Administrators group while Windows is not passing administrator credentials. Start the connecting program with Run as administrator, then add the Windows user as its own login.
Disruptive step: adding the Windows user as its own login grants that person access to the instance. Grant only the access the person needs. To back out, drop the login you added.
Wrong server. Correct the connection string to the intended server. Microsoft names a DEV or QA connection string left in production as an example.
Risk: the application will start reading and writing the database on the server you name, so confirm it is the intended server before restarting the application. To back out, restore the previous connection string.
Failures that carry the blank, (null) or NT AUTHORITY\ANONYMOUS LOGON user name belong to Kerberos, domain controller reachability or SPN problems rather than passwords. Microsoft's page points to SETSPN -X and -Q to look for duplicate or misplaced SPNs, and to NLTEST /SC_QUERY:<domain> to see the secure channel. Work through the results of those two checks before changing any account or SPN.
Confirm the fix
- Repeat the sign-in from the same client and the same account. The new error log should show no new 18456 entry for it.
- To test independent of the application, connect with SSMS or a UDL file using a hard-coded connection string, as Microsoft suggests. If that works but the application does not, the application's connection string or configuration is the cause.
- Where you fixed database access, open the target database as the account and run a simple read.
Example (illustrative): application server app01 in CONTOSO connects to sql01 as svc-orders. At 02:10 the monitoring shows 42 failures in six minutes, which is 7 a minute. The error log on sql01 reads Error: 18456, Severity: 14, State: 38. with the reason "Failed to open the database 'Orders2025'" and a CLIENT address of 10.0.20.15. State 38 means the requested database could not be found. The administrator finds the application's configuration was updated to a database name that exists only on the test server sql02. The fix is the connection string on app01; no login or password was touched.
What to watch afterwards
- Repeated 18456 entries from one CLIENT address at fixed intervals point to a service or scheduled job holding old credentials.
- Entries with state 18 or 7 after a password policy change can come from many applications at once.
- After any authentication-mode change, confirm each application that used SQL logins still connects.
- Keep the error log long enough to cover the period between the failure and the person noticing it.
Read next
Related articles you may want next.
- TroubleshootingThe Database Restored, but the Application Cannot Log InTrace SQL logins, database users and SID mappings after a restore, and validate application access without broadening application privileges.Read article
- TroubleshootingSQL Kerberos After Migration: Check SPNs and NTLM UseTrace client names, SQL service identities and SPNs to explain failed Kerberos authentication after migration without weakening authentication.Read article
- Backup & RecoverySQL Agent backup job fails with error 15404: could not obtain information about Windows userSQL Agent job fails with error 15404 'Could not obtain information about Windows NT group/user'? Check the job owner, test it, then change it deliberately.Read article
Risks and limits
These commands have not been run or lab-tested. State numbers and reason text come from Microsoft's published page; other states exist, and the page says they signify an unexpected internal processing error. On Microsoft's page, states 102 to 111 and 132 to 133 are listed as Azure AD failures, and states 122 to 124 as a failure due to an empty user name or password.
Do not reset passwords, enable sa or change authentication mode just to get a sign-in working. Each of those widens who can reach the instance. If the error log cannot be read because the account lacks permission, the answer is to get the log, not to grant a broader role.
Sources
- MSSQLSERVER_18456 — Microsoft Learn, page dated 2025-11-25, accessed 18 November 2026
- Fix Network-Related or Instance-Specific Errors in SQL Server — Microsoft Learn, page dated 2026-06-02, accessed 18 November 2026
This article was originally published on .