MS SQL - Can't login with SQL Server Authentication user

By xngo on October 21, 2021

Issue

After I did a clean installation of MS SQL Server, I created a new user using SQL Server Authentication. When I used MS SQL Server Management Studio to connect, I got the "Login failed for user ..." error:

SQL Server - SQL Server Authentication - Login failed for user

I double-check my password and give it admin roles. Everything seems to be fine but the login still failed.

Solution

I found out that by default, MS SQL server is set to Windows Authentication mode. So, if you try to access using SQL Server Authentication, it will not work. But, why allow that option in the first place to connect.

Anyway, from the database server Properties > Security, under the Server authentication section, I select SQL Server and Windows Authentication mode and then restart MS SQL server.

SQL Server - SQL Server - Properties > Security > SQL Server and Windows Authentication mode

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.