Blog | Jul 8, 2014

How to Manage SQL Server Reporting Services Security with Encryption Keys

In our line of business, we come across a variety of issues and error messages in working with our clients’ SQL Server environments. For instance, the one outlined below:

"The report server cannot decrypt the symmetric key that is used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content."

If you have ever encountered the above error flashing across your screen, this blog will help you! We were able to deal with the error well.  As a result, it is worth sharing the experience and key learnings uncovered while accessing the SQL Server Reporting Services.

Typically, if you have the backup key and associated password, you can restore the key, delete the encrypted content or create a new version of the encryption key.

However, this was a tricky one because:
a) Restoring the key was not possible as the password was missing
b) Deleting encrypted content would cause issues with the connections, because this was recently built
c) Creating a new version of the encryption key while replacing the old one was ruled out

To resolve the error, we had to delete the encrypted content enabling access to the reporting server. This was made possible because we were working on the development server, however it would have been a larger challenge if we had been working on the Production server.

Security is a very crucial aspect for any organization and it is important to configure and manage the encryption keys on the SQL Server Reporting Services. There are a few things that are required to manage the encryption keys and based on our experience, we recommended having them together in one place.

Below, we outline and answer the “HOW TOs” for managing and configuring the SSRS encryption keys.

Background to the Encryption key:
The Reporting Services use encryption keys to secure credentials and connection information and stores it in the Report server database. This encryption is supported by a combination of public, private, and symmetric keys used for protecting the sensitive data. The public and the private keys are created by the operating system for protecting the symmetric key.
Configuring and Managing Encryption keys implies that a DBA should be able to take backup of an encryption key and understand when and where to use the key. A DBA should know and decide how and when to backup/restore/delete/change these keys. You must have a backup copy of the key in case a migration or scale-out development is being planned.

Now let’s review what utility is available to perform all these tasks:
1) The Report Server Configuration Tool, this is a Graphical User Interface (GUI) and all the steps mentioned below can also be performed using this tool.
2) The rskeymgmt utility, using a command line utility is always more challenging than using a GUI.

The rskeymgmt Utility can be used by a DBA to perform the below tasks:  

• For adding or removing a report server instance from a scale-out deployment, where multiple report servers share a single report server database and the symmetric key.

To add a server to scale-out development
rskeymgmt -j -m <remotecomputer> -n <namedreportserverinstance> -u <administratoraccount> -v <administratorpassword>

Use the -j argument to join a report server to the report server database. Alternatively, we can also use Reporting Configuration Tool.

To remove a server to scale-out development.
rskeymgmt -r <installation ID>
<installation ID> can be found in the rsreportserver.config file.  Alternatively, we can also use Reporting 
Configuration Tool.

• For creating a backup copy of the symmetric key so that it can be used to recover a report server installation or as part of a planned migration. A backup copy of the key is required for many routine operations and enables you to reuse an existing report server database for a new installation.

To backup the encryption key
rskeymgmt –e –f c:\rsdbkey.snk –p<password>
Alternatively, we can also use Reporting Configuration Tool. The –e parameter is used to extract the copy of the key,   –p is used to provide the password required in case the key needs to be restored.

• For re-creating the symmetric keys and re-encrypt the data periodically. This is required where the existence of the symmetric key is compromised. We recommend this as a best practice.

To re-create encryption keys, first disable Reporting Service Web Service and HTTP Access and use below command
rskeymgmt –s
-s parameter is used to reset the key. Now restart the Reporting service and enable HTTP access.

• For restoring a previously saved symmetric key to a report server database. This will allow a new report server instance to access existing data that it did not originally encrypt. A restore could be needed in the following scenarios:

• Changing the Report Server Windows service account name or resetting the password.
• Renaming the computer or instance that hosts the report server.
• Migrating or configuring a report server to use a different report server database.
• Recovering a report server installation due to a hardware failure.

To restore the encryption key
rskeymgmt –a –f c:\rsdbkey.snk –p<password>
Alternatively, we can also use Reporting Configuration Tool. The –a parameter is used to restore the key,   –p is used to provide the password (password specified while backing up the key).

• For deleting the encrypted data in a report server database where you can no longer access the encrypted data. A deleted encrypted content cannot be recovered. Following are implications of deleting encrypted content:
• Connection strings in shared data sources are deleted.
• Stored credentials are deleted, requires to be reconfigured.
• Subscriptions are deactivated.

To delete the encryption key
rskeymgmt –d
Alternatively, we can also use Reporting Configuration Tool. The –e parameter is used to extract the copy of the key,   –p is used to provide the password required in case the key needs to be restored.

We hope that if you ever encounter this situation, this information will help you to resolve it.

About TriCore Solutions
TriCore Solutions, the application management experts, provides a full suite of scalable and reliable managed application, cloud, infrastructure hosting, and consulting services to enterprise organizations. The company delivers its services and the TriCore Trusted Promise to more than 250 companies worldwide to reduce costs, raise service levels, improve customer experience, increase business agility, and accelerate innovation, unlocking the business value from their IT investments. TriCore Solutions is headquartered in Boston, MA, with offices in Gurgaon, Hyderabad and Philippines.