Microsoft KB Archive/838710

= How to configure ISA Server 2004 and ISA Server 2006 to log data to an SQL Server database =

Article ID: 838710

Article Last Modified on 12/4/2007

-

APPLIES TO


 * Microsoft Internet Security and Acceleration Server 2004 Standard Edition
 * Microsoft Internet Security and Acceleration Server 2004 Enterprise Edition
 * Microsoft Internet Security and Acceleration Server 2006 Enterprise Edition
 * Microsoft Internet Security and Acceleration Server 2006 Standard Edition

-



SUMMARY
This article describes how to configure Microsoft Internet Security and Acceleration (ISA) Server to log on to a Microsoft SQL Server database and to save the ISA Server log information to an SQL Server database.



IN THIS TASK

 * INTRODUCTION
 * How to create a log database and tables in SQL Server
 * How to set up SQL Server to accept the Open Database Connectivity (ODBC) from the ISA Server
 * How to set up the ODBC data source on the ISA Server computer
 * How to configure ISA Server to log information to an SQL Server database



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

back to the top

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:  Start SQL Server Enterprise Manager. Expand Microsoft SQL Servers. Connect to the SQL Server that you want to host the database files. On the Tools menu, click SQL Query Analyzer. On the File menu, click Open. 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.</li> For the Web Proxy service, open the W3proxy.sql file.</li></ul>

Note These files are available on your ISA Server CD-ROM at the following location:

ISA\FPC\Program Files\Microsoft ISA Server

</li>  Type the following lines at the top of the script: Create database  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. </li> On the Query menu, click Execute.</li> Quit Query Analyzer.</li> Press F5 to update.</li></ol>

back to the top

How to set up SQL Server to accept the Open Database Connectivity (ODBC) from the ISA Server
To set up SQL Server to accept the ODBC data connection from the ISA Server computer, follow these steps: <ol> Start SQL Server Enterprise Manager.</li> Expand Microsoft SQL Servers.</li> Connect to the SQL Server that you want to host the database files.</li> Expand your SQL Server.</li> Expand Security, and then right-click Logins.</li> Click New Login.</li> 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. <ul> ==== Windows authentication ====

<ol style="list-style-type: lower-alpha;"> Type in the Name box, where   is the name of your domain, and where   is the NetBIOS name of your ISA Server .</li> Click the Database Access tab.</li> <li>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.</li></ol> </li> <li>==== SQL Server authentication ====

<ol style="list-style-type: lower-alpha;"> <li>Type a name to identify the logon method in the Name box.</li> <li>Click the Database Access tab.</li> <li>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.</li></ol> </li></ul> </li> <li>Under Database roles for , click to select the db_datareader and the db_datawriter check boxes.</li> <li>Click OK.</li> <li>Quit SQL Server Enterprise Manager.</li> <li>Click Start, point to Programs, point to Administrative Tools, and then click Services.</li> <li>Right-click the MSSQLSERVER service, and then click Restart.</li></ol>

back to the top

How to set up the ODBC data source on the ISA Server computer
To set up ODBC data source on the ISA Server 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.
 * 1) Type a description in the Description box.
 * 2) Click the SQL Server that you want to connect to in the Server list.
 * 3) Click Next.
 * 4) The SQL Server uses the following two options to verify the authenticity of the login ID:
 * 5) * 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.
 * 6) * 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.
 * 1) 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.
 * 1) Click Next, and then click Finish.

back to the top

How to configure ISA Server to log information to an SQL Server database
To configure ISA Server to log information to an 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:
 * 5) * Click Configure Firewall Logging to configure the location of the Firewall log.
 * 6) * Click Configure Web Proxy Logging to configure the location of the Web Proxy log.
 * 7) Click the Log tab, and then click SQL Database.
 * 8) Type the DSN in the ODBC data source (DSN) box.
 * 9) 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 with the .sql script.
 * 1) 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.
 * 1) Click OK.
 * 2) In the ISA MMC, click Apply to save the changes made to ISA Server.
 * 3) Restart the ISA Server 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.

back to the top

Keywords: kbhowto kbhowtomaster kbisa2006swept KB838710

-

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

© Microsoft Corporation. All rights reserved.