Upgrade from SharePoint 2007 to SharePoint 2010 - Reporting Services in SharePoint Integrated Mode

  • Article

When upgrading existing contents from SharePoint 2007 to SharePoint 2010, there are scenarios where we need to ensure existing SSRS reports still work. For the SharePoint 2007 environment, in this case, we have Reporting services (SQL 2005) in the SharePoint integrated mode. In previous post, I have outlined how to configure Reporting services (SQL 2008 R2) in the SharePoint integrated mode for SharePoint 2010 environment. In this post, we will look at what we need to migrate from the old environment in order to ensure existing reports still work in SharePoint 2010.

After SharePoint content database has been upgraded from 2007 to 2010, when running existing reports, you might get different types of errors related to the report server, rdl files, or unrecognized accounts. We need to migrate existing report server databases to the new report server.

 Backup Encryption Key from Report Server

  • From the Report server in the SQL 2005 environment, launch Reporting Services Configuration Manager
  • Connect to the reporting services currently integrated with SharePoint 2007
  • Click Encrytpion Keys
  • Click Backup button
  • At the prompt, select a file path to store encrytion key and create a password to be used later when restoring the key in the new environment
  • Click OK

 

Backup Report server databases from SQL 2005

  • Launch SQL management studio to connect to the SQL 2005 server hosting the report server databases
  • Select ReportServer and ReportServerTempDB databases to backup

Restore Report server databases in SQL 2008 R2

  • Launch SQL management studio to connect to the SQL 2008 server that will be hosting the report server databases
  • Select ReportServer and ReportServerTempDB databases to restore respectively

Configure Report services in the SQL 2008 environment to use restored databases

  • From the Report server in the SQL 2008 environment, launch Reporting Services Configuration Manager
  • Connect to the reporting services currently integrated with SharePoint 2010
  • Click Databases
  • Select "Choose an existing report server database"
  • Connect to the SQL server hosting the restored report server databases, select the ReportServer database

Restore Encryption Key to the new Report Server

  • From the Report server in the SQL 2008 environment, launch Reporting Services Configuration Manager
  • Connect to the reporting services currently integrated with SharePoint 2010
  • Click Encrytpion Keys
  • Click Restore button
  • At the prompt, select the backup encrytion key file and type the password used earlier when backing up the key in the old environment
  • Click OK

Once the configurations are set, you might see the following error in the event log.

You will need to restart the reporting service (SQL Server Reporting Services) from Services to ensure all the changes have been updated.

NOTE: If the Reporting services add-in was installed and activated before the content database was upgraded, you need to activate the Reporting feature at the site collection level.

 

[Optional - if database server for data sources used in existing reports has been changed/moved]One last step, update the existing data source file to point to the new database server.

  • Navigate to the list hosting Reports Data Connection
  • Select the data connection file (.rsds) > Send to > Download a copy
  • Modify the rsds file in notepad: change "Data Source" value in <ConnectString> to point to the new database server name
  • Upload the updated rsds file to the Reports Data Connection list

 

 [Optional - when loading a report, if you get this error: "the user or group name is not recognized"]

This error could be resulted from moving the reports from one domain to another.

  • You need to download a copy of the report locally.
  • Delete the original report from SharePoint
  • upload the report to SharePoint again.

[Optional - when loading a report, if you get this error: "The report server cannot process the report or shared dataset. The shared data source for the report server or SharePoint site is not valid."

This error could be resulted from an update to the shared data source.

  • Highlight the report. Click on the arrow to the right of the report
  • From the dropdown, click "Manage Data Sources"

 

  • Click the name of the shared data source
  • For Data Source Link, click the button next to the link field, navigate the site to set the location of the shared data source (You may need to click UP arrow to find the Data connection list).
  • Click OK