共用方式為

Issue with TMG remote SQL logging

  • 發行項

We recently received a case from a customer reporting that the TMG log data were not being properly stored in a remote SQL database but was accumulated in the Large Logging Queue (LLQ).

The LLQ is an improvement added in TMG, particularly useful in scenarios where logging to a remote SQL Server is involved.
This feature allow the logging to continue even if the database is unavailable: log data is stored in a local folder and will be replayed to the database once it becomes available again.
You can read more of this feature here: http://technet.microsoft.com/en-us/library/dd183731.aspx

In the case of our customer the database was available but TMG was logging to LLQ for some reason.
The alert we were getting on the console was:

Forefront TMG failed to connect to SQL Server for Forefront TMG Web filter logging. This failure may be due to a temporary condition, low resources, or inadequate permissions. SQL Server error description: Invalid or unknown table specified.

Connecting to SQL Server will be retried periodically. Until a connection is established, log records will be saved in a log queue on the disk on the local computer. Forefront TMG will continue to operate normally, but the log records in the log queue will not be available in the Forefront TMG log viewer. After a connection to SQL Server is established, the log records in the log queue will be moved to SQL Server and will be available in the log viewer.

The failure is due to error: Invalid or unknown table specified.

The status of the LLQ was particularly concerning, with several GB of data waiting to be committed to the database:

clip_image002

The error suggested Invalid or unknown table so we first checked the configured tables and their structure.
From the TMG console in Logs and reporting we have the current configuration:

clip_image003

Checking in SQL Server we have the tables with the expected names:

clip_image004

Next we checked the table structures.
The table definition files (fwsrv.sql and w3proxy.sql) are in the TMG installation directory which normally can be found in “C:\Program Files\Microsoft Forefront Threat Management Gateway”.

The structure of the current table in the database can also be exported to a text file from the SQL Server Management Studio interface:

clip_image006

Comparing the reference and the current table we found that the customer had added another column in the WebProxyLog table.
The extra column was supposed to cause no harm but in the internal tracing we found that TMG detects a different structure, not matching with any of the supported schemas, and therefore refuses to log into that table.

After removing the extra column the data from the LLQ were properly written to the database and everything resumed working correctly.

 

Author:
Gianni Bragante
Support Engineer - Microsoft CSS Forefront Security Edge Team

Reviewer:
Lars Bentzen
Escalation Engineer - Microsoft CSS Forefront Security Edge Team

Comments

  • Anonymous
    May 22, 2012
    I have table structure nerver changed, but After I updated to SP2, the same issue happened. If I uninstall sp2, everything is ok. what's wrong?