SQL Server Login Failed for User – Quick Fix & Causes

You're staring at Error 18456, and it's annoying. Let's fix it.

I know that feeling—you type your credentials, hit connect, and SQL Server slams the door. The error number is 18456, and the message says Login failed for user. In 90% of cases, the fix is one of three things. I'll walk you through them, and you'll probably be back in business in under two minutes.

Step 1: Check SQL Server's Authentication Mode

If you're using SQL Server Authentication (a login name and password, not Windows), SQL Server must be configured to allow it. Here's the quick check:

1. Open SQL Server Management Studio (SSMS). Connect using Windows Authentication (you probably have admin rights).
2. Right-click the server in Object Explorer and go to Properties.
3. Click the Security page. Look at Server authentication.
4. If Windows Authentication mode is selected, switch to SQL Server and Windows Authentication mode.
5. Click OK, then restart the SQL Server service.

After the restart, try connecting again. This is the single most common reason for error 18456—especially on fresh installs or when someone changed the setting. I've seen it trip up DBAs who forgot to flip that switch after a migration.

Step 2: Verify the Login Exists and Is Enabled

Even if SQL Auth is on, the login might not exist, or it could be disabled. Check here:

-- Run this in a query window connected with Windows Auth
SELECT name, is_disabled FROM sys.sql_logins WHERE name = 'your_login_name';

If the login shows is_disabled = 1, you need to enable it:

ALTER LOGIN your_login_name ENABLE;

Also, check if the password expired. Recent versions of SQL Server (2016 and later) default to Enforce password policy on new logins. If the password is too old, the login fails immediately. Reset it:

ALTER LOGIN your_login_name WITH PASSWORD = 'new_strong_password' UNLOCK;

Step 3: The Hidden Culprit—User Mapping

Error 18456 can also say Cannot open database requested by the login. That happens when the login doesn't have access to the default database. Maybe the database was deleted or renamed.

1. In SSMS Object Explorer, expand Security > Logins.
2. Right-click your login and choose Properties.
3. On the General page, change the Default database to master (or a database you know the login can access).
4. Click OK and test the connection.

If that works, you need to fix the user mapping in the actual database. Drop and recreate the user, or use ALTER USER to point it to the right login.

Why Error 18456 Happens

SQL Server logs the failed login attempt with a state number. The state tells you why it failed. Here are the most common ones:

You can see the state in SQL Server's error log. Open SSMS, go to Management > SQL Server Logs, double-click the current log, and search for 18456. The state number is right there. This saved me hours once—I could tell the exact issue without guessing.

Less Common Variations

Sometimes the fix is weirder. Here are two edge cases I've hit:

Case 1: The 'sa' Account Is Disabled by Default

On a fresh SQL Server 2019 or 2022 install, the sa login is disabled with a random password. If you try to use it, you get 18456 state 8. Enable it with:

ALTER LOGIN sa ENABLE;
ALTER LOGIN sa WITH PASSWORD = 'your_strong_password' UNLOCK;

I'd also recommend renaming it or using a different admin login—sa is a common brute-force target.

Case 2: Windows Auth User's Group Membership Changed

If you're connecting with Windows Authentication and get 18456, the issue might be that the user's group membership (like an AD group) changed, but the SQL Server cached info is stale. Run this to force a refresh:

-- This clears the credential cache
DBCC FREESYSTEMCACHE('TokenAndPermUserStore');
-- Then check the login again
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly');

This is rare, but I've seen it after AD group restructures. It's a five-second fix that beats restarting the server.

How to Prevent Error 18456

Here's what I do to keep this error from haunting me:

• Set clear password policies. Use CHECK_POLICY = OFF only on dev boxes. Production should enforce policies, but set expiry to 90 days and alert before it hits.
• Use a monitoring script. I run a daily query that checks for disabled or expired logins. Simple but saves panic calls.
• Document your default database. If you change a database name or delete one, update the logins' default database to master first. Otherwise, next connect attempt fails.
• Test connection strings after any security change. A minor group policy update can break hundreds of applications. Validate with a quick PowerShell script.

If you followed the steps above and still see error 18456, check the SQL Server error log for the state. It'll point you straight at the real problem. And if you're stuck, drop a comment—I read every one.