Microsoft KB Archive/838253

= How to configure ISA Server 2004 logging to log data to a SQL database =

PSS ID Number: 838253

Article Last Modified on 11/24/2004

-

The information in this article applies to:


 * Microsoft Internet Security and Acceleration Server 2004, Standard Edition

-





For a Microsoft Internet Security and Acceleration Server 2000 version of this article, see 300211.



SUMMARY
''In Microsoft Internet Security and Acceleration (ISA) Server 2004, you can configure both the Firewall service log and the Web Proxy log to log their data to a Microsoft SQL Server database. To do this, you must add a new database to the SQL server, configure the appropriate ODBC connections, and then configure the ISA Server to permit remote SQL Server logging and to store the log data in the SQL server database. If ISA Server cannot connect to the SQL Server database, the Firewall service will not start.''



IN THIS TASK

 * INTRODUCTION
 * Set up a database file on the SQL server
 * Set up SQL Server to accept the Open Database Connectivity (ODBC) data connection from the ISA Server 2004 computer
 * Set up the ODBC data source on the ISA Server 2004 computer
 * Set up ISA Server 2004 to log on to the SQL database





INTRODUCTION
This step-by-step article describes how to configure Microsoft Internet Security and Acceleration (ISA) Server 2004 logging to log to a Microsoft SQL Server database. In ISA Server 2004, you can configure both Microsoft Firewall service logging and Web Proxy logging to log data to a SQL server.

back to the top

Set up a database file on the SQL server
 On the computer that is running SQL Server, start SQL Server Enterprise Manager. Connect to the SQL server that you want to use to host the database file. On the Tools menu, click SQL Query Analyzer. On the File menu, click Open. Locate and then double-click the Microsoft ISA Server folder on the ISA Server 2004 CD. (The Microsoft ISA Server folder is located in the :\FPC\Program Files\ folder.)</li> Open one of the following .sql files, based on the information that you want to log to the SQL database: <ul> For Firewall service logging, click the Fwsrv.sql file, and then click Open.</li> For Web Proxy logging, click the W3proxy.sql file, and then click Open.</li></ul> </li> Add the following lines to the top of the script:

<pre class="fixed_text">create database, and then expand Security.</li> Right-click Logins, and then click New Login.</li> If your SQL server is located in the same Windows Server 2003 domain or Windows 2000 Server domain as the ISA Server computer, follow these steps: <ol style="list-style-type: lower-alpha;"> On the General tab, click Windows Authentication.</li> In the Name box, type the following, where  is the name of your Windows Server 2003 or Windows 2000 Server domain and   is the name of your ISA Server computer:

\ $

Note This step does not work in a Windows NT 4.0 domain as you cannot specify a computer account.</li> Click the Database Access tab.</li> Click to select the check box next to the database that the ISA Server will log to, and then click OK.</li></ol>

If your SQL server is located on a different domain than the ISA Server computer, or you have a Windows NT 4.0 domain environment, follow these steps: <ol style="list-style-type: lower-alpha;"> On the General tab, click SQL Server Authentication.</li> In the Name box, type a SQL Server account user name. You do not have to type a specific name.</li> In the Password box, type a password.</li> Click the Database Access tab.</li> Click to select the check box next to the database that the ISA Server will log to, and then click OK.</li> Retype the password in the Confirm new password box, and then click OK.</li></ol> </li> <li>In the left pane, expand Databases, expand the ISA Server logging database name, and then click Tables.</li> <li>Right-click the table that will store the ISA Server data, and then click Properties. By default, the table name for Firewall service logging is FirewallLog and the table name for Web Proxy logging is WebProxyLog.</li> <li>On the General tab, click Permissions.</li> <li>For the  $ computer account or for the SQL Server account, click to select the SELECT check box and the INSERT check box, and then click OK two times.</li> <li>If you ran both .sql files to generate both the FirewallLog table and the WebProxyLog table, repeat steps 6 through 8 of this section for the second table.</li></ol>

back to the top

Set up the ODBC data source on the ISA Server 2004 computer

 * 1) On the ISA Server 2004 computer, click Start, point to Programs, point to Administrative Tools, and then click Data Sources (ODBC).
 * 2) Click the System DSN tab.
 * 3) Click Add.
 * 4) Click the SQL Server driver, and then click Finish.
 * 5) In the Name box, type the same name that you used for the database file.
 * 6) In the Description box, type a description or leave it blank.
 * 7) In the Server list, click or type the name of the SQL server where you created the ISA Server logging database, and then click Next.
 * 8) There are two options for database authentication. These options correspond to the account that you set up in step 4 of the &quot;Set up SQL Server to accept the Open Database Connectivity (ODBC) data connection from the ISA Server 2004 computer&quot; section.
 * 9) * 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 Windows Server 2003 or a Windows 2000 Server domain.
 * 10) * Click With SQL Server authentication using a login ID and password entered by the user to use a SQL account for authentication. If you click this option, type the appropriate logon name and password in the Login ID and the Password boxes.
 * 11) Click Next.
 * 12) Click to select the Change the default database to check box, click the ISA Server logging database in the list, click Next, and then click Finish.
 * 13) Click Test Data Source to make sure that you can successfully connect to the SQL database, and then click OK three times.

back to the top

Set up ISA Server 2004 to log on to the SQL database

 * 1) On the ISA Server 2004 computer, click Start, point to Programs, point to Microsoft ISA Server, and then click ISA Server Management.
 * 2) Expand , where   is the name of your ISA Server computer, and then click Monitoring.
 * 3) In the middle pane, click the Logging tab.
 * 4) In the right pane, click the log that you want to configure. Click either Configure Firewall Logging or click Configure Web Proxy Logging.
 * 5) On the Log tab, click SQL database.
 * 6) In the ODBC data source name (DSN) box, type the data source name (DSN) that you created earlier.
 * 7) In the Table name box, type the appropriate table name. By default, the table name for the Firewall service is FirewallLog and the table name for Web proxy is WebProxyLog.
 * 8) If you used the With Windows NT authentication using the network login ID option when you configured the ODBC data source, you can leave the Use this account box blank. In this case, if you type an account name, the credentials will be ignored for the specific log.

If you used the With SQL Server authentication using a login ID and password entered by the user option when you configured the ODBC data source, you must click Set Account, next to the Use this account box. Type the user name and password, and then click OK.
 * 1) Click OK.
 * 2) If you want to configure both the Firewall service log and the Web Proxy log to log their data to the SQL database, repeat steps 3 through 8 of this section for the second log.
 * 3) In the left pane, click Firewall Policy.
 * 4) In the right pane, click the Tasks tab, and then click Edit System Policy.
 * 5) In the Logging configuration group, click Remote Logging (SQL), click to select the Enable check box, and then click OK.
 * 6) Click Apply to save the changes and to update the firewall policy.
 * 7) After the configuration changes are applied, click OK.

Note After you configure ISA Server logging to a SQL server, if the ISA Server computer cannot contact or log on to the SQL server. The Firewall service cannot start, and it logs an error message in the application event log.

back to the top

Keywords: kbHOWTOmaster kbFirewall kbinfo KB838253

Technology: kbAudITPro kbISAS2004Search kbISAS2004Std kbISAServSearch

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.