SQL Server Reporting Services (SSRS) Encryption Key

Problem

My SQL Server Reporting Services database and server are secure and only used internally; do
I really need to backup the SSRS keys? Also, is there a command line tool to
handle this process?

I seem to hear the above noted questions quite often. Alternately, I
will hear a DBA who handles SSRS say that he / she is "backing up the
actual ReportingServices and ReportServiceTemp databases, so there is
no need to backup the keys. All the data is retained in the databases, right?"
One last quote, I hear, although not as often, is "all our SSRS rdl files are
kept in some sort of source control application / system (which is a great
practice), so I do not need to backup the SSRS keys." While all these questions /
responses are responsible, none is a good reason to not backup your SSRS key.

Solution

This tip is intended to encourage
everyone to be sure to backup their
SSRS keys, potentially often. SSRS uses symmetric and asymmetric keys
which are generated from the Windows OS. If your SSRS setup uses a farm
approach with multiple instances, then every instance must use a copy
of the symmetric key.

The actual SSRS items which are encrypted include:

Data source credentials which are stored in the database in order to connect to external databases and data sources

The actual symmetric key used by SSRS to encrypt data

The unattended user account information which is used to connect to a remote server in order get external images or data

Credentials used to connect to the Report Server database itself.

The
encrypted values are stored both in the Reporting Services
configuration files and in the Report Server database. In the event
you restore a SSRS database to a new server, the encryption keys will
need to be loaded onto the new server in order to allow that server to
read and utilize all of the items noted in the above list. Otherwise an error will
result when attempting to navigate to the Report Server. Furthermore,
your embedded data sources would be unreadable if you add a new key.
Of course you could recreate a SSRS key on the new server and then
redeploy all the data sets, data sources, and reports. In that
situation though, you would still have to recreate all the folders and
more importantly, the security for those folders (and related reports).
An easier alternative is the backup and restore the SSRS key, and
specifically use the command line tool, rskeymgmt, to handle these
tasks.

Working with the SSRS Keys

Two main methods exist for working with the SSRS key. First you
can use SQL Server 2012 Reporting Services Configuration Manager; as
part of Tim Ford's tip, SQL Server Reporting Services Configuration Tool, he covers, in great detail, using the SSRS Configuration Manager to
backup and restore the SSRS key. In this tip, though, we will explain how to
accomplish similar tasks using the rskeymgmt utility, which is one of
the SSRS command line utility tools.

The rskeymgmt utility can be found in
the binn sub-directory of your SQL Server install directory. On my local server,
it resides in: C:\Program Files (x86)\Microsoft SQL
Server\110\Tools\Binn. Of course depending on the install process,
your location may be different. Opening the command prompt and
navigating to this directory, we can run rskeymgmt -? to get a list of
arguments and additionally some example commands.

The top 7 arguments from the help list are the 7 methods that you
will use most often. The remaining arguments play a support role to the
other arguments. We should also note that you need to be an administrator
on the machine which you will run rskeymgmt, and it must be a report server
machine. Also, you can only manage a local key and not keys on remote
machines. Last, you will need to run the command prompt in "Run as
Administrator" mode; otherwise you will get an Access Denied error as
shown below.

To get started, we will
use the -l list argument to see the servers which are connected to this
report server database. The command would be: rskeymgmt.exe -l -i
SQL2012. The successful running of this command is shown below. This
particular machine contains several versions of SQL Server, so we also
must specify the instance name "SQL2012".

If this server had been part of a farm scale out design, then the
above command would have listed multiple servers. Of course using the
command line allows you to automate the process especially if you have
many servers upon which the key needs to be applied.

Extract / Backup the Key

To "backup" or
extract our key, we would issue the following command: rskeymgmt.exe -e -f
c:\tools\SSRS2012_key -p WeAreSecureT0day -i SQL2012.

For this
command we specify the location where the key should be saved via the
-f argument and a password for the key file via the
-p argument.
Note when you use the -f argument, you must also specify a password
with the -p argument. As shown next, the key is extracted to
the noted location; you even get a warning to "SECURE THE FILE IN A
SAFE LOCATION".

Apply / Restore the Key

To "apply" the key to a report server, we execute this command: rskeymgmt.exe
-a -f c:\tools\SSRS2012_key -p WeAreSecureT0day -i SQL2012. This
command simply returns success when the key file is applied to the server as
illustrated below.

Delete a Key

Two methods can be used to disengage a Report Server's access to the
encrypted data. The first method is using the -d argument, and it
stands as the most severe method in that it DELETES ALL encryption keys
and encrypted data. When you use SSRS is in a multiple server / farm architecture,
the -d / delete method removes all keys for all servers. In this case,
the report server will no longer function until a new key is generated.
For our example the command to issues to delete all encrypted data would be:
rskeymgmt.exe -d -i
SQL2012. Fortunately, the command prompts you to confirm that you want to delete the key, because, again, ALL
key data is removed! This situation would be similar to restoring just
the SSRS databases and having to create a new key.

Generate a New Key

After deleting our key using the -d option, we could regenerate a new key using the
-s argument: rskeymgmt.exe
-s -i SQL2012 as seen in the screen prints below. Please note
that this process only regenerates the keys. It does not restore all
the encrypted data, such as the data source connection information. You
would have to read the connection data.

Remove a SSRS Instance

Alternately, a single instance of a Report Server can be removed by
using the -r argument; this argument requires the installationID (see
list -l option above). The syntax for this command is: rskeymgmt.exe -r
xxyyzzz where xxyyzzz is replaced with the actual ID.

Rejoin a SSRS Farm

To have that
server rejoin the scaled out SSRS farm, you could issue this command:
rskeymgmt -j -m remotecomputerNameOnNetwork -n
namedreportserverinstance -u administratoraccountOnRemoteComputer -v
administratorpasswordOnRemoteComputer. Unfortunately, I do not have a
farm environment to show you the exact examples. However, this method
does NOT delete the key and encryption information in the database like the -d argument.

Output Error Messages

Finally, you can output error messages and information from the
rskeymgmt utility to the SSRS Trace Log by adding the -t argument. For
instance the command to extract the key would be: rskeymgmt.exe -e
-f c:\tools\SSRS2012_key -p WeAreSecureT0day -i SQL2012 -t .

The SSRS
log file is saved in a location similar to: C:\Program Files\Microsoft
SQL Server\MSRS11.SQL2012\Reporting Services\LogFiles and as illustrated
subsequently. We also see in the screen prints a copy of the SSRS trace logs which reflect the extract command.

Conclusion

I cannot stress enough the importance of backing up your SSRS keys.
The key backup process can be accomplished in two ways: either using
Reporting Service Configuration Manager or the rskeymgmt command line
utility. The rskeymgmt utility provides a method to backup and apply
the SSRS symmetric key which is used to store data source connection and
configuration data in the SSRS database and configuration file. This key is further used to
allow multiple servers to join a SSRS database when used in a farm or
scale out deployment of SSRS. You can also delete the key information,
but you should be forewarned that all encrypted data is deleted along
with the key when the delete command is used.

About the author

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

They create jobs in the SQL Agent, so you will need to make sure the jobs get created when you start the service.... best place to review these items is: https://msdn.microsoft.com/en-us/library/ms156421.aspx

My question is somewhat related. I have a new SSRS web server, and database server setup. I started setting up reports, and found out one of the departments has a bunch of subscriptions they want to migrate over along with their reports to this new server. I'm not aware of any issues with backing up/ restoring the ReportServer, and ReportServerTempdb over an existing SSRS Report Server are you?

I have a scenario where we want to take SSRS databases from one system and move them onto a new server in a new location quite regularly.

Whenever we do this we find we have to delete the encrypted data and either rekey, or redeploy the reports.

If I restore the key then the old server appears in the scaleout deployment list and can't be removed because the old server is not available to the new server.

What am I missing?

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.