Login Failed for User - SQL Server 18456

Login failed for user with Error: 18456 is SQL Server rejecting an authentication attempt without telling the client why:

Login failed for user 'appuser'. (Microsoft SQL Server, Error: 18456)

That is deliberate: the client is given the least informative version so an attacker cannot learn whether a login exists, is disabled, or merely had the wrong password. The real reason exists — as a State number — in the server's error log. Everything below depends on reading it.

Why This Happens

SQL Server logs two lines per failure. The first carries the state; the second carries the login and the client address:

2026-08-12 20:12:56.34  Logon  Error: 18456, Severity: 14, State: 8.
2026-08-12 20:12:56.34  Logon  Login failed for user 'appuser'. [CLIENT: 10.0.3.14]

State 8 means the password was wrong. State 5 means the login does not exist. State 38 means the database could not be found. These call for completely different fixes, and the client message is identical for all of them — which is why 18456 generates so much wasted effort.

Fix 1: Read the State Number

In SQL Server Management Studio: expand Management → SQL Server Logs, open the current log, and filter on 18456. Or query it directly, which is faster:

EXEC xp_readerrorlog 0, 1, N'18456', NULL, NULL, NULL, N'DESC';

On Linux the log is a file, typically /var/opt/mssql/log/errorlog:

sudo grep -A1 "Error: 18456" /var/opt/mssql/log/errorlog | tail -20

Now look your state up.

The State Table

Fix 2: By State

States 2 and 5 — the login does not exist

The log confirms it with Reason: Could not find a login matching the name provided. Check what actually exists:

SELECT name, type_desc, is_disabled, create_date
FROM sys.server_principals
WHERE type IN ('S','U','G')
ORDER BY name;

This is very often a connection string still pointing at a development server after a deployment. Verify the target before creating anything:

SELECT @@SERVERNAME AS server_name, @@VERSION AS version;

If the login genuinely belongs on this instance, create it with only what it needs — not a server role:

CREATE LOGIN [appuser] WITH PASSWORD = N'<strong-password>';
USE [appdb];
CREATE USER [appuser] FOR LOGIN [appuser];
ALTER ROLE db_datareader ADD MEMBER [appuser];
ALTER ROLE db_datawriter ADD MEMBER [appuser];

Grant db_owner or sysadmin only when the application genuinely performs schema changes — those memberships are how a single leaked application credential turns into a full server compromise.

State 8 — the password is wrong

The log says Reason: Password did not match that for the login provided. Nothing is wrong with the server — fix the credential where it lives. Check for a trailing space, an unescaped character in a config file, or a rotated password that reached one deployment target but not another.

If the password must be reset:

ALTER LOGIN [appuser] WITH PASSWORD = N'<new-strong-password>';

State 7 — disabled login

The account exists but is disabled. This is the state you get when someone tries sa on an instance where it is correctly turned off. Leave sa disabled — it is a universally known superuser name and the first thing credential-stuffing tools try. Create a named login for the job instead. If a legitimate application login was disabled by mistake:

ALTER LOGIN [appuser] ENABLE;

States 11 and 12 — valid login, server access failed

For a Windows account that is an administrator only through group membership, the client may be running without an elevated token — launch it with Run as administrator. The durable fix is to grant the account access explicitly so the permission survives changes to that group. The log may also show Token-based server access validation failed with an infrastructure error, which points at Kerberos, SPNs, or a DENY on a group the user belongs to.

States 38, 46 and 126 — the database is the problem

Authentication succeeded; the database did not. The log names it:

Login failed for user 'appuser'. Reason: Failed to open the database 'appdb' specified in the login properties

Check the database is present and online:

SELECT name, state_desc, user_access_desc
FROM sys.databases
ORDER BY name;

Anything other than ONLINE is a database availability problem, not a login one. If the database is fine but the login has no user in it, map one with CREATE USER [appuser] FOR LOGIN [appuser];.

If the login's default database is unavailable you may also see Cannot open user default database. Login failed. with Error: 4064. Point the login at an available default, or name a database explicitly in the connection string:

ALTER LOGIN [appuser] WITH DEFAULT_DATABASE = [appdb];

States 6 and 58 — wrong authentication mode

State 58 means a SQL login was offered to an instance accepting only Windows Authentication. The log spells it out: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.

The lower-impact fix is a trusted connection — add Integrated Security=SSPI (or Trusted_Connection=Yes for ODBC) to the connection string.

Changing the server's authentication mode requires restarting the SQL Server service, which disconnects every session and takes the instance down until it comes back. That is a maintenance window, not a quick toggle. Only do it if the application genuinely cannot use Windows Authentication, and schedule it.

Verify the Fix

Test as the actual identity against the actual database, not as yourself:

SELECT SUSER_NAME()   AS login_name,
       USER_NAME()    AS database_user,
       DB_NAME()      AS current_database,
       IS_SRVROLEMEMBER('sysadmin') AS is_sysadmin;

is_sysadmin should be 0 for an application login. From the command line:

sqlcmd -S sqlhost -U appuser -d appdb -Q "SELECT SUSER_NAME(), DB_NAME();"

Then re-read the error log and confirm no new 18456 entries appear for that login.

Prevent It Coming Back

• Give every application its own named login with database-scoped roles, so a failure names the application and a leak is contained.
• Keep sa disabled and renamed where policy allows.
• Alert on repeated 18456 entries. A burst of State 5 or State 8 from one address is a credential-stuffing attempt, not a configuration problem — the [CLIENT: <ip>] field is what makes that visible.
• Store connection strings in a secret manager so a rotated password reaches every deployment target at once.
• Set an explicit default database on each login so a database going offline does not break logins that never used it.

Next Steps

• Fix "FATAL: Peer authentication failed" for the PostgreSQL equivalent
• Fix ERROR 1045 Access denied for the MySQL equivalent
• Where are IIS logs stored when the failing client is a web application