Setting up SQL Server for logging

  • Article

This topic describes the procedures required to use a remote SQL Server database for Forefront TMG logging. The following steps are described:

  • Creating a database and tables for Firewall and Web Proxy logging. If Forefront TMG is not located in the same domain, you must set up a SQL Server account.
  • Configuring a data connection to the database
  • Configuring an encrypted connection to the database
  • Configuring Forefront TMG system policy rules to allow a connection to the remote SQL server.

Creating a log database and tables

Set up SQL Server with the database as follows:

  1. On the computer running SQL Server, start SQL Server Enterprise Manager.

  2. Expand Microsoft SQL Servers, and connect to the computer running SQL Server that you want to host the database files.

  3. On the Tools menu, click SQL Query Analyzer.

  4. On the File menu, click Open.

  5. In the program files directory, open the Forefront TMG installation folder. Open the following sample scripts provided by Forefront TMG to create a database table for each type of log:

    • To log traffic handled by the Microsoft Firewall service, open the Fwsrv.sql file.
    • For to log Web Proxy traffic, open the W3proxy.sql file.

  6. Create a database. To do this, add the following lines to the top of each script:

    Use <database name>Go

  7. On the Query menu, click Execute.

  8. Close SQL Query Analyzer and press F5 to update settings.

  • To log traffic handled by the Microsoft Firewall service, open the Fwsrv.sql file.
  • For to log Web Proxy traffic, open the W3proxy.sql file.

Setting up SQL Server to accept the data connection

To set up SQL Server to accept the data connection from the Forefront TMG computer, perform the following procedure:

  1. Start SQL Enterprise Manager.
  2. Expand Microsoft SQL Servers, and connect to the computer running SQL Server that you set up to host your databases.
  3. Click Microsoft SQL Servers, click SQL Server Group, click the server name, click Security.
  4. Right-click Logins, and then click New Login.
  5. Configure authentication. If the SQL Server computer is located in the same domain as Forefront TMG, you can log on to the SQL Server computer using either Windows authentication or SQL Server authentication. If the SQL Server is located in a different domain. You must use SQL Server authentication. Configure as follows:
    • To use Windows Authentication, in Name, type domainname\TMGname$, where "TMGname" is the NetBIOS name of the Forefront TMG server. On the Database Access tab select the databases that this logon method can access (the databases that you created in the previous procedure).
    • To use SQL Server Authentication, in Name, type a name that identifies the logon method and enter a password for the method. On the Database Access tab select the databases that this logon method can access (the databases you created in the previous procedure).
  6. In Database roles for <databasename>, select the db_datareader (SELECT permissions) and db_datawriter (INSERT) check boxes. In addition, grant db_executor (EXECUTE permissions) for the sp_batch_insert procedure for Forefront TMG services logging to this database.
  7. Click OK, and close SQL Enterprise Manager.
  8. Restart the MSSQLSERVER service.

Setting up an encrypted connection

By default, Forefront TMG uses an HTTPS connection to the SQL Server computer, to help secure the sensitive data in the log files. To use an encrypted connection, you must set up a certificate on the SQL Server computer, and install a root certification authority (CA) certificate on the Forefront TMG computer. For more information, see Encrypting connections to SQL Server, at Microsoft TechNet.

Configure system policy rules for SQL Server logging

The remote logging system policy configuration group must be enabled to log on to an SQL database. Do this as follows:

  1. In the Forefront TMG console tree, right-clic Firewall Policy, and then click Edit System Policy. 
  2. In the System Policy Editor, do the following:
  3. In the Configuration Groups list, click Logging. On the General tab, select Enable this configuration group.
  4. This rule assumes that the SQL Server is located in the default Internal network. To change the rule destination, click the To tab, and edit the destination accordingly. We recommend that you modify the destination to include only the SQL Server computer.