Msg 18456, Level 14, State 1, Login failed for user 'someuser'. is the most common security-related error in SQL Server, and it’s also the most useless on its own. Microsoft deliberately hides the real reason from the client for security (so an attacker can’t tell “wrong password” from “account doesn’t exist”), which means the error a user pastes into a ticket almost never tells you what actually happened. The real reason is a State number, and it only shows up in the SQL Server error log, not the client error message.
This is the diagnostic path: get the real state code from the error log, match it against what it actually means, then fix the specific cause rather than guessing.
Step 1: Get the Real State Code
The client-visible error is always State 1, regardless of cause, by design. The real state is in the SQL Server error log on the server itself:
EXEC xp_readerrorlog 0, 1, N'Login failed', NULL, NULL, NULL, N'desc';
Or use Get Error Log Patterns to see login failures grouped with everything else happening around the same time, useful when a failure correlates with something else (a deployment, a service restart, a password rotation).
The real log entry looks like Login failed for user 'someuser'. Reason: Password did not match... [CLIENT: 10.0.0.15], State 8 in this example. That reason text and state number are what actually tells you what to fix.
Step 2: Match the State Code
| State | Meaning | What to check |
|---|---|---|
| 5 | Login ID doesn’t exist | The account was never created, or was dropped. Confirm with SELECT * FROM sys.server_principals WHERE name = 'someuser' |
| 6 | Windows login name used with SQL auth attempt | The client is passing a domain-format username through a driver configured for SQL auth. Check the connection string’s authentication mode |
| 7 | Login valid but password mismatch, and the account is also disabled | Fix the disabled state first, then the password |
| 8 | Password mismatch | The classic wrong-password case. Also check for CAPS LOCK-style client issues and recently rotated service-account passwords not yet updated everywhere |
| 11 / 12 | Valid login, but denied server access (explicit DENY CONNECT SQL, or not covered by any login that grants access) |
SELECT * FROM sys.server_permissions WHERE grantee_principal_id = SUSER_ID('someuser') |
| 13 | SQL Server is paused | Check service state, not the login itself |
| 16 | Login valid, but access to the specific database or object failed at a lower level | Rare as a login-level state; usually a symptom of something else |
| 18 | Password expired and must be changed | Enforced by CHECK_EXPIRATION = ON on the login |
| 27 | Login not associated with a trusted SQL Server connection (Windows auth misconfiguration) | Kerberos/NTLM delegation issue, or the client isn’t actually part of the trusted domain |
| 38 | Database specified in the login’s default database no longer exists or is inaccessible | Fix with ALTER LOGIN [someuser] WITH DEFAULT_DATABASE = master (or a real accessible database) |
| 40 | Cannot open the default database, general | Same fix as State 38, check sys.server_principals.default_database_name first |
| 58 | Client used SQL auth against a server only accepting Windows auth (or a TLS/encryption mismatch) | Check SERVERPROPERTY('IsIntegratedSecurityOnly') and the server’s forced-encryption setting |
Step 3: Check the Account and Permissions Directly
Once you know roughly which category, confirm against the real login and permission state:
SELECT name, is_disabled, default_database_name, type_desc,
LOGINPROPERTY(name, 'IsExpired') AS is_expired,
LOGINPROPERTY(name, 'IsLocked') AS is_locked
FROM sys.server_principals
WHERE name = 'someuser';
Get Sysadmin Members also surfaces weak login settings sitewide (password policy off, expiration off), which is worth a periodic check independent of any specific incident, since accounts with those flags off are the ones state 7/8/18 problems accumulate around. Get Login Security Audit covers failed-login history and last-activity per login, useful for spotting a login that’s been failing repeatedly (a misconfigured service, a forgotten scheduled task) rather than a one-off.
Common Causes That Aren’t in the State Table
A few patterns show up often enough to call out directly, none of them State-code-diagnosable on their own:
- Case sensitivity on a case-sensitive collation instance. A password that’s correct except for case fails with the same State 8 as a genuinely wrong password.
- Connection pooling with a stale credential. An application’s connection pool can hold a cached, now-wrong password long after the actual password was rotated, producing intermittent failures that look random.
- SQL auth attempted against
IsIntegratedSecurityOnly = 1. No SQL login will ever work here, regardless of password; this is a server-level setting, not a login problem. - A login that exists but has no matching database user with a valid SID in the specific database being connected to; this looks like a login failure to some clients but is really an orphaned-user problem inside the database. Fix Orphaned Users is the script for exactly this, generating the remap DDL once you’ve confirmed it’s the cause.
Best Practices
- Always check the server-side error log state before troubleshooting from the client error alone; the client message is deliberately uninformative.
- Fix
default_database_nameissues (States 38/40) proactively when decommissioning a database, not reactively after the next login attempt fails. - Treat a sudden cluster of State 8 failures from one application as a credential-rotation problem first, a security incident second, most of the time it’s the boring answer.
- Audit for
CHECK_EXPIRATION/CHECK_POLICYdisabled logins periodically; they’re where State 7/8/18 problems concentrate over time.
Related Scripts
- Get Sysadmin Members, includes weak login settings audit
- Get Login Security Audit, failed-login history and last-activity per login
- Fix Orphaned Users, for the orphaned-user variant of this problem
- Get Error Log Patterns, to see login failures in context with everything else happening on the instance
Leave a Reply