After you create a new login for a database in your SQL Server and try to use it to connect to your database, you may encounter a “login for ‘your_username’ failed” error. The reason for this common error is that by default MS SQL Server runs in “Windows Authentication mode”. And you will need to change it to “SQL Server and Windows Authentication mode” by following the steps below:
- Launch SQL Server Management Studio
- Log into your SQL Server using Windows Authentication
- Right-click the server name and choose “Properties”‘
- Click “Security” in the left panel then choose “SQL Server and Windows Authentication mode”
If now you exit SQL Server Management Studio and relaunch it, you will find that you may encounter another error when trying to log in:
A connection was successfully established with the server, but then an error occurred during the login process
If you click “OK” and try again to connect to the database, you will get the same “login failed” error as before.
So what is happening? Well, the second error is not helpful and is misleading. It seems that the login credentials are not correct, but actually there is nothing related to your login credentials. The cause is that you must restart the SQL Server service after you change the Authentication mode in SQL Server Management Studio.
To restart the SQL Server service in Windows 10, search and run “Administrative Tools”, then click “Services”, then find “SQL Server (SQLEXPRESS)” from the list, then click “Restart the service” on the left. After restarting the SQL Server service, you will be able to log into your database with your newly created login credentials.
Hope this helps.