We help IT Professionals succeed at work.

Using Microsoft Access to Report on ISA Server 2004 Log Files

Does anyone know how to link Access 2003 to the ISA Server 2004 .mdf log files?

Environment:
--Small Business Server 2003 R2 with ISA Server 2004
--SQL Server 2005 Express
--2nd Computer - Server 2003 R2 Standard

I would like to link to the ISA Server 2004 log files with Access 2003.
I have managed to create an OBDC connection to what I believe is the SQL Server but the available (visible) tables do not appear to contain ISA log data.

The data I want is contained in the folder = C:\Program Files\Microsoft ISA Server\ISALogs\???.mdf files.


Any help wpuld be appreciated.
Comment
Watch Question

Bill RossProgrammer

Commented:
Hi,

These are SQL server db fiels so you can run SQL server and link MS Access to the SQL server instance once the files are on line as a SQL database.

Bill.

Author

Commented:
Thanks for the response Bill.

I agree - they are SQL Server 2005 Express database files created by ISA Server 2004.
My problem is two fold:
1) The ISA .mdf databases are not visible via the Microsoft SQL Server Management Studio Express console
2) I have successfully created a Access-->SQL OBDC connection - but the .dbo tables do not contain the ISA log data.

If I had to guess, it seems like the ISA SQL databases are (somehow) hidden.

So the problem seems to be how/if the ISA databases can be made available - unless I am really missing something, which is certainly possible due to my inexperience with SQL & ISA Server...
You need to attach your mdf files to SQL with Management Studio.
When log is reached limits (in ISA logging properties) it is closed and new mdf is created.
Enterprise Architect
Top Expert 2008
Commented:
You are correct, they are effectively hidden. ISA uses one of three methods for logging. You can either use text-based log files, one for each service; MSDE in which case ISA stops other 'management tools' from accessing (seeing) the instance or you can get ISA to write directly to a different SQL system. In the third instance, you can then use SQL query and such in the normal way. This is by design.



 

Author

Commented:
Thanks Keith for the response - I was looking for confirmation from someone with more knowledge/experience of ISA than I had.  I will award you the points.

Cleanup Item:
I am going to post a 2nd & 3rd EE Question that are related but different - maybe you can take a look at these also...

FYI - 2nd Question (ISA Server 2004 Configuration):
-----------------------------------------------------------------
What does it mean when the ISA Server 2004 configuration for logs = MSDE but there is no indication an instance of MSDE is installed/running?
I can only see one instance of a database running:  Microsoft SQL Server Workgroup Edition (9.0.4053 SP3) - which is part of SBS Premium.


FYI - 3nd Question (SQL - What Versions Installed/Running):
---------------------------------------------------------------------------
How can I determine (for sure) all of the SQL databases (especially MSDE & SQL Express) installed/running on an SBS Premium machine?
I can only see one instance of a database running:  Microsoft SQL Server Workgroup Edition (9.0.4053 SP3) - which is part of SBS Premium.  I have looked around (services, programs, tools, etc.) but only see one instance.
The reason for my question is that (based on a SBS program's configuration), there should be an instance of MSDE installed/running.

Keith AlabasterEnterprise Architect
Top Expert 2008

Commented:
As per my first comment, the ISA services effectively hide the msde instance used for its logging (assuming you are using the MSDE option as opposed to straign textual log files of course). You cannot access that instance through other services such as sql query etc, only through the ISA real time log monitor (isa gui - monitoring - logging) or the ISA inbuilt reports.