When performing SSL and TLS hardening on Microsoft Forefront Threat Management Gateway (TMG) 2010 or Forefront Unified Access Gateway (UAG) 2010 servers, disabling SSL 3.0 and TLS 1.0 is often required to meet regulatory and compliance guidelines for security. However, disabling SSL 3.0 and TLS 1.0 causes the SQL Server (ISARS) and SQL Server Express services to fail on start up.
Switching from SQL logging to text file logging can be employed as a workaround. However, when using text file logging, generating historical reports in the TMG management console is no longer supported.
To restore full functionality for SQL logging and reporting when SSL 3.0 and TLS 1.0 are both disabled, an update to the local security policy of the server is required. Open the Local Security Policy editor by clicking on the Start button and navigating to Administrative Tools and Local Security Policy. Expand Local Policies and click on Security Options. Double-click on System cryptography: Use FIPS compliant algorithms for encryption, hashing, and signing. Click Enabled and then click OK.
Restart the computer for the changes to take effect. Once complete, all SQL services should start and run without issue.
Note: If Remote Desktop Services (RDS) is used to manage the Forefront TMG firewall it will be necessary to install update KB3080079.
When performing POODLE attack mitigation on the Forefront TMG 2010 firewall by disabling SSL 3.0, you may encounter a scenario in which TMG’s SQL services fail to start after a reboot.
Looking through the Windows system event log you may see an error message logged by the Service Control Manager with event ID 36871 which states:
A fatal error occurred while creating an SSL server credential. The internal error state is 10013.
In addition you may also see an error message logged by the Service Control Manager with event ID 7024 which states:
The SQL Server (ISARS) service terminated with service-specific error %%-2146893007.
This can occur when SSL 3.0 is disabled at the same time that TLS 1.0 is also disabled. Even though TLS 1.1 and 1.2 might be enabled, TMG requires that TLS 1.0 specifically be enabled for SQL server services to function properly when SSL 3.0 is disabled.
To resolve this issue, enable TLS 1.0 Server in the registry by changing the value of Enabled to 1, as shown here. If these registry keys do not exist, create them.
Restart the server for the change to take effect.
Today Fastvue announced the general availability of TMG Reporter v2.1, the latest release of their advanced reporting and alerting platform built specifically for Forefront TMG 2010. I’ve been working with some of the early release builds and although this is technically considered a minor release, this latest update includes some important new features that make this a worthwhile and compelling upgrade. Here are some of the great new features included with TMG Reporter v2.1:
Activity Reports – Easily the single most important feature included in Fastvue TMG Reporter v2.1 is the addition of detailed activity reports. Prior to v2.1 only overview reports were available. You can now create extremely granular reports on user, site, category, or application activity that include timestamps, full URLs, browse time, category, and disposition (allowed or denied).
Scheduled Custom Reports – Another excellent new feature in Fastvue TMG Reporter v2.1 allows the administrator to create custom reports using the new filtering interface and schedule them for automated delivery to recipients.
Comprehensive Filtering – Fastvue TMG Reporter v2.1 includes a powerful new filtering facility that allows security administrator to dig deep in to the logging data to find the pertinent information they desire. The new enhanced filtering feature is available for use on all reports.
Drilldown Reporting – Another feature that has been greatly improved in the v2.1 release is drilldown reporting. Previously executing a drilldown report resulted in the creation of a completely new and separate report. With v2.1, the new drilldown report now respects the initial filter settings, allowing the administrator to see detailed information as necessary while maintaining the context that the original report was created within.
SQL Server integration – This is one of my favorite (and most requested!) new features. TMG Reporter v2.1 now includes native support for SQL server logging. There are many compelling reasons to configure the Forefront TMG 2010 firewall to log to an external SQL server, and now TMG Reporter can connect directly to your TMG firewall’s configured SQL server to collect report data.
Full support for Windows 8 and Server 2012 – You can now install TMG Reporter on Windows 8 and Windows Server 2012! This is great news as many organizations are beginning to deploy Windows 2012 server and Windows 8 desktops. The TMG Reporter installer now detects which server platform you are installing on and installs the necessary prerequisites automatically. The TMG Reporter web interface is also fully supported on Windows 8 with Internet Explorer 10 (modern and desktop).
Since its initial release a few years ago, Fastvue TMG Reporter has continued to add features and functionality. If you’re running an older version, or if you haven’t yet made the switch to TMG Reporter, the new capabilities in TMG Reporter v2.1 may finally compel you to take the leap and implement or upgrade to TMG Reporter v2.1 for your Forefront TMG monitoring, alerting, and report needs. Download a free, fully functional evaluation copy of Fastvue TMG Reporter v2.1 today. You’ll be glad you did, trust me!
Recently I was given the opportunity to write some guest blog posts about Forefront TMG 2010 for Fastvue. Fastvue develops TMG Reporter, an excellent real-time dashboard, alerting, and reporting tool that integrates with Forefront TMG 2010. Be sure to check out these new articles that have been posted:
Logging Improvements in Forefront TMG 2010
Enterprise Reporting Challenges with Forefront TMG 2010
Tips for Healthy Logging and Reporting in Forefront TMG 2010
Forefront TMG 2010 Enterprise Logging with Remote SQL Server
TMG Reporter is a significant upgrade from the native reporting tools included with Forefront TMG 2010. Download a free evaluation of their software today and give it a try. You won’t be disappointed!
When Forefront TMG 2010 is installed, an instance of SQL Server 2008 Express is also installed to facilitate local firewall and web proxy logging. Some TMG administrators have reported that the SQL server process (sqlservr.exe) may consume an excessive amount of memory.
This occurs because SQL server performs its own internal memory management. By design it will consume large amounts of memory and hold it in reserve, which may appear to be a memory leak. However, if you observe the memory consumed by SQL server over an extended period of time, you will notice that it will periodically release memory as well.
In most cases, allowing SQL to handle the job of managing its memory without restriction poses no real problem. However, if your system is exhibiting signs of high memory pressure and you are confident that there are no other processes that are consuming excessive amounts of memory, you can configure SQL to limit the amount of memory that it will reserve. Before manually configuring SQL memory limits, review this blog post for a list of recommended settings.
Next, open an elevated command prompt and enter the following command:
osql –E –S .\msfw
After connecting to the SQL instance, enter the following commands:
USE master GO EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE GO EXEC sp_configure 'max server memory (MB)', 1024 --configure 1GB limit, adjust as needed GO EXEC sp_configure 'show advanced options', 0 RECONFIGURE WITH OVERRIDE GO
This script assumes that you’ve chosen a 1GB (1024MB) memory limit. You can adjust the script above to reflect the values for your environment as required. Once complete, restart the SQL server service for the changes to take effect.
To view the currently configured memory limit, simply omit the numeric value after max server memory (MB), as shown here:
EXEC sp_configure 'max server memory (MB)'
To make things easier you can download these as script files and execute them using the following command:
osql –E –S .\msfw -i <path to script file>
You can find script files to show and set SQL memory limits here:
Keeping the base operating system of your Forefront TMG 2010 firewall up to date is vitally important to the overall security of your edge security solution. To manage system updates, many administrators will configure their Forefront TMG 2010 firewalls to use Windows Update or WSUS, or manage them using System Center Configuration Manager (SCCM) or another third-party systems management platform.
In my experience, SQL server running on the Forefront TMG 2010 firewall is often overlooked and commonly not updated. I believe this happens because updates for SQL server are classified as optional.
So, as a reminder, don’t overlook updates for SQL server on Forefront TMG 2010 firewalls or UAG 2010 servers! Using the Windows Update control panel application, select the option to install the latest service pack for Microsoft SQL Server 2008, which at the time of this writing is Service Pack 3. You can install the service pack directly if you choose; SQL Server 2008 Express SP3 can be downloaded here. After applying the latest service pack you can confirm that SQL has been updated by opening an elevated command prompt and entering the following commands:
osql -E -S .\msfw select @@version [press enter] go [press enter]
The output of the command should indicate that the installed SQL version is Microsoft SQL Server 2008 (SP3) – 10.0.5500.0 (X64).
Note: Applying service packs and updates to SQL is highly recommended to maintain the most secure Forefront TMG 2010 firewall possible. Upgrading the version of SQL installed on the TMG firewall is not supported and definitely not recommended, so don’t attempt to upgrade to SQL Server 2008 R2 Express.