SQL Server Reporting Services SSRS 2017 Backup and Encryption Key Maintenance




By:
Overview

Now that you have your SSRS reports and website up and running, you need to think about the backup processes you will employ to keep your work from succumbing to a disaster.

Source Control - check!

First, if possible, I would recommend using a source control system to maintain the report, data source, solution, and project files that you are developing in SSDT-BI (Visual Studio). A source control system, such as Team Foundation Server (TFS), will allow you to maintain multiple iterations of each of these files. If you do not use a source control system, you should have a file backup process in place to copy files to an alternate location. Additionally, if you customize your SSRS settings, it would be beneficial to also backup and source control the report server configuration files such as, Rsreportserver.config, Rssvrpolicy.config, Rsmgrpolicy.config, Reportingservicesservice.exe.config, and Web.config.

Backup Databases - check!

After setting source control and / or backup for your SSRS files, the next step is to regularly backup your ReportServer and ReportServerTempDB databases. The backup process is no different than the method you would use to backup other databases; for details on completing a full database, I would recommend this tutorial.

Also, be sure that if you are using a 3rd party backup tool, it is completing SQL Server native backups and not just a file system or image backup. The size of the reporting services databases is usually rather small, so full backups can normally be easily executed.

Backup Encryption Key - check!

When security is employed by SSRS, it applies encryption to the following items:

  • Data source credentials.
  • The unattended user account information which is used to connect to a remote server in order get external images or data.
  • Encrypted values in a subscription.
  • Credentials used to connect to the Report Server database itself.

In the event you restore a SSRS database, the encryption key will need to be loaded onto the new server to allow that server to read and utilize all the items noted in the above list. Otherwise an error will result when attempting to navigate to the Report Server. 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 security for those folders (and related reports) too. Having the key file provides a quick and efficient way to restore security for a restored SSRS instance.

Backing up (and restoring the key) can all be done from the SQL Server 2017 Reporting Services Configuration Manager.

backup key - Description: backup key

Clicking on the Backup button allows for the creation of the SSRS Key file. Be sure to use a sophisticated password for the key file, keep the SNK encryption file somewhere safe (source control maybe!), and also log the password (for remembering later) in your password manager.

backup encryption key

In the event a SSRS database needs to be restored onto a new server, the key file is loaded using the restore option. Once you select the location and file, the password needs to be entered.

restore encryption key

The key can also be regenerated using the change option or deleted using the delete option. However, note the delete option requires you to re-establish encrypted items including data source connections and subscriptions. This option is a last resort effort.

With a backup plan in place, you are now ready to show off your report server work to your boss, your colleagues, and whomever else will listen!

Additional Information

Last Update: 12/29/2017




More SQL Server Solutions











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.






download





get free sql tips

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.



Learn more about SQL Server tools