How to configure Forefront TMG logging into a central Microsoft SQL Server database
The default log settings for Forefront TMG are set to a local Microsoft SQL Server Express 2008 SP1 database. During the Forefront TMG installation a local Microsoft SQL Server Express database will be installed. If you want to change this local SQL Server to a central SQL database instance, you have to perform several tasks in advance. The high level steps are:
- Create the Forefront TMG databases in your central SQL Server
- Execute the Forefront TMG SQL scripts to create the necessary SQL tables in the database
- Configure permissions for the TMG Server to access the SQL database
- Change the SQL logging in Forefront TMG
- Optional: Force data encryption between the TMG and the SQL Server
- Test the connection
As a first step, we have to locate the SQL scripts which create the required fields, tables, views and other SQL elements. You can find the SQL scripts in the Forefront TMG installation directory. Copy the scripts to your SQL Server.
Figure 1: Locate the TMG .SQL scripts
The FWSRV.SQL file is for the Firewall logging, while the W3PROXY.SQL file is for the Web Proxy logging.
The following screenshot shows the content of the W3PROXY.SQL file.
Figure 2: TMG SQL script content
Next, start the SQL Server Management Studio application to create the databases for Firewall and Web Proxy logging.
Figure 3: Create a new database for SQL logging
The default database name for the Firewall logging is TMG-FWLOG. If you want to change the name you must also change the name of the database in the SQL script.
Figure 4: Specify location and other settings for the new database
Do the same for the Forefront TMG Web Proxy database.
Next, we must execute the SQL script from Forefront TMG to create the required tables, views and fields for the SQL Server database. Start the SQL Server Management Studio application and start a new query. Paste the entire SQL script into the query editor and execute the query. Do the same for the TMG Web Proxy database.
Figure 5: Execute the SQL script to create tables and more
After executing the query, check the results. For example navigate to the Columns tab and verify that there are new entries as shown in the following screenshot.
Figure 6: Database after script execution
Next, we must allow the TMG Server computer account access the SQL Server and the created databases. Because we are using Windows integrated authentication on the SQL Server we create a new login based on Windows user accounts, in this case the computer account of the TMG Server. Because you cannot browse for computer objects in the object picker of the SQL Server, you must manually enter the TMG Server computer account with the notation DOMAIN\Computername$ as shown in the following screenshot. Set the default database to the TMG-FWLOG database (optional) for example.
Figure 7: New Windows login for the Forefront TMG computer account
In the login properties for the new SQL login we must configure the user mapping so that the TMG Server computer account has the necessary permissions to access the SQL database(s).
Figure 8: Configure permissions for the account
After all requirements on the SQL Server are configured, we can change the Forefront TMG logging from local SQL Server Express to central SQL Server logging. Start the Forefront TMG MMC and navigate to the Logs & Reports node and in the Task pane Configure Firewall Logging or Configure Web Proxy Logging.
Click the radio button SQL database and click the options button.
Figure 9: Change TMG logging to central SQL logging
Enter the FQDN of the SQL Server, the port to use (default is 1433).
Make sure that SQL Server listens on port 1433 from remote connections.
Enter the name of the database previously created on the SQL Server and the name of the SQL table (created by the SQL script). For additional security it is also possible to enable the force Data encryption option but this requires additional settings. I will refer to it later.
Figure 10: Specify the SQL Server and additional parameters
After the configuration has been completed, you can click the Test button to test the SQL connection. After you click the OK button, Forefront TMG will inform you that a Forefront TMG system policy will be activated which allows a SQL connection from LOCAL HOST to the internal network. For security reasons you should limit the system policy to allow access to only the SQL Server.
Figure 11: Warning message that TMG system polices rules must be activated
One of the limitations of a central SQL Server logging is that from now onwards you cannot create Forefront TMG reports, so you have to create your own reports using the SQL Server utilities.
Figure 12: Warning message that no reports can be generated when central SQL logging is used
As previously said, it is possible to encrypt the data connection between the SQL Server and the TMG Server through the appropriate option.
Figure 13: Optional: It is possible to enable data encryption between TMG and SQL Server
If you want to enforce encryption between the SQL Server and the TMG Server (and all other servers accessing the SQL Server) you must force encryption in the SQL Server instance properties in SQL Server Management studio. If you don’t want to enforce encryption for all connections, you can leave the default setting unchanged, so the SQL Server will negotiate encryption with only clients which request encryption.
Figure 14: Force encryption on the SQL Server
You must use a computer certificate on the SQL Server which is used to create the secure channel between the SQL Server and the TMG Server. The certificate must be issued by a trusted certification authority (CA) which the TMG and SQL Server trust. For more information about configuring SQL Server for SSL encryption, please read the following article.
Figure 15: Select the appropriate certificate
In this article, I discussed how to reconfigure the local Forefront TMG SQL logging to a central Microsoft SQL Server. Central SQL logging has some pros like central management, backup and restore but also some cons like inability to use the reporting capabilities in Forefront TMG, so you have to create your own reporting with Microsoft SQL Server or third party tools.
- Configuring Forefront TMG logs
- Microsoft Forefront TMG – Logging options in Forefront TMG
- How to View TMG Logs when using SQL Server Express for Logging
- Relocating SQL Database Files on Forefront TMG 2010
- Firewall Logging using a Microsoft SQL database
- SQL Server encryption configuration