Login with SQL Server Authentication is throwing an error is a standard interview question. So, in this SQL Server article, we will show you the step-by-step approach to resolving Login Error. First, Let me show you the error. From the below screenshot, you can see the Login Error for user Suresh.
Before we start doing anything to resolve SQL Server login errors, let me show you the list of available logins in our Management Studio. From the below screenshot, you can see that the Sql Server user exists. I suggest you refer to Create a Login article to understand the steps to create users.
SQL Server Login Errors to Access
If your account doesn’t have permission to log in, right-click on the Username and select properties from the context menu.
Selecting the properties option will open the following window
Under the status tab, check the permissions to resolve the Login Errors.
- Grant: Granting permission to users to connect with the database engine.
- Enabled: Enable this user to login with his / her details.
In most cases, the above-mentioned method will resolve the errors.
Sql Server Authentication Login Error Fix
In some cases, your database will accept only Windows authentication. In these cases, you have to change the authentication mode to dual. Right-click on the instance name and select the Properties option from the context menu
It opens a properties window
Please navigate to the Security tab. From the below screenshot, you can see that the Server Authentication is selected in Windows Authentication Mode. It means it only accepts local Windows users.
Please change the option to SQL Server and Windows Authentication Mode option.
Click Ok. Remember, configuration changes will take effect only after the restart. So, restart the instance
Try with Username Suresh and server authentication.
We successfully entered the management studio.