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

Question

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:

   Enter image description here

   I enabled the SQL Server authentication:

   Enter image description here

   Enter image description here

   Enter image description here

   Enter image description here

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

   Enter image description here

How can I fix this?

Answer 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: enter image description here

Don't forget you will need to restart the service otherwise the behavior will not change.

Answer 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

Answer 3

Right-click on the server name in SSMS and go to properties and under the security page.

Change the authentication from windows authentication mode to sql server and windows authentication mode .

Don't forget to restart the SQL Server Instance, as it will restart the windows services. enter image description here

• Ronaldo: Welcome to DBA SE. It seems your answer does not add any new info other than the correct answer already provides.
• Aman singh Parihar: Thanks Ronaldo, I have faced similar issue but did not work until I restarted the connection.