locked
Forefront TMG Remote Logging and Reporting RRS feed

  • Question

  • Hello,

    I have recently been involved in the implementation of a new Forefront TMG server array as part of an upgrade to our web proxy services. As the web filtering is now performed by TMG rather than SurfControl and our system outputs approximately 10GB per day of logs, we have implementing a dedicated SQL Server 2008 server to send the logs too.

    OUR SITUATION

    We have successfully configured our TMG server arrays to log all "Firewall Logging" and "Web Proxy Logging" data into our external SQL Server 2008 databases, however this now means we are unable to run any reports from within the TMG Enterprise Management Console. We are now looking to "copy" all the default reports into the dedicated reporting server as a starting point before creating a series of our own custom reports.

    So far I have managed to extract all the .rdl report templates from a node in our development array and copied them over to our SQL server, however these reports use a series of stored procedures from the default "ISARS" database instance (installed by TMG on each array node) which I am unable to even query, let alone export the data from.

    If anyone has any information on how to do this, or knows of a better way to enable all the default TMG reporting functions when utilising an external logging database I would be extremely grateful!

    Thank you

    KevinR
    Friday, January 22, 2010 4:01 PM

Answers

  • Hi,

     

    Thank you for the post.

     

    Before going any further, I’d like to know the meaning of “however this now means we are unable to run any reports from within the TMG Enterprise Management Console”? Do you have set the remote SQL Server as your Report Server?

     

    Please refer this article to set up a remote SQL database for TMG.

    http://technet.microsoft.com/en-us/library/bb794867.aspx

     

    Regards,


    Nick Gu - MSFT
    Wednesday, January 27, 2010 7:05 AM
    Moderator

All replies

  • Further to my previous post, I have found out the following:

    In addition to the "MSFW" default database instance used to store configuration settings and log files, TMG installs an additional database instance called "ISARS" which is used to store all the reporting service configuration data, including 2 standard databases for core reporting service functions and an additional encrypted database (called ISA_RS_Db).

    The "ISA_RS_Db" database appears to contain all of the report definitions and stored procedures needed to create the standard built-in TMG reports. As the database is encrypted and uses a predefined username/password combination specified during installation, I don't think it's possible to copy or move this database to another server (i.e. the dedicated SQL Reporting Server) so while I was able to download the .rdl report templates from one of the array nodes, it does not appear to be possible to use them without access to the "ISA_RS_Db" database.

    Looking around at 3rd party solutions, the "ISA Server (Forefront TMG) Toolkit" from Redline Software seems to have some handy tools in it, but still doesn't help with my reporting issue. Hopefully Microsoft have (or will) release a toolkit to enable us to get around these hurdles, but so far I'm unable to find anything that will help and it looks like I may have to write the reports from scratch.

    If anyone has any other ideas, thoughts or suggestions please post on this thread and I will continue to update my findings here too...
    Monday, January 25, 2010 3:02 PM
  • Hi,

     

    Thank you for the post.

     

    Before going any further, I’d like to know the meaning of “however this now means we are unable to run any reports from within the TMG Enterprise Management Console”? Do you have set the remote SQL Server as your Report Server?

     

    Please refer this article to set up a remote SQL database for TMG.

    http://technet.microsoft.com/en-us/library/bb794867.aspx

     

    Regards,


    Nick Gu - MSFT
    Wednesday, January 27, 2010 7:05 AM
    Moderator
  • Hi there.

    This seems to be a strange limitation to Forefront. We are currently trying to implement TMG 2010 in our corporation, but this exact reporting issue has been slowing the process down. Local sql express server works fairly well, but given the fact that weekly reports are required by the administration, we need a disaster recovery plan for a sql failure. The straightfoward solution was to enable logging to a external sql cluster, but then, reports are gone. Completely.

    Is there any update regarding a solution from MS to allow reporting with an external sql database?

    Regards.

    Wednesday, October 13, 2010 1:31 PM