How to configure ISA Server 2004, ISA Server 2006, and Microsoft Forefront Threat Management Gateway, Medium Business Edition to log data to an SQL Server database

This step-by-step article describes how to configure ISA Server to save log information that is in a SQL Server database.

How to create a log database and tables in SQL Server

You must have a separate log database and separate tables for the Microsoft Firewall service and for the Web proxy application filter in the SQL Server to save ISA Server log information to an SQL Server database.

The ISA Server CD-ROM includes both the Fwsrv.sql script and the W3proxy.sql script to help create databases and tables in SQL Server.

To create both databases and tables in SQL Server, follow these steps:

1. Start SQL Server Enterprise Manager.
2. Expand Microsoft SQL Servers.
3. Connect to the SQL Server that you want to host the database files.
4. On the Tools menu, click SQL Query Analyzer.
5. On the File menu, click Open.
6. Open one of the following .sql files, depending on the ISA Server service that you want to set up to log to aSQL database:
   • For the Microsoft Firewall service, open the Fwsrv.sql file.
   • For the Web Proxy service, open the W3proxy.sql file.
   Note These files are available on your ISA Server CD-ROM at the following location:
   ISA\FPC\Program Files\Microsoft ISA Server
7. Type the following lines at the top of the script:

   Create database <database name>
   Go
   Use <database name>
   Go
   

   Note<database name> is the name that you assign to the SQL database. The ISA Server service that you set up will log data to this SQL database.
8. On the Query menu, click Execute.
9. Quit Query Analyzer.
10. Press F5 to update.

How to set up SQL Server to accept the Open Database Connectivity (ODBC) from the ISA Server or from Microsoft Forefront Threat Management Gateway, Medium Business Edition

To set up SQL Server to accept the ODBC data connection from the ISA Server computer, follow these steps:

1. Start SQL Server Enterprise Manager.
2. Expand Microsoft SQL Servers.
3. Connect to the SQL Server that you want to host the database files.
4. Expand your SQL Server.
5. Expand Security, and then right-click Logins.
6. Click New Login.
7. If your SQL Server is located in the same domain as the ISA Server , you can log on to an SQL Server by using any one of the following methods.

   • Windows authentication

     1. Type <domainname>\<isaservername>$ in the Name box, where <domainname> is the name of your domain, and where <isaservername> is the NetBIOS name of your ISA Server .
     2. Click the Database Access tab.
     3. Click the databases that you created using the Fwsrv.sql and W3proxy.sql scripts.
        
        Note The Windows authentication method does not work in a Microsoft Windows NT 4.0 domain.

   • SQL Server authentication

     1. Type a name to identify the logon method in the Name box.
     2. Click the Database Access tab.
     3. Click to select the databases that you created by running the Fwsrv.sql and the W3proxy.sql scripts.
        
        Note If your SQL Server is not in the same domain as your ISA Server , use the SQL Server authentication method.
8. Under Database roles for <databasename>, click to select the db_datareader and the db_datawriter check boxes.
9. Click OK.
10. Quit SQL Server Enterprise Manager.
11. Click Start, point to Programs, point to Administrative Tools, and then click Services.
12. Right-click the MSSQLSERVER service, and then click Restart.

How to set up the ODBC data source on the ISA Server or Microsoft Forefront Threat Management Gateway, Medium Business Edition computer

To set up ODBC data source on the ISA Server or Microsoft Forefront Threat Management Gateway, Medium Business Edition computer, follow these steps:

1. Click Start, point to Programs, point to Administrative Tools, and then click Data Sources (ODBC).
2. Click the System DSN tab, and then click Add.
3. Under Select a driver for which you want to set up a data source, click SQL Server.
4. Click Finish.
5. Type the data source in the Name box.
   
   Note Data Source Name (DSN) is the name that you assign the SQL database.
6. Type a description in the Description box.
7. Click the SQL Server that you want to connect to in the Server list.
8. Click Next.
9. The SQL Server uses the following two options to verify the authenticity of the login ID:
   • Click With Windows NT authentication using the network login ID to use the ISA Server computer account for authentication. This option can only be used in a Microsoft Windows 2000 domain.
   • Click With SQL Server authentication using a login ID and password entered by the user to use an SQL account for authentication.
     
     Note Click the option that corresponds to the account that you set up to log on to the SQL Server.
10. Click to select the Change the default database to check box, and then type the DSN that the ISA Server will connect to.
    
    Note Use the same DSN that you assigned in step 5 earlier in this article.
11. Click Next, and then click Finish.

How to configure ISA Server or Microsoft Forefront Threat Management Gateway, Medium Business Edition to log information to an SQL Server database

To configure ISA Server 2004 to log information to a SQL Server database, follow these steps:

1. Start the ISA Microsoft Management Console (MMC).
2. Expand your ISA Server, and then click Monitoring.
3. Click the Logging tab.
4. On the Task pane, click the Tasks tab, and then select the appropriate task:
   • Click Configure Firewall Logging to configure the location of the Firewall log.
   • Click Configure Web Proxy Logging to configure the location of the Web Proxy log.
5. Click the Log tab, and then click SQL Database.
6. Type the DSN in the ODBC data source (DSN) box.
7. Type the name of the table in the Table name box.
   
   Note The information that is in the Table Name box is the name of the table that you created in SQL Server by using the .sql script.
8. Type the account that will be used to log on to the SQL Server in the Use this account box.
   
   Note If you want to change the user account, click Set Account, type the user name in the User box, type the password in the Password box, and then retype the password in the Confirm password box. If you use Microsoft Windows NT authentication, leave this box blank.
9. Click OK.
10. In the ISA MMC, click Apply to save the changes that you made to ISA Server .
11. Restart the ISA Server or Microsoft Forefront Threat Management Gateway, Medium Business Edition computer.

Note The system policy rule Allow remote Logging using NetBIOS transport to trusted servers must be turned on to log to an SQL database.

To configure ISA Server 2006 to log information to a SQL Server database, follow these steps:

1. Start the ISA Microsoft Management Console (MMC).
2. Expand the ISA Server, and then click Monitoring.
3. Click the Logging tab.
4. On the Task pane, select the appropriate task:
   • Click Configure Firewall Logging to configure the location of the Firewall log.
   • Click Configure Web Proxy Logging to configure the location of the Web Proxy log.
5. Click the Log tab, click SQL Database, and then click Options.
6. Type the server name in the Server box, type the DSN in the Database box.
7. Type the name of the table in the Table name box.
   
   Note The information that is in the Table Name box is the name of the table that you created in SQL Server by using the .sql script.
8. Type the account that will be used to log on to the SQL Server in the User and Password boxes.
9. Click OK two times.
10. In the ISA MMC, click Apply to save the changes that you made to ISA Server.
11. Restart the ISA Server computer.

Note The Allow remote Logging using NetBIOS transport to trusted servers system policy rule must be turned on to log to an SQL database.