CRITICAL

How to Fix SQL Server Msg 18456: Login Failed for User

Quick Fix Summary

TL;DR

Check SQL Server error log for state code, then verify login exists and authentication mode is correct.

SQL Server Msg 18456 indicates a failed login attempt due to authentication or authorization failure. The specific cause is detailed in the SQL Server error log with a state code.

Diagnosis & Causes

Incorrect username or password.

SQL Server is in Windows Authentication mode only.

Password has expired or does not meet policy.

Recovery Steps

Step 1: Check the SQL Server Error Log for State Code

The generic error message hides the real cause. The SQL Server error log contains a 'State' code that pinpoints the exact issue.

sql

-- Read the current error log for recent 18456 errors
EXEC xp_readerrorlog 0, 1, N'18456', N'login', NULL, NULL, N'desc';

Step 2: Verify Login Existence and Properties

Confirm the login is present on the server and check its status (enabled, locked).

sql

-- Check if login exists and its type
SELECT name, type_desc, is_disabled FROM sys.server_principals WHERE name = 'YourLoginName';
-- For a Windows user/group:
SELECT name, type_desc, is_disabled FROM sys.server_principals WHERE name = 'DOMAIN\UserName';

Step 3: Reset Password for SQL Login

If the password is incorrect or expired, reset it. Enforce a strong password.

sql

ALTER LOGIN [YourLoginName] WITH PASSWORD = 'NewStrongP@ssw0rd!' OLD_PASSWORD = 'OldPassword'; -- If known
-- If password is unknown or login is locked:
ALTER LOGIN [YourLoginName] WITH PASSWORD = 'NewStrongP@ssw0rd!' UNLOCK;

Step 4: Enable SQL Server Authentication Mode

If using a SQL Login but the server only accepts Windows Authentication, change the mode.

sql

-- 1. Using SQL Server Management Studio (SSMS): Right-click server -> Properties -> Security -> Select 'SQL Server and Windows Authentication mode'.
-- 2. Restart SQL Server service for change to take effect.
-- 3. Verify:
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode'; -- 2=Mixed, 1=Windows only

Step 5: Grant Server-Level Permission (CONNECT SQL)

The login must have CONNECT SQL permission to access the server instance.

sql

GRANT CONNECT SQL TO [YourLoginName];

Step 6: Resolve Windows Authentication Issues (State 5)

If State code is 5, Windows cannot validate the credentials. This is often a domain/trust issue.

bash

-- On the SQL Server host, verify:
1. The Windows user/group exists in Active Directory.
2. SQL Server service account has necessary permissions.
3. There is a trust relationship between the server and domain.
-- Temporary workaround (if appropriate): Create a SQL Login instead.

Architect's Pro Tip

"For State 8 (password mismatch), the error log shows the failed password hash. Compare it with `SELECT PWDCOMPARE('guess', password_hash) FROM sys.sql_logins` to confirm the exact wrong password used."

Frequently Asked Questions

What does the 'State' code in error log mean for Msg 18456?

The State code is key. Common ones: State 5 = Invalid Windows credential, State 8 = Wrong password, State 9 = Invalid password (policy), State 11/12 = Valid login but server access denied.

I fixed the login, but my application still gets error 18456. Why?

The application likely uses connection pooling with old credentials. Clear the pool: for .NET, use `SqlConnection.ClearAllPools()`. Restart the app pool or service.

How do I prevent this error in production?

Use Managed Service Accounts or Group Managed Service Accounts for Windows Auth. For SQL Logins, implement a robust secret rotation process and avoid hardcoding passwords in connection strings.

Related SQL Server Guides

Msg 1205