Introduction
When deployed as an edge firewall or secure web gateway, Forefront TMG provides network engineers and security administrators with a power platform to control inbound and outbound network communication. Deep application layer traffic inspection, strong user and group-based authentication, and advanced web protection facilities can be leveraged to provide a high level of protection for users accessing resources on the Internet as well as enabling secure remote access to on-premises applications. However, an essential component of providing this protection is being able to generate reports to demonstrate the effectiveness of the security solution. Forefront TMG installs an instance of Microsoft SQL Server Express 2008 and leverages the included SQL Server Reporting Services to provide out-of-the-box reports that allow the administrator to quickly and easily identify trends and determine if security controls are operating as expected. As an enterprise-class firewall, Forefront TMG logs each packet to the configured logging database, but it is possible that generated reports may be missing data or fail to run altogether. Troubleshooting reporting issues can be challenging and frustrating, so I’ll share with you a few tips you can use to hopefully resolve those issues quickly.
Before You Begin
Before diving in to troubleshooting any issues with Forefront TMG reporting, it is recommend that you install the latest service pack and update rollup to resolve common reporting issues. For TMG Enterprise arrays make certain to always install updates in the correct order. For more information regarding the correct procedure to install TMG service packs and hotfixes click here.
Supported Configuration for Native TMG Reporting
By default TMG logs to a local SQL database and reporting services are provided by the native SQL Server Reporting Services. This means that reports can only be generated if you are using the default logging configuration. If you have chosen to log to text files or a remote SQL server, you will receive the following report generation warning when making those changes.
Figure 1
Initial Troubleshooting
After you’ve confirmed that you are logging to the local SQL database, it is essential that all of the TMG firewall’s supporting services are up and running. Open the services management console and check that all of the following services are started and running:
- Microsoft Forefront TMG Control
- Microsoft Forefront TMG Firewall
- Microsoft Forefront TMG Job Scheduler
- Microsoft Forefront TMG Managed Control
- Microsoft Forefront TMG Storage
- SQL Server (ISARS)
- SQL Server (MSFW)
In addition, verify that logging is enabled. Surprisingly, logging is optional! In the TMG management console highlight the Logs & Reports node in the navigation tree, then click the Logging tab in the center pane. On the right in the Tasks pane click Configure Firewall Logging and verify that Enable logging for this service is checked. Be sure to repeat this process for the web proxy logs as well.
Figure 2
Next select the Fields tab and check that any and all log fields are selected for logging. If you are unsure as to which log fields should be logged, choose Select All.
Figure 3
Log Summary, Report Server, and Intra-Array Communication
As an enterprise-class firewall, Forefront TMG logs each and every packet received by one of its configured network interfaces. As such, the log files contain a wealth of detailed information about every request that the firewall receives. However, reports are generated from summarized log data, not the raw data contained in the logging database itself. This is an important point to remember because logging may be working correctly, but if the report summarization job does not run the reports will be outdated or not contain information at all. To confirm that daily and monthly summaries are enabled, open the TMG management console and highlight the Logs & Reports node in the navigation tree. Click the Reporting tab in the center pane, then click the Configure Reporting Settings link and select the Log Summary tab. Ensure that Enable daily and monthly summaries is enabled.
Figure 4
You can confirm that summary jobs have been completed successfully by executing the following command from an elevated command prompt:
osql -E -S .\isars -Q “select * from isa_rs_db..tblserverparticipationsummary_daily order by [fromdate] asc”
Figure 5
For Enterprise arrays, one member of each array is responsible for generating reports. If this server is offline or has been removed from the array, this will obviously prevent reports from being generated. Select the Report Server tab and confirm that a report server has been selected and that this server is online.
Figure 6
It is important also to check that intra-array communication is functioning properly for Enterprise arrays. This can be confirmed by pinging the intra-array IP address of each array member from the other array members. Also, review the live log for any traffic that is being denied from other array members. If you have configured a dedicated intra-array network, verify that the correct IP address is being used by highlighting the System node in the navigation tree, then right-clicking each node and choosing Properties and selecting the Communication tab.
Figure 7
Logging Issues
If TMG reports contain no data, a good place to start troubleshooting is to verify that data is actually being stored in the database. If TMG is unable to log to the database, logged data will be written to a local log queue. This provides some resiliency for TMG, allowing it to continue processing requests while the log database is offline. However, if this continues for an extended period of time it can adversely affect TMG reports. If the database is indeed offline and TMG is logging to the local log queue you should find evidence of this in the Alerts tab of the Monitoring node, typically with Log Formatting Failure or Log Queue Usage Started alerts. You will also notice the existence of .llq files in the log file folder, which by default is configured to be C:\Program Files\Microsoft Forefront Threat Management Gateway\Logs.
Figure 8
Reporting Services Configuration ID
In some cases the Reporting Services Configuration ID can get out of sync between TMG storage and the SQL database which can cause reporting issues. To check that these IDs are the same, running the following PowerShell commands from an elevated PowerShell window:
$FPC = New-Object -ComObject FPC.Root
$Array = $FPC.GetContainingArray()
$Array.Reports.ReportingServicesProperties.ReportingServicesConfigurationId
Next, issue the following SQL server command:
osql -E -Q “select ConfigurationVersion from [isa_rs_db].[dbo].[tblversionconfig]”
Figure 9
Compare the GUIDs returned from TMG storage and the SQL database. If the GUIDs do not match it will be necessary to trigger the TMG job scheduler service to reload the configuration. To trigger a reload you must change one of the reporting services properties. The best way to do accomplish this is to change the ReportingServicesConfigurationID property. The new value can be any random GUID. A GUID can be generated online here and is updated by executing the following PowerShell commands:
$FPC = New-Object -ComObject FPC.Root
$Array = $FPC.GetContainingArray()
$Array.Reports.ReportingServicesProperties.ReportingServicesConfigurationId = “{<random GUID here>}”
$Array.Save()
Missing Report Data
In some instances reports may generate properly, however they may appear to be missing data or information. Since logging can be enabled or disabled on a per-rule basis, review any firewall policy rules for to verify that logging is indeed enabled.
Figure 10
Summary
There can be many causes for reporting issues in Forefront TMG. Often reporting issues can be resolved by installing the latest service pack and update rollup. Occasionally reporting issues can be caused by services that aren’t running or simple configuration errors. If you’ve followed the guidance outlined here you’ll find that most reporting issues can be resolved using the techniques I’ve demonstrated. However, we’ve just scratched the surface here and this article is by no means a comprehensive troubleshooting guide for reporting issues in Forefront TMG. In some instances it may become necessary to engage with Microsoft Product Support Services (PSS) to preform additional troubleshooting in order to resolve any issues with reporting.
