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:
- Create the database, on the computer running SQL Server.
- 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.
- /E indicates a trusted connection.
Setting up SQL Server to accept the data connection
To set up SQL Server to accept the data connection from the Forefront TMG computer:
- Start Microsoft SQL Server Management Studio and connect to
your SQL instance.
- Click Security.
- 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).
- 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 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.
- 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:
- In the Forefront TMG console tree, right-click Firewall
Policy, and then click Edit System Policy.
- In the System Policy Editor, in the Configuration
Groups list, click Remote Logging (SQL).
- On the General tab, select Enable this configuration
group.
- 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.