# ERROR 18456: Login Failed for User in SQL Server

Source: https://www.bytebase.com/reference/sqlserver/error/18456-login-failed-for-user/

---

## Error Message

The client sees a short message with no reason attached:

```sql
Login failed for user 'app_user'. (Microsoft SQL Server, Error: 18456)
```

A Windows login shows the domain, which is the quickest signal that the failure is a Windows account rather than a SQL login:

```sql
Login failed for user 'CONTOSO\jdoe'.
```

## Find the State Number First

SQL Server hides the cause from the client on purpose, so every 18456 looks identical no matter what went wrong. The real reason is a **State** number written only to the SQL Server error log. Read it before you change anything:

```sql
EXEC xp_readerrorlog 0, 1, N'Login failed', N'18456';
```

Look for a line like `Error: 18456, Severity: 14, State: 8.`. That number maps to a specific cause (confirmed against the Microsoft Learn reference, last revised November 2025, and unchanged from SQL Server 2019 through SQL Server 2022):

| State | Meaning |
|-------|---------|
| 2, 5 | Login name doesn't exist |
| 6 | A Windows login name was used with SQL authentication |
| 7 | Login is disabled **and** the password is wrong |
| 8, 9 | Password is wrong |
| 11, 12 | Login is valid, but server access failed |
| 18 | Password expired and must be changed |
| 38, 46 | Login is valid, but the requested database isn't available |
| 58 | Server allows Windows authentication only, client used SQL auth |
State 1 means your own account can't read the error detail. Connect as an administrator and check the log again to see the true state.

## Fix by State

### State 2 or 5: the login doesn't exist

The name you connected with isn't a login on this instance. The usual cause is a connection string carried from dev or QA into production without updating the server or account. Confirm, then create or correct it:

```sql
SELECT name FROM sys.server_principals WHERE name = 'app_user';

CREATE LOGIN app_user WITH PASSWORD = 'a-strong-password';
```

If the login exists but is misspelled in the application, fix the connection string instead of creating a duplicate.

### State 7, 8, 9, or 18: password problems

State 8 and 9 mean the password is wrong. State 7 means the login is also disabled. State 18 means the password expired. Reset or re-enable as needed:

```sql
ALTER LOGIN app_user WITH PASSWORD = 'a-strong-password';  -- wrong password
ALTER LOGIN app_user ENABLE;                               -- disabled (state 7)
```

One gotcha catches people right after switching a server to mixed mode: the `sa` login starts disabled, which surfaces as State 7 for `sa`. Enable it with `ALTER LOGIN sa ENABLE` and set a password.

### State 58: the server is in Windows-only authentication mode

This is the most common wall on a fresh install. The instance accepts Windows logins only, but the client (or its driver) tried SQL authentication. Switch the instance to mixed mode: in SSMS 20 or 21, right-click the instance, open **Properties → Security**, select **SQL Server and Windows Authentication mode**, then restart the SQL Server service for the change to take effect.

For the narrower case where the server is already in mixed mode but an ODBC connection still hits this, add `TRUSTED_CONNECTION = TRUE` to the connection string so the driver stops trying to impersonate.

### State 38 or 46: the login is fine, the database isn't

Authentication succeeded. The login's default database (or the database named in the connection string) is offline, was renamed or dropped, or the login has no access to it. The error log spells it out: `Failed to open the explicitly specified database 'app_db'`. Point the connection at a database that's online, or grant access:

```sql
ALTER LOGIN app_user WITH DEFAULT_DATABASE = master;

USE app_db;
CREATE USER app_user FOR LOGIN app_user;
```

### State 11 or 12: valid login, server access failed

The login is real but the server rejected the session. The frequent cause is a Windows account that has access through a group while running without elevation. Start the client with **Run as administrator** so the full token reaches SQL Server. If that doesn't resolve it, look for a group `DENY` on the login or a Service Principal Name (SPN) problem in the error log.

## Prevention

- Read the State before touching anything. Resetting a password does nothing for a State 38 database problem or a State 58 auth-mode problem.
- Give every application its own login scoped to a single database. Don't hand out `sa`. See [database access control best practices](/blog/database-access-control-best-practices/).
- Keep dev, QA, and production connection strings separate so a State 2 from a stale string never reaches production. Treating SQL Server changes as a reviewed pipeline (see the [SQL Server schema migration guide](/blog/sql-server-schema-migration-guide/)) keeps credentials and target servers explicit.
- On Azure SQL Database you can't change the server authentication mode, and a login's default-database behavior differs, so State 38 there usually means a contained-user or firewall issue rather than an offline database.

> **Note:** Handing out `sa` or sharing one SQL login across a team is how credentials leak and logins go untracked. Bytebase puts a review-and-access layer in front of SQL Server, so engineers connect through their own identity with scoped permissions instead of a shared account. See [Bytebase database permissions](https://www.bytebase.com/docs/security/database-permission/overview/).