This is the second part of my two part series on Reporting Services Encryption. The first part of the series can be read here.
This article will explain how we maintain the keys and various recovery scenarios.
What Data is Encrypted
First off let’s understand what data is encrypted in Reporting Services.
Reporting Services encrypts and stores in the ReportServer database the following:
· Data Source Connection Strings. This is the information required to connect to a data source in order to retrieve data for a report. The connection string could contain a username and password.
· Data Source Credentials (Username and Passwords). These are the stored credentials outside of the connection string used for connecting to a data source in order to retrieve data for a report.
· Subscriptions that store credentials, for example when using the File Share delivery mechanism. This is the credentials required to access the file share.
· Unattended User Account Credentials. This account is used for unattended report processing where you would not need an account to access a data source.
Encrypted data is held in the following tables in the ReportServer database:
· DataSource
· Subscriptions
What about the Credentials to Connect to the ReportServer Database
You may have noticed that there is some encrypted data in the ReportServer configuration file:
C:\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\ReportServer\rsreportserver.config
Under the <Configuration> section is a <DSN> element with encrypted data. This holds the Connection String the ReportServer web service and Report Server Windows Service uses in order to connect to the ReportServer database. During Reporting Services Configuration when you went through the Database Setup, you had to select the credentials for connecting to the ReportServer database. By default the connection allows windows authenticated access however; you can change the connection string information using the command RSCONFIG.EXE
Also under the <UnattendedExecutionAccount> section is the encrypted account name used for Unattended Execution operations. This can be set using the Reporting Services Configuration Manager or using the command RSCONFIG.EXE.
The data is encrypted using CRYPTPROTECTDATA and CRYPTUNPROTECTDATA API’s using the machine key. A machine key is generated for a machine that any user can use to decrypt data. This is important because the account running the ReportServer application pool and the Reporting Services windows service account needs to be able to connect to the ReportServer database however; they do not need to be the same account name.
Maintaining the Encryption Keys
Maintaining the keys is about having a backup and restore strategy for the keys and the ability to recover from any situation.
Backing up the Symmetric Key
Once a symmetric key is created it remains the same for the life of the Reporting Services installation or until it is manually changed by a DBA. So theoretically we only need to backup a key once or after it is changed however; you would have to be very confident that the backup file is available if you ever need to recover it.
The backup of the symmetric key only takes seconds to complete so my advice would be to schedule it on a weekly basis or after a key change and ensure the backup file is written to tape. This way you do not run the risk where having one tape from being corrupt, lost or reused.
When you backup the Symmetric key you need to supply a password to the backup. This is to ensure the Symmetric key is not stored in the backup file in a decrypted format which can easily be feed into an app to decrypt the information in Reporting Services. So this means you need to manage a password for the backup files otherwise you will never be able to restore the key. How you do this will depend on the number of DBA’s you have, whether there are separate global teams and what systems you have in place to store passwords.
Note: The Asymmetric keys are not backed up because they can be regenerated automatically from the Reporting Services Windows Service. I will explain this in more detail in the coming sections.
There are two ways to backup the Symmetric key:
· Use the Backup option under Encryptions Keys in the Reporting Services Configuration Manager for the Reporting Services instance and supply a password and backup file.
· Use the command RSKeyMgmt.exe as shown below
For a default installation the RSKeyMgmt.exe command will be in the folder:
C:\Program Files\Microsoft SQL Server\90\Tools\binn
You run the command with the following parameters
RSKeyMgmt –e –f <backup file> -p <password> -i <instance>
-e means Extract to retrieve the Symmetric Key
-f is the file to contain the Symmetric key
-p is a password which is used to encrypt the Symmetric key before it is written to the backup file
-i is the local Instance of Reporting Services. The default is MSSQLSERVER which points to a default instance.
Note: There is no Server parameter in order to backup the keys remotely. You can manually back them up remotely using the Reporting Services Configuration Manager and connecting to the remote instance.
Restoring the Symmetric Key
I will now explain how restore the Symmetric key from a backup however; in later sections I will explain when to restore a key.
The restoration of the key only updates the Keys table in the ReportServer database. The key is extracted from the backup file, encrypted with the Public Asymmetric key for that instance and then written into the Keys table. If the restoration of the key is successful this does not mean any credentials can be decrypted, this can only be determined by running a report.
There are two ways to restore the Symmetric key:
· Use the Restore option under Encryptions Keys in the Reporting Services Configuration Manager for the Reporting Services instance and supply a password and backup file.
· Use the command RSKeyMgmt.exe as shown below
For a default installation the RSKeyMgmt.exe command will be in the folder:
C:\Program Files\Microsoft SQL Server\90\Tools\binn
You run the command with the following parameters
RSKeyMgmt –a –f <backup file> -p <password> -i <instance>
-a means Apply to restore the Symmetric Key
-f is the file to contain the Symmetric key
-p is a password which was used to encrypt the Symmetric key before it is written to the backup file
-i is the local Instance of Reporting Services. The default is MSSQLSERVER which points to a default instance.
Note: There is no Server parameter in order to restore the keys remotely. You can restore them remotely using the Reporting Services Configuration Manager and connecting to the remote instance.
How Do I Change the Symmetric Key
OK so now we are getting into the sections which deal with recovery and troubleshooting.
For security or auditing reasons you may be forced to periodically change the Symmetric key. This process is very straight forward however; there is one important point you must understand. You cannot change the Symmetric key if you cannot first decrypt the data with the original key. So this means if you lose the Symmetric key for any reason, you will not be able to decrypt the data and give it a new key. In this situation your only choice is to delete the encrypted data as described further on.
You have to make sure that there are no reports running or users accessing using Reporting Services when you do this. The length of time to complete the change will depend on the number of entries in the DataSource and Subscriptions tables. Also note in a Scale-Out environment when there are two or more Reporting Services, changing the Symmetric key will change it for all the Reporting Services installations and so you need to ensure neither one is being used.
You can prevent anyone from using Reporting Services as follows:
· Start the SQL Server Surface Area Configuration tool
· Click Surface Area Configuration for Features
· Select your Reporting Services Instance
· Click Web Service and HTTP Access
· Remove the check from Enable Web Service and HTTP access
There are two ways to change the Symmetric key:
· Use the Change option under Encryptions Keys in the Reporting Services Configuration Manager for the Reporting Services instance and supply a password and backup file.
· Use the command RSKeyMgmt.exe as shown below
For a default installation the RSKeyMgmt.exe command will be in the folder:
C:\Program Files\Microsoft SQL Server\90\Tools\binn
You run the command with the following parameters
RSKeyMgmt –s -i <instance>
-s means we are going to generate a new Symmetric key and re-encrypt all the encrypted data.
-i is the local Instance of Reporting Services. The default is MSSQLSERVER which points to a default instance.
Important: Now you have generated a new key make sure you make a backup straight away.
When do I Need to Restore the Symmetric Key
Firstly let’s discuss what changes to Reporting Services causes a need to restore the Symmetric key. As I have mentioned before the Symmetric key never changes otherwise you cannot decrypt the data. If you do change the key you would have to decrypt the data with the old key and then encrypt it again with the new key and described in the previous section.
There are occasions when you need to restore a Symmetric key even when it has not changed. This is when the Asymmetric key has changed and you need to encrypt the original Symmetric key with the new Public Asymmetric key.
The following list details when you should restore the Symmetric Key
· Changing the Service Account of the Reporting Services Windows Service
· Resetting the password for the Reporting Services Windows Service account
· Rebuilding a Reporting Services Machine
· Upgrading Reporting Services
Interestingly there are places in Books Online that says renaming the machine will require the symmetric key to be restored. When I tested renaming a machine it worked OK without requiring a restore. In fact the only thing I had to do after the rename is what is shown in books online under “Renaming a Report Server Computer”.
Changing the Service Account of the Reporting Services Windows Service
I explained in the first article that the Asymmetric keys are generated by the Windows Service. It generates keys based on what we call Machine Store and User Store values. The Machine Store is the name of the machine and the User Store is based on the User that generates the key i.e.; the Windows Service account. The reason for having two is so we are able to generate the original Private key if either the Machine name changes or we change the Windows Service account. This is so we can decrypt the Symmetric key with the original Private key and re-encrypt with the new Private key. Obviously what we cannot do is change both the machine name and the User account at the same time.
If you ever need to change the Windows account then use the Reporting Services Configuration Manager to complete the task. This is because it carries out various tasks under the covers to ensure it has the right access.
You can change the Service Account as follows:
· Start the Reporting Services Configuration Manager for the Instance you wish to change
· Click on Windows Service Identity on the left hand side
· Enter or Select the Account you wish to
· Click Apply at the bottom of the Screen
· You will be prompted to backup the Symmetric key. Enter a password and a file name
· Click OK
· When prompted for an Administrator account in order to carry out the changes, select and enter the appropriate credentials for your installation.
· The change will now take place and complete when the Task Status finishes with “Restoring Encryption Key”
Resetting the password for the Reporting Services Windows Service account
If you change the password of the windows service account by specifying the old password followed by the new password, the keys remain the same and everything is fine. If you have a system administrator reset your password by not specifying the old password, the keys become invalid.
If the Windows password is reset you will need to perform the following:
· Stop the Windows service
· Change the Password for the Windows Service using the services.msc snap in.
· Restart the Windows service
· Check the Windows Service Errorlog ReportServerService_<timestamp>.log in C:\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\LogFiles and look for any Encryption errors.
· If the Windows service is unable to decrypt the Symmetric key, restore the Symmetric key from your backup.
· Stop and Start the Windows Service and repeat the above.
Rebuilding a Reporting Services Machine
When you rebuild a Reporting Services Installation, in order to use an existing encryption key you need to ensure the following are the same:
· The Installation ID is the same
· The Machine Name is the same
· The Instance Name is the same
· The Windows Service account and password are the same.
The first three are held in the Keys table in the ReportServer database and are used to identify the row containing the Symmetric Key. The Windows Service account and password are used to generate the Asymmetric keys.
The Installation ID is contained in the rsreportserver.config file in the ReportServer Virtual Directory under the element <InstallationID>.
Any changes in the above will require you to restore the Symmetric key from the backup.
Upgrading Reporting Services
Books Online describes the upgrade process which I will not cover here. I just wanted to add that depending on how you do the upgrade will depend on whether you need to restore the Symmetric key.
Troubleshooting Encryption Failures
What Errors Indicate an Encryption Problem
A good indication as to whether you have an encryption problem is to run a report that uses stored credentials. You may get the error
An error has occurred during report processing.
The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content and then restart the service.
Check the documentation for more information.
(rsReportServerDisabled) Bad Data.
You can also receive a similar message in the log file ReportServerService_<timestamp>.log when the Reporting Services Windows Service starts up and validates the key in the ReportServer database.
If you receive any errors that are related to decryption problems then just restore the Symmetric key. There are no side affects or issues restoring the key. It is also worth restarting the Windows service after you restore the key and ensure there are no error messages in the log file ReportServerService_<timestamp>.log
I mentioned that the credentials used by the Windows Services and Web Services are stored in the configuration file rsreportserver.config in the ReportServer Virtual Directory. If there is an issue decrypting this information then you will get the following error:
The encrypted value for configuration setting Dsn cannot be decrypted. (rsFailedToDecryptConfigInformation)
An internal error occurred on the report server. See the error log for more details. (rsInternalError)
You can use the RSCONFIG.EXE utility to specify and verify the account used for connecting to the ReportServer database.
What happens if I do Not have a Backup
If you do not have a backup then you will not be able to decrypt any encrypted data. This is very bad news and can result in a lot of effort to manually recreate all the data sources and subscription information.
When you delete the Symmetric key the Windows service will automatically create a new one. In a Scale-Out environment you will need to initialize each Report Server from the server where you originally deleted the key.
There are two ways to delete the encrypted content.
· Use the Delete option under Encryptions Keys in the Reporting Services Configuration Manager for the Reporting Services instance and supply a password and backup file.
· Use the command RSKeyMgmt.exe as shown below
For a default installation the RSKeyMgmt.exe command will be in the folder:
C:\Program Files\Microsoft SQL Server\90\Tools\binn
You run the command with the following parameters
RSKeyMgmt –d -i <instance>
-d means we are going to delete all the encrypted content
-i is the local Instance of Reporting Services. The default is MSSQLSERVER which points to a default instance.
After this command is run you need to manually add back in all the credential information.
You can list all the Data Sources by running the following in the ReportServer database and then try and determine what information need adding.
SELECT c.Path
FROM DataSource d
JOIN Catalog c ON d.ItemID = c.ItemID
WHERE c.Type = 5
ORDER BY 1
You can list all the Subscriptions by running the following in the ReportServer database and then you need to view each of them to determine which ones need credentials added.
SELECT c.Path,
s.Description
FROM Subscriptions s
JOIN Catalog c ON c.ItemID = s.Report_OID
ORDER BY 1,2
That brings me to the end of this article. I hope it was useful.