Backup your Reporting Services Encryption Key

If you run SQL Server Reporting Services, part of your DR plan needs to include a backup of the encryption key for SSRS. This sadly is an all to often overlooked part of the solution, even though it is incredibly easy to do. If you don’t have a backup of the encryption key during a restore, the report server will never be able to decrypt the encrypted content (connection strings, passwords, etc) stored in the database, and your only recourse would be to delete the encrypted content and recreate it manually or through a redeployment of datasources.

https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/ssrs-encryption-keys-back-up-and-restore-encryption-keys?view=sql-server-2017

For those that are into Powershell, Microsoft has provided a simple function Backup-RsEncryptionKey that can be utilized for this as well:

https://www.powershellgallery.com/packages/ReportingServicesTools/0.0.0.2/Content/Functions%5CAdmin%5CBackup-RsEncryptionKey.ps1

Make sure that you’ve covered all your bases with backups of other keys and certificates as well. Even if you think you’ve got your bases covered, now is a great time to verify, especially for anyone using Transparent Data Encryption for a database. One of the worst emails we’ve ever gotten from someone in the community was that they had backups of their database but not the certificate used for encryption and they couldn’t restore the database or access the data, and there is absolutely nothing anybody can do to help you if this happens.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.