FIX: You receive an "Error: Subreport could not be shown" error message in the User Activity or Site Activity report in a Forefront Threat Management Gateway 2010 environment

Symptoms

Consider the following scenario. You run the User Activity report or the Site Activity report in a Microsoft Forefront Threat Management Gateway 2010 environment. The report may run for some time and then finish suddenly and open in Windows Internet Explorer. In this scenario, the report has no details, and only the report headers and frames are displayed. You may receive the following error message in the frame where the report data should be:

Cause

The User Activity and Site Activity reports use COM objects in the stored procedures that generate the report and therefore enable the Ole Automation Procedures option. The setting to enable Ole Automation Procedures option is a global setting on the SQL Reporting Server, and the report run may fail if this functionality is turned off while the report is still running. This problem may occur when any changes are made to the Forefront Threat Management Gateway configuration while the report is running or when multiple administrators try to run activity reports at the same time.

Resolution

To resolve this issue, install the hotfix package that is described in the following Microsoft Knowledge Base article:

2649961 Rollup 1 for Forefront Threat Management Gateway (TMG) 2010 Service Pack 2To enable this fix, run the FixActivityReports.js script on one of the Forefront Threat Management Gateway servers. You can run this script on any Forefront Threat Management Gateway server, on an array node, or on an Enterprise Management Server.

Note The script enumerates all available arrays and corrects the stored procedure so that the Ole Automation Procedures option is not turned off for the activity reports. You may have to rerun the script if new arrays are added to the storage.

1. Start Notepad. To do this, click Start, click Run, type notepad.exe in the Open box and then click OK.

2. Paste the following script into a new file in Notepad:

   var guidGenerator = new ActiveXObject("Scriptlet.TypeLib");
   var fpcRoot = new ActiveXObject("FPC.Root");
   try
   {
     WScript.Echo("Enumerating arrays:");
     var arraysCollection = new Enumerator(fpcRoot.Arrays);
     for (; !arraysCollection.atEnd(); arraysCollection.moveNext())
     {
         try
         {
             var array = arraysCollection.item();
             var found = false;
             WScript.Echo("    " + array.Name);
             var rdlDefsCollection = new Enumerator(array.Reports.RdlReportDefinitions);
             for(; !rdlDefsCollection.atEnd(); rdlDefsCollection.moveNext())
             {
                 var rdlDef = rdlDefsCollection.item();
                 if((rdlDef.Name != "Ips_Metadata") && (rdlDef.Name != "UserActivity_Sites") && (rdlDef.Name != "Urlf_Metadata"))
                 {
                     continue;
                 }
   
                 if (rdlDef.DataGenerationStatement.search(/--EXEC sp_configure \'Ole Automation Procedures\', 0/) != -1)
                 {
                     WScript.Echo("---- DataGenerationStatement for " + rdlDef.Name +" already updated");
                     continue;
                 }
   
                 found = true;
                 WScript.Echo("Found RDL definition " + rdlDef.Name);
                 WScript.Echo("---- Old version start");
                 WScript.Echo(rdlDef.DataGenerationStatement);
                 WScript.Echo("---- Old version end");
                 rdlDef.DataGenerationStatement = rdlDef.DataGenerationStatement.replace(/EXEC sp_configure \'Ole Automation Procedures\', 0/g, "--EXEC sp_configure \'Ole Automation Procedures\', 0");
                 WScript.Echo("---- New version start");
                 WScript.Echo(rdlDef.DataGenerationStatement);
                 WScript.Echo("---- New version end");
             }
             if (found)
             {
                 WScript.Echo("Updating configuration ID...");
                 WScript.Echo("Old configuration ID:" + array.Reports.ReportingServicesProperties.ReportingServicesConfigurationId);
                 array.Reports.ReportingServicesProperties.ReportingServicesConfigurationId = guidGenerator.Guid;
                 WScript.Echo("New configuration ID:" + array.Reports.ReportingServicesProperties.ReportingServicesConfigurationId);
   
                 WScript.Echo("Saving the current configuration...");
                 array.Save();
                 WScript.Echo("Waiting for configuration reload...");
                 array.WaitForReload();
             }
         }
         catch (err)
         {
           WScript.Echo("An error occurred. Error Description: " + err.description);
           WScript.Echo("Try running the script as an administrator");
         }
     }
   }
   catch (err)
   {
     WScript.Echo("An error occurred. Error Description: " + err.description);
     WScript.Echo("Try running the script as an administrator");
   }

3. Save the file as FixActivityReports.js.

4. On a server, open an administrative command prompt.

5. Run the script in step 2 by using the following command:

   cscript.exe FixActivityReports.js

Wait for the script to complete and the configuration to synchronize. You should now be able to generate User Activity and Site Activity reports.

Workaround

To work around this issue, make sure that the configuration is fully synchronized before you click Generate and View Report. To determine whether the Forefront Threat Management Gateway configuration is synchronized, click Configuration on the Monitor menu, and then check the Status column.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References

For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:

824684 Description of the standard terminology that is used to describe Microsoft software updates