My experience with migrating SSRS reports from SSRS 2014 to SSRS 2017

In this post I would like to share my experience of migrating our Reporting server from sql server We installed SQL SERVER 2017 on a new VM.  Now I wanted to migrate our SSRS reports to this server.

There are two steps involved to migrate SQL Server Reporting Services to SSRS 2017.

• ·       The first step is to Install the SQL Services Reporting Services application. 
• ·       The second step is to migrate the existing reports into the new installation.

Step 1

Installation of SQL Server Reporting Services 2017

In SQL Server 2017, it is available as a separate menu item on the Installation Tab of the SQL Server 2017 Installation center as shown below.

When you click on this, it takes you to the download page of SQL Server Reporting Services as shown below.  

SSRS 2017 download link

This is because now because of the integration between Power BI Report Server and the Reporting Services there is a separate installer.

Click on the Download button.

 After the file is downloaded, double click on the application and install SQL Server Reporting Services as below.

I have posted some screenshots below.

  Choose Developer in the below screen. 

 Choose Configure Report Server in the below screen. 

This will open the Reporting Services Configuration Manager as shown below:

I have not made any changes to the Service Account tab.

On the Web Service URL Tab - I have just clicked apply to get the recommended suggestions updated.

On the Database Tab -- I have clicked on the Change Database button -- Opted for Creating a new database named (ReportServer) and clicked on apply.  This created two new databases named ReportServer and ReportServerTemp.

On the Web Portal URL Tab -- I have clicked  Apply so that the default settings are applied.

I did not make any other changes and clicked Exit.

Now, I tried to connect to the browser url http://localhost/reportserver , then the following error appeared.

To get rid of this error, I followed the below steps.

1. Bring up SQL Server 2017 Configuration Manager .
2. Click the dropdown arrow in front of “SQL Server Network Configuration”, and click the “Protocols for MSSQLSERVER”. (If you are using a named SQL Server instance, please select the named instance instead.)
3. Make sure both the “TCP/IP” and “Named Pipes” protocols are enabled. If they are disabled, enable them by clicking on the dropdown as shown

Then I got an access denied error.  

The report server cannot open a connection to the report server database. The log on failed. (rsReportServerDatabaseLogonFailed) Get Online Help Cannot open database "ReportServer" requested by the login. The login failed. Login failed for user

To get rid of this, I followed the below steps

1. Bring up the  SQL Server Reporting Services Configuration Manager,
2. Click the "Database" tab and click change credential as shown below.

1. Make sure the “Current User-Integrated Security” is selected and click Next.
2. Ensure that the “Authentication Type” is set to “Service Credential”. This will use the user that the Reporting Services running under to connect to the report server database as shown below.
3. Click Next and Next to apply.

Now I was able to access the ssrs site http://localhost/reportserver

Step 2

Migrate the existing reports into the new installation of SQL Server Reporting Services 2017

The next step is to migrate the existing reports into the new installation.  To do this, follow the below steps.

• 1.     Back up the ReportServer and ReportServerTemp databases from the old Reporting Services installation. 
• 2.     Restore these two database on to the new SSRS installation database.

Now you will find the reports from the old SSRS server in the new SSRS server if you browse through the url http://localhost/reports on the new ssrs installation box.

So I tried running a report, but I encountered the below error.

  

• The report server was unable to validate the integrity of encrypted data in the database. (rsCannotValidateEncryptedData)

When you restore the ReportServer from a back up, it also restores the encryption keys.  So in order to resolve this error, I had to delete the encryption keys and regenerate them.  To do this go to Reporting Services Configuration Manager and click on Encryption Key tab and click on the delete button and Apply as shown below.

After getting this done, the report execution was successful.

However, when I looked at the Subscriptions of each report, they were not transferred.  So I will be migrating the subscriptions later and I will post my experience.

To be continued…………..