Website Security Solutions | Latest Guides | Blog

Microsoft’s SQL Server Reporting Services is a popular Business Intelligence framework. Through SSRS, administrators can analyze, collate, and synthesize data from a variety of sources into easy to consume web-based reports. It is common for third-party software to rely on the rich underpinnings of SSRS for integration purposes, choosing to make SSRS reports available through its own interface.

Since data is used to make decisions, it is very important to protect privacy and prevent tampering over the network. Imagine a nosy employee intercepting salary data, or a bad actor manipulating a report in-flight in order to present altered data! This is why securing SSRS with SSL/TLS is so important – SSL/TLS provides both encryption and message integrity. Securing SSRS can be confusing at first however, with a couple of “gotchas” along the way.

Configuring SSRS

It is recommended to configure SSRS BEFORE applying an SSL/TLS certificate.

Configure Microsoft SSRS

By default, SSRS will create a “virtual service account”. For those familiar with Active Directory, this is somewhat similar to a localized version of a GMSA (Group-managed service account). This special local account doesn’t require you to set a password. In certain situations, it is preferable to use a domain account. In the case of third-party integration, often the account running the software also runs the SSRS instance!

Configure Microsoft SSRS

On this screen, simply hit “Apply” to accept the defaults. We will come back in and replace this with an HTTPS connection later, but this will allow us to proceed through the rest of the setup.

Configure Microsoft SSRS Database

Under “Database” we must create a database for our SSRS instance. Somewhat counterintuitively, select “Change Database”.

Create New Report Server

Select “Create a new report server database”. (Or, if this is an upgrade/migration, choose an existing report server database instead). Usually, SQL Server runs on the server running Reporting Services itself, but it is possible to configure it to point to another server.

Create New Report Server DAtabase

If the user you are signed in as has sysadmin level privilege on your local SQL Server instance, simply select “Next”. This establishes a connection to the instance for configuration purposes, but the credentials chosen on this screen are NOT persistent. Don’t worry about SSRS running as your administrative user in perpetuity!

Database Name

Select “Next” to create your Report Server database. Most of the time, your report server mode will be “Native”. The alternative is a Sharepoint-managed SSRS instance, which is installed as part of a Sharepoint deployment through a different tool.

Database Auth Type

On this screen, we specify the credentials SSRS will use going forward to connect to SQL Server. The default is to create an account for this purpose with least privilege. In most cases, this is the best choice.

Create Database Summary

On the summary screen, select “Next”. SSRS will begin its database configuration routine.

Create Database Success

The wizard takes three to four minutes. When it completes, select “Finish”.

Web Server Portal

Select “Web Portal URL” and choose “Apply”.

Email Settings

Email settings are important to configure if you want to be able to email out reports on a schedule.

Execution Account Setup

Execution Account allows you to specify a service principal in whose context certain operations to remote servers will run. Most of the time, we can leave this blank. In the cases it is required, it is usually called out in vendor documentation.

setup encryption keys

Take a backup of your encryption keys.

backup encryption keys

You will need to specify a password, which will be used to protect the symmetric key. Be sure to record this passphrase for safekeeping, this will be absolutely required during upgrades of SSRS.

Unless your vendor documentation says otherwise, “Subscription Settings”, “Scale-Out Deployment” and “Power BI Service” can usually be ignored. These constitute certain advanced features of SSRS.

Adding an SSL/TLS Certificate

Your SSL/TLS certificate must now be setup in two separate spots, which can be confusing at first. Since SSRS’s configuration tool will let you specify a certificate that is present in the machine store’s personal certificate node, first we must import a certificate to this location. Alternatively, we could create our CSR from the MMC on the SSRS Server (which stores the private key on this machine in the “Certificate Enrollment Requests” node), and subsequently import our signed response from our CA.

n.b. Ensure when creating your CSR to populate the SAN name field in the certificate, even if it is the same as the common name. This is required for browsers to trust your certificate!

Older versions of SSRS were notorious for being unable to work with wildcard certificates. More recent versions do not have this problem.

Under “Web Services URL”, select “Advanced”.

web service url Add web service url

Choose “Add” on the bottom.

Add web service url

Choose a “Certificate”. Available options are sourced by enumerating anything in the machine store’s node. Ensure that you select a certificate with a corresponding private key! Anything in the personal node will appear, but a certificate without a private key is not a valid selection, and will result in cryptic errors.

Add web service url

Note that “URL” is sourced from the common name of the certificate.

Advanced Multiple Site configuration

Optionally, remove the HTTP Identity, and then select “OK”.

web server url error

The above error is common in the case of using an internally signed CA. The underlying windows machine MUST trust the certificate chain of the certificate you select in order to create a binding for it. If needed, add relevant root and intermediate certificates into the appropriate machine certificate stores!

web server url cleared error

Once the configuration has been appropriately applied, the “apply” button will become greyed out.

Next, select “Web Portal URL”.

Portal URL

Select “Advanced”.

Portal URL Advanced

Once again, select the bottom “Add” button.

Portal URL Advanced Add

Choose your certificate (the same selection as before is fine).
Select “OK”.

Portal URL finished

SSRS is now configured to use SSL/TLS!


Author: Jeremy Schatten
Published:

    Next Guide...
    SSL Installation Guide for Apache on RedHat Linux (RHEL)

    Red Hat Enterprise Linux (RHEL) is a Linux-based operating system from Red Hat designed for organizations. RHEL can work on desktops, on servers, in hypervisors or in the cloud. Red Hat and its community-supported counterpart, Fedora, are among the most widely used Linux distributions in the world.…