Archive

Archive for the ‘Logging’ Category

How to configure the TMG Service Account to avoid problem with logging on SQL Server

One of the features introduced with TMG Service Pack 2 is to run the Firewall Service with a Domain account, this allow users to authenticate with Kerberos when using NLB.
Find more information about this feature here: http://technet.microsoft.com/en-us/library/hh454304.aspx

However you should pay attention when specifying the account name to avoid problems with logging to SQL Server, either local or remote.

The account specified is used by TMG to configure the service and also to create the Login in SQL Server.
For the TMG Firewall service to start any format is fine, but for SQL Server only the format domainName\loginName is valid.

For example if you want to use the account TMGSvc in the domain CONTOSO you have to enter CONTOSO\TMGSvc.

clip_image001

Using the UPN (User Principal Name) format or the FQDN (Fully Qualified Domain Name) does not work.
For example you cannot use TMGSvc@Contoso.com or Contoso.com\TMGSvc

The SQL Server documentation for the CREATE LOGIN command has the following note:

"When you are creating logins that are mapped from a Windows domain account, you must use the pre-Windows 2000 user logon name in the format [<domainName>\<loginName>]."

If you try using an invalid format you will see the Log Status as Disconnected and your LLQ folder growing:

clip_image002

 

Author:
Gianni Bragante
Support Engineer – Microsoft Forefront Edge Security Team

Reviewer:
Lars Bentzen
Sr. Escalation Engineer – Microsoft Forefront Edge Security Team

Categories: Logging, sql, TMG Tags:

How to configure the TMG Service Account to avoid problem with logging on SQL Server

One of the features introduced with TMG Service Pack 2 is to run the Firewall Service with a Domain account, this allow users to authenticate with Kerberos when using NLB.
Find more information about this feature here: http://technet.microsoft.com/en-us/library/hh454304.aspx

However you should pay attention when specifying the account name to avoid problems with logging to SQL Server, either local or remote.

The account specified is used by TMG to configure the service and also to create the Login in SQL Server.
For the TMG Firewall service to start any format is fine, but for SQL Server only the format domainName\loginName is valid.

For example if you want to use the account TMGSvc in the domain CONTOSO you have to enter CONTOSO\TMGSvc.

clip_image001

Using the UPN (User Principal Name) format or the FQDN (Fully Qualified Domain Name) does not work.
For example you cannot use TMGSvc@Contoso.com or Contoso.com\TMGSvc

The SQL Server documentation for the CREATE LOGIN command has the following note:

"When you are creating logins that are mapped from a Windows domain account, you must use the pre-Windows 2000 user logon name in the format [<domainName>\<loginName>]."

If you try using an invalid format you will see the Log Status as Disconnected and your LLQ folder growing:

clip_image002

 

Author:
Gianni Bragante
Support Engineer – Microsoft Forefront Edge Security Team

Reviewer:
Lars Bentzen
Sr. Escalation Engineer – Microsoft Forefront Edge Security Team

Categories: Logging, sql, TMG Tags:

How to configure the TMG Service Account to avoid problem with logging on SQL Server

One of the features introduced with TMG Service Pack 2 is to run the Firewall Service with a Domain account, this allow users to authenticate with Kerberos when using NLB.
Find more information about this feature here: http://technet.microsoft.com/en-us/library/hh454304.aspx

However you should pay attention when specifying the account name to avoid problems with logging to SQL Server, either local or remote.

The account specified is used by TMG to configure the service and also to create the Login in SQL Server.
For the TMG Firewall service to start any format is fine, but for SQL Server only the format domainNameloginName is valid.

For example if you want to use the account TMGSvc in the domain CONTOSO you have to enter CONTOSOTMGSvc.

clip_image001

Using the UPN (User Principal Name) format or the FQDN (Fully Qualified Domain Name) does not work.
For example you cannot use TMGSvc@Contoso.com or Contoso.comTMGSvc

The SQL Server documentation for the CREATE LOGIN command has the following note:

"When you are creating logins that are mapped from a Windows domain account, you must use the pre-Windows 2000 user logon name in the format [<domainName><loginName>]."

If you try using an invalid format you will see the Log Status as Disconnected and your LLQ folder growing:

clip_image002

 

Author:
Gianni Bragante
Support Engineer – Microsoft Forefront Edge Security Team

Reviewer:
Lars Bentzen
Sr. Escalation Engineer – Microsoft Forefront Edge Security Team

Categories: Logging, sql, TMG Tags:

TMG Logging to LLQ

September 18th, 2012 No comments

One of the problems causing TMG to log to LLQ instead of the database is the presence of orphaned databases in the local SQL Server instance.
In other words you may have some databases that are registered on the local SQL Server but the corresponding .mdf and .ldf files are missing from the disk. This may occur if the files were manually deleted, the volume with the files are no longer available or for other reasons.

It’s also important to note that this may happen when logging is configured for either the local database or remote SQL database. This is because TMG still checks the health of the local instance even if you log to a remote database.

When this problem occurs you may see something like this:

image

To understand if this is the case you will need to check the logs of the local SQL Server instance that are located by default in C:\Program Files\Microsoft SQL Server\MSSQL10.MSFW\MSSQL\Log while the databases themselves are by default in ‘C:\Program Files\Microsoft Forefront Threat Management Gateway\Logs\.

Open the file ERRORLOG from the logs folder and search for lines that looks like the following:

2012-09-05 10:44:52.01 spid54 Starting up database ‘ISALOG_20120831_FWS_000’.
2012-09-05 10:44:52.02 spid54 Error: 17204, Severity: 16, State: 1.
2012-09-05 10:44:52.02 spid54 FCB::Open failed: Could not open file C:\Program Files\Microsoft Forefront Threat Management Gateway\Logs\ISALOG_20120831_FWS_000.mdf for file number 1. OS error: 2(failed to retrieve text for this error. Reason: 15100).
2012-09-05 10:44:52.15 spid54 Error: 17207, Severity: 16, State: 1.
2012-09-05 10:44:52.15 spid54 FileMgr::StartLogFiles: Operating system error 2(failed to retrieve text for this error. Reason: 15105) occurred while creating or opening file ‘C:\Program Files\Microsoft Forefront Threat Management Gateway\Logs\ISALOG_20120831_FWS_000.ldf’. Diagnose and correct the operating system error, and retry the operation.

Next thing you should check is what happened to the missing files.

If you changed the log location to another volume and this volume is currently unavailable then try to get the volume back online if possible.

If there is no way to get those files back you should proceed by removing the database registrations from the local master database. You can identify the names of the orphaned databases by running this command at an elevated command prompt:

OSQL -E -S .\MSFW -Q “select name from sysdatabases where name like ‘%isalog%’”

Compare the names from the output of the above command with the database files in the log file directory. Once you have identified the names of the missing databases you should prepare a text file with the commands to drop each missing database. It should look something like this:

drop database ISALOG_20120831_FWS_000
go
drop database ISALOG_20120831_WEB_000
go
drop database ISALOG_20120901_FWS_000
go
drop database ISALOG_20120901_WEB_000
go

Save this file, for example, as c:\DropDB.sql

Then from an elevated command prompt execute this command:

OSQL -E -S .\MSFW -i c:\DropDB.sql

Now restart the “Microsoft Forefront TMG Firewall” service and then check back the Log Status, you should see the current status no longer as “Disconnected” but as “Queue in use”. If you click Refresh you should also see the LogQueue(KB) decreasing.

image

Depending on how long the issues lasted and the amount of data being logged by your server, it may take few minutes or a few days for the queue data to be processed.

Once this operation completes you should see the status as Ready again.

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

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

Categories: Logging, TMG Tags:

TMG Logging to LLQ

September 18th, 2012 No comments

One of the problems causing TMG to log to LLQ instead of the database is the presence of orphaned databases in the local SQL Server instance.
In other words you may have some databases that are registered on the local SQL Server but the corresponding .mdf and .ldf files are missing from the disk. This may occur if the files were manually deleted, the volume with the files are no longer available or for other reasons.

It’s also important to note that this may happen when logging is configured for either the local database or remote SQL database. This is because TMG still checks the health of the local instance even if you log to a remote database.

When this problem occurs you may see something like this:

image

To understand if this is the case you will need to check the logs of the local SQL Server instance that are located by default in C:\Program Files\Microsoft SQL Server\MSSQL10.MSFW\MSSQL\Log while the databases themselves are by default in ‘C:\Program Files\Microsoft Forefront Threat Management Gateway\Logs\.

Open the file ERRORLOG from the logs folder and search for lines that looks like the following:

2012-09-05 10:44:52.01 spid54 Starting up database ‘ISALOG_20120831_FWS_000’.
2012-09-05 10:44:52.02 spid54 Error: 17204, Severity: 16, State: 1.
2012-09-05 10:44:52.02 spid54 FCB::Open failed: Could not open file C:\Program Files\Microsoft Forefront Threat Management Gateway\Logs\ISALOG_20120831_FWS_000.mdf for file number 1. OS error: 2(failed to retrieve text for this error. Reason: 15100).
2012-09-05 10:44:52.15 spid54 Error: 17207, Severity: 16, State: 1.
2012-09-05 10:44:52.15 spid54 FileMgr::StartLogFiles: Operating system error 2(failed to retrieve text for this error. Reason: 15105) occurred while creating or opening file ‘C:\Program Files\Microsoft Forefront Threat Management Gateway\Logs\ISALOG_20120831_FWS_000.ldf’. Diagnose and correct the operating system error, and retry the operation.

Next thing you should check is what happened to the missing files.

If you changed the log location to another volume and this volume is currently unavailable then try to get the volume back online if possible.

If there is no way to get those files back you should proceed by removing the database registrations from the local master database. You can identify the names of the orphaned databases by running this command at an elevated command prompt:

OSQL -E -S .\MSFW -Q “select name from sysdatabases where name like ‘%isalog%'”

Compare the names from the output of the above command with the database files in the log file directory. Once you have identified the names of the missing databases you should prepare a text file with the commands to drop each missing database. It should look something like this:

drop database ISALOG_20120831_FWS_000
go
drop database ISALOG_20120831_WEB_000
go
drop database ISALOG_20120901_FWS_000
go
drop database ISALOG_20120901_WEB_000
go

Save this file, for example, as c:\DropDB.sql

Then from an elevated command prompt execute this command:

OSQL -E -S .\MSFW -i c:\DropDB.sql

Now restart the “Microsoft Forefront TMG Firewall” service and then check back the Log Status, you should see the current status no longer as “Disconnected” but as “Queue in use”. If you click Refresh you should also see the LogQueue(KB) decreasing.

image

Depending on how long the issues lasted and the amount of data being logged by your server, it may take few minutes or a few days for the queue data to be processed.

Once this operation completes you should see the status as Ready again.

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

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

Categories: Logging, TMG Tags:

TMG Logging to LLQ

September 18th, 2012 No comments

One of the problems causing TMG to log to LLQ instead of the database is the presence of orphaned databases in the local SQL Server instance.
In other words you may have some databases that are registered on the local SQL Server but the corresponding .mdf and .ldf files are missing from the disk. This may occur if the files were manually deleted, the volume with the files are no longer available or for other reasons.

It’s also important to note that this may happen when logging is configured for either the local database or remote SQL database. This is because TMG still checks the health of the local instance even if you log to a remote database.

When this problem occurs you may see something like this:

image

To understand if this is the case you will need to check the logs of the local SQL Server instance that are located by default in C:\Program Files\Microsoft SQL Server\MSSQL10.MSFW\MSSQL\Log while the databases themselves are by default in ‘C:\Program Files\Microsoft Forefront Threat Management Gateway\Logs\.

Open the file ERRORLOG from the logs folder and search for lines that looks like the following:

2012-09-05 10:44:52.01 spid54 Starting up database ‘ISALOG_20120831_FWS_000’.
2012-09-05 10:44:52.02 spid54 Error: 17204, Severity: 16, State: 1.
2012-09-05 10:44:52.02 spid54 FCB::Open failed: Could not open file C:\Program Files\Microsoft Forefront Threat Management Gateway\Logs\ISALOG_20120831_FWS_000.mdf for file number 1. OS error: 2(failed to retrieve text for this error. Reason: 15100).
2012-09-05 10:44:52.15 spid54 Error: 17207, Severity: 16, State: 1.
2012-09-05 10:44:52.15 spid54 FileMgr::StartLogFiles: Operating system error 2(failed to retrieve text for this error. Reason: 15105) occurred while creating or opening file ‘C:\Program Files\Microsoft Forefront Threat Management Gateway\Logs\ISALOG_20120831_FWS_000.ldf’. Diagnose and correct the operating system error, and retry the operation.

Next thing you should check is what happened to the missing files.

If you changed the log location to another volume and this volume is currently unavailable then try to get the volume back online if possible.

If there is no way to get those files back you should proceed by removing the database registrations from the local master database. You can identify the names of the orphaned databases by running this command at an elevated command prompt:

OSQL -E -S .\MSFW -Q “select name from sysdatabases where name like ‘%isalog%'”

Compare the names from the output of the above command with the database files in the log file directory. Once you have identified the names of the missing databases you should prepare a text file with the commands to drop each missing database. It should look something like this:

drop database ISALOG_20120831_FWS_000
go
drop database ISALOG_20120831_WEB_000
go
drop database ISALOG_20120901_FWS_000
go
drop database ISALOG_20120901_WEB_000
go

Save this file, for example, as c:\DropDB.sql

Then from an elevated command prompt execute this command:

OSQL -E -S .\MSFW -i c:\DropDB.sql

Now restart the “Microsoft Forefront TMG Firewall” service and then check back the Log Status, you should see the current status no longer as “Disconnected” but as “Queue in use”. If you click Refresh you should also see the LogQueue(KB) decreasing.

image

Depending on how long the issues lasted and the amount of data being logged by your server, it may take few minutes or a few days for the queue data to be processed.

Once this operation completes you should see the status as Ready again.

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

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

Categories: Logging, TMG Tags:

Issue with TMG remote SQL logging

April 30th, 2012 No comments

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

Categories: Logging, TMG Tags: