In this article, I am going to explain fixing a problem related login failure error with SQL Server.
The Problem
One of the common error in the SQL Server error log is
"Login failed for user 'DomainName\ServerName$'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]".
Even though it says that the login failure from user 'DomainName\ServerName$', the actual user can be different. So the error message is a kind of misleading. This is the main problem with this error. So it is very hard to find our from where the error is coming.
If you look at the error log further, you can see that there is another line which gives some more details of the error as below
"Error: 18456, Severity: 14, State: 5."
Root Cause Identification
Based on above two errors, we can narrow down to a specific service or process running in the local machine. Something is frequently trying to access the SQL Server. However a specific login name is not available.
In different blogs I've read, the resolution has been focused on very specific user accounts, but the correct account could be different in your scenario. Now the question is, how to find what is the specific user account which tries to access the database? The answer for this is very simple.
Resolution
First, verify whether there are any specific services or applications hosted on the local server. You should verify whether those services try to access the SQL Server with a specific user account and whether that account is created as a login in the SQL Server. If you find such an account or login is missing, just create it.
The next thing to do is open the Services applet, as shown in the below image, and verify whether the service accounts used in all the services that are related to SQL Server are available as logins in the SQL Server and have the access to the instance.
If any of these are not there, just create the account, and the error will go away.
Further Details
Further, even after creating the account, it might log a new error in the Error log will sometimes show this:
"Login failed for user 'AccountName'. Reason: Failed to open the explicitly specified database 'DBName'. [CLIENT: <local machine>]"
This is due to the account not having enough privileges to access the database. In this case, the good thing is the error will specifically mention the name of the account that needs access to the specific database. Further the "state" of the error gives a good indication of the reason and different reasons for each state can be found in this blog.
Based on this information you can resolve this issue by doing either one of following
- create the specific user or login & give the necessary permission
- disable or remove a particular process or service if it not required
Hopefully this helps you debug login errors that appear in your log.
