The month of April has been too hectic for me. Earlier it was Incident Management at Office and Toddler Management at Home. This month People Management also got added to the list because of my role change. As a result even though I had quite a few items to blog about, I could not find time to do so. Now somehow I was able to squeeze in some time for this post.
On the 30th of March 2o11, one of the DBAs in other team reached out to me for an issue related to SQL Server Reporting Server 2008 R2 (64 bit). For some testing purpose, they had installed SQL Server Reporting Server 2005 on the same server and uninstalled it later. Ever since the Reporting Server service would not start and the following error message was logged in the Reporting Server log.
The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing., ;
The report server cannot open a connection to the report server database.
A connection to the database is required for all requests and processing.
System.NullReferenceException: Object reference not set to an instance of an object.
From this message, it appeared to be an issue with the Privileges of the SSRS Service Account. It was not be. The Service Account had all the requisite privileges on the Reporting Server databases. The last line in the above error message caught my attention. It appeared that while starting the service, it found a NULL in an object which was supposed to have some value in it. Like I always, I rely on Process Monitor in a situation like this. Here is what I saw in Process Monitor.
Bingo! In the Registry under
HKLMSYSTEMCurrentControlSetServicesMSRS 2008 Windows ServicePerformance, Counter Names & Counter Types keys were missing. On quickly checking the registry entries on a server running SQL Server Reporting Service this was confirmed.
Now that I know the reason for failure, I exported MSRS 2008 Windows ServicePerformance on the working server to a .reg file. Then opened the .reg file in notepad and cleaned up everything except the Counter Names and Counter Types in it. Importing this .reg file on the Server having problems ensured that the requisite keys were present in the registry. The Counter* keys were missing under MSRS 2008 Web Service on this server. I repeated the same exercise of exporting and importing the registry keys. After this the Reporting Service started without any problem!
Once I logged off from the call, checked if someone else had faced similar issues. To my surprise, this is a known issue and it is well documented in this Microsoft KB article! Wish I had Googled well before troubleshooting (which I always do!).