Today I was debugging some issue in some application. I looked in event log on my machine and saw that log was full of Sql Server Errors as shown below.Login failed for user 'NT AUTHORITY\LOCAL SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT:
As you can see that error message is not of much help. It tells me that SQL Server login is failing but it does not tell me what application is causing it and more important what database is failing. At that point I turned to SQL Server that was in question. I brought up SQL Profiler. By default the profiler does not show failed login entries. So before you launch sql profiler instance, make sure you configure it to include Audit: Login Failed. Another important thing you will need to configure is the columns that you want to see in the profiler. By default name of the database or database ID is not configured to be included in the report. So you will have to enable that as well in profiler configuration. Following image shows how I configured it on my machine.
After running the profiler, I could see that login was failing for MASTER database. And the application name was showing up as .Net Sql Data Provider. At this point I had following pieces of information.
Based on this information I could deduce that is some windows or web service that is pinging MASTER database to get some metabase information. Considering it was related to .Net framework, suspect was some Microsoft service or component. After looking at the databases in server, I saw that I have TFS installed on same server. When I went to TFS configuration console the error was right there that the whole service was failing with SQL errors. After I fixed the TFS configuration, these errors went away.
The point I am trying to stress in this post is that if you run into this error, quickly launch SQL Profiler and find out whata database is in question and what application is causing it. And you will have solution to the problem quickly.