SQL Server Authentication

SQL Server Error 18456: Login Failed (Fix Guide)

Fix SQL Server Error 18456 login failed. Covers authentication mode, Windows vs SQL login, password policy, and disabled accounts.

Mar 12, 2026 5 min read

The Error Message

Login failed for user 'username'

What Causes SQL Server 18456?

SQL Server Error 18456 occurs when login authentication fails. The error state number (visible in the SQL Server error log) reveals the specific cause.

Common Causes

Wrong password

The password does not match the SQL Server login.

SQL Auth not enabled

SQL Server is in Windows Authentication only mode, but you're trying SQL login.

Login disabled

The SQL Server login exists but has been disabled.

Database does not exist or inaccessible

The login's default database was dropped or is offline.

How to Fix It

Step 1: Enable mixed authentication mode

If SQL Server is in Windows-only mode, SQL logins will always fail. Enable mixed mode.

-- In SSMS:
-- Right-click Server > Properties > Security
-- Select: SQL Server and Windows Authentication mode
-- Restart SQL Server service

-- Or via T-SQL:
EXEC xp_instance_regwrite
  N'HKEY_LOCAL_MACHINE',
  N'Software\Microsoft\MSSQLServer\MSSQLServer',
  N'LoginMode', REG_DWORD, 2;

Step 2: Reset the password

Reset the password and make sure the login is enabled.

-- Connect as sysadmin:
ALTER LOGIN your_username WITH PASSWORD = 'NewPassword123!';

-- Enable if disabled:
ALTER LOGIN your_username ENABLE;

Step 3: Check the error log for state number

The error state in the log reveals the exact cause. The client error message intentionally hides this for security.

-- Read error log:
EXEC xp_readerrorlog 0, 1, N'18456';

-- Common states:
-- State 2/5: Invalid user ID
-- State 7: Login disabled
-- State 8: Wrong password
-- State 11/12: Login valid but server access failure
-- State 40: Default database unavailable

Step 4: Fix default database

If the login's default database is unavailable, change it to master and reconnect.

-- If default database was dropped:
ALTER LOGIN your_username WITH DEFAULT_DATABASE = master;

-- Then connect and switch:
USE your_actual_database;

How to Prevent This Error

Use Windows Authentication when possible (more secure). Store SQL passwords in connection string secrets. Set default database to a database that won't be dropped.

Fix SQL Server Errors with AI2SQL

Instead of debugging SQL syntax manually, describe what you need in plain English and let AI2SQL generate the correct query for SQL Server.

Try AI2SQL Free

No credit card required

Frequently Asked Questions

What does SQL Server Error 18456 mean?

Error 18456 means login authentication failed. The exact cause can be wrong password, disabled login, SQL auth not enabled, or default database issues. Check the SQL Server error log for the state number.

How do I find the real cause of Error 18456?

The detailed state number is in the SQL Server error log, not the client error message. Run EXEC xp_readerrorlog 0, 1, N'18456' to see it.

Windows Auth vs SQL Auth — which should I use?

Windows Authentication is more secure (no passwords in connection strings) and easier to manage. Use SQL Authentication only when Windows Auth is not possible (e.g., cross-platform apps, Docker).