This paper is based on testing and recovery steps that I have tried and

tested in various environments.  Hopefully they will serve as a useful

guide, and if there are any other experiences or feedback out there I'd be

grateful for it.

Before I start I need to cover off a few things regarding encryption keys, as

the recovery planning steps discussed are based on the following assumptions:

1. There are regular and up to date backups of the ReportServer and

   ReportServerTempDB databases

2. There is a backup of the encryption keys

Managing the Encryption Keys is a core to any SQLRS database recoveries that

may need to be done. It is therefore recommended that those responsible for

supporting SQLRS instances familiarise themselves with the concepts discussed

SQLRS Books Online (BOL) under “Managing Encryption Keys”

During setup SQLRS creates encryption keys, which are then used to secure

credentials, connection information, and accounts that are used in server

operations. As soon as you finish installing SQLRS, you should make a copy of

the symmetric key. If you ever need to repair a Reporting Services installation due to changes in computer name, instance name, or user account values, you can apply the key

to make the report server database operational.

Note: You can still recover a report server database if you do not have a key

backup. In this case, you must delete encrypted data and will need to respecify

all encrypted values used in your installation. (Deleting encrypted data is

discussed later in this paper).

Backing Up The Encryption Keys

To back up the Encryption Keys, use the rskeymgmt utility to extract a copy of

the encryption key from the ReportServer database. This utility writes the key

to a file that you specify, and then scrambles the key using a password that you

provide. After the file is created, it should be stored in a secure location, as

well as the appropriate steps taken to protect the password for this file.

To create a backup of the encryption key, run rskeymgmt.exe locally on the

computer that hosts the report server. You must use the -e extract argument to

copy the keys, provide a fully-qualified file name, and specify a password. The

following example illustrates the arguments you must specify:

rskeymgmt -e -fc:\rsdbkey.txt -p<password>

This .txt file should then be moved off the c:\ root and saved to the

appropriate location on the network.

Now lets discuss some of the recovery scenarios

Scenario A: Server Farm

To help clarify these examples, we are going to assume that

• the ReportServer and ReportServerTempDB databases are located on a SQL

  Server called SQL01

• the SQLRS components are installed on a web server called WEB01

Loss of SQL Server backend.

In this scenario the recovery plan is relatively straightforward in that it

is a case of simply restoring the backups of the ReportServer and

ReportServerTempDB databases in order for the report server to connect as

expected.

In the event of the backend server being rebuilt with a new name, or migrated

to a different SQL Server, then once the database backups have been restored the

rsconfig utility needs to be run with the suggested usage below.

rsconfig -c -s<ServerName> -dReportServer

-a<Authtype> -u<username> -p<password>

<ServerName> is the new Server name <Authtype> is ‘Windows’ or ‘SQL’

authentication of the service account SQLRS will use to connect to the

ReportServer database <UserName> & <Password> is the name/password of the

service account mentioned above.

Eg : rsconfig -c –sSQL01 -dReportServer -aWindows

–uSDM\svcSQLRS –ppass@word

Loss of the SQLRS FrontEnd

This scenario is somewhat more complicated because it involves the loss of

the config files and binaries which hold the encryption data. The first step is to reinstall the SQLRS components. Some of points to note in this regard:

1. You will need to remove the ReportServer and ReportServerTempDB

   databases from SQL01 as the installation will need to create it’s own.

2. Simply reinstalling on the server with the same name is not sufficient

   to retain the encryption data in the encryption keys. Even if the new

   computer uses the same name as the old computer, the installation ID in

   RSReportServer.config will not be valid for the new computer.

3. You must install the SQLRS component to the same Version Level as the

   previous installation otherwise the databases will return compatibility

   errors.

Once the installation has been completed these new databases can be backed up

and then dropped; and replaced with the original ReportServer and

ReportServerTempDB databases. At this point, when you browse to http://WEB01/Reports you will get the following error:

“The report server installation is not initialized. Check the

documentation for more information. (rsReportServerNotActivated) Get Online Help

-2147159550 0x800a0bcd”

This is because the keys used by the SQLRS services do not match the

encryption keys stored in the database. We now have 2 options:

A) We have a backup of the Key File

If we have our original encryption keys backed up, we can run the rskeymgmt

utility.

rskeymgmt -a -fc:\rsdbkey.txt -p<password>

We will need to run IISReset after this, and this is all that should be

required. However, after this step, the browsing to http://WEB01/Reports may

still produce this error:

“The report server installation is not initialized. Check the

documentation for more information. (rsReportServerNotActivated) Get Online Help

-2147159550”

If this occurs first try and rerun the rskeymgmt command again. If this keeps

failing the next thing to try is the RSActivate command as follows:

RSActivate –c "C:\Program Files\Microsoft

SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config"

If this step still fails then there is no choice but to drop into option B.

This is because a report server will be activated only if there is a match

between the installation identifier and the public key. If the match succeeds, a

symmetric key is created that permits reversible encryption. If the match fails,

the report server is disabled, in which case you may be required to delete any

encrypted data.

B) We don’t have a Key File Backup

If we don’t have a key file then we are going to loose all the encrypted data

such as secure credentials, connection information, and accounts that are used

in server operations. These will need to be recreated manually. However, we

should not loose the Report definitions, structure, snapshot histories,

subscription information etc.

The first thing we’ll need to do is to delete the existing encryption data

using the rskeymgmt command as follows: rskeymgmt –d

This will clear the encryption keys that exist, and should get us to the

stage where the reports are all functional, but are simply missing the

configuration data. So information such as secure credentials, connection

information etc will need to be recaptured.

Scenario B: Single Server Installation

To help clarify these examples, we are going to assume that the ReportServer and ReportServerTempDB databases are located on a single SQL Server

instance called SQL01. This scenario is virtually identical to the section in scenario A which deals with the loss of SQL FrontEnd components as this case also involves the loss of the config files and binaries which hold the encryption data.

The first step is to reinstall the SQLRS components. Some of points to note

in this regard:

1. If they still exist and are attached, you will need to remove

   the ReportServer and ReportServerTempDB databases from SQL01 as the installation

   will need to create it’s own.

2. Simply reinstalling on this same server is not

   going to be sufficient to retain the encryption data in the encryption keys

   because the installation ID in RSReportServer.config will not be valid for the

   new installation.

3. You must install the SQLRS component to the same Version

   Level as the previous installation otherwise the databases will return

   compatibility errors.

Once the installation has been completed these new databases can be backed up

and then dropped; and replaced with the original ReportServer and

ReportServerTempDB databases.

At this point, when you browse to http://SQL01/Reports you will get the

following error:

“The report server installation is not initialized. Check the

documentation for more information. (rsReportServerNotActivated) Get Online Help

-2147159550 0x800a0bcd”

This is because the keys used by the SQLRS services do

not match the encryption keys stored in the database.

We now have 2 options:

A) We have a backup of the Key File

If we have our original encryption keys

backed up, we can run the rskeymgmt utility.

rskeymgmt -a -fc:\rsdbkey.txt -p<password>

We will need to run IISReset after this, and this is all that should be

required.

After this step, the browsing to http://SQL01/Reports may still this error:

“The report server installation is not initialized. Check the documentation

for more information. (rsReportServerNotActivated) Get Online Help -2147159550”

If this occurs first try and rerun the rskeymgmt command again. If this keeps

failing the next thing to try is the RSActivate command as follows:

RSActivate –c "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting

Services\ReportServer\RSReportServer.config"

If this step still fails then there is no choice but to drop into option B.

This is because a report server will be activated only if there is a match

between the installation identifier and the public key. If the match succeeds, a

symmetric key is created that permits reversible encryption. If the match fails,

the report server is disabled, in which case you may be required to delete any

encrypted data.

B) We don’t have a Key File Backup

If we don’t have a key file then we are going to loose all the encrypted data such as secure credentials, connection information, and accounts that are used in server operations. These will need to be recreated manually. However, we should not loose the Report definitions, structure, snapshot histories, subscription information etc. The first thing we’ll need to do is to delete the existing encryption data using the rskeymgmt command as follows:

rskeymgmt –d

This will clear the encryption keys that exist, and should get us to the

stage where the reports are all functional, but are simply missing the

configuration data. So information such as secure credentials, connection

information etc will need to be recaptured.