Fix SQL Error 18456 in 3 Steps

That Dreaded 18456: Your Login's Been Rejected

If you're staring at "Login failed for user 'sa'" or "Login failed for user 'YourDomain\YourName'" with error 18456, you're not alone. I've seen this at least a dozen times this year alone—last month a client's entire accounting team couldn't access their database because someone changed the SA password during a server migration and forgot to update the connection string.

The good news? In 80% of cases, this is a simple fix. Let's work through it from fastest to most thorough.

1. The 30-Second Fix: Check the Password

Yes, really. Nine times out of ten, someone fat-fingered the password. Try typing it manually (no copy-paste) in a new SSMS connection window. If you're using Windows Authentication, double-check you're not on a different domain or VPN issue.

Quick test: Open Command Prompt and run:

sqlcmd -S YourServerName -U sa -P YourPassword

If that connects, your app has a bad connection string. If it fails with the same error, move to Step 2.

2. The 5-Minute Fix: Reset the SA Password (or Enable Your Windows Account)

This works if you have another admin account on the server. If you don't, skip to the advanced fix.

Open SQL Server Management Studio (SSMS) with Windows Admin rights—right-click SSMS and choose "Run as administrator." Connect using Windows Authentication.

In Object Explorer, expand Security > Logins. Right-click 'sa' and choose Properties. Enter a new strong password in the Password box—something like P@ssw0rd!2024 works fine for testing. Uncheck "Enforce password policy" if you want to avoid complexity rules. Click OK.

If your Windows account itself is denied: Right-click the 'NT AUTHORITY\SYSTEM' login (or 'BUILTIN\Administrators') and check Properties > Status. Make sure "Permission to connect to database engine" is set to "Grant." Click OK. Then reconnect.

3. The Advanced Fix: Single-User Mode and Permission Recovery (15+ Minutes)

This is for when you're locked out completely—no admin account works, not even Windows Admin. This happens after someone accidentally removes all sysadmin roles or corrupts the login. I had a client last month whose junior DBA deleted the BUILTIN\Administrators login by accident. Took us 20 minutes to restore.

Step 3.1: Stop SQL Server in Single-User Mode

Open Command Prompt as Administrator. Stop the SQL Server service:

net stop MSSQLSERVER

Then restart it in single-user mode (only the local admin can connect):

net start MSSQLSERVER /m

Step 3.2: Connect and Fix Permissions

Now open SSMS—again, Run as Administrator. In the Connect to Server dialog, type localhost as the server name. Select Windows Authentication. Click Connect.

Run this T-SQL to add your Windows account as a sysadmin:

CREATE LOGIN [YOURDOMAIN\YourUserName] FROM WINDOWS;
ALTER SERVER ROLE sysadmin ADD MEMBER [YOURDOMAIN\YourUserName];
GO

Replace YOURDOMAIN\YourUserName with your actual domain and username (or use NT AUTHORITY\SYSTEM).

Step 3.3: Restart SQL Server Normally

Back in Command Prompt:

net stop MSSQLSERVER
net start MSSQLSERVER

Now connect with SSMS using Windows Authentication. You're in.

One More Thing: Check the Error Log

Error 18456 has sub-states (the number after the error). Run this in SSMS to see the exact reason:

SELECT * FROM sys.dm_exec_sessions WHERE login_name = 'sa';

Or check the SQL Server log in SSMS: Management > SQL Server Logs. Look for "Error: 18456, State: 8" (state 8 means password mismatch, state 11 means valid login but server access denied, state 12 means valid login but database access denied). Knowing the state saves you time.

Bottom Line

Error 18456 is almost always a password typo, a disabled account, or a permissions screw-up. Start with the password test—seriously, it's the most common cause. If that fails, reset the SA password or add your Windows account. Only go to single-user mode if you're totally locked out. You'll be back in under 10 minutes in most cases.