After months of smooth operation, my SQL Server 2005 Express instance suddenly rejected the 'sa' account login with the classic error:

osql -S .\\INSTANCE -U sa -P password
Login failed for user 'sa'

What's particularly puzzling is that Windows authentication (-E switch) works perfectly, indicating the instance itself is running. Let me walk through my complete diagnostic and repair process.

First, I confirmed these basic configurations:

• Mixed authentication mode is enabled (Registry shows LoginMode=2)
• The 'sa' account is enabled (verified via T-SQL):

SELECT name, is_disabled FROM sys.sql_logins WHERE name = 'sa'

Interestingly, password policies became a key factor in our Windows 2003 environment where complex passwords are mandated.

The system's password expiration might have locked the account. Here's how I checked and reset:

-- Check if password expired
SELECT name, is_expired FROM sys.sql_logins WHERE name = 'sa'

-- Comprehensive reset command
ALTER LOGIN sa WITH 
    PASSWORD = 'N3wC0mpl3xP@ssw0rd',
    CHECK_POLICY = ON,
    CHECK_EXPIRATION = ON,
    UNLOCK
GO

Important note: When password policy is enabled, simple passwords like "password" will be rejected immediately.

In some cases, the protocol configuration might prevent authentication:

-- Verify enabled protocols
EXEC xp_readerrorlog 0, 1, N'Server is listening on'

If only named pipes is enabled but you're trying TCP/IP connection, this could cause failures. Ensure both are enabled in SQL Server Configuration Manager.

When basic checks don't reveal the issue, SQL Server's extended events can help:

-- Create login failure trace
CREATE EVENT SESSION [Login_Failures] ON SERVER 
ADD EVENT sqlserver.error_reported(
    WHERE ([error_number] = 18456))
ADD TARGET package0.event_file(SET filename=N'Login_Failures')
GO

ALTER EVENT SESSION [Login_Failures] ON SERVER STATE = START
GO

This will capture detailed error information that's normally hidden in the generic 18456 error message.

In my case, these steps resolved the issue:

1. Connected via Windows auth (sqlcmd -E)
2. Reset password with complexity requirements
3. Verified TCP/IP protocol was enabled
4. Restarted SQL Server service

The complete fix command sequence:

-- Connect via Windows auth first
sqlcmd -E -S .\\INSTANCE

-- Inside SQLCMD:
ALTER LOGIN sa WITH PASSWORD = 'S7r0ngP@ssw0rd2023' UNLOCK
GO
EXEC sp_configure 'remote access', 1
RECONFIGURE
GO
EXEC xp_instance_regwrite 
    N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'LoginMode', REG_DWORD, 2
GO

---

When attempting to connect to SQL Server 2005 Express using the sa account with the command:

osql -S .\\INSTANCE -U sa -P password

You receive the frustrating error message: Login failed for user 'sa'. This occurs despite previously working authentication and successful Windows Authentication connections (-E switch).

From your troubleshooting attempts, we know:

• Mixed authentication is enabled (Registry shows LoginMode=2)
• The sa account is enabled via T-SQL
• Password changes were attempted through both sp_password and ALTER LOGIN
• Other SQL logins work, just not sa

Let's explore deeper diagnostic approaches:

1. Check Account Lockout Status

Run this query while connected via Windows Authentication:

SELECT name, is_disabled, lockout_time 
FROM sys.sql_logins 
WHERE name = 'sa'

If lockout_time shows a date, the account may be locked due to password policy violations.

2. Verify Password Policy Enforcement

Windows Server 2003's password policies can affect SQL logins. Check if enforcement is enabled:

SELECT name, is_policy_checked, is_expiration_checked
FROM sys.sql_logins
WHERE name = 'sa'

If both columns return 1, your new password must comply with domain policies.

3. Examine SQL Server Error Logs

Locate the SQL Server error log (typically in Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG) and search for:

findstr /i "sa" ERRORLOG*

Look for messages like "password validation failed" or "login blocked".

Reset with Policy-Compliant Password

Try this sequence to completely reset the sa account:

-- First disable policy checks temporarily
ALTER LOGIN sa WITH CHECK_POLICY = OFF;
GO

-- Set a complex password that would meet requirements
ALTER LOGIN sa WITH PASSWORD = 'P@ssw0rd!2023';
GO

-- Re-enable policy checks
ALTER LOGIN sa WITH CHECK_POLICY = ON;
GO

-- Verify the changes
SELECT name, is_policy_checked, is_expiration_checked
FROM sys.sql_logins
WHERE name = 'sa'

Alternative Connection Methods

If VNC prevents SSMS access, try these command-line alternatives:

# PowerShell alternative
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server=.\INSTANCE;User ID=sa;Password=P@ssw0rd!2023;"
$sqlConnection.Open()

After making changes, test connectivity with:

sqlcmd -S .\INSTANCE -U sa -P "P@ssw0rd!2023" -Q "SELECT @@VERSION"

If this succeeds, you've resolved the authentication issue.