SQL Server Error 18456: How to Fix Login Failed for User (All States)

SQL Server Error 18456 means the server rejected a login attempt, but the message on screen never says why. The real reason is a state code buried in the error log, and each state points to a different fix. This guide shows how to pull that state code and apply the exact fix it calls for.

What Causes SQL Server Error 18456

The error appears whenever SQL Server rejects a login, and the client message hides the exact reason on purpose.

• The username does not exist or was typed incorrectly
• The password is wrong, expired, or must be changed
• The login is disabled
• The instance runs Windows Authentication only, but the app uses a SQL login
• The login exists but lacks access to the target database

Try This First: Run SSMS as Administrator

Before digging into state codes, try one quick check: close SSMS, then right-click it and select Run as administrator. On local or dev instances, SSMS launched without admin rights can fail to authenticate even with the correct credentials, because of how the local Windows account maps to sysadmin. If this fixes it, you can skip the rest of this guide.

Decode the State Code Before You Fix Anything

The message SQL Server shows on screen almost never explains the actual cause, so the fix always starts in the error log. You need an account with sysadmin rights (or a Windows Admin login) to view the log and run the fixes below.

Open SSMS, expand Management, then open SQL Server Logs. Search the Current log for 18456. The matching line looks like this:

Error: 18456, Severity: 14, State: 8.
Login failed for user 'AppUser'

The number after State: tells you which fix applies. If you cannot open SSMS at all, the log file sits on disk at ...\MSSQL\Log\ERRORLOG and opens in any text editor.

You can also pull the same information straight from T-SQL. Open a New Query window in SSMS, connected with an admin account, and run:

EXEC xp_readerrorlog 0, 1, N'Login failed', NULL, NULL, NULL, N'DESC';

In every script below, replace your_login or your_database with the actual login or database name from your own error.

Fix 1: State 8 or 9, Incorrect Password

State 8 means SQL Server checked the password and rejected it. Confirm the account is not already locked before you reset anything.

SELECT name,
       is_disabled,
       LOGINPROPERTY(name, 'IsLocked')         AS is_locked,
       LOGINPROPERTY(name, 'IsMustChange')     AS must_change,
       LOGINPROPERTY(name, 'BadPasswordCount') AS bad_password_count
FROM   sys.sql_logins
WHERE  name = N'your_login';

A high bad_password_count usually points to a service or app that kept retrying an old password. Find that source first, then reset the password:

ALTER LOGIN [your_login] WITH PASSWORD = 'NewStrongPassword123!';

Update every application or connection string that stores the old credential, or the same failure returns within minutes.

Fix 2: State 2 or 5, Login Does Not Exist

These states mean SQL Server never found a matching login, usually from a typo or a login that got dropped.

• Check the spelling in the connection string against sys.sql_logins or sys.server_principals
• For a Windows login, confirm the domain prefix is correct, such as DOMAIN\username
• Create the login if it was genuinely deleted:

CREATE LOGIN [your_login] WITH PASSWORD = 'StrongPassword123!';

Fix 3: State 7, Login Disabled

Security audits often disable logins for compliance, which produces this state even when the password is correct.

Go to Security > Logins in Object Explorer, right-click the account, open Properties, then set Status > Login to Enabled. The equivalent script:

ALTER LOGIN [your_login] ENABLE;

Fix 4: State 11 or 12, Valid Login but No Server Access

The login itself is fine, but it lacks the Connect permission on the instance. This is common after a Windows group membership change.

SELECT sp.name, spm.permission_name, spm.state_desc
FROM   sys.server_principals sp
LEFT JOIN sys.server_permissions spm
       ON spm.grantee_principal_id = sp.principal_id
      AND spm.permission_name = 'CONNECT SQL'
WHERE  sp.name = N'DOMAIN\your_login';

An empty result set means no CONNECT SQL grant exists yet. A row with state_desc showing DENY means access was explicitly blocked. Either way, restore it:

GRANT CONNECT SQL TO [DOMAIN\your_login];

Grant access through Active Directory groups instead of individual logins so a membership change does not break access again.

Fix 5: State 18, Password Must Be Changed

The login has MUST_CHANGE set, so SQL Server blocks the connection until the password is updated.

ALTER LOGIN [your_login] WITH PASSWORD = 'NewStrongPassword123!' , MUST_CHANGE;

Have the user change the password through the application or SSMS on first login after this reset.

Fix 6: State 38 or 40, Database Problem, Not a Login Problem

The next two states are different from the ones above: the login itself is fine, and authentication actually succeeds. These states fool people because the login authenticates correctly. The failure comes from the database it is trying to reach.

SELECT name, state_desc, user_access_desc
FROM   sys.databases
WHERE  name = N'your_database';

If state_desc shows anything other than ONLINE, that is the cause. RECOVERING is usually transient and clears once SQL Server finishes bringing the database back online. SUSPECT or OFFLINE means the database needs a DBA to investigate or restore it before anyone can connect.

For state 40 specifically, the login’s default database cannot open. Pointing the login’s default database elsewhere restores the login itself, but it does not restore access to the original database:

ALTER LOGIN [your_login] WITH DEFAULT_DATABASE = [master];

Treat this as a way to unblock the login while the actual database gets fixed, not as the final answer if the app needs that specific database.

Fix 7: State 58, SQL Login on a Windows-Only Instance

Check whether the instance only allows Windows Authentication:

SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS windows_auth_only;

A result of 1 means SQL logins cannot connect at all. Switch the instance to Mixed Mode:

1. Right-click the server name in Object Explorer and open Properties
2. Go to the Security page
3. Select SQL Server and Windows Authentication mode
4. Restart the SQL Server service to apply the change

Treat this as a scheduled change, since the service restart drops active connections.

When None of the States Above Match

If the error log has no matching entry at the time of the failure, the connection never reached authentication. That points to a network, firewall, or TLS problem rather than a login issue.

One of the most common causes here is the TCP/IP protocol being disabled, which happens by default on a fresh SQL Server install. To check and fix it:

1. Open SQL Server Configuration Manager
2. Expand SQL Server Network Configuration and select Protocols for your instance
3. Right-click TCP/IP in the right pane and select Enable
4. Restart the SQL Server service for the change to take effect

If TCP/IP is already enabled, verify the server is reachable on its port and that the certificate configuration matches before touching any login settings.

Frequently Asked Questions

Read More

• How to Fix DMP Files in System32 Windows 11 (4 Easy Methods)
• Spotify Audiobooks Not Working? Here’s Why & How to Fix It
• Cineplus App Crashing? Here’s How to Fix It
• WhatsApp Web Not Connecting? 11 Fixes to Restore the Link
• Snipping Tool Not Working on Windows 11: 11 Fixes That Actually Work
• L2TP VPN Not Responding on Windows: 5 Fixes That Work