Remote SQL Logging for Forefront Threat Management Gateway (TMG) 2010 using Microsoft Azure
Detailed, accurate logging is essential for any security solution, and Forefront TMG 2010 includes a number of storage options for access log files. By default, TMG is configured to log to a local installation of SQL 2008 Express, which is installed automatically when TMG is installed. This option suffers from several shortcomings, however. Logging locally is unhelpful if the TMG firewall is not accessible for any reason. In addition, if the TMG firewall fails completely and has to be rebuilt, replaced, or have a virtual snapshot restored, valuable log data will be lost. For these reasons and more it is recommended that logging be configured to use a remote data store. TMG includes an option to log to a remote Microsoft SQL database server, and TMG administrators would be well advised to explore this option. Using a remote SQL server has several advantages and mitigates some of the challenges imposed by local SQL Express logging. Log data is never lost in the event of a TMG failure or rollback, and it doesn’t consume local resources (CPU, memory, and disk) as the local SQL does. Using a remote SQL server does mean that you’ll have to create your own reports, as the native TMG reporting feature is not supported when TMG is configured to log to a remote SQL server. Many organizations leverage their existing SQL database expertise to build much better reporting than TMG provides, so this can be viewed as a positive and not a negative.
It is assumed that you have an Azure account. If you do not already have an Azure account, you can sign up now for a free one month trail here.
Deploy a Microsoft Azure SQL Server Virtual Machine
Begin by opening the Azure management portal and clicking Virtual Machines in the navigation tree and then clicking Create a Virtual Machine. Click From Gallery and then click SQL Server. Scroll down and select SQL Server 2008 R2 SP2 Standard.
Enter a name for the virtual machine and select the tier and size appropriate for your environment. Provider a user name and password.
Select an existing cloud service or choose the option to create a new one. Enter a public DNS name for the cloud service (if required) and select a geographical region to host the new virtual SQL server. Select an existing storage account or use an automatically generated one. Optionally you can add the new virtual server to an existing availability set or create a new one.
The cloud service DNS name will be used later to configure TMG logging.
Select any optional VM agents, configuration extensions, or security extensions to install to complete the virtual machine configuration.
Once the virtual machine has finished provisioning, click on the new virtual machine and select Endpoints. Click Add at the bottom of the screen and select Add a stand-alone endpoint.
From the Name drop down list choose MSSQL.
Once the MSSQL endpoint has been configured, highlight the new endpoint and click Manage ACL at the bottom of the screen. Provide a descriptive name for the new ACL, select Permit for the Action, and specify the remote subnet where the TMG firewall will be communicating from. If the TMG firewall is edge-facing, this will be the IPv4 address assigned to its external network interface. If the TMG firewall is located behind a NAT device, use the external IPv4 address of the device performing outbound NAT. If you wish to restrict access to a single IPv4 address you can use a /32 subnet mask, as shown here.
Configure SQL Server for TMG Logging
Next click on Dashboard and then click Connect at the bottom of the screen to establish a remote desktop session to the Azure virtual machine. Once the RDP session has been established, copy fwsrv.sql and w3proxy.sql from the \FPC\Program Files folder on the Forefront TMG 2010 installation media to the Azure virtual machine. Now open the SQL Management Studio by navigating to Start-All Programs-Microsoft SQL Server 2008 R2-SQL Server Management Studio. Right-click the SQL server in the navigation tree and choose Properties. Highlight Security and select the option to use SQL Server and Windows Authentication mode. Click Ok and then restart the Microsoft SQL services for the change to take effect.
In the SQL management console, click New Query and create a new database by executing the following commands.
create database [tmglogs]
To create the tables required for TMG SQL logging, click on File from the SQL Management Studio drop-down list and choose Open, and then File. Open fwsrv.sql, and to ensure that the tables are created in the correct database, place the following commands at the beginning of the script.
Now click Execute or simply press F5 to run the script. Repeat this process with the w3proxy.sql SQL script file.
To create a login for the TMG firewall to use to upload data to the database, open a new query window and execute the following commands.
create login tmgsqlsrv with password='<password>.', default_database=tmglogs
Next create a SQL user for the login by executing the following commands.
create user tmgsqlsrv for login tmgsqlsrv
The TMG firewall requires only the privilege to read and write to the database. Those permissions can be assigned to the SQL login used by TMG by executing the following commands.
exec sp_addrolemember 'db_datareader', ‘tmgsqlsrv’
exec sp_addrolemember 'db_datawriter', 'tmgsqlsrv’
Finally, the TMG SQL login will need permission to execute two stored procedures. Permission to execute these store procedures can be granted by issuing the following commands.
grant execute on sp_batch_insert to tmgsqlsrv
grant execute on sp_batch_discard to tmgsqlsrv
Configure Forefront TMG for Remote SQL Logging
To configure TMG to send log data to our remote SQL server in Azure, open the TMG management console, right-click the Firewall Policy node in the navigation tree and choose All Tasks, System Policy, and Edit System Policy. Scroll down to the Logging group and highlight Remote Logging (SQL). On the General tab select the option to enable the configuration group.
Select the To tab, highlight the Internal network, and then click Remove. Now click Add, expand Networks, highlight External, and click Add and then Close.
I’ve chosen to grant access to the external network for the sake of simplicity. For an actual production environment, this access is much too broad. It would be better to restrict access to a single IPv4 address. However, IP addresses are dynamically assigned in Azure, by default. It is possible to configure a static IP address assignment for the Azure cloud service. For more details, click here.
The system policy has been configured to allow remote SQL logging, highlight Logs & Reports in the navigation tree and in the Tasks pane click Configure Firewall Logging. Select SQL Database and then click Options.
For the Database Connection Parameters enter the cloud service DNS name for the SQL server and enter the name of the database. If you want to encrypt SQL server communication between the TMG firewall and the remote SQL server, leave the option to Force data encryption checked. Enabling this option requires additional configuration on the SQL server, however. For more information, click here.
Select Use SQL server authentication and enter the SQL username and password created earlier (not the SQL admin). Clicking test should return a successful result.
Save and apply the changes, and then repeat these steps for web proxy logging. As stated previously, the native TMG reports will no longer function when using a remote SQL server. TMG will surface a warning message to remind you of this.
Verify that TMG log data is being recorded in to the remote SQL server database by opening a new SQL query window and executing the following commands.
select * from firewalllog
You’ll quickly notice that IP address information is recorded in hexadecimal format in the TMG logs. These values can easily be converted to the familiar dotted decimal notation using a SQL server function documented here.
Configuring Forefront TMG to log to a remote SQL server has several distinct advantages over logging locally, both from a security and performance perspective. It does also introduce additional complexity, and requires that you create your own reports, so this option may not be a fit for all environments. Leveraging the power of the cloud can provide additional flexibility when using SQL as a TMG log data store. At the time of this writing, Microsoft Azure supports virtual machines with up to 32 processing cores, 448 GB RAM, and 6.5TB SSD storage, and that trend is sure to continue so you should never be lacking in resources.