Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

SQL Server Reporting Services (SSRS) Encryption Key


By:   |   Read Comments (6)   |   Related Tips: > 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.

key managment help

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.

Access Denied

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".

list SSRS

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".  

Extract

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.

Apply

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.

key delete

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.

regenerate the key

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. 

trace location

trace log

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 


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





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.



    



Monday, August 03, 2015 - 12:36:44 PM - Scott Back To Top

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


Sunday, August 02, 2015 - 7:28:04 PM - eric81 Back To Top

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?


Thursday, September 04, 2014 - 7:18:01 AM - Carl Back To Top

You're right.

I had been doing everything within the Configuration Manager UI, using the command line with appropriate options I can do everything I want.

Cheers.


Tuesday, September 02, 2014 - 8:26:02 AM - scott murray Back To Top

ps... You should be able to use the RSKeyMgmt -r command to remove the old server.


Tuesday, September 02, 2014 - 8:23:15 AM - scott murray Back To Top

That is odd; I have not experienced that particular issues.  When you do a restore are you using the -j arguement?

 


Tuesday, September 02, 2014 - 7:28:30 AM - Carl Back To Top

Hi,

Excellent article.

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?

 


Learn more about SQL Server tools