Securing Reporting Services Data Sources

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

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles