Reporting service ENCRYPTION KEY automated backup REPORT utility

I’ve come across immeasurable number of circumstances where I meet customers with a non working Reporting Service production instance due Encryption key lost or corrupted.

To add more to the agony we end up figuring out that there is no valid encryption key back and hence all your data source information are lost, subscriptions are disabled etc., etc..

This made to think for a while on how this encryption key can be backed up automatically and more importantly with minimal human effort. The process that I’ll be discussing in this blog is just the evolution of the longer accumulation of my thoughts which has bubbled up as a handy tool.

Ever since Reporting service 2000 was released first and till now, the importance of reporting service encryption keys has remained on top of the items to be secured for an obvious reason,

Reporting Services uses encryption keys to secure credentials and connection information that is stored in a report server database. In Reporting Services, encryption is supported through a combination of public, private, and symmetric keys that are used to protect sensitive data. The symmetric key is created during report server initialization when you install or configure the report server, and it is used by the report server to encrypt sensitive data that is stored in the report server. Public and private keys are created by the operating system, and they are used to protect the symmetric key. A public and private key pair is created for each report server instance that stores sensitive data in a report server database.

 

So, if you either loose the encryption keys or corrupt the keys, you’ll lose all your data source connection and other sensitive information that are stored in reporting services. This will result in report execution errors, disabling of subscriptions etc..

Possible reasons for the encryption keys to be corrupted?

The most common mistake that happens is, when you change the Reporting services service credentials from the services.msc. This is certainly not the right approach and just remember, Reporting service encryption key corruption is an irreversible process. So if the key is lost or corrupt, then your production server instance will certainly be down and will not be able to process any report until corrective measures are taken like restoring a valid key back up and if you don’t have one, then reconfiguring all the report data sources. Again remember neither the product support nor the product development team can help you to get the keys back unless you’ve a valid backup of the encryption keys.

When you change the Report Server Windows service account, the asymmetric keys can become invalid, which will disrupt server operations. To avoid this problem, always use the Reporting Services Configuration tool to modify service account settings. When you use the configuration tool, the keys are updated for you automatically. For more information, refer Configure the Report Server Service Account.

 

Right from day 1, the responsibility of taking periodic reporting service encryption key backup falls on the reporting services server administrator. And the usual way to take the backup is to take it from the Reporting services configuration manager tool or using the RsKeymgmt command line utility.

But, neither of them provides you a way to take periodic encryption key backup, without the human intervention. This is where my thought originated and the end solution is something I’m going to discuss further.

When this thought got originated in my mind, my first objective was to ensure I should just keep it simple. With that simple objective in my mind, it was easier for me to further visualize the things on how I’m going to achieve it.

And now, I’ve a solution in the form of a report it self! Yes, it is as simple as that. All I’ve done is the following simplified process.

1. Developed a custom assembly that makes appropriate WMI calls to create the encryption key backup.

2. A report that calls the custom assembly and takes the backup of encryption key at will.

Apart from taking the encryption key back up, it does log the success / failure event details in to the Application Event logs under the source “BackupSSRSEncKeys”. This will help you in making sure whether the key backup operation is successful or not.

Additionally, if there is an error, you have the option to enable detailed tracing in the report itself which would help you in further troubleshooting. The error details will be logged in the Application event logs under the source named, “BackupSSRSEncKeys”.

 

Recommended usage: Create a simple FILE SHARE subscription for this REPORT with the frequency of execution to be once in a week or as per your organizational requirement. This will automate the encryption key backup process and ensures the back ups are taken in the scheduled intervals. You could visit the FILE SHARE that you specify during the subscription creation, to view the copy of the recently executed report to see whether the backup is successful or any error has occurred.

Here are the simple 3 steps that you need to do to automate the encryption key backup process.

Make sure you download the attached Report and the custom assembly, BackupEncKeysWmi.dll from this blog.

 

a) Deploy the assembly:   Make sure you copy the assembly named “BackupEncKeysWmi.dll” to the following location:   <Installation Directory>:\Program Files\Microsoft SQL Server\MSRSx.Instance name\Reporting Services\ReportServer\bin

b) Add entry to the rssrvpolicy.cofig file: [Take the config file backup before modifying the same] 1. Open the Rssrvpolicy.config file located in the location: <Installation Directory>:\Program Files\Microsoft SQL Server\MSRSx.Instance name\Reporting Services\ReportServer   2. Locate the CodeGroup section that contains Url="$CodeGen$/*" and go to the closing tag of the same CodeGroup section, which is just below </CodeGroup>. 3. Paste the below CodeGroup section immediately below the closing tag which you just located in #2.   <CodeGroup                                                                                                 class="UnionCodeGroup"                                                                                                 version="1"                                                                                                 PermissionSetName="FullTrust"                                                                                                 Name="MyCustomCodeGroup"                                                                                                 Description="Code group for BackupEncKeysWmi.">                                                                                                             <IMembershipCondition class="UrlMembershipCondition"                                                                                                             version="1"                                                                                                             Url=" <Installation Directory>:\Program Files\Microsoft SQL Server\MSRSx.Instance name\Reporting Services\ReportServer\bin \BackupEncKeysWmi.dll" />                                                                                     </CodeGroup>   4. Make sure you’ve put in the right path location of your assembly in the step #3.   5. Save the file and make sure you restart the Reporting Services service from the configuration manager.

c) Edit and Upload the report: Go to your report manager URL and upload the report named “BackupSSRSKeys”, Few parameters for the report are to be provided through REPORT VARIABLES. It requires the report to be in edit mode either from BIDS / SSDT or Report Builder 2.0 (For RS 2008) / 3.0 (For RS 2008 R2 and above) and then to be re-deployed / saved back to the disk or report server (This is required to be done only for the first time and there after it doesn’t require any edit operation until you decide to change any of the variable values). Kindly refer the below screen shot:   Editing report variables: (Refer the last part of this blog for variable definitions) BIDS / SSDT: Go to Report menu -> Report properties and then move to Variables tab. Report builder 2.0 / 3.0: (This even applies to BIDS / SSDT as well) Right click anywhere outside report canvas, select Report properties and go to Variables tab.

 

That’s it! You’re now ready to offload your encryption key backup process to reporting service itself and that too without the worry of remembering when was the last time that you took the backup.

This encryption key automation solution has been tested on Reporting services 2008 and above running in native mode. It might work with Reporting services 2008 and 2008 R2 in integrated mode. But I’m yet to perform the testing on this.

 

This report is not meant to be executed as an on-demand report (Though it does no harm). The preferred way is to create a subscription for the report and schedule it to run in the desired frequency in which you want the encryption keys backup needs to be taken. This is sole purpose for which I created this report.

 

It is recommended that you override the default security on this report and ensure only necessary users (Reporting server admins preferred) have the browser privilege on this report. This measure is to enforce restricted security on this report and the information’s that are stored within the report.

 

Since the report takes sensitive details like Encryption key backup password, Local Administrator user credentials of Reporting services server, the input details for them have to be provided through REPORT VARIABLES. It requires the report to be in edit mode either from BIDS / SSDT or Report Builder 2.0 (For RS 2008) / 3.0 (For RS 2008 R2 and above) and then to be re-deployed / saved back to the report server (only for the first time and there after it doesn’t require any edit operation until you decide to change the variable values). This measure has been taken to ensure the sensitive details are not exposed. Please refer the screen shots placed at the end of this document on how to access these report variables.

 

General recommendation would be to avoid using any personal user account to be stored in the report variables. Though there are maximum security measures taken in order to safe guard sensitive information, users belonging to local administrators group in Reporting services server can still download the Report and view the stored user credentials. As a best practice, either create a local machine account / domain user account, add it to local administrator group and use it dedicatedly for this purpose as this provides maximum security cushion.

 

If you’ve more than one instance OR Version of Reporting service installed in the same machine, you don’t need to deploy the report in each instance. The backup of encryption keys can be taken on any instance (Need to be run separately for each instance) irrespective of the version, provided if they exist on the same machine. Again this applies only to instances running on or above Reporting service 2008.

 

Report variables and their definitions:

Sl.no	Report variable	Mandatory requirement
1.	keybackuppassword	a. Type a password that protects the encryption key against unauthorized access. Only users who know the password will be able to restore the key that is locked inside the file. If you lose this password, then it is an irreversible process.   b. Reporting Services enforces a strong password policy. The password must be at least 8 characters and include a combination of uppercase and lowercase alphanumeric characters and at least one symbol character.
2.	Impersonateusername (Local Administrator on Reporting Services machine and have WRITE permission on the Key file path)	a. Provide the user name without any domain prefixes. b. This user name should be a local administrator on the Reporting Service machine. c. This will be the user context under which the entire encryption key back up will be executed. d. The user should have WRITE access on the folder that you specified in parameter #2 (Refer above table) .
3.	Impersonateuserdomain (Domain to which the user specified in variable #2 belong. Ignore, if it is a local machine account)	1. Domain name that the user in parameter #5 belongs to. 2. You can ignore this parameter, if the user is a local machine account in Reporting services server. 3. There is no harm in providing the local machine name as well, in case of Reporting services server local machine account.
4.	Impersonateuserpassword (Corresponding password for user specified in Variable #2. )	The corresponding password for the user name that is specified in the variable #2.

 

Report Parameters and their definitions:

Sl.no	Report Parameter	Mandatory requirement
1.	Reporting services Instance Name(S) (Separate each instance names with ';' if reporting service is scaled out):	a. At least one report server instance name is required. b. If the reporting service is scaled out, then make sure you provide all the instance names, each separated by a “;” . c. One of the way to pick up Reporting service instance name is from the below path, <Installation Directory>:\Program Files\Microsoft SQL Server\MSRSx.Instance name\Reporting Services  The section after “MSRSx.” gives you the required Instance name.
2.	Path to store the backup key (UNC path like \\machine1\keys is preferred):	a. This path will be used to store the backed up encryption key. b. It is preferred if you provide a UNC path. But not restricted to it. c. Make sure the path does exist else backup operation would fail. d. The user information that you provide in parameters #5, 6, and 7 should have WRITE access on this folder.
3.	Reporting Services Version:	a. This is super important for Reporting service to communicate with the right Reporting service instance. b. At this point of time, you can take back up Reporting service 2008, R2 and 2012 instances in Native mode. c. Your option should be one of them.
4.	Enable Advanced Diagnostics (Logged in Application event logs):	a. If you encounter any error message and you want to send the details across to me, then enable it by setting the parameter to “True”. b. By default, the value is “False”.

 

I sincerely believe this will be a great asset in your organization from saving the Reporting server instance going down unexpectedly due to encryption keys getting corrupted.

Please do let me know if you come across any difficulties in using it, failures and I’ll rectify them.

 

HTH!

Selva.

[All the posts are AS-IS without any warranty]

SSRSEncryptionKeyBackup.zip