Introduction
By default, the standard Installation of Microsoft Forefront TMG installs a version of Microsoft SQL Server 2008 Express SP1. The Forefront TMG Web proxy and Firewall components will use the installed SQL Server 2008 version to store generated log files. The built-in Reporting and Logging functionalities of Forefront TMG will use some underlying SQL Server and SQL Server Reporting features. For example, the built-in Reporting function in the Forefront TMG console will use the stored data in the SQL databases to generate one time and recurring reports. These generated reports can be saved in a file share on the local or a remote Server for later review or a report summary can be send via e-mail. The SQL Server 2008 Express installation does not require an installed Internet Information Service (IIS) for the SQL Server Reporting services which will reduce the attack surface of your Forefront TMG Server.
Get your copy of the German language “Microsoft ISA Server 2006 – Das Handbuch”
Let’s start with the configuration of the SQL Server 2008 Express Reporting Service configuration. Start the Reporting Services Configuration Manager on the Forefront TMG Server.
Figure 1: SQL Report Server Status
As you can see in figure 1, the SQL Server Reporting data will be stored in a database called ISARS in Report Server Native Mode which is the default mode for Report Server installations.
SQL Server Report Server uses the built in Local System account to run the Report Server Report services. If you want to change the account for administrative or security reasons, you can do this here.
Figure 2: SQL Report Server Service Account
The next step allows you to modify the URL which is used to access the Report Server. It is possible to specify multiple URLs to access the Report Server. The Default URL allows access to the Report Server only through the HTTP protocol on Port 8008, which should not be conflicting with other services on the Forefront TMG server. If you want to change the Report Server URL to HTTPS you have to install the right certificate on the Forefront TMG Server. The certificate will be used to create an SSL Socket for the SQL Report Server. When you use HTTPS to access the Report Server make sure that you don’t create a Socket which is bound to all IP addresses. Otherwise, if you want to publish additional services with Forefront TMG which also uses port 443 they will conflict with the Report Server HTTPS port. You must use unique Sockets else the publishing of secure Web servers with Forefront TMG will fail.
Figure 3: SQL Server Express Web Service URL
The default database name for the Microsoft SQL Server 2008 Reporting Services is called ReportServer$ISARS which is installed in an SQL Server instance called ISARS.
Figure 4: SQL Server Report Manager URL
Click Advanced to add a Report Manager SSL Binding. Specify the IP address, port and certificate for the SSL Listener.
Figure 5: Enable HTTPS for the Report Manager
Next, verify that the certificate binding was successful and test the new HTTPS URL by accessing the Report Manager using the new URL.
Figure 6: Successful SSL Binding
You will now see the ISA2008 Reports. Yes, Microsoft has forgotten to change the name of the ISA Server 2006 successor in the SQL Server Reporting Services configuration 🙂
Figure 7: ISA 2008 Reports
Note:
You can change the language of the Report Server settings from the Internet Explorer’s advanced settings. I tested the setup using the German language locale and an English version of Windows Server 2008 R2 and Forefront TMG.
Next, you can create additional SQL Server Reporting Services Roles to delegate the administration of the SQL Server Reporting Services. It is possible to assign users or groups to one or more roles to administer the SQL Server Reporting Services.
Figure 8: SQL Server Reporting Role assignment
The SQL Server Reporting Services Site Settings section allows you to configure how many historical reports should be kept and the timeout for a Report execution can be configured.
Figure 9: SQL Server Reporting Services Site Settings
Let’s switch back to the Reporting Services Configuration Manager wizard. As a next step it is possible to configure some E-mail settings for sending reports. Specify the Sender address and the SMTP Server for sending E-Mails. You cannot specify credentials here for connecting to the SMTP Server, so be sure that the Reporting Server can send E-Mail to the SMTP Server.
Figure 10: SQL Server Reporting E-mail Settings
For some special configurations it is possible to specify an Execution account in the SQL Reporting Services Configuration Manager. The Execution Account allows SQL Reporting Services to operate under certain circumstances when credentials are not supplied, such as, XML. As a best practice you should specify an account which is different from any of the other SQL service accounts. As a best practice you should create a dedicated service account which has sufficient rights to read the locations holding the data. The account will also need to have network login permissions.
Figure 11: SQL Server Reporting Execution Account
Encryption keys are used in SQL Server Reporting services to encrypt credentials, connection strings and other sensitive data that is stored in the Reporting Services configuration database. It is possible to Backup, Restore and change the encryption key in the Reporting Service Configuration wizard.
Figure 12: Encryption Key Management
Because scale-out Deployment is not available in SQL Server 2008 Express editions, I would not tell you more about this configuration. If you want to know more about scale-out Deployment read the following article.
Figure 13: Scale-out Deployment
Conclusion
In this article we have gone through the configuration steps of the SQL Server 2008 Express Reporting Services that are found in Forefront TMG. With this additional information you should now be able to customize some Microsoft SQL Server Reporting aspects.
Related links
