Scenario
You often get complained that user is not able to connect to SQL server or Application is not able to connect to SQL for some reason. There might be any of the below reasons for which the user/application is not able to connect to SQL.
As a resolution first thing, you have to check the SQL server error log. Based on the error log you will need to take action.
Resolution 1
If you see in the error below error.
Login failed for user ‘XXXXX’. Reason: Password did not match that for the login provided.
Login failed Incorrect Password
Resolution: The error message itself giving you the solution. In this case, the user is using SQL login and the password is incorrect. So we need to supply the correct password.
Resolution 2
If you see in the error below error.
Login failed for user ‘XXXX’. Reason: Could not find a login matching the name provided.
Resolution: The error message itself giving you the solution. There is no login name with XXXXX or other login associated with this user. There should be a login name present in the server mapped with that particular user.
We can check if a login or login group is present on the server mapped to that user by executing the below script.
exec xp_logininfo 'Domain\Account','all'
If the result is empty then you will need to create a login to the SQL instance associated with that user.
Resolution 3
If you see in the error below error.
Login failed for user ‘XXXXX’. Reason: An attempt to login using SQL authentication failed. The server is configured for Integrated authentication only.
Resolution: Looking at the error message we can understand that the SQL server is not configured for Mixed mode authentication but the user/login is trying to connect using SQL authentication. In this case, we have to configure the SQL server to accept both the authentication type login (SQL and Windows login).
Right-click on the SQL instance –> Properties –> Security –> Select ” SQL Server and Windows Authentication Mode”
Keep in Mind. It needs a SQL instance restart.