By: Scott Murray | Comments (7) | Related: > Reporting Services Security
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.
In order to work with a live data for the key process, we will use AdventureWorks 2012 SQL Server database; the database is available on Codeplex at http://msftdbprodsamples.codeplex.com/releases/view/55330. Once you download and install the SQL Server database, we will subsequently use the SSRS 2012 sample reports which can be downloaded at http://advworks2012sssrs.codeplex.com/releases/view/106799. We will assume that you have installed and initialized the SSRS instance and it is up and running.
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.
Next Steps
- SSRS Configuration Manager - http://msdn.microsoft.com/en-us/library/ms157133.aspx
- Scale out SSRS Deployment - http://msdn.microsoft.com/en-us/library/ms159114.aspx
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
