Configuring ISA Server 2000 MySQL Database Logging by Brian Bailey
Configuring Microsoft ISA server to log to a MySQL database is a relatively easy task. This tutorial will walk you through it step by step.
Requirements for this tutorial include MS ISA Server (of course), MS Access 97, 2000, or 2002), and MySQL version 4.0.13 or higher (MySQL 3.23 and below was untested, and therefore may not be compatible with this tutorial).
Let’s take a quick overview of whats going to happen before we dive into the step by step. First, I bet you’re asking “I thought we were logging to MySQL, why in the world is Access required?”. Well, when ISA tries to log directly to the MySQL server, the sql insert queries from the ISA server do not match what the MySQL server is expecting. So, you get the beloved “Syntax Error” message from the MySQL server. This is where Access steps in. We’re going to use Access as a “middle-man” so to speak. Using linked tables in Access, we’re simply going to pass the ISA logs from the ISA server through Access and then to the MySQL database. See? Like I said, pretty easy. You may have noticed this same exact technique used in Euticio Montelongo’s tutorial on logging to an Oracle database. Ok, let’s get started!
Part One: Creating the MySQL database
First things first. We need to set up the MySQL datbase. The sql script below does everything for you. You may choose to change some of the variables in there, such as the database name or the MySQL user. Keep in mind though that the rest of this article assumes that you’re keeping the defaults.
Create file called ‘create_isa.sql’
Paste contents below into file ‘create_isa.sql’
On lines 8 and 12 the password is set for the user ‘isa_server’. Please replace the word password with your own password. The new password must be enclosed in single quotes.
—–Begin Copying—–
create database isa_mysql;
connect isa_mysql;
grant create, select, insert, update
on isa_mysql.*
to isa_server@localhost
identified by ‘password’;
grant create, select, insert, update
on isa_mysql.*
to isa_server@’%’
identified by ‘password’;
create table WebProxyLog (
ClientIP varchar(32),
ClientUserName varchar(32),
ClientAgent varchar(128),
ClientAuthenticate varchar(5),
logDate datetime,
logTime datetime,
service varchar(16),
servername varchar(32),
referredserver varchar(32),
DestHost varchar(255),
DestHostIP varchar(32),
DestHostPort int,
processingtime int,
bytesrecvd int,
bytessent int,
protocol varchar(12),
transport varchar(8),
operation varchar(8),
uri varchar(255),
mimetype varchar(32),
objectsource varchar(12),
resultcode int,
CacheInfo int,
`rule#1` varchar(128),
`rule#2` varchar(128)
);
create index IX_WebProxyLog_Date ON WebProxyLog(logDate);
create index IX_WebProxyLog_DateTime ON WebProxyLog(logDate, logTime);
create table FirewallLog (
ClientIP varchar(32),
ClientUserName varchar(32),
ClientAgent varchar(128),
ClientAuthenticate varchar(5),
logDate datetime,
logTime datetime,
service varchar(12),
servername varchar(32),
referredserver varchar(32),
DestHost varchar(255),
DestHostIP varchar(32),
DestHostPort int,
processingtime int,
bytessent float,
bytesrecvd float,
protocol varchar(8),
transport varchar(8),
operation varchar(8),
uri varchar(255),
mimetype varchar(32),
objectsource varchar(12),
resultcode int,
CacheInfo int,
`rule#1` varchar(128),
`rule#2` varchar(128),
sessionid int,
connectionid int
);
create index IX_FirewallLog_Date ON FirewallLog(logDate);
create index IX_FirewallLog_DateTime ON FirewallLog(logDate, logTime);
create table PacketFilterLog (
PFlogDate datetime,
PFlogTime datetime,
SourceAddress varchar(25),
DestinationAddress varchar(25),
Protocol varchar(8),
`Param#1` varchar(8),
`Param#2` varchar(8),
TcpFlags varchar(255),
FilterRule varchar(16),
Interface varchar(25),
IPHeader varchar(255),
Payload varchar(255)
);
create index IX_PacketFilterLog_Date ON PacketFilterLog(PFlogDate);
create index IX_PacketFilterLog_DateTime ON PacketFilterLog(PFlogDate, PFlogTime);
—–End Copying—–
Copy this file named create_isa.sql to your MySQL server.
Connect to your MySQL server as root
From the MySQL command prompt, type: source /path/to/create_isa.sql
You should be getting a whole bunch of messages saying “Query Ok, 0 rows affected”. If you’re not, verify the path to the create_isa.sql file. Ok, now that the MySQL database has been created we can move on!
Part Two: Create MySQL System DSN
First you’ll need the MyODBC software from mysql.com. This is located at http://www.mysql.com/downloads/api-myodbc.html.
Download the current production version. (Currently 3.51)
Install this on your ISA server, you shouldn’t have to reboot.
Create the System DSN:
Open ODBC Data Source Administrator (Control Panel -> Administrative tools -> Data Sources (ODBC)
Click on the System DSN tab and click Add
Select the MyODBC Driver and click Finish
The MySQL ODBC configuartion windows should appear
Enter isa_mysql in the Data Source Name field
Enter your MySQL server’s IP address in the host field
Enter isa_mysql in the Database name field
Enter isa_server in the user filed
Enter your password for user isa_server
Now click on the Test Data Source button
You should get a message that says the connection tested successfully.
Click OK twice.
Ok, now let’s go ahead and get Access configured!
Part Three: Create Access Database and System DSN
If its not already, install MS Access 97, 2000, 2002
Create a blank database called isa_access.mdb in your ISA server Installation directory.
Next, click on File -> Get External Data -> Link Tables
In the new Link window that opened up, select ODBC databases under Files of Type.
The Select Data Source window should open, select the Machine Data Source Tab.
Locate isa_mysql, and double-click on it.
There should be three entries here. One for the firewal log, the webproxy log, and the packetfilter log. Select all three and click ok.
A window called ‘Select Unique Identifer’ will pop up three times. Accept defaults for each one.
Ok! We’ve just linked our Access database to our MySQL database!
Exit MS Access
Start ODBC DataSource Administrator.
Create a System DSN for your Access database.
Select Microsoft Access Driver.
Name your Data Source Name isa_access.
Click Select and locate the isa_access.mdb database under your ISA installation directory.
Click OK twice.
Now you should have two system data sources, one for MySQL (isa_mysql) and one for Access (isa_access).
We’re finally ready to configure the ISA server…
Part Four: Configuring ISA Logging
Open the ISA mmc, expand Monitoring and Configuration, and select Logs
Double-click on ISA Server Web Proxy Service
Select Database.
In the ODBC field type in isa_access
Type WebProxyLog in the Tables field (case sensitive).
Make sure the “Use this account field” is blank.
Repeat previous steps for the packetfilter log, and the firewall log.
Table names are PacketFilterLog and FirewallLog
Ok what’s next…Wait! That’s it! Your ISA server should now be happily logging to your MySQL database!
I’d like to give a special thanks to Alin Selicean. He helped me out along the way, and without him I probably would have given up on this little project. Thanks Alin!
