Australian cloud for business software — built and run in Sydney since 2003

+61 2 8282 5656
support@netleverage.com
Talk to an engineer
Talk to an engineer
NetLeverage
NetLeverage

Restoring SQL Server Reporting Services: the encryption key, the two report server databases and what else has to move

Technical article
Backup & Recovery

Originally published:

About this article: AI-assisted writing, reviewed against the cited sources. This is source research, not a lab validation of the procedures. Check the stated versions and your own recovery requirements before making changes.

SQL Server Reporting Services (SSRS) is easy to overlook in a recovery plan. The server itself holds little; reports, schedules, folders and permissions all live in SQL Server databases that are probably already backed up. It is tempting to conclude that restoring those databases restores Reporting Services.

It restores most of it. The part it does not restore is the part that makes reports work: the encrypted connection strings and saved credentials that let a report reach its data, and the subscriptions that send reports on a schedule. Those depend on an encryption key whose separate recoverable backup is required for the new installation.

This article sets out what Microsoft documents and turns it into a checklist for rebuilding a report server on new hardware. It covers native mode; Microsoft notes that Reporting Services integration with SharePoint is no longer available after SQL Server 2016.

A stateless server with two databases

Microsoft's overview of backup and restore operations for Reporting Services explains the design: Because a report server is a stateless server, all application data is stored in the reportserver and reportservertempdb databases on a SQL Server Database Engine instance.

The migration article describes what the main database holds: The report server database contains published reports, models, shared data sources, schedules, resources, subscriptions, and folders. It also holds properties and the permissions on report server content.

Microsoft's backup guidance for the two databases differs:

  • Use the full recovery model for the main report server database.
  • Use the simple recovery model for the temporary database.

The overview explains that the only reason to back up the temporary database is to avoid recreating it after a hardware failure. Its data does not need to be recovered, but its table structure does, and Microsoft warns that If you lose reportservertempdb, the only way to get it back is to recreate the report server database.

In our reading, that last sentence is the reason to keep backing up the temporary database even though its contents are disposable. Losing it turns a short restore into a rebuild.

The migration article adds two warnings about moving the databases. Don't copy the databases; copying doesn't transfer all of the security settings to the new installation. And the two databases are interdependent and must be moved together.

The encryption key needs a separately usable backup

What the key protects

Microsoft's page Configure and Manage Encryption Keys states that Reporting Services uses encryption keys to secure credentials and connection information stored in the report server database. The key at the centre of this is a symmetric key, created when the report server is initialised.

The page also explains why a backup copy matters: If you migrate a report server installation or configure a scale-out deployment, you must have a backup copy of the symmetric key to apply to the new installation.

When to back it up

The backup overview says to back up the key when the installation is first configured, and again whenever the service account identity changes or the computer is renamed.

Microsoft's article Back up and restore SQL Server Reporting Services (SSRS) encryption keys article lists the occasions when the backup copy is restored, including:

The same page draws a distinction worth knowing: Resetting the password isn't the same as changing the password. Microsoft explains that only a password reset requires the key to be restored.

How the backup file works

Microsoft describes two tools for backing up the key: the Report Server Configuration Manager and a command-line utility, rskeymgmt. Either way, the result is a file.

You only need to back up one copy of the symmetric key. Microsoft explains that there is a one-to-one relationship between a report server database and its symmetric key, though in a scale-out deployment the key may be restored to several report servers.

The backup writes the key to a file and protects it with a password you provide. Microsoft notes that the tool adds a .snk extension, and suggests storing the file on a disk separate from the report server.

In our reading, the key file and its password are two separate items to keep. A key file without its password cannot be used, and a password without the file is no help either. We recommend storing them in different places with different controls, and recording where each is.

Key changes make old key backups stale

Microsoft recommends changing the key from time to time. The management page states that Periodically changing the Reporting Services encryption key is a security best practice, and suggests doing it immediately after a major version upgrade. It also describes recreating the symmetric key periodically, for example every few months.

In our reading, this creates a dependency that is easy to miss. A key backup taken before the key was changed matches database backups from before the change, not after. After every key change, we recommend backing up the new key straight away, labelling each key file with the date it was taken, and keeping older key files for as long as database backups that need them are kept. A recovery that pairs a database backup with a different, incompatible key will not decrypt its protected content.

What happens if the key is lost

Microsoft's article on deleting and recreating encryption keys describes the last resort. If for some reason you cannot restore the encryption key, the report server will never be able to decrypt and use any data that is encrypted with that key.

The way back to a working server is to delete the encrypted content. Microsoft lists what that means:

  • Connection strings in shared data sources are deleted, and users running reports see the exact error "The ConnectionString property has not been initialized."
  • Stored credentials are deleted, and reports and data sources switch to prompting for credentials.
  • Model-based reports that require shared data sources with stored or no credentials will not run.
  • Subscriptions are deactivated.

Microsoft is clear that this is permanent: Once you delete encrypted content, you cannot recover it. Connection strings and credentials have to be entered again, and subscriptions reactivated. Unencrypted data, such as the reports themselves, is left intact.

In our reading, the cost of losing the key depends on how many shared data sources, stored credentials and subscriptions the server holds, and on whether anyone still knows the credentials. For a server built years ago by someone who has left, that can be a long and uncertain job. The key backup is cheap by comparison.

Configuration files, extensions and project files

The databases and the key are not the whole server. Microsoft's overview lists configuration files to back up when the server is first configured and after any custom extensions are deployed. Its list includes RSReportServer.config, the policy files, the service configuration file, and the web.config and machine.config files used by the ASP.NET parts of the server. The migration article gives a longer list for migrations, adding the web application and manager policy files.

The overview also recommends backing up:

The migration article adds a detail for customised sites: If you use custom cascading style sheets in your reporting environment, they can't be migrated. They have to be moved by hand.

In our reading, configuration files should be backed up for reference rather than copied blindly onto a new server. Microsoft’s migration steps explicitly say to edit RSReportServer.config to include custom settings from the old installation, which suggests comparing and carrying settings across, not overwriting.

Rebuilding on new hardware

Microsoft's migration article is the closest documented procedure to rebuilding after a hardware loss. In outline, as we read it:

  1. Install a new report server, which Microsoft suggests doing in files-only mode so it can be configured with non-default values.
  2. Restore the two report server databases to the target Database Engine instance, together.
  3. If that Database Engine has never hosted a report server database, create the role the report server needs in the system databases, which Microsoft calls RSExecRole.
  4. Move custom assemblies and extensions.
  5. Configure the report server to use the restored database.
  6. Restore the symmetric key.
  7. Carry across custom configuration settings.

Two notes from the migration article are worth calling out:

  • SQL Server Agent jobs. Don't move SQL Server Agent jobs for scheduled report server operations. Microsoft explains that the report server recreates them automatically. In our reading, this is why subscriptions and schedules come back with the databases, provided the key is restored.
  • Scale-out deployments. If the restored database was shared by several report servers, Microsoft says the old keys stored in the database must be removed before the new server is configured, or it will try to start in scale-out mode. The migration article notes this is done in SQL Server Management Studio, not the Configuration Manager. It explicitly requires backing up the current symmetric encryption key before deleting the old keys. In our reading, this is a change to the restored database and should be done only following Microsoft's current guidance, on the restored copy, with the original backup kept untouched.

A recovery checklist

From the points above, these are our recommendations for what a Reporting Services recovery kit should contain:

  1. Both report server databases, backed up on suitable schedules, and restored together.
  2. The symmetric key file, refreshed after service account changes and renames, stored away from the report server.
  3. The key file's password, stored separately from the file.
  4. Configuration files, kept as a reference for settings to carry across.
  5. Custom extensions, assemblies and style sheets, with their versions.
  6. Report project files and deployment scripts, as a second way to republish content.
  7. A note of what the encrypted data covers: which data sources use stored credentials, and which subscriptions exist, so their loss is noticed if the key ever fails.
  8. A test restore to a server that has never run Reporting Services, ending with a report that reaches its data and a subscription that sends.

Keep the restored server from delivering real reports during a test

A restored reporting service can carry production subscription destinations and stored credentials. Its rehearsal needs an isolated delivery plan so that validating a schedule does not send duplicate reports to customers or write to a production file share. Agree on a controlled destination and representative report before enabling execution. This article describes acceptance criteria, not permission to activate subscriptions on a copied server.

Match the database backup and encryption-key generation, then verify a report using stored credentials as well as one that prompts for credentials. Check custom assemblies, rendering extensions, service identity and access to the underlying data source separately. A report catalogue that opens in the browser only proves that some catalogue data is readable. Record whether the report rendered correctly, whether its authorised users could access it, and whether the controlled subscription completed under the restored service identity.

Risks and limits of this article

  • Based on Microsoft documentation as read on 11 November 2026, for Reporting Services 2016 and later in native mode.
  • This article does not cover Power BI Report Server or SharePoint mode.
  • The step outline is our reading of Microsoft's migration article, not a full procedure. Follow Microsoft's current guidance for the version in use.
  • No restores, key operations or commands were run for this article, and command examples are deliberately not reproduced.

Sources

Writer research: 11 November 2026. Independently reopened for review: 13 November 2026.

This article was originally published on .