SQL Error 18456: Login Failed, and the State Number That Tells You Why

Error 18456 means SQL Server rejected a login, and the message you see is deliberately vague. The server error log records the same event with a state number, and that number names the exact cause.

The vagueness is intentional. Telling an unauthenticated caller whether the account exists but the password was wrong is an enumeration vector, so the client always gets the same generic text regardless of what actually failed.

The server log has no such constraint. Reading it is the difference between guessing at five possible causes and knowing which one you have, and it is the step most people skip.

Read the state number first
The authentication mode mismatch
Login exists, user does not
When it worked yesterday
Making authentication failures less frequent
How this fits the rest of the stack
FAQ

Read the state number first

Every 18456 in the SQL Server error log carries a severity and a state, and the state is the diagnostic.

-- Read the error log without leaving your query window.
EXEC xp_readerrorlog 0, 1, N'18456';

You are looking for a line of the form Logon Error: 18456, Severity: 14, State: 8. The states you will actually encounter:

State 2 and 5: the login does not exist. Usually a typo, or a connection string still pointing at a different environment.
State 6: a Windows login was supplied for SQL authentication. Mismatched authentication method.
State 7: the login exists but is disabled, and the password was also wrong.
State 8: the password did not match. The login exists.
State 11 and 12: valid login, but access was denied by a Windows-level permission or a group policy.
State 18: the password is correct but expired and must be changed.
State 38: the login succeeded but the database named in the connection string is unavailable to it.

State 8 and state 5 are the two most common, and they point in completely different directions: one is a password problem, the other is a wrong-server or wrong-name problem. Knowing which halves your investigation immediately.

The authentication mode mismatch

If the log shows a message about SQL authentication failing because the server is configured for Windows authentication only, the instance is simply not accepting the kind of credential you are sending.

SQL Server runs in one of two modes. Windows authentication only accepts domain and local Windows accounts. Mixed mode accepts those plus SQL Server logins with their own passwords. A fresh installation frequently defaults to Windows only.

-- 1 means Windows only, 0 means mixed mode.
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS windows_auth_only;

Changing it is a server property change plus a service restart, which is not something to do casually on a shared instance. The alternative is to use Windows authentication from the application instead, by adding integrated security to the connection string.

; SQL authentication
Server=host;Database=app;User Id=appuser;Password=...;Encrypt=True;

; Windows authentication
Server=host;Database=app;Integrated Security=SSPI;Encrypt=True;

Choose deliberately rather than by trial. Windows authentication is generally preferable inside a domain because credentials are not stored in a connection string at all; SQL authentication is what you need when the application runs outside the domain.

This trips up almost everyone new to SQL Server, because there are two separate objects and they are easy to confuse.

A login is server-level: it grants the ability to connect to the instance. A user is database-level: it grants the ability to use a particular database. A login with no corresponding user in the target database can connect to the server and then fail on the database, which shows as state 38.

-- Does the server-level login exist?
SELECT name, type_desc, is_disabled FROM sys.server_principals WHERE name = 'appuser';

-- Does the database-level user exist and map to it?
USE app;
SELECT dp.name, dp.type_desc, sp.name AS login_name
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.name = 'appuser';

If the login exists and the user does not, create and grant it:

USE app;
CREATE USER appuser FOR LOGIN appuser;
ALTER ROLE db_datareader ADD MEMBER appuser;
ALTER ROLE db_datawriter ADD MEMBER appuser;

The related classic is the orphaned user, which appears after restoring a database onto a different server. The user exists in the restored database with a security identifier that matches no login on the new instance. The fix is to remap it:

USE app;
ALTER USER appuser WITH LOGIN = appuser;

When it worked yesterday

A login that has started failing without a code change is one of a short list, and the state number narrows it fast.

Password expiry. State 18. SQL logins can inherit the Windows password policy including expiry, which surprises people who assumed a service account was exempt.
Account locked out. Repeated failures triggered a lockout policy, often caused by an old instance of the application retrying with a stale password.
Login disabled. Someone disabled it during a cleanup, or an automated process did.
A restore replaced the users. Restoring a database over another brings the source server’s users with it, orphaning them.
The default database is offline. If the login’s default database is unavailable and the connection string does not name one explicitly, the connection fails even though the credential is fine.
A credential rotation reached some services and not others.

-- Expiry policy, lockout state and default database in one look.
SELECT name, is_disabled, is_expiration_checked, is_policy_checked,
       LOGINPROPERTY(name, 'IsLocked')  AS is_locked,
       LOGINPROPERTY(name, 'IsExpired') AS is_expired,
       default_database_name
FROM sys.sql_logins
WHERE name = 'appuser';

That default database column is worth noting. Naming the database explicitly in the connection string makes your application immune to the login’s default becoming unavailable, and it costs nothing.

Making authentication failures less frequent

Most recurring 18456 problems are credential lifecycle problems rather than SQL Server problems.

One credential per application, so rotating one does not break the others and a lockout is contained.
Store it outside the code. An environment variable or a secret store, never a connection string in a repository, so rotation is a configuration change rather than a deploy.
Turn off password expiry for service accounts deliberately, or automate the rotation. A service account expiring at 2am is a self-inflicted outage.
Name the database explicitly in every connection string.
Grant the least privilege that works. An application that only reads should not hold write permissions, and an application should never connect as a server administrator.
Monitor failed logins. A sudden rise is either a broken deployment or an attack, and both are worth knowing about before a lockout.

The broader observation: this class of problem scales with how many places a credential lives. Where connection details are set per service as environment variables and rotated in one place, most of the list above stops being a procedure and becomes a property of the setup. The database user management docs cover the equivalent on RunxBuild’s managed MySQL and Postgres.

How this fits the rest of the stack

Database access problems are rarely about the database engine and usually about how many separate places hold a credential and how hard it is to change them all at once. Reducing that count is worth real money in avoided incidents, and the RunxBuild hosting calculator prices the managed-database shape where connection limits, backups and user management come with the plan rather than as separate work.

Useful related references:

FAQ

What does SQL Server error 18456 mean?

The login attempt was rejected. The client message is deliberately generic to avoid revealing whether the account exists, but the server error log records the same event with a state number that identifies the exact cause, from a wrong password to a disabled login to an unavailable default database.

How do I find the state number for an 18456 error?

Read the SQL Server error log, either through Management Studio or by running EXEC xp_readerrorlog 0, 1, N’18456’. Look for a line reading Logon Error: 18456, Severity: 14, State: n. The state is the diagnostic; the client message is not.

What does State 8 mean?

The password did not match. The login exists and is valid, so this is a credential problem rather than a missing account. State 5 by contrast means the login does not exist at all, which usually points at a typo or a connection string aimed at the wrong environment.

Why can I connect to the server but not the database?

Because a login and a user are separate objects. A login grants access to the instance; a user grants access to a specific database. A login with no corresponding user in the target database connects to the server and then fails, typically with state 38.

What is an orphaned user?

A database user whose security identifier does not match any login on the current instance, which typically happens after restoring a database onto a different server. The user exists inside the database but has nothing at server level to authenticate against. Remap it with ALTER USER … WITH LOGIN.