Moving the Report Server Databases to another server

We recently had a requirement to move the databases storing our Reporting Services data from one server to another.

Initially, I thought this would be a fairly simple lift and shift - and for the most part it is. However, there were a few SQL user permission related issues to overcome which I thought it was worth blogging about.

The main problem I was having was as I tried to change the Reporting Services SQL user to the one based on the new server, I was receiving an 'applying connection rights' error. As long as you follow all of the steps listed below, this should resolve that issue.

Most of this information comes from Microsoft TechNet, but there are some additional details that I have pooled together from other resources.

Back up ReportServer databases

First off you'll want to back up the ReportServer and ReportServerTempDB from your existing server. Both the reportserver and reportservertempdb databases must be moved or copied together. A Reporting Services installation requires both databases; the reportservertempdb database must be related by name to the primary reportserver database you are moving.

Create the RSExecRole

In order for a user on the new server to have sufficient permissions to run reporting services, you need to:

• Create and provision the RSExecRole in the Master system database
• Create and provision the RSExecRole in the MSDB system database

Create the RSExecRole in Master

Reporting Services uses extended stored procedures for SQL Server Agent service to support scheduled operations. The following steps explain how to grant Execute permissions for the procedures to the RSExecRole role.

1. Start SQL Server Management Studio and connect to the Database Engine instance that hosts the report server database.
2. Open Databases.
3. Open System Databases.
4. Open Master.
5. Open Security.
6. Open Roles.
7. Right-click Database Roles, and select New Database Role. The General page appears.
8. In Role name, type RSExecRole.
9. In Owner, type DBO.
10. Click Securables.
11. Click Search. The Add Objects dialog box appears. The Specific Objects option is selected by default.
12. Click OK. The Select Objects dialog box appears.
13. Click Object Types.
14. Click Extended Stored Procedures.
15. Click OK.
16. Click Browse.
17. Scroll down the list of extended stored procedures and select the following:
a. xp_sqlagent_enum_jobs
b. xp_sqlagent_is_starting
c. xp_sqlagent_notify
18. Click OK, and the click OK again.
19. In the Execute row, in the Grant column, click the check box, and then click OK.
20. Repeat for each of the remaining stored procedures. RSExecRole must be granted Execute permissions for all three stored procedures.

 Create RSExecRole in MSDB

1. Repeat similar steps for granting permissions to stored procedures and tables in MSDB. To simplify the steps, you will provision the stored procedures and tables separately.
2. Open MSDB.
3. Open Security.
4. Open Roles.
5. Right-click Database Roles, and select New Database Role. The General page appears.
6. In Role name, type RSExecRole.
7. In Owner, type DBO.
8. Click Securables.
9. Click Add. The Add Objects dialog box appears. The Specify Objects option is selected by default.
10. Click OK.
11. Click Object Types.
12. Click Stored Procedures.
13. Click OK.
14. Click Browse.
15. Scroll down the list of items and select the following:
    1. sp_add_category
    2. sp_add_job
    3. sp_add_jobschedule
    4. sp_add_jobserver
    5. sp_add_jobstep
    6. sp_delete_job
    7. sp_help_category
    8. sp_help_job
    9. sp_help_jobschedule
    10. sp_verify_job_identifiers
16. Click OK, and the click OK again.
17. Select the first stored procedure: sp_add_category.
18. In the Execute row, in the Grant column, click the checkbox, and then click OK.
19. Repeat for each of the remaining stored procedures. RSExecRole must be granted Execute permissions for all ten stored procedures.
20. On the Securables tab, and click Add again. The Add Objects dialog box appears. The Specify Objects option is selected by default.
21. Click OK.
22. Click Object Types.
23. Click Tables.
24. Click OK.
25. Click Browse.
26. Scroll down the list of items and select the following:
    1. syscategories
    2. sysjobs
27. Click OK, and the click OK again.
28. Select the first table: syscategories.
29. In the Select row, in the Grant column, click the checkbox, and then click OK.
30. Repeat for the sysjobs table. RSExecRole must be granted Select permissions for both tables.

Applying the database permissions to a SQL user

For the SQL user that you are planning to use to run Reporting Services, provide them with the following permissions:

• RSExecRole, db_accessadmin & db_owner for the Master database
• RSExecRole, db_accessadmin & db_owner for the MSDB database
• RSExecRole & db_owner for the ReportServer database
• RSExecRole & db_owner for the ReportServerTempDB database

How to Configure the Report Server Database Connection

1. Start the Reporting Services Configuration Manager and open a connection to the report server.
2. On the Database page, click Change Database. Click Next.
3. Click Choose an existing report server database. Click Next.
4. Select the SQL Server that now hosts the report server database and click Test Connection. Click Next.
5. In Database Name, select the report server database that you want to use. Click Next.
6. In Credentials, specify the credentials that the report server will use to connect to the report server database. Click Next.
7. Click Next and then Finish.

SSRS Subscriptions / Scheduled operations

It is important to note that moving a report database does not effect scheduled operations that are currently defined for report server items - i.e. your SSRS subscriptions.

Once you have completed the migration of the databases, you must restart the Report Server service. All schedules will be recreated on this first restart.

You might also want to delete the old SSRS SQL jobs on the server that will no longer be used - these will not be removed without manual intervention!

 That's it - all done :)