How to Enable ISA Server Logging to an Oracle Server.
Part I: Install Oracle Client Software on ISA server.
You need to install Oracle Client Software on your local ISAserver. The SQLnet layer is needed for ODBC connectivity to work. You can install the minimum requirements since all you need is the SQLnet layer and Oracle ODBC driver. If you use custom install verify that Oracle's ODBC driver is included. However, Microsoft's Oracle ODBCdriver should work as well.
Start SQL*Plus from your ISA Server. You need to logon with an account that has create users, tables, and indexes priviledges. I used my system account. Of course, you can perform this operation on any computer with access to your Oracle database.
CREATE TABLE ISA.FirewallLog (
CREATE TABLE ISA.PacketFilterLog (
CREATE INDEX ISA.IX_PacketFilterLog_DateTime ON
CREATE TABLE ISA.WebProxyLog (
CREATE INDEX ISA.IX_WebProxyLog_DateTime ON ISA.WebProxyLog(logDate,
Start ODBC DataSource Administrator located in the Administrator folder. Create a System DSN for your Oracle database. I used Oracle's ODBC Driver. Name your Data Source Name ISAORA and be sure to include the UserID (Oracle) or Username (Microsoft Oracle).
Exit registry editor.
Part IV: Install and configure translation gateway
Fancy phrase for using Microsoft Access. You'll need to install Microsoft Access 97, 2000 or 2002. Once installed, create a blank database called isalog.mdb within your ISA server installation directory.
C:\Program Files\Microsoft ISA Server\isalog.mdb
Open isalog.mdb if you haven't already.
We'll be using Microsoft Access as our translation gateway between our ISA server and Oracle by linking the user isa tables.
Click File->Get External Data->Link Tables.
Under Link dialog box select ODBC databases under the Files of Type. This will start the Select Data Source dialog.
Click Machine Data Source and double click ISAORA. If you are prompted for a password then you have incorrectly modified the Password registry entry and need to repeat the last step before you can proceed.
Select ISA.FIREWALLOG, ISA.PACKETFILTERLOG and ISA.WEBPROXYLOG. Click OK. You will be prompted to select a unique record identifier, just click OK for each table. You will now have three linked tables named ISA_FIREWALLLOG, ISA_PACKETFILTERLOG, and ISA_WEBPROXYLOG. If you accidently included other tables you can safely delete them. Right click and select delete.
Now you will need to rename each table. Remove the prefix "ISA_" from each table. Right click and select rename. You will now have three tables named FIREWALLLOG, PACKETFILTERLOG, WEBPROXYLOG. When you are finished you should only have three linked tables in this database.
Exit Microsoft Access.
Part V: Create Access System DSN
Start ODBC DataSource Administrator located in the Administrator folder. Create a System DSN for your Access database. Select Microsoft Access Driver. Name your Data Source Name ISALOG. Click Select and locate the isalog.mdb database under C:\Program Files\Microsoft ISA Server. Click OK. Click OK. When you are finished you should have two System Data Sources, called ISAORA and ISALOG. ISAORA using the Oracle ODBC driver or Microsoft Oracle driver and ISALOG using Microsoft Access driver.
Exit ODBC DataSource Administrator.
Part VI: Test translation gateway
Oracle provides an ODBC testing tool located under Start->Programs->Oracle->Network Administration. Click Oracle ODBC Test. Click Connect. Click Machine Data Source. Click ISALOG. Be sure to select ISALOG, we need to test from Access side of things. Click OK. Within the dialog box below the connect button type "select count(*) from webproxylog" without the quotes and click execute. You should get result of zero. If you are prompted for a password then you will need to verify that you have correctly included the Password registry entry. We have just verified that Access is able to communicate with Oracle.
Part VII: Configure ISA Logging. - Finally
Start the ISA Management console.
Configure Packet Filter Logging:
Configure ISA Server Firewall service Logging:
Configure Packet Filter Logging:
Feel free to select change the field logging for each service.
You have now successfully configured ISA to log to an Oracle Database.