Richard Hicks' Forefront TMG Blog

In response to my last blog post disucssing remote SQL logging for Microsoft ISA Server, several people asked about the data stored in the IP address fields of the ISA 2006 log database.

ISA 2006 IP Address Fields

Clearly these are not IP addresses. In ISA versions up to and including ISA Server 2004, the data type for these fields were varchar(32) and contained IP addresses in the familiar dotted decimal notation.

ISA 2004 IP Address Fields

Beginning with ISA Server 2006, the data type for these fields were changed to bigint. In order to convert the data from these fields to the more familiar dotted decimal notation, use the following SQL function (adapted from this KB article):

CREATE FUNCTION [dbo].[fnConvertIPToText] (
@ISALogIPAddress [bigint]
)
RETURNS varchar(15) AS
BEGIN

RETURN @ConvertedAddress
END

[Download script here.]

(Special thanks to my good friend Christopher Schau, DBA extraordinaire, for creating this function for me!)

When selecting data from the table, use the function created above to convert the data follows:

SELECT
LogTime,
dbo.fnConvertIPToText(SourceIP) AS [SourceIP],
dbo.fnConvertIPToText(DestinationIP) AS [DestinationIP],
dbo.fnConvertIPToText(OriginalClientIP) AS [OriginalClientIP]
FROM
firewalllog

Data from the IP address fields returned from this query will now appear in the familiar dotted decimal notation.

ISA 2006 IP Address Fields - Converted

Looking ahead to Microsoft Forefront Threat Management Gateway (TMG), the developers have once again changed the data type for IP address fields. In TMG, the data type for these fields were changed to uniqueidentifier. This was done in order to support IPv6 entries in these fields. In order to convert the data from these fields in to the more familiar dotted decimal notation, use the following SQL function:

CREATE FUNCTION [dbo].[fnIpAddressToText]
(
@Ipv6Address [uniqueidentifier]
)
RETURNS varchar(40) AS
BEGIN
DECLARE @strInAddress varchar(40)
DECLARE @strOutAddress varchar(40)
SET @strInAddress = LOWER(CONVERT(varchar(40), @Ipv6Address))
SET @strOutAddress = ”

SET @intH = ASCII(SUBSTRING(@strInAddress, 7, 1))
IF (@intH <= @IsNum) SET @intH = @intH – @ZERO ELSE SET @intH = @intH – @IsAlpa
SET @intL = ASCII(SUBSTRING(@strInAddress, 8, 1))
IF (@intL <= @IsNum) SET @intL = @intL – @ZERO ELSE SET @intL = @intL – @IsAlpa
SET @strOutAddress = @strOutAddress + CONVERT(varchar(3), @intH * 16 + @intL)
END
ELSE
BEGIN
— ipv6
SET @strOutAddress = @strOutAddress + SUBSTRING(@strInAddress, 1, 4) + ':'
+ SUBSTRING(@strInAddress, 5, 4) + ':'
+ SUBSTRING(@strInAddress, 10, 4) + ':'
+ SUBSTRING(@strInAddress, 15, 4) + ':'
+ SUBSTRING(@strInAddress, 20, 4) + ':'
+ SUBSTRING(@strInAddress, 25, 4) + ':'
+ SUBSTRING(@strInAddress, 29, 4) + ':'
+ SUBSTRING(@strInAddress, 33, 4)
END
—- guid sample '6F9619FF-8B86-D011-B42D-FFF34FC964FF'
RETURN @strOutAddress
END

[Download script here.]

(Special thanks to Avi Sander with Microsoft Israel for sharing this SQL code with me. Shortly after I received this code it was also posted on the TMG Product Team Blog as well.)

I apologize in advance if any of the SQL code listed in this post is not readable. When posting code like this, formatting with WordPress can cause problems. If you have any issue with code on this page, send me an e-mail and I’ll be glad to send you the actual script files.

Recently I had the opportunity to assist one of my customers with configuring their ISA firewalls to log to a central, remote SQL server. As it turns out, configuring remote SQL logging was not as simple and straightforward as I had anticipated, so I decided to document the process here for reference.

I’ll start out by saying that I’m not particularly a big fan of remote SQL logging for ISA because there are some serious risks involved in doing so. Remote SQL logging brings added complexity and introduces additional moving parts and potential single points of failure. By default, the ISA firewall will shut down if it is unable to write to the log, which means that if the SQL database is unavailable for any reason (offline for maintenance, out of disk space, network communication failure, etc.) the firewall service will go into lockdown mode and it will stop servicing requests (there is a workaround for this, but since it is something that I strongly discourage, I have chosen not to document that here. Also, Forefront Threat Management Gateway includes new functionality that addresses this specific issue – see below). It also requires (obviously) that you purchase an SQL license and have another server to install the software on (NEVER install SQL on the ISA firewall itself!).

Of course if you take steps to mitigate some of these concerns, there are some advantages to remote SQL logging for ISA. It certainly is much more robust that MSDE, and using an SQL database for logging allows you to access historical data from the ISA management console as well (this requires that you install the advanced logging components, even though you will not be using the local MSDE database). There are some advantages to having all of the ISA firewalls in your enterprise log to a central location, and of course you can also leverage any existing SQL reporting tools that you may already have and be familiar with, too. Ultimately the decision to use remote SQL logging for ISA is up to you. Before making that decision I would strongly encourage you to review the Best Practices for Logging in ISA Server 2004/2006 document on TechNet. If you decide to use remote SQL logging, the best advice I can give you is to ensure you have abundant, highly reliable network bandwidth between your ISA firewalls and your SQL server. In very busy network environments it might even be desirable to dedicate a separate network interface solely for SQL communication in order to accomplish this.

Configuring the Database Server

Before we configure the ISA firewall for remote SQL logging, the first thing that we need to do is configure the database on the SQL server (I am going to make the assumption that the reader has some familiarity with SQL, as detailed SQL configuration is beyond the scope of this article).

To create a database, open Microsoft SQL Server Management Studio, then click on ‘New Query’. In the new query window, execute the following commands:

create database [isalogs]
go
use [isalogs]
go

This is a very simplistic way to create a database, of course. Ideally you (or your DBA) would follow SQL best practices and place the data and log files on separate partitions, configure database sizes, specify autogrowth options, and whatever else a ‘real’ DBA would do (that’s not me, for sure!).

Next, locate the two SQL scripts that will be used to create the required tables for ISA logging. The two script files are ‘fwsrv.sql’ and ‘w3proxy.sql’ and they are located in the \Program Files\Microsoft ISA Server folder on the ISA firewall itself, or on the ISA installation CD in the \FPC\Program Files\Microsoft ISA Server folder. Copy these scripts to a location that is accessible from the SQL server, then in the ‘Microsoft SQL Server Management Studio’ window, choose ‘File | Open | File’ (or just Ctrl-O) and select each script. Once the script appears in the query window, execute the script by pressing ‘F5’ and then close the window.

To continue we’ll need to create a SQL login for the new database. In the Microsoft SQL Server Management Studio console window, expand the ‘Security’ node in the ‘Object Explorer’ in the left pane, then right-click ‘Logins’ and choose ‘New login’.

Best practices dictate that Windows authentication should be used for optimum security, so enter the name of the ISA firewall in the ‘Login name’ box as domain\computername$. For the ‘Default database’ select ‘isalogs’.

Select ‘User Mapping’, then select the checkbox next to the ‘isalogs’ database. Choose the ‘db_datareader’ and ‘db_datawriter’ database roles (‘public’ is checked by default) and then choose ‘Ok’.

Repeat this process for each ISA firewall that will be logging to this database.

Now that we’ve created the login, we need to grant some additional privileges in order for the ISA firewall to successfully log data to the database. First we’ll begin by creating a new database role for our database. In the ‘Object Explorer’, expand the ‘isalogs’ database, then expand ‘Security’ and then ‘Roles’. Right-click on ‘Database Role’ and choose ‘New Database Role’.

Call the new role name ‘db_batch_insert’, then add each of the ISA firewall logins you created earlier. Choose ‘Ok’ twice to complete.

Once the database role has been configured, open a new query window in the Microsoft SQL Server Management Studio console and execute the following command:

use [isalogs]
go
grant execute on [dbo].[sp_batch_insert] to [db_batch_insert]
go

If you are performing these steps on a Forefront Threat Management Gateway system, you will need to also execute the additional following command:

use [isalogs]
go
grant execute on [dbo].[sp_batch_discard] to [db_batch_insert]
go

Note: If you have only a single ISA firewall, you can skip the above steps creating a new database role and simply grant execute access for the ISA firewall directly to the stored procedure itself by executing the following command:

use [isalogs]
go
grant execute on [dbo].[sp_batch_insert] to [domain\computername$]
go

That’s it for the database configuration! Now let’s move on to the ISA firewall configuration.

Configuring the ISA Firewall

To allow for remote SQL logging, two specific system policy rules need to be enabled. In the ISA management console, right-click on ‘Firewall Policy’ and choose ‘Edit System Policy’. In the left pane of the System Policy Editor, under the ‘Logging’ configuration group, highlight the ‘Remote Logging (NetBIOS)’ policy and select the option to enable the configuration group. Next click on the ‘To’ tab. You’ll notice that the rule applies to traffic sent to the Internal network. While this works, I prefer to follow the principle of least privilege wherever possible, so I would suggest that you restrict this policy to only your authorized SQL servers.

Repeat these steps for the ‘Remote Logging (SQL)’ system policy, choose ‘Ok’, then apply the changes.

Next, in the ISA Management console, expand your array and then highlight the ‘monitoring’ node. Click on the ‘Logging’ tab, then in the right hand pane under ‘Tasks’ choose ‘Configure Firewall Logging’.

Select the ‘SQL Database’ option, then click on ‘Options’. Enter the FQDN for the database server, then enter the name of the database you created earlier. Since ISA firewall logging data is potentially sensitive, it is highly recommended that you select the option to ‘Force data encryption’. This will require that a valid server certificate be installed on your SQL server, however (for more information on how to configure SQL to use SSL, please read How to enable SSL encryption for an instance of SQL Server). Click on the ‘Test’ button and if everything is configured correctly, you should receive a message stating that the connection succeeded.

Once the test has been completed successfully, choose ‘Ok’, then click on the ‘Fields’ tab. At the bottom of the window, choose the option to ‘Select All’. This will ensure that all logging fields are recorded in the SQL database.

When finished, repeat these steps to configure web proxy logging, then apply the configuration changes to complete the process.

That’s it! You should now be logging data to your remote SQL server. To verify operation, open a new query window in the Microsoft SQL Server Management Studio console and enter the following commands:

use [isalogs]
go
select * from [firewalllog]
go
select * from [webproxylog]
go

If everything is working correctly you should now see data populated in both of these tables (for an explanation of why the IP address field does not return data in the familiar dotted decimal notation, see this blog post) . It takes a minute or two before the ISA firewall begins to populate the database with data, so be patient. : )

One last note in regard to remote SQL logging; if you choose not to install the ISA advanced logging components, you can still log to a remote SQL server. You will not, however, be able to view historical data in the ISA management console. This was something that I discovered when I configured my lab for documentation purposes. If you want to conserve resources and reduce the attack surface on the ISA firewall (an excellent idea!), I recommend removing (or not installing) the ISA advanced logging components. Keep in mind that to view historical data you will need to query your SQL server directly.

A Note about Logging with Forefront Threat Management Gateway

At the beginning of this post I had indicated that there are some potential issues with SQL logging for the ISA firewall. The good news is that there have been some significant improvements in the area of logging in Forefront Threat Management Gateway. First, TMG now uses SQL 2005 Express instead of MSDE, which is wonderful. Second, and very important for those of you considering remote SQL logging, TMG now has the capability to queue log data on the firewall itself.

This means that in very busy environments with high utilization, the likelihood of a logging failure (and subsequent firewall shutdown) due to the inability to write to the logs in a timely manner is greatly reduced. The TMG firewall can now queue logging requests during periods of high utilization, then write them out to the log later when more resources are available. Another benefit to this queuing is that when you are using a remote SQL server, the TMG firewall can continue to log and service requests even if the remote SQL server is offline for some reason. The TMG firewall will simply spool any queued log data out to the remote SQL server once it is back online. Great stuff!