How to Enable ISA Server Logging to an Oracle Server


How to Enable ISA Server Logging to an Oracle Server
By Euticio Montelongo


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.


Exit Oracle installation.


Part II: Create Oracle user


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.


Modify the create user statement as needed. If you decide to change the user name be sure to modify all instances of the user name in all create statements. I would recommend changing the password and tablespace. I created a 2GB tablespace dedicated for ISA logging and assigned it to the ISA users default tablespace. The amount of logging information for me has been around 75-100MB a day.


Cut and paste the script below and save it to a file on your local hard drive. I choose c:\temp\isalog.sql.


——- CUT BELOW HERE ——-


CREATE USER “ISA” IDENTIFIED BY “ISA” DEFAULT TABLESPACE “USERS”


TEMPORARY TABLESPACE “TEMP” ACCOUNT UNLOCK;


 


GRANT “CONNECT” TO “ISA”;


 


CREATE TABLE ISA.FirewallLog (


ClientIP varchar2(32),


ClientUserName varchar2(32),


ClientAgent varchar2(128),


ClientAuthenticate varchar2(5),


logDate date,


logTime date,


service varchar2(12),


servername varchar2(32),


referredserver varchar2(32),


DestHost varchar2(255),


DestHostIP varchar2(32),


DestHostPort number,


processingtime number,


bytessent number,


bytesrecvd number,


protocol varchar2(8),


transport varchar2(8),


operation varchar2(8),


uri varchar2(255),


mimetype varchar2(32),


objectsource varchar2(12),


resultcode number,


CacheInfo number,


rule#1 varchar2(128),


rule#2 varchar2(128),


sessionid number,


connectionid number


);


 


CREATE INDEX ISA.IX_FirewallLog_Date ON ISA.FirewallLog(logDate);


 


CREATE INDEX ISA.IX_FirewallLog_DateTime ON ISA.FirewallLog(logDate,


logTime);


 


CREATE TABLE ISA.PacketFilterLog (


PFlogDate date,


PFlogTime date,


SourceAddress varchar2(25),


DestinationAddress varchar2(25),


Protocol varchar2(8),


Param#1 varchar2(8),


Param#2 varchar2(8),


TcpFlags varchar2(255),


FilterRule varchar2(16),


Interface varchar2(25),


IPHeader varchar2(255),


Payload varchar2(255)


);


 


CREATE INDEX ISA.IX_PacketFilterLog_Date ON


ISA.PacketFilterLog(PFlogDate);


 


CREATE INDEX ISA.IX_PacketFilterLog_DateTime ON


SA.PacketFilterLog(PFlogDate, PFlogTime);


 


CREATE TABLE ISA.WebProxyLog (


ClientIP varchar2(32),


ClientUserName varchar2(32),


ClientAgent varchar2(128),


ClientAuthenticate varchar2(5),


logDate date,


logTime date,


service varchar2(16),


servername varchar2(32),


referredserver varchar2(32),


DestHost varchar2(255),


DestHostIP varchar2(32),


DestHostPort number,


processingtime number,


bytesrecvd number,


bytessent number,


protocol varchar2(12),


transport varchar2(8),


operation varchar2(8),


uri varchar2(255),


mimetype varchar2(32),


objectsource varchar2(12),


resultcode number,


CacheInfo number,


rule#1 varchar2(128),


rule#2 varchar2(128)


);


 


CREATE INDEX ISA.IX_WebProxyLog_Date ON ISA.WebProxyLog(logDate);


 


CREATE INDEX ISA.IX_WebProxyLog_DateTime ON ISA.WebProxyLog(logDate,


logTime);


 


COMMIT;


—— CUT ABOVE HERE ——


Within SQL*Plus execute the SQL file as follow:


@c:\temp\isalog.sql


If you saved this file somewhere else or with a different name you’ll need to specify a different path or filename.


@<saved directory>\<filename>


Exit SQL*Plus.


Part III: Create Oracle System DSN


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 ODBC DataSource Administrator.


Start regedit and locate HKEY_LOCAL_MACHINE\Software\Odbc\Odbc.ini\ISAORA and create a string key called Password and fill in your user password. I have included the following registry sample below. You only need to modify the password.


—— CUT BELOW HERE ——


Windows Registry Editor Version 5.00


 


[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ISAORA]


“Password”=”isa”


—— CUT ABOVE HERE ——


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:


Click <Your server>->Monitoring Configuration->Logs. Double click Packet filters. Under the Log tab select Database. Fill in ISALOG for ODBC data source (DSN). Fill in PacketFilterLog for Table name. No need to type in a account since we have included the information under the DSN registry entry. Verify Enable logging for this service is checked. Click OK.


Configure ISA Server Firewall service Logging:


Click <Your server>->Monitoring Configuration->Logs. Double click ISA Server Firewall service. Under the Log tab select Database. Fill in ISALOG for ODBC data source (DSN). Fill in FirewallLog for Table name. No need to type in a account since we have included the information under the DSN registry entry. Verify Enable logging for this service is checked. Click OK.


Configure Packet Filter Logging:


Click <Your server>->Monitoring Configuration->Logs. Double click ISA Server Web Proxy Service. Under the Log tab select Database. Fill in ISALOG for ODBC data source (DSN). Fill in WebProxyLog for Table name. No need to type in a account since we have included the information under the DSN registry entry. Verify Enable logging for this service is checked. Click OK.


Feel free to select change the field logging for each service.


You have now successfully configured ISA to log to an Oracle Database.


Enjoy!


Be sure to watch your tablespace. ISA does a lot of logging.

Leave a Comment

Your email address will not be published.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Scroll to Top