Securing Reporting Services Data Sources

Monday Sep 14th 2009 by Marcin Policht

Marcin Policht continues his discussion of SQL Server 2005 Express Edition security related topics by describing functionality that facilitates protecting HTTP-based communication with the Reporting Services Web site by leveraging Secure Sockets Layer (SSL) encryption.

In the recent installments of our series dedicated to the most important features of SQL Server 2005 Express Edition, we have been discussing its implementation of Reporting Services. After introducing their most basic characteristics and stepping through the generation of a few sample reports, our focus has shifted to security-related topics. We are going to continue this subject here by describing functionality that facilitates protecting HTTP-based communication with the Reporting Services Web site by leveraging Secure Sockets Layer (SSL) encryption.

As we have explained earlier, the Report Server, which handles report processing and rendering, as well as interaction between management components (including Report Manager, Reporting Services Configuration Manager, command-line utilities or any third-party programs) and the database layer (where data referenced by reports, report definitions, and Report Server metadata reside) operates as a Web Service. As such, it depends on settings assigned to the local instance of Internet Information Services. In particular, even though its Secure Sockets Layer characteristics can be set via Reporting Services Configuration Manager (more specifically, its Report Sever Virtual Directory section), they are contingent on the presence of this feature on the Web site level (which, in turn, requires properly configured computer certificates). In order to properly illustrate these dependencies, we will briefly describe the underlying concepts and present a sample process of setting up SSL encryption in the context of SQL Server 2005 Express Edition-based Reporting Services.

It is important to note that SSL-based communication is based on Public Key Infrastructure (PKI) technology. It leverages both symmetric and asymmetric (in the form of Web Server certificates, along with a public/private key pair issued to a Web site owner) keys. At the beginning of an SSL session, triggered by pointing a browser to an https:// URL, designating a target site, a client obtains its certificate, along with the corresponding public key. As long as the issuing Certificate Authority is trusted (typically by the virtue of the fact that its own certificate resides in the user's or computer's Personal Trusted Root Certificate Authorities store) a secure session can be established. Once the client's browser has verified the certificate's validity, it generates a random symmetric key, encrypts it with the Web site's public key, and sends it over to the target URL. The receiving host decrypts the communication using its unique private key and uses the newly received symmetric key to protect all future communication that is part of the same session.

Let's take a look at a sample scenario demonstrating implementation of SSL certificates to protect Reporting Services Web site sessions. In our example, we will assume internal PKI infrastructure, based on Windows Server 2008 Certificate Services, but the process would be very similar (at least from our perspective) when using third-party Certificate Authorities. In short, we will need to go through the following steps in order to accomplish our goal:

  • create a Web Server certificate request for the Web site hosting Reporting Services virtual directories,
  • submit the request to a trusted Certificate Authority,
  • install resulting certificate on the Reporting Services Web site,
  • configure Report Server virtual directory SSL settings.

To start, launch Internet Information Services (IIS) Manager (from the Administrative Tools menu) on the computer hosting our SQL Server 2005 Express Edition-based Reporting Services instance. If you are using Windows XP Professional with IIS 5.x, display the Properties dialog box of the target Web site via its context sensitive menu. Switch to the Directory Security tab and click on Server Certificate... command button to launch the Web Server Certificate Wizard. On its initial page, choose the Create a new certificate option, followed by Prepare the request now, but send it later. Next, provide a friendly name identifying the resulting certificate to your potential clients (simply make sure that it sufficiently identifies its purpose and your organization). At this point, you also have the ability to choose the length (in bits) of encryption key (take into consideration security and performance implications, which are, respectively, directly and inversely proportional to its value) and decide whether you want to use a non-default cryptographic service provider (CSP) for the certificate (other than Microsoft RSA SChannel Cryptographic Provider). Respond to the wizard's prompts for names of your Organization and Organizational unit (corresponding typically to a division or department). Keep in mind that Common name (that you will be asked to specify next) needs to match either the Web server's NetBIOS or fully qualified DNS name (alternatively, you can use a host header for this purpose). Finally, assign the values to Country/Region, State/province, and City/locality. The information you provided gets stored in an arbitrarily named request file.

If you are running SQL Server 2005 Express Edition with Reporting Services on a Vista computer (with IIS 6.x), the sequence of steps is a bit different. Once you launch Internet Infromation Services Manager, select its top level node representing the Web server. At that point, in the IIS section within the details pane of Microsoft Management Console v3, you should be able to locate the Server Certificates feature. After you open it, the Actions pane will display the Create Certificate Request... entry. Clicking on it will trigger the Request Certificate wizard. On its first page, type in the Common name, Organization, Organizational unit, as well as City/locality, State/province, and Country/Region. Choose a cryptographic service provider and bit length on the next page, and lastly specify the location and name of a file where the request will be stored.

The resulting certificate request needs to be processed by either internal or external Certificate Authority (optionally, in a small-scale or test deployments, you might consider using self-signed certificates). In the first case (better suited for end-users within your organization), you can use Windows Server 2003 or 2008-based Certificate Services for this purpose, which leverage built-in Web Server certificate templates. The latter (more appropriate in Internet or extranet scenarios, where SQL Server 2005 Express Edition is seldom deployed), relies on the services of commercial CAs. Regardless of the approach, the outcome consists of a certificate file, which needs to be installed on the target Web site (hosting Reporting Services instance). With IIS 5.0, this is accomplished by importing it while running the IIS Certificate wizard (invoked by clicking on the Server Certificate command button from the Directory Security tab of the Web site's Properties dialog box. From the same interface (via Edit... command button), you can also specify whether a secure channel will be required for all communication (assuming that the SSL port has been assigned on the Web Site tab).

In the case of IIS 6.0, Action pane in the IIS Manager console includes a Complete Certificate Request... link, which brings up a page prompting you for the name and location of a file containing CA response and a friendly name that will help identify the certificate. Forcing encryption involves modifying the configuration of the SSL Settings feature (appearing in the IIS section of the Details pane in the IIS Manager console). This, however, requires that you add a binding of https type (with the corresponding IP address, port, or host header parameter, as well as the newly imported certificate), via the Edit Bindings... entry in the context sensitive menu of the target Web site. Note that changes to RSReportServer.config file (which include modifying the UrlRoot configuration) are not applicable in the context of SQL Server 2005 Express, since they are intended to facilitate e-mail delivery extensions, present only in full-featured editions.

In order to complete the setup, launch Reporting Services Configuration Manager on the target computer and switch to its Report Server Virtual Directory section. Next, enable the Require Secure Socket Layer (SSL) connections checkbox located in the lower portion of the window. Assign a value to the Certificate Name textbox (you should type the Common name of the certificate in there, which matches either the computer's NetBIOS name, fully qualified DNS name, or the Web site's host header), pick one of the three entries (1 - Connections, 2 - Report Data, or 3 - All SOAP APIs) in the Require for listbox (your decision would depend on the level of security you are supposed to provide and performance implications of each), and click on the Apply command button to finalize your choices.

At this point, an attempt to connect to your Reporting Services Web site via HTTP will result in an error message stating that The underlying connection was closed: Could not establish trust relationship for the SSL/TSL secure channel. Effectively, you will be forced to use an HTTPS-based connection and specify a target URL that includes the common name assigned to the certificate. If such an approach triggers a display of There is a problem with this website's security certificate page, it is likely that a certificate of the issuing CA is not present in the Trusted Root Certificate Authorities personal store on the client's computer. To remediate this issue, distribute it to all client computers prior to implementing SSL encryption (in an Active Directory environment, this can be automated by leveraging Group Policies) or ask your users to install the certificate during their initial connection (via the Install Certificate... command button in the Certificate's properties dialog box).

This concludes our discussion regarding the use of SSL encryption in protecting communication targeting SQL Server 2005 Express Edition-based Reporting Services Web site. In our next article, we will continue presenting methodologies that help you securing your Reporting Services environment.

» See All Articles by Columnist Marcin Policht

Mobile Site | Full Site