SQL Server Error 18456: Fix Login Failed Fast

Cause #1: SQL Server Is in Windows Authentication Mode Only (Most Common)

You try to connect with a SQL login like 'sa' or a custom user, and boom — error 18456. Nine times out of ten, SQL Server is set to Windows Authentication mode only. That means it won't accept any SQL login, even if the username and password are correct.

I had a client last month who spent three hours rebuilding a web app because they thought the connection string was wrong. Nope. SQL Server just wasn't listening for SQL auth.

How to Check and Fix

1. Open SQL Server Management Studio (SSMS).
2. Right-click the server in Object Explorer and pick Properties.
3. Go to the Security page.
4. Under Server authentication, select SQL Server and Windows Authentication mode.
5. Click OK.
6. Restart the SQL Server service. Don't skip this — the change won't take effect until you do.
7. Now try connecting again with your SQL login.

If you can't get into SSMS at all (like if you locked out the SA account), use sqlcmd from a command prompt with Windows auth:

sqlcmd -S YourServerName -E
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'login mode', 2;
RECONFIGURE;
GO
EXIT

Then restart the service via net stop MSSQLSERVER and net start MSSQLSERVER (or use Services.msc).

Cause #2: The SA Account Is Disabled or Password Expired

Second most common: you're trying to log in as 'sa', but the account is disabled by default in modern SQL Server versions. Or the password expired. Or someone changed it and didn't tell you.

I've seen this happen after a security audit where the DBA disabled SA because it was a security risk, then forgot to tell the dev team. Suddenly no one could log in.

How to Check and Fix

You need Windows admin access to the server and a Windows-authenticated connection. If you don't have that, you're stuck — call your sysadmin.

1. Connect to SQL Server using Windows Authentication in SSMS or sqlcmd.
2. Open a new query and run:

SELECT name, is_disabled, LOGINPROPERTY(name, 'IsLocked') AS is_locked
FROM sys.sql_logins
WHERE name = 'sa';

If is_disabled is 1, enable it:

ALTER LOGIN [sa] ENABLE;
GO

If it's locked, unlock it:

ALTER LOGIN [sa] WITH PASSWORD = 'YourNewStrongPassword' UNLOCK;
GO

Always set a strong password. 'sa' is a common brute-force target. Use something like S@f3L0g!n2024 — not 'password123'.

Cause #3: User Does Not Have Permission to Connect to the Database

This one's sneaky. The login succeeds at the server level but fails when it tries to hit a specific database. The error message might still say 18456, but the state number in the SQL Server error log tells a different story.

Check the SQL Server error log (in SSMS, under Management > SQL Server Logs). Look for the 18456 entry with a state number. State 8 means permission denied for a specific database. State 1 means generic auth failure.

How to Fix

1. Connect with a Windows-authenticated account that has sysadmin rights.
2. Run this query to see which databases the login can access:

SELECT dp.name AS login_name,
       dp.type_desc,
       dp.is_disabled,
       m.name AS database_name,
       m.state_desc AS database_access
FROM sys.server_principals dp
LEFT JOIN sys.databases m ON dp.sid = m.owner_sid
WHERE dp.name = 'YourLoginName';

If the login doesn't appear in the database's list of users, add it:

USE YourDatabaseName;
CREATE USER YourLoginName FOR LOGIN YourLoginName;
EXEC sp_addrolemember 'db_datareader', 'YourLoginName';
EXEC sp_addrolemember 'db_datawriter', 'YourLoginName';
GO

Don't just give them db_owner unless they really need it. That's a common over-permission mistake I see all the time.

Quick-Reference Summary Table

If none of these fix it, check the SQL Server error log for the exact state number, then Google that state. State 9 means database is in single-user mode. State 12 means login is valid but credential mismatch. But honestly, these three causes cover 95% of the 18456 errors I've seen in ten years of fixing SQL Server for small businesses.