How To Repair SSRS When ResetSRS.exe Fails?

Even though the installer of OM12 is hugely improved compared to the one used by SCOM R2, sometimes an installation might go wrong. Yesterday evening I had such an event. The installation of the OM12 Reporting component failed, resulting in a defect SQL Server Reporting Services (SSRS) environment.

The tool will help me, or not?
Normally the tool ResetSRS.exe comes to the rescue (found on the installation media, in the folder ~\SUPPORTTOOLS\<architecture>) and restores the SSRS instance back to a working condition. When one opens the url http://localhost/reports afterwards all will be fine again.

But sometimes that tools fails to run as well, for instance when SSRS is suffering too much from the installation failure of the OM12 component. So now what?

Back to the old days…
In the SQL Server 2005 days, it was easy. No removal of SSRS was required. Only a deletion of the related websites, databases and with some tools a fresh SSRS instance was created. But with SQL Server 2008 the IIS dependency was removed since SQL Server 2008 introduced it’s own ‘IIS’ component for SSRS. This is a huge improvement since it made SSRS way more robust. However, it makes it harder to fix when SSRS turns sour. And yes, there are some ways to ‘refresh’ SSRS without removing it. But it’s like gutting a SQL Server which isn’t a good idea at all.

Therefore this posting contains an overview about the steps you need to take in order to restore SSRS when it’s broken AND the tool ResetSRS.exe won’t fix it for you. Let’s start.

Step 01: Removal of the SSRS component
In this step you’ll remove the defect SSRS component from the SQL Server instance.

1. Go to Control Panel > Uninstall a Program > select Microsoft SQL Server 2008 R2 (64-bit) > right click > click on Uninstall/Change;
   
2. Click on Remove;
   
3. OK > !!! select the correct SQL Server instance !!! > Next > place a checkmark for Reporting Services;
   
4. Next > Next > Remove. Follow the rest of the wizard and SSRS will be removed.
5. Open SQL Server Management Studio and logon to the SQL Server instance from which SSRS is removed. Remove both the SSRS databases (ReportServer and ReportServerTempDB);
6. A reboot of the SQL Server is required now. Otherwise Step 02 won’t work…

Step 02: New installation of the SSRS component
In this step you’ll reinstall the SSRS component on the correct SQL Server instance.

1. Start the utility SQL Server Installation Center (64-bit) on the SQL Server (found under Microsoft SQL Server 2008 R2 > Configuration Tools);
2. Go to Installation > click on New installation or add features to an existing installation;
3. If needed, point the wizard to the installation media of SQL Server 2008 R2;
4. OK > Install (Setup Support Files will be installed now…);
5. Next > select the option Add features to an existing installation and select the correct SQL Server instance;
6. Place a checkmark for Reporting Services;
   
7. Next > Next > Next > enter the credentials of the account used by SSRS;
8. In an ideal world you could select the option Install the native mode default configuration. But since the SQL instance is already in place you can only select the option Install, but do not configure the report server (yes, that’s right, Step 03 is going to cover that );
   
9. Select the error reporting options > Next > Next > Install;
10. Now SSRS will be installed. Just be patient and follow the instructions of the wizard.

Step 03: Configuration of the freshly installed SSRS component
In the final step you’ll configure the freshly installed SSRS component .

1. Go to Start > Microsoft SQL Server 2008 R2 > Configration Tools > and start Reporting Services Configuration Manager;
2. Go to Web Service URL. The wizard already proposes some suggestions. Many times they’re OK and comply with your environment. Simply click Apply and look in the Results pane. When all is well, (all green check marks)
   
   go to the next option Database;
3. Click on the button Change Database simply because there isn’t no SSRS database yet:
   
4. Follow the wizard Report Server Database Configuration Wizard and select the option Create a new report server database;
5. Enter the correct SQL Server instance name and hit the Test Connection button. When all is well this screen is shown:
   
6. Accept the Database Name ReportServer and ascertain yourself Report Server Mode is set to Native Mode. Also the option Language must be set to English (United States) > Next;
7. When required enter the credentials > Next. A summary is shown. Check it, when all is OK > Next;
8. The SSRS database is created now. This takes a few minutes. When all is well this screen is shown:
   
   Hit Finish.
9. Go to Report Manager URL. The wizard already proposes some suggestions. Many times they’re OK and comply with your environment. Simply click Apply and look in the Results pane. When all is well, (all green check marks)
   
10. SSRS is fully configured now > Exit. Test SSRS by starting IE with elevated permissions. Go to the url http://localhost/reports. It might take a while before the page is loaded but when all is well, this page is shown:
    
11. Now everything is OK, except for ONE thing: DON’T FORGET TO INSTALL THE SP and CUs for SQL SERVER AGAIN! Otherwise SSRS won’t be on the correct patch level compared to the SQL instance…

Now the OM12 Reporting component can be installed. Simply insert the OM12 installation media and hit Install. Soon the installer will detect the missing component (OM12 Reporting). Select it and follow the wizard. Within a few minutes all will be fine .