Migrating SQL Server Reporting Services to 2017 and Beyond!

Today, I realized there have been no new blog posts for quite some time! Here is a fun one — migrating SQL Server Reporting Services (SSRS) from 2016 to a new server running SSRS 2017 or 2019.

After beating my head against the wall, it’s a fairly painless process (once you figure out the instructions from Microsoft are wrong).

All of my steps are taken from the documentation provided by Microsoft — “Migrate a Reporting Services Installation (Native Mode)“.

Starting with SSRS 2017, it is no longer part of the SQL Server install — it is now a separate download.

Here’s SSRS 2017:
https://www.microsoft.com/en-us/download/details.aspx?id=55252

Here’s SSRS 2019:
https://www.microsoft.com/en-us/download/details.aspx?id=100122

One other thing I realized, you need your SQL Server product key from the SQL Server installer. Simple: just fire up the installer, start a new installation, copy the product key and then cancel the install.

Step 1: Back Up the Encryption Key (!!!)

Unless you want to basically start over from scratch, back up your encryption key from the existing SSRS 2016 server.

Fire up the Reporting Services Configuration Manager, connect to your instance, click Encryption Keys and click the Backup button.

Save it someplace safe and give it a strong password.

Step 2: Back Up the Databases

You’ll need to back up BOTH the ReportServer and ReportServerTempDB databases. They are a package deal, a married couple, a two-fer, BFFs…you get it.

Step 3: Create the RSExec Role

If you’re moving your SSRS databases to a new SQL Server that never had SSRS installed before, you’ll need to create the RSExec role again. Just follow this guide from Microsoft step-by-step:

https://docs.microsoft.com/en-us/sql/reporting-services/security/create-the-rsexecrole?view=sql-server-ver15

Step 4: Spin up SSRS 2017 (or 2019)

Accept all the defaults and let it install!

Step 5: Restore the Databases

Restore the databases to your fancy new SQL Server instance.

Step 6: Let’s Configure SSRS!

Open the Report Server Configuration Manager, connect to the instance and configure the Service Account, Web Service URL, Web Portal URL and E-mail Settings.

One note about the E-mail Settings: you don’t need to do this now, but since you’re configuring everything, may as well do it now while you’re thinking about it. Then you won’t open the properties of a report, click Subscriptions and then search for a generic error. The error is because the email settings aren’t configured!

Now, time to connect to the databases to SSRS 2017 or 2019. In the Database tab, click the Change Database button and connect to your SQL Service instance and the database you restored. Let it do its thing!

Step 7: DO NOT DO THIS PART MICROSOFT SAYS!

The instructions and guide from Microsoft (linked here) is not clear in the section called “Configure the Report Server.”

Skip the whole two paragraphs about deleting the keys from the Keys table. If you do that, the server becomes unusable, you’ll never get the encryption keys to restore and you get to start over!

This is what you do:

1. Open SQL Server Management Studio.
2. Connect to the ReportServer database.
3. Run this query on the Keys table:

DELETE FROM Keys where InstanceName ='MSSQLSERVER'


You’ll notice there are two other entries in the Keys table — one has an instance name of NULL and the other has an instance name of SSRS. YOU NEED THESE.

Step 8: Restore the Encryption Key

We’re almost done! Now, go back to Report Server Configuration Manager, click Encryption and click Restore. Browse to where you saved the key, enter the password and restore away!

This will give you all green checkmarks and GUESS WHAT?! Your SSRS is migrated, upgraded, the encrypted content is accessible and all of the report subscriptions have been recreated!

YAYAYAYAYAYAYAYYY!

Step 9: Backup the Encryption Key!

Back up the encryption key on the new server and KEEP IT IN A SAFE PLACE (like OneDrive).

Step 10: Pat Yourself on the Back

NICE JOB SELF!

For systems like Dynamics 365 and Dynamics GP, SSRS is a key component you should be running. Benefit from my pain with migration and follow these steps.