If you would like to read the other parts in this article series please go to:
- Understanding TMG Logging (Part 1)
- Understanding TMG Logging (Part 2)
- Understanding TMG Logging (Part 4)
Working with SQL Express
As we mentioned in part 2, SQL Express Database is the default logging option for the TMG firewall. Unlike with the ISA firewall, this functionality cannot be removed through Control Panel’s Programs and Features applet (Control Panel, Add/Remove Programs in Windows Server 2003). You are probably familiar with SQL Server Express. TMG Medium Business Edition (which was available in the now defunct Small Business Server) uses SQL Server 2005 Express for local SQL logging, while the TMG firewall uses SQL Server 2008 Express.
One of the benefits of using SQL Server 2008 Express is that the SQL 2008 Reporting Services does not require Internet Information Service (IIS) to be installed on the TMG firewall. Having one less service to be concerned with lowers the attack surface, so that’s a security advantage.
Now let’s look at how to configure logging. If you click the Options button, you will be able to change several aspects of SQL Express logging. SQL Server 2008 Express sets limits on database file sizes and the TMG firewall is designed to work with these constraints when monitoring the file size and disk space consumption.
Understanding file names
The files that are created by SQL Server Express are ISALOG_YYYYMMDD_SVC_###.ldf and ISALOG_YYYYMMDD_SVC_###.mdf. Just in case you need some help in understanding the syntax:
- YYYYMMDD is the local system date—for example, 20090606.
- SVC is the relevant TMG service log. FWS is used for the firewall service log and WEB is used for the Web proxy service log.
- ### is the file index for the TMG service for that day. This number increments by one for each new log file created during that day. If the traffic logs reach 80% of the maximum size (which is 4GB for SQL 2008 Express), TMG will instruct SQL Express to create a new log file.
- .ldf is the SQL transaction log file for the log database file.
- .mdf is the actual database file.
Log file size limitations
Because SQL Server 2008 Express limits the size of database files to 4 GB (as opposed to MSDE, which limits database files to 2 GB), the default value of 8 GB for the maximum disk usage control may only allow two active log files if your TMG is tasked with a high daily traffic load. For this reason, you should keep a close watch on your log file sizes until you get a sense of the file sizes created by your traffic profile.
SQL vs. SQL Express
SQL database logging is a bit different from SQL Express in the following ways:
- The log file maintenance is left to the SQL Server database administrators (DBA), so the log file controls that are used for SQL Express and text logging are unavailable.
- SQL database logging is very dependent on the network and SQL Server responsiveness.
- SQL database logging presents a different set of options that are unavailable for SQL Express or text logging. The figure below shows these options.
Figure 1
Understanding configuration options
The Database Connection Parameters and Authentication Details sections define the primary criteria for TMG to use when sending log data to the SQL server instance. Similar data is used for SQL Express logging, but because this can be configured during TMG installation, and because TMG uses shared memory logging for SQL Express, there is no need to bother you with these details.
Let’s look at each of the fields shown in the Figure:
- Server – This field will contain the fully-qualified name or IP address of the server hosting the TMG firewall’s logging instance. In preference order, this field should be populated using:
- IP Address – To avoid connectivity problems caused by name resolution errors.
- Fully Qualified Domain Name (FQDN) – To reduce name resolution time.
- Unqualified Name – This is the least reliable specification because it relies not only on name resolution, but also may also incur a dependency on domain name suffix devolution to determine the IP address of the SQL server.
- Port – Enter the listening port used by the remote SQL instance. Typically, this is 1433, but your database administrator (DBA) may have defined a custom port for various reasons.
- Database – Enter the name of the database provided by your DBA for TMG firewall logging.
- Table – Enter the table designated for this service log within the selected database.
Note that failure to provide accurate information for any of the above fields will result TMG failing to connect to the SQL instance for logging. In that case, the TMG firewall will log an alert and a Windows Application event log entry will be made.
Next, you need to select authentication options:
- Use Windows Authentication – If the SQL DBA has configured your SQL instance for Windows authentication, you must use this option.
- Use SQL Server Authentication – If the SQL DBA has configured your SQL instance for SQL authentication, you must use this option.
Note that there has been some debate as to whether Windows or SQL authentication is more secure. See https://techgenix.com/SQL_Server_2000_Authentication/ for a fairly detailed discussion on this point. You will need to discuss this with your SQL DBA and come to an agreement on this point before SQL Database logging will be usable.
Testing and application
The Test button allows you to validate the specified connection criteria as well as the network connectivity between the TMG firewall and the SQL server. An error encountered as a result of this test will be displayed in an error dialog such as the one shown in the figure below. You must correct any errors discovered through the test button before attempting to use SQL Database logging.
Figure 2
When you complete the SQL Database configuration test and apply the new settings, you will see a dialog box like that shown in the figure below. This describes the need to modify the system policies so that TMG can communicate with the SQL instance you’ve specified.
Figure 3
You should allow the TMG firewall to enable the default rule so that when the service restarts, the log destination service will be available. As noted in the dialog box, you should edit the system policy to limit traffic to only the specified SQL server. One thing to consider when defining this limitation is that if you change to another SQL server, you must remember to change the system policy destination to agree with this change or else you’ll end up with a long running troubleshooting problem that may be difficult to solve.
Logging to Text Files
Text file based logging uses a logging format that consumes fewer resources, but as with remote SQL logging, it also removes the option of historical log viewing through the integrated TMG firewall log viewer. Unlike SQL Express and SQL database logs, you have the option of two file based log formats: Forefront TMG and W3C format. The differences between these two log formats are as follows:
- W3C – The filename follows the same format as with SQL Express, except that there is only one file per log and the log file extension is .w3c. Each log file begins with a header that is written in accordance with the standard defined by the World Wide Web Consortium (W3C).The log header identifies the log source, log format version, the GMT date this log file was started, and the log fields that may be seen in this log.
- Forefront TMG – The filename follows the same format as W3C, except that the file extension is .iis. TMG-format text logs do not include a header, but are written in a specific format that follows IIS log formatting.
In both formats, any log fields that were disabled through the Fields tab in the log configuration are written to the log as a dash (-) rather than simply leaving them empty. This is done to ensure that each log entry clearly consumes the same number of fields, whether they are used or not. Doing this ensures that any log analysis tools will have a consistent data format regardless of whether a specific field is actually used.
The disadvantage of using text logs is that they’re more difficult to search for specific data. Fortunately, there are tools are available that can make this task easier for you. One such free tool is LogParser, which is available here. This tool was written specifically to ease the task of performing statistical analysis on text logs, but it eventually evolved to handle much more.
Summary
In this, part 3 of our series on logging in the TMG firewall, we spend some time discussing what you need to know in order to configure SQL logging for logging to an off box SQL Server. We then finished up the article by talking about some of the things you need to think about when doing text logging.
If you would like to read the other parts in this article series please go to:
