Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Recovery Planning For SQL Reporting Services

By Mike Pearson, (first published: 2005/01/06)

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.

Total article views: 28688 | Views in the last 30 days: 21
 
Related Articles
FORUM

Encrypt the whole database

Encrypt the whole database

FORUM

Fresh install no database server

Fresh install no database server

FORUM

SQL Server 2005 encryptation

SQL Server 2005 encryptation

FORUM

Encrypting the entire database.

Encrypting the entire database.

FORUM

Problem with encryption

encryption

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones