Setting up SQL Server for logging

Updated: February 1, 2011

Applies To: Forefront Threat Management Gateway (TMG)

This topic describes how to use a remote SQL Server database for Forefront TMG logging.

Setting up an SQL server for logging involves the following procedures:

  • 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. See Creating a log database and tables.

  • Configuring a data connection to the database. See Setting up SQL Server to accept the data connection.

  • Configuring an encrypted connection to the database. See Setting up an encrypted connection.

  • Configuring Forefront TMG system policy rules to allow a connection to the remote SQL server. See Configuring system policy rules for SQL Server logging.

Creating a log database and tables

Sample scripts are provided to create databases for the Microsoft Firewall service log and the Web Proxy traffic log. The scripts are located in the Forefront TMG installation folder directory.

To set up SQL Server with the database:

  1. Create the database, on the computer running SQL Server.

  2. At the command prompt, type the following:

    sqlcmd –E –S InstanceName –i “Path\ScriptFile" –d <dbName>

    Where:

    • /E indicates a trusted connection.

    • /S indicates the server.

    • InstanceName is the database instance.

    • /i indicates the input file.

    • Path is the path to the Forefront TMG installation.

    • ScriptFile is the name of the database script file, either Fwsrv.sql for the Microsoft Firewall service log, or W3proxy.sql for the Web Proxy traffic log.

    • /d indicates the database file.

    • dbName the log database in which to create the tables.

Setting up SQL Server to accept the data connection

To set up SQL Server to accept the data connection from the Forefront TMG computer:

  1. Start Microsoft SQL Server Management Studio and connect to your SQL instance.

  2. Click Security.

  3. To create a login, right-click Logins, click New Login and then 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 with user credentials (this is the recommended way to use Windows Authentication), create a login based on an existing user or use an existing login. In Name, type a name that identifies the logon method. On the Database Access tab, select the databases that this logon method can access (the databases you created in the previous procedure).

    • To use Windows Authentication without user credentials (that is, using the machine account), 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).

  4. To use an existing login, right-click the login you wish to use, select Properties. On the Database Access page of the Login Properties dialog box, select the row containing the database.

  5. 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.

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.

Configuring system policy rules for SQL Server logging

In order to log on to an SQL database, the remote logging system policy configuration group must be enabled.

To enable the remote logging system policy configuration group:

  1. In the Forefront TMG console tree, right-click Firewall Policy, and then click Edit System Policy

  2. In the System Policy Editor, in the Configuration Groups list, click Remote Logging (SQL).

  3. 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. It is recommended that you modify the destination to include only the SQL Server computer.

Concepts

Configuring Forefront TMG logs