Fix SQL Server Error 18456: Login Failed for User

Symptoms

When attempting to connect to SQL Server, you receive the following error message:

Login failed for user 'username'. (Microsoft SQL Server, Error: 18456)

The error may appear in SQL Server Management Studio (SSMS), SQLCMD, or any client application. The connection may fail even with correct credentials. The error often includes a state number that helps narrow down the cause.

Root Causes

Error 18456 can be caused by several issues, including:

Invalid login credentials: Wrong username or password.
Disabled login account: The SQL Server login is disabled.
Authentication mode mismatch: Server is set to Windows Authentication only, but SQL Authentication is attempted.
Password expired or must change: The login requires a password change.
Login not mapped to a database user: The login exists but has no access to the target database.
Server role permissions missing: The login lacks the 'CONNECT SQL' permission.
Corrupted login or orphaned user: Especially after restoring a database from another server.

Step-by-Step Fix

Step 1: Check the Error State

The error message includes a state number (e.g., State 1, State 2, etc.). Common states and their meanings:

State 1: General login error (check credentials).
State 2: User ID not found.
State 5: Invalid login (wrong password).
State 6: Windows login attempt with SQL Authentication.
State 7: Login disabled.
State 8: Password expired.
State 9: Password must change.
State 12: Login valid but server access denied.

Step 2: Verify Authentication Mode

Open SQL Server Management Studio, right-click the server in Object Explorer, select Properties, then go to the Security page. Ensure SQL Server and Windows Authentication mode is selected if you're using SQL logins. If it's set to Windows Authentication only, change it and restart the SQL Server service.

Step 3: Enable the Login

If the login is disabled, enable it using SSMS or T-SQL:

ALTER LOGIN [username] ENABLE;

In SSMS, expand Security > Logins, right-click the login, and choose Properties. Uncheck Login is disabled and click OK.

Step 4: Reset Password

If the password is expired or needs to be changed, reset it:

ALTER LOGIN [username] WITH PASSWORD = 'newpassword' UNLOCK;

For the sa account, use:

ALTER LOGIN sa WITH PASSWORD = 'newpassword' UNLOCK;

Step 5: Grant Server Permissions

Ensure the login has the 'CONNECT SQL' permission. If not, grant it:

GRANT CONNECT SQL TO [username];

You can also add the login to the public server role (default).

Step 6: Map Login to Database User

If the login exists but cannot access a specific database, map it:

USE [database_name];
CREATE USER [username] FOR LOGIN [username];
EXEC sp_addrolemember 'db_datareader', 'username';

Step 7: Fix Orphaned Users

After restoring a database, the database user may be orphaned. Fix it:

USE [database_name];
EXEC sp_change_users_login 'Auto_Fix', 'username';

Or manually:

ALTER USER [username] WITH LOGIN = [username];

Alternative Fixes

Use Windows Authentication: If SQL Authentication fails, try connecting with Windows Authentication using a domain account that has SQL Server access.
Check SQL Server error logs: Run EXEC xp_readerrorlog 0, 1, N'Login failed'; to see detailed state and reason.
Restart SQL Server service: After changing authentication mode or enabling logins, restart the service.
Verify firewall and network: Ensure the SQL Server port (default 1433) is open and accessible from the client.

Prevention

Use strong passwords and enforce password policy.
Regularly audit login accounts and disable unused ones.
Always use mixed mode authentication if SQL logins are needed.
Backup logins and permissions using scripts or tools.
Document all login mappings for databases.
Monitor SQL Server logs for failed login attempts.

By following these steps, you can resolve SQL Server error 18456 and prevent future occurrences. Always test changes in a non-production environment first.