In SQL Server, Login with SQL Server Authentication is throwing an error is a common SQL Question. So, in this article, we will show you the step by step approach to resolve SQL Server Login Error. First, Let me show you the error.
From the below screenshot you can see the SQL Server Login Error for user Suresh
Before we start doing anything, let me show you the list of available logins in our SQL Server Management Studio. From the below screenshot you can see that the user exists
TIP: I suggest you to refer Create a Login article to understand the steps to create Sql Server users.
SQL Server Login Error – Access Errors
If your account doesn’t have the permission to login then, 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
- Grant: Granting permission to usere to connect with database engine
- Enabled: Enable this user to login with his / her login details
In most cases this will resolve the Sql Server login errors.
SQL Server Login Error – Sql Server Authentication
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 Server name and select Properties option from context menu
This will open the properties window
Please navigate yourself to Security tab. From the below screenshot you can see that the Server Authentication is selected to Windows Authentication Mode. It means, it will only accept local windows users.
Please change the option to SQL Server and Windows Authentication Mode option
Remember, configuration changes will take effect only after the server is restarted. So, restart the server instance
Try to login with Username Suresh
You can see that we successfully logged in to the server.