How can I fix “Login failed for user sa” error in SQL Server?

All we need is an easy explanation of the problem, so here it is.

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

I have two issues though:

1. 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:

   

   

   

   

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

   

How can I fix this?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

In SSMS, right-click on the server and go to Properties and under the security page, check what Server authentication looks like. Odds are it’s set to Windows Authentication only and needs to change if you want to use SQL Server users:

Method 2

Alternative ways to change the Server authentication mode

From Windows registry:

Step 1: Open the registry editor.

Step 2: Navigate to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.10.SQLEXPRESS\MSSQLServer

Step 3: Change the value of LoginMode from 1 to 2.

From query:

Just run

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', 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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0