Logging to a SQL Server

  • Article

Updated: July 31, 2012

Applies To: Unified Access Gateway

Forefront Unified Access Gateway (UAG) supports logging to a SQL Server database using the logging functionality provided by Forefront Threat Management Gateway (TMG). You can enable logging to a remote SQL server, or to a local SQL Server Express database running on the Forefront UAG server. After SQL Server logging is enabled, Forefront UAG events written to the Forefront UAG in-built logging mechanism are also written to the SQL Server log.

Configuring SQL Server logging consists of the following steps:

  1. Configuring logging to a local SQL Server database—By default, Forefront TMG logs to a local SQL Server Express database running on the Forefront UAG server. Follow this procedure to set up local SQL Server Express logging.

  2. Configuring logging to a remote SQL Server database—Follow the instructions in this procedure to log to a remote SQL Server database. Note that you must set up the database on the SQL Server computer. Forefront TMG provides two SQL scripts used to create the tables for recording the log data. For more information, see Setting up SQL Server for logging in the Forefront TMG TechNet library.

  3. Enabling SQL Server logging in Forefront UAG—Enable SQL Server logging on the Forefront UAG server.

  4. Viewing SQL Server logs—View Forefront UAG events logged to SQL Server in the Forefront TMG Management console.

Configuring logging to a local SQL Server database

Ensure that Forefront Threat Management Gateway (TMG) is configured to use SQL Server logging and add Forefront UAG fields to the log as follows.

To configure SQL Server logging in Forefront TMG

  1. Click Start, click All Programs, click Microsoft Forefront TMG, and then click Forefront TMG Management.

    If the Getting Started wizard appears automatically in the Forefront TMG Management console, close it.

  2. In the console tree, expand Forefront TMG, and then click Logs & Reports.

  3. In the details pane, click the Logging tab.

  4. On the Tasks tab, click Configure Web Proxy Logging.

  5. On the Logs tab, ensure that Enable logging for this service is selected.

  6. Click SQL Database, and then click Options.

  7. Select ISALogs to store logs in the default location. To store files in an alternative location, click This folder, and specify the path. There are a number of requirements when specifying an alternate location. For more information, see Configuring the log location, at Microsoft TechNet.

  8. Configure the log properties as follows:

    • Select Limit total size of log files and specify a maximum size. Each log file is limited to 1.5 GB. When a log file reaches 1.5 GB, a new file is automatically created.

    • Select Maintain free disk space and specify the free space.

    • Select Deleting older log files as necessary to specify that the oldest log files are deleted automatically in accordance with the specified size limits.

    • Select Discarding new log entries to stop logging new entries (while keeping all the old log information) in accordance with the specified size limits. New entries are not logged until you change limits or delete old files. An alert is issued to notify you of this event.

    • Select Delete files older than to delete log files older than the specified days. To delete old files from storage, decrease this number.

    • Select Compress log files to reduce log file size. Compression is only applied to log files stored on NTFS volumes.

  9. On the Fields tab, select the Forefront UAG fields to display in the SQL Server log, and then click OK.

Configuring logging to a remote SQL Server database

To configure logging to a remote SQL Server

  1. Click Start, click All Programs, click Microsoft Forefront TMG, and then click Forefront TMG Management.

    If the Getting Started wizard appears automatically in the Forefront TMG Management console, close it.

  2. In the console tree, expand Forefront TMG, and then click Logs & Reports.

  3. In the details pane, click the Logging tab.

  4. On the Tasks tab, click Configure Web Proxy Logging.

  5. On the Logs tab, ensure that Enable logging for this service is selected.

  6. Click SQL Database, and then click Options.

  7. In Database Connection Parameters, specify the SQL Server database details, as follows:

    1. In Server, type the name of the computer running SQL Server to which the information will be logged.

    2. In Port, type the port number to use. The default port of the computer running SQL Server is 1433.

    3. In Database, type the name of the database on the computer running SQL Server.

    4. In Table, specify a table name.

    5. Click Force data encryption to specify that a secure connection should be used between Forefront TMG and the SQL Server computer. This setting is enabled by default to help secure log file information. To use this setting, you must have a server certificate configured on the SQL Server computer, and a root certificate for the CA that issued the server certificate on the Forefront TMG server. For more information, see Encrypting connections to SQL Server, at Microsoft TechNet.

    6. In Authentication Details, select Use Windows authentication to authenticate to the SQL Server using the computer account, or select Use SQL server authentication to authenticate against SQL Server using a SQL Server account.

    7. In User and Password, type the credentials to be used. Ensure that the account has permissions to authenticate to the SQL Server computer.

    8. Click Test to verify connectivity to the SQL Server computer.

  8. On the Fields tab, select the Forefront UAG fields to display in the SQL Server log, and then click OK.

Enabling SQL Server logging in Forefront UAG

To enable SQL Server logging, run a script as follows

To enable SQL Server logging

  1. Open a command line prompt and navigate to the MonitorMgr folder of the Forefront UAG installation directory. If Forefront UAG is installed in Program Files, the folder is located as follows: Program Files\Microsoft Forefront Unified Access Gateway\utils\MonitorMgr\.

  2. At the command line, type the following:

    1. MonitorMgrUtil –setsqllogging 1, to enable SQL Server logging

    2. MonitorMgrUtil –setsqllogging 0, to disable SQL Server logging

    3. Note that instead of typing “setsqllogging” you can use the shorter abbreviation of “ssl”. For example MonitorMgrUtil –ssl 1, to enable SQL Server logging. In this case “ssl” is not an abbreviation for secure sockets layer.

  3. On the toolbar of the Forefront UAG Management console, click the Activate configuration icon, and then click Activate.

  4. Restart the Forefront UAG Monitor Manager service.

Viewing SQL Server logs

View Forefront UAG events logged to SQL Server in the Forefront TMG Management console, as follows.

To view SQL Server logs

  1. In the Forefront TMG Management console, in the console tree, click Logs & Reports.

  2. In the details pane, click the Logging tab.

  3. On the Tasks tab, click Edit Filter.

  4. In the Edit Filter dialog box, In Filter by, set Log Record Type to Web Proxy Filter. To save the filter definition, click Save Filter and specify a name for the .xml query file.

  5. Click Start Query. Query results are displayed in the Logging tab.

  6. To customize the fields displayed in the logging tab, right-click any column title header in the results list, and then click Add/Remove columns. In the Add/Remove Columns dialog box, add or remove columns as required. For a list of Forefront UAG-specific logging fields, see SQL Server logging fields in the Technical Reference.