SQL Server Login Issue

 

Step-by-Step Solution

Step 1: Verify Current Authentication Mode

Run this query in SSMS (logged in as a Windows admin user like RAJ\User):

EXEC xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'LoginMode';

LoginMode	Meaning
1	Windows Authentication only
2	SQL Server and Windows Authentication (Mixed Mode)

✅ If the result is 1, SQL Authentication (like sa) won’t work.

---

Step 2: Enable Mixed Mode Authentication

1. In SQL Server Management Studio (SSMS):

   • Right-click your server name (e.g. RAJ\SQLEXPRESS03) → Properties

   • Select Security tab

   • Choose SQL Server and Windows Authentication mode

   • Click OK

2. Restart the SQL Server service for changes to take effect.

---

Step 3: Restart SQL Server Service

Option 1 — via Configuration Manager

• Open SQL Server Configuration Manager

• Select SQL Server Services

• Right-click SQL Server (SQLEXPRESS03) → Restart

Option 2 — via PowerShell or Command Prompt (as Administrator)

net stop "MSSQL$SQLEXPRESS03"
net start "MSSQL$SQLEXPRESS03"

---

Step 4: Enable and Reset sa Login

Back in SSMS, execute:

ALTER LOGIN sa WITH PASSWORD = 'Strong@123!' UNLOCK;
ALTER LOGIN sa ENABLE;

Then verify its state:

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

✅ Result should show is_disabled = 0 and IsLocked = 0.

---

Step 5: Verify Default Database

Sometimes login fails if the default database is offline.

SELECT name, default_database_name
FROM sys.server_principals
WHERE name = 'sa';

If it’s not master, fix it:

ALTER LOGIN sa WITH DEFAULT_DATABASE = master;

---

Step 6: Test Connection

Open SSMS → click Connect → Database Engine

Field	Value
Server name	RAJ\SQLEXPRESS03
Authentication	SQL Server Authentication
Login	sa
Password	Strong@123!
Encryption	Optional
✅ Check	Trust server certificate

Click Connect — should work successfully 🎉

---

Step 7: Confirm Audit Level and Login Success

Check if login auditing is set (useful for diagnosing future errors):

EXEC xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'AuditLevel';

Value	Meaning
0	None
1	Successful logins
2	Failed logins
3	Both success and failed logins

Recommended: 2 or 3.

---

✅ Verification Checklist

Checkpoint	Command	Expected
Authentication Mode	xp_instance_regread 'LoginMode'	2
sa Enabled	SELECT is_disabled FROM sys.sql_logins	0
sa Not Locked	LOGINPROPERTY('sa','IsLocked')	0
Default Database	master	OK
Connection Test	SSMS login	✅ Successful

---

🧩 Optional Notes

• If error 18456 State: 8 appears → wrong password

• If error 18456 State: 11/12 → login valid but no access

• If error says “Integrated only” → Mixed Mode not enabled

• sa should always have sysadmin role for administrative access