Error while generating any kind of report on Threat Management Gateway 2010

 

While working on a TMG 2010 issue, we were getting an error when generating any kind of report. The error is mentioned below.

================================================================================

System.Web.Services.Protocols.SoapException: The item '/ISA2008

Reports/Summary_ServerParticipation' cannot be found. --->

Microsoft.ReportingServices.Diagnostics.Utilities.ItemNotFoundException: The

item '/ISA2008 Reports/Summary_ServerParticipation' cannot be found.

at

Microsoft.ReportingServices.WebServer.ReportExecution2005Impl.LoadReport(String

Report, String HistoryID, ExecutionInfo2& executionInfo)

at

Microsoft.ReportingServices.WebServer.ReportExecutionService.LoadReport(String

Report, String HistoryID, ExecutionInfo& executionInfo).

The error occurred on object 'Reports' of class 'Reports Configuration' in the

scope of array <Servername>

=================================================================================

It is always a good idea to actually look if actually the Report “Summary_ServerParticipation” existed in the SQL or not.

So we opened the Reporting Services Configuration Manager on the TMG 2010 Server.

Clicked the Highlighted link below.

This opened an IE window and we could not find any report in the location mentioned.

Compared this to a healthy TMG 2010 Server and I could see a bunch of reports present. Highlighted is the report that we are actually getting an error:

So, our Task now was to do a restore of the Database from a healthy TMG 2010 server that is holding the above populated report to the TMG 2010 server, we are facing issue with.

Details of the TMG servers.

TMG2 – TMG 2010 Server with the issue.

TMG1 – Healthy TMG 2010 Server.

Take a backup before we proceed.

Based on the complexity of the implementation of the action plan, we will be accomplishing this task in Phases:

Phase 1: Taking Backup of the Database from TMG 2:

How to: Back Up a Database (SQL Server Management Studio)

https://technet.microsoft.com/en-us/library/ms187510(v=sql.105).aspx

Database’s that we need to take a backup for:

 

Phase 2: Backing Up the Encryption Key from Reporting Services Configuration Manager:

Backing up the Encryption Key.

Back up encryption keys -Reporting Services Configuration Manager (Native Mode)

https://msdn.microsoft.com/en-us/library/ms157275.aspx

Now as we are done backing up the database’s from TMG2, we need to perform the same steps for exporting the database from the working TMG1 Server.

At the end we will have 3 files that we need:

EK- Encryption Key

ReportsServer$ISARS – Report Server Database.

ReportsServer$ISARSTempDB – Report Server Temporary Database.

 

Phase 3: Deleting the Database from the TMG 2 and Importing a healthy Database from TMG 1:

On TMG 2 we need to delete only ReportsServer$ISARS from the SQL Management Studio.

Now why we delete this database only is because firstly this is the database where our report Summary_ServerParticipation is under. And we want to replace our Database ReportsServer$ISARS with a healthy one .

How to: Restore a Backup from a Device (SQL Server Management Studio)

https://technet.microsoft.com/en-us/library/ms188312(v=sql.105).aspx

We need to only import ReportsServer$ISARS as that’s what we are recovering.

Phase 4: Importing the Encryption Key onto the TMG 2 server that we exported from the TMG 1 Server:

Restore encryption keys -Reporting Services Configuration Manager (Native Mode)

https://msdn.microsoft.com/en-us/library/ms157275.aspx .

It will take a little bit to complete this process , do watch out for the message in the Results Section.

 

Phase 5: Delete the TMG 1(Working Server) instance from the Scale-out Deployment.

Delete the Working Server Instance(TMG1) and after deleting we should see only one server which is TMG2.

How to Delete TMG1 Instance:

On the ReportsServer$ISARS Datbase execute the below mentioned query.

delete from keys where machinename=’TMG1’.

To confirm the Exact Machine Name:

On the table dbo.keys under ReportsServer$ISARS Database execute the below mentioned query.

Select [MachineName] from [ReportServer$ISARS].[dbo].[Keys]

After deleting the Instance TMG1, visit the Scale-out Deployment Status.

We would see only TMG2 Server listed there.

This completes the process and now to confirm if we can see the Reports on TMG2. Follow the same procedure mentioned in the starting of the article.

Or you can directly generate a report and see if we are still getting the error. If followed in the exact way we should see the issue getting rectified.

I hope this article will come handy in solving these kind of Reporting issues while working on Forefront Threat Management Gateway.

Author:

Kaustubh Dwivedi
Microsoft Security Support Engineer

Reviewer:

NITIN SINGH
Microsoft Security Support Escalation Engineer