What to do if login failed for user sa?

395    Asked by AndrewJenkins in SQL Server , Asked on Oct 4, 2022

 I can access my databases through SQL Server Management Studio (SSMS) by using my Windows account no problem.

I have two issues though: I want to enable sa and then try to log in using sa and the password. I get this error:

I enabled the SQL Server authentication

I tried to create a new user, but that user cannot connect either:

How can I fix this? 

Answered by Andrea Bailey

If login failed for user sa - Alternative ways to change the Server authentication mode

From Windows registry:
Step 1: Open the registry editor.
Step 2: Navigate to:
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.10.SQLEXPRESSMSSQLServer
Step 3: Change the value of LoginMode from 1 to 2.
From query:

Just run

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'LoginMode', REG_DWORD, 2

Note: changing the SQL Server authentication mode requires the service to be restarted to take effect.

Source:

4 Ways to Enable Mixed Mode Authentication for SQL Server Change authentication mode with SSMS



Your Answer

Interviews

Parent Categories