Introduction
Forefront Threat Management Gateway (TMG) 2010 supports a number of different logging options to meet administrators needs. By default, TMG is configured to log to a local SQL Express database which is installed along with TMG. Logging to this local instance of SQL is acceptable in most cases, but it does have some serious drawbacks. In terms of performance, SQL express can be potentially limiting. It consumes a considerable amount of system resources on the TMG firewall (CPU, RAM, disk I/O) which can degrade performance. In addition, having all of your TMG log data stored locally presents some challenges as well. For example, if you restore a virtual machine (VM) snapshot, or perform a restore to last known good or factory default on an appliance, much or all of your log data will be lost. To address these concerns, I recommend configuring Forefront TMG to log to a remote SQL server. Logging to a remote SQL server reduces resource consumption on the TMG firewall, freeing up valuable CPU cycles and RAM for handling network requests. With log data stored remotely, a VM snapshot restore or appliance reset will not result in a loss of log data. In addition, having Forefront TMG log data stored in an SQL database enables more flexible reporting along with higher availability and improved data redundancy over local logging options. This month I’ll outline in detail how to prepare an SQL server to accept log data from Forefront TMG and demonstrate how to configure Forefront TMG 2010 for remote SQL server logging.
Configure SQL Server
We’ll begin by configuring the SQL server to accept log data from the Forefront TMG firewall. To start, we need to first create the database itself. To do this, open the SQL Server Management Studio console, open a new query window and execute the following commands:
create database [tmglogs] go use [tmglogs] go
Note:
I realize this is a highly simplistic way to create an SQL database. I’m certain there are better methods to accomplish this task by following implementation best practices like placing database files and log files on separate partitions, configuring autogrowth constraints, etc. I’ll leave that as an exercise to the reader (or your database administrator!).
Next, retrieve the two SQL scripts for creating TMG logging tables from the TMG firewall located in the C:\Program Files\Microsoft Forefront Threat Management Gateway folder. Copy fwsrv.sql and w3proxy.sql to the SQL server. In the SQL Server Management Studio console press CTRL+O and open each SQL script file and then press F5 to execute them. Make sure you execute these scripts on the tmglogs database! You can ensure this happens by prepending use tmglogs at the beginning of the scripts before you execute them. Once complete, this will create tables for the web proxy and firewall logs produced by the TMG firewall.
Figure 1
Next we’ll create an SQL login for the TMG firewall to access the new database. In the SQL Server Management Studio console Object Explorer, expand Security and then right-click Logins and choose New Login.
Figure 2
Select the option to use Windows authentication and enter the name of the TMG SQL service account using the domain\username format. At the bottom of the dialog box select tmglogs for the default database.
Figure 3
Now highlight User Mapping, select the tmglogs database, and select the db_datareader and db_datawriter database roles.
Figure 4
In the SQL Server Management Studio console Object Explorer, expand Databases, expand the tmglogs database and then Security. Right-click Roles and choose New Database Role.
Figure 5
Enter db_batch_insert for the Role name and add the TMG SQL service account as a member of this role.
Figure 6
Finally, open a new query window in the SQL Server Management Studio console and execute the following commands:
use [tmglogs] go grant execute on [dbo].[sp_batch_insert] to [db_batch_insert] go grant execute on [dbo].[sp_batch_discard] to [db_batch_insert] go
Configure Forefront TMG SQL Logging
To enable remote SQL server logging in Forefront TMG 2010 it will be necessary to enable the system policy rules to allow this communication. Right-click Firewall Policy in the navigation tree and choose All Tasks, System Policy, and then Edit System Policy. Under the Logging folder highlight Remote Logging (NetBIOS) and choose Enable this configuration group.
Figure 7
For the highest level of security, select the To tab and remove the default Internal network destination and create a computer object for the remote SQL server. Repeat these steps for the Remote Logging (SQL) system policy rule, then save and apply the configuration.
Figure 8
In the Forefront TMG 2010 management console, highlight Logs & Reports in the navigation tree and click Configure Firewall Logging in the Tasks pane.
Figure 9
On the Log tab select the option to use a SQL Database and click Options. Enter the name of the SQL server and the name of the database. I would strongly encourage you to leave the default option Force data encryption selected, as TMG log data contains potentially sensitive information and should be protected at all times. Be advised that enabling encryption may require additional configuration on your SQL server, however. Select the option to Use Windows authentication and enter the username and password for the TMG SQL service account. Click Test to verify the connection to the remote SQL server database.
Figure 10
After successfully testing the connection to the remote SQL server, click Ok. You’ll receive a warning that reports cannot be generated when using the remote SQL server logging option. We’ll discuss this in more detail later.
Figure 11
After you’ve finished enabling firewall logging to remote SQL server, repeat the steps above for web proxy logging. To verify that Forefront TMG log data is being populated in the remote SQL server database you can execute the following SQL command:
use [tmglogs] go select * from webproxylog orderby logtime desc go
The results pane should show logged data in the table. Note that the ClientIP field will contain data that looks nothing like a typical IPv4 address. This is because the field is configured to support IPv6. The data in the field is actually the IPv4 address represented in HEX and padded. To convert this data to the familiar dotted decimal notation, you can download an SQL script here.
Figure 12
Log Queuing
Although remote SQL server logging was supported in earlier versions of ISA server, it wasn’t really a viable option due to the architecture of the firewall core at the time. With ISA, by default, the firewall service would stop if it was unable to write to the log source. For a local text file there were never any issues. However, with a remote SQL server there are many factors that can come into play and prevent the firewall from writing to the database. It could be caused by the SQL server being busy, which can happen when the SQL server is shared among many resources. It could also be called by network latency and congestion. With Forefront TMG the entire logging infrastructure was redesigned, and local log queuing was introduced. With local log queuing, any time the log database is unavailable, log data will be stored in a local temporary file and written back to the database once it is available again. When using a remote SQL server for logging, it is a good idea to review the log queue settings to make certain they are configured according to best practices. By default, local log queuing is configured to store data on the system partition, which is a bad idea. Ideally the log queue will be configured to log to a separate, dedicated partition with ample free space to sustain prolonged database server outages. You can find the local log queue settings by highlighting Logs & Reports in the TMG management console navigation tree and then selecting Configure Log Queue in the Tasks pane.
A Word about Reporting
As you noticed earlier, when configuring Forefront TMG 2010 to log to a remote SQL server you lose the ability to create reports using the native TMG reporting tools. For most this is a non-issue, as the native reporting tools leave a lot to be desired. In fact, many of the customers that I implement remote SQL server logging for are doing so specifically for the purpose of creating their own custom reports from logged SQL data. If you have SQL reporting skills you will find that generating your own reports is not that difficult. If you want to take advantage of the benefits of remote SQL server logging but don’t want to create your own reports, have a look at TMG Reporter from Fastvue. It provides granular, highly detailed, and interactive reports and also integrates with remote SQL server logging configurations.
Summary
Remote SQL server logging for Forefront TMG 2010 provides a number of important benefits over default local logging options. Logging to a remote SQL server consumes less system resources on the TMG firewall and provides higher availability for logged data when performing virtual machine snapshot restores or appliance resets. It also provides more flexible reporting options, allowing administrators to create custom reports of their liking, and it supports integration with popular third-party reporting tools as well. If you’re not using remote SQL server logging for your Forefront TMG 2010 firewall deployment, you’re missing out! Do it today…you’ll be glad you did.
