Error 18456 All States - Diagnosis & Resolution

Error 18456 originates in SQL Server's authentication subsystem during the login negotiation phase, before query execution begins. The error contains state codes that pinpoint the exact failure reason, though these states are only visible in the SQL Server error log, not to the client application for security purposes.

The authentication process involves multiple internal components. The SQL Server Network Interface (SNI) layer first establishes the connection, then passes credentials to the authentication subsystem. For SQL Server authentication, the system validates against sys.sql_logins, checking password hashes using SHA-512 (SQL Server 2012+) or SHA-1 (earlier versions). Windows authentication involves SSPI calls to the domain controller or local SAM database.

Common state codes indicate specific failure points: State 1 (invalid username, error details hidden from client), State 2 (valid username but wrong password), State 5 (valid login but no database access), State 6 (Windows login attempting SQL authentication), State 7 (login disabled and password incorrect / password policy violations), State 8 (mismatched password formatting), State 11 (valid login but no server access token — typically Windows group membership problems), State 18 (disabled account). Starting with SQL Server 2016, additional states were introduced for password policy enforcement and Azure AD authentication scenarios.

The authentication subsystem maintains internal counters for failed attempts per login, implementing account lockout policies when configured. Each failed attempt generates an entry in the security event log and increments perfmon counters. High volumes of authentication failures can create lock contention on internal authentication structures, though this rarely impacts overall performance unless dealing with thousands of concurrent failed attempts.

SQL Server 2019 introduced additional authentication telemetry and improved error reporting for hybrid scenarios. SQL Server 2022 enhanced Azure AD integration and added more granular state reporting for cloud authentication failures.

-- Check recent authentication failures from error log
EXEC xp_readerrorlog 0, 1, N'Login failed', N'18456'

Reveals recent 18456 errors with timestamps and state codes from the current error log.

-- Monitor authentication failure counters
SELECT 
    object_name,
    counter_name,
    cntr_value
FROM sys.dm_os_performance_counters 
WHERE object_name LIKE '%Access Methods%' 
   OR (object_name LIKE '%General Statistics%' AND counter_name LIKE '%Login%')

Shows perfmon counters for login attempts and failures to identify patterns.

-- Check for disabled or expired logins
SELECT 
    name,
    is_disabled,
    is_policy_checked,
    is_expiration_checked,
    create_date,
    modify_date,
    LOGINPROPERTY(name, 'IsLocked') AS is_locked,
    LOGINPROPERTY(name, 'IsExpired') AS is_expired
FROM sys.sql_logins
WHERE is_disabled = 1 
   OR LOGINPROPERTY(name, 'IsLocked') = 1
   OR LOGINPROPERTY(name, 'IsExpired') = 1

Identifies problematic SQL Server authentication accounts that may be causing failures.

-- The built-in system_health XE session does NOT capture login_failed events.
-- Create a dedicated XE session to capture authentication failures:
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = 'login_failures')
    DROP EVENT SESSION login_failures ON SERVER;

CREATE EVENT SESSION login_failures ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.username)
    WHERE ([error_number] = 18456))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY = 4096 KB, STARTUP_STATE = ON);

ALTER EVENT SESSION login_failures ON SERVER STATE = START;

-- Query the ring buffer for captured login failures
SELECT 
    event_data.value('(@timestamp)[1]', 'datetime2') AS event_time,
    event_data.value('(data[@name="error_number"]/value)[1]', 'int') AS error_number,
    event_data.value('(data[@name="state"]/value)[1]', 'int') AS state,
    event_data.value('(data[@name="message"]/value)[1]', 'varchar(max)') AS message,
    event_data.value('(action[@name="client_hostname"]/value)[1]', 'varchar(100)') AS client_hostname,
    event_data.value('(action[@name="username"]/value)[1]', 'varchar(100)') AS username
FROM (
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_sessions s
    JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address
    WHERE s.name = 'login_failures'
      AND t.target_name = 'ring_buffer'
) AS session_data
CROSS APPLY target_data.nodes('//RingBufferTarget/event') AS x(event_data)
ORDER BY event_time DESC;

Extracts detailed authentication failure information from a dedicated XE session.

Reset locked SQL Server login account

-- Unlock and reset SQL Server authentication account
-- Test in development environment first
DECLARE @LoginName NVARCHAR(128) = N'YourLoginName'

-- Check current status
SELECT 
    name,
    LOGINPROPERTY(name, 'IsLocked') AS is_locked,
    LOGINPROPERTY(name, 'IsExpired') AS is_expired
FROM sys.sql_logins 
WHERE name = @LoginName

-- Unlock the account (requires sysadmin)
ALTER LOGIN [YourLoginName] WITH PASSWORD = 'NewSecurePassword123!' UNLOCK
-- Expected impact: Immediately allows login attempts for this account

Unlocks SQL Server authentication accounts that have been locked due to failed login attempts. Test password complexity requirements before executing.

Grant server access to existing Windows login

-- Add Windows login with server access (State 11 resolution)
-- Replace with actual domain\username
DECLARE @WindowsLogin NVARCHAR(128) = N'DOMAIN\username'

-- Verify login doesn't already exist
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @WindowsLogin)
BEGIN
    CREATE LOGIN [DOMAIN\username] FROM WINDOWS
    PRINT 'Login created successfully'
END
ELSE
BEGIN
    PRINT 'Login already exists - check permissions instead'
END
-- Expected impact: Resolves State 11 errors for valid Windows accounts

Creates server login for Windows accounts that authenticate successfully but lack server access permission.

Fix database user mapping issues

-- Resolve orphaned database users (common cause of authentication issues)
USE [YourDatabase]
GO

-- Identify orphaned users
SELECT 
    dp.name AS PrincipalName,
    dp.type_desc AS PrincipalType,
    sp.name AS LinkedServerPrincipal
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U') 
  AND sp.name IS NULL 
  AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')

-- Fix specific orphaned user (example)
ALTER USER [YourDatabaseUser] WITH LOGIN = [YourServerLogin]
-- Expected impact: Resolves database access issues after login succeeds

Repairs orphaned database users that can cause post-authentication access failures. Run in specific databases experiencing issues.

Enable comprehensive authentication monitoring

-- Create extended events session for authentication monitoring
-- Monitor all authentication events for troubleshooting
CREATE EVENT SESSION [Authentication_Monitoring] ON SERVER 
ADD EVENT sqlserver.login_failed(
    ACTION(sqlserver.client_hostname, sqlserver.database_name, sqlserver.username)
),
ADD EVENT sqlserver.login_succeeded(
    ACTION(sqlserver.client_hostname, sqlserver.database_name, sqlserver.username)
)
ADD TARGET package0.event_file(SET filename=N'C:\temp\auth_monitoring.xel')
WITH (STARTUP_STATE=OFF)

-- Start the session
ALTER EVENT SESSION [Authentication_Monitoring] ON SERVER STATE = START
-- Expected impact: Provides detailed authentication logging for analysis

Establishes comprehensive authentication monitoring for troubleshooting persistent issues. Monitor disk space when running in production.

Configure SQL Server authentication policies appropriately for your environment. Enable account lockout policies on SQL Server logins when security requirements demand it, but monitor for legitimate applications that may trigger lockouts during connection pool recycling or failover scenarios.

Implement proper password rotation procedures for SQL Server authentication accounts, coordinating changes with application teams to prevent service disruptions. Use Windows authentication whenever possible as it provides better security and reduces password management overhead.

Monitor authentication failure patterns through SQL Server Agent alerts on Error 18456 or perfmon counter thresholds. Set alerts for unusual spikes in authentication failures that may indicate brute force attacks or application configuration issues.

Establish baseline authentication metrics during normal operations to identify anomalies. Track login success rates, failure patterns by time of day, and state code distributions to understand normal authentication behavior.

Configure connection pooling properly in applications to reduce authentication overhead and prevent unnecessary login attempts. Validate connection strings in non-production environments before deployment to catch authentication configuration errors early.

Implement network security measures including firewall rules and VPN requirements for SQL Server access to reduce exposure to external authentication attacks. Consider using SQL Server audit features to track authentication events for compliance and security analysis.

Review server and database permission structures regularly to ensure appropriate access levels and remove unused accounts that may become security liabilities or sources of authentication errors.