What to do if login failed for user sa?

436 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?

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

What to do if login failed for user sa?

Your Answer