The report server Cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database SSRS Error

Question

I am getting the following error when trying to deploy my SSRS reports on our SQL 2008 R2 Server "The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database...". Most of the solutions on the Web suggest to delete the encryption keys, then reconfigure the datasources. I am still a beginner in SSRS, Is there another solution to fix this issue, Thanks

Answer 1

After checking this link Microsoft support link, it seems that this is a know issue in SSRS reports. And it seems the only way to fix it is to delete the Encryption keys.

1. Open Reporting Services Configuration Tool ( Programs->Microsoft SQL Server 2008 R2 -> Configuration tools -> Reporting Services Configuration Manager)
2. Go to Encryption Keys
3. Click Delete.

This solved my problem

Answer 2

I ran into this with a Microsoft Dynamics CRM 2016 Reporting Extensions Setup after changing the SQL Server Reporting Services account from services.msc. This is because the Microsoft Dynamics CRM 2016 Reporting Extensions Setup requires a non-local service account. https://technet.microsoft.com/en-us/library/hh699754.aspx The key trigger here that is likely the root cause seen in the Haasan's question was the changing of the SQL Server Reporting Services service account without backing up the encryption key. While what he did with deleting encryption keys worked, it has drawbacks of losing that encryption information and if possible, you should use the steps below to revert back to the original service account user and then change the service account using the steps documented below and in the reference article.

The identity account running the instance of Microsoft SQL Server Reporting Services where the Microsoft Dynamics CRM Reporting Extensions are running can’t be the local system or a virtual account. This is required for Microsoft Dynamics CRM reporting to work because the identity account must be added to the PrivReportingGroup Active Directory security group that is used by Microsoft Dynamics CRM.

The long story here is that when changing the SQL Server Reporting Services account, you need to do that from the SQL Server Services Reporting Manager as that will prompt you to back up the Symmetric encryption key that SQL Server Reporting Services uses and restore it with the new service account user.

The Report Server service uses the symmetric key to access the encrypted data in a report server database. This symmetric key is encrypted by using an asymmetric public key that corresponds to the computer and the user account that is used to run the Report Server service. When you change the user account that is used to run the Report Server service, the report server cannot use the asymmetric public key to decrypt the symmetric key. Therefore, the Report Server service cannot use the symmetric key to access the data from the report server database.

This will be doing the following when changing the service account from the SQL Server Reporting Services Reporting Manager:

Automatically adds the new account to the report server group created on the local computer. This group is specified in the access control lists (ACLs) that secure Reporting Services files. Automatically updates the login permissions on the SQL Server Database Engine instance used to host the report server database. The new account will be added to the RSExecRole. The database login for the old account will not be removed automatically. Be sure to remove accounts that are no longer in use. For more information, see Administer a Report Server Database (SSRS Native Mode) in SQL Server Books Online. Granting database permissions to new service account only occurs if you configured the report server database connection to use the service account in the first place. If you configured the report server database connection to use a domain user account or a SQL Server database login, the connection information is not affected by the service account update. Automatically updates the encryption key to include the profile information of the new account.

If like in my scenario, you happen to know what the previous service account user was, the fix is to change the SQL Server Report Service account user back to the originally specified account and then to use the SQL Server Reporting Services Reporting Manager to change the account and to ensure that you backup the encryption key as that process automates the restore of the encryption key when the new service account user is set.

References: https://msdn.microsoft.com/en-us/library/ms160340.aspx - Configure the Report Server Service Account (SSRS Configuration Manager)

https://support.microsoft.com/en-us/kb/842421 - You receive an error message in the Reporting Services trace log when you restart the Report Server service after you change the user account that is used to run the Report Server service (This is an old KB article, but the general problem and resolution still applies with newer versions of SQL Reporting Services)

Answer 3

Hopefully this might save someone some time if deleting the key is not an option.

I ran into this issue after moving the ReportServer and ReportServerTempDB from a working server to a different environment running Reporting Services. Deleting the encryption keys was not an option and I knew the password used to create the encryption key, so I took a backup of the key from the working server and restored it using Reporting Services Configuration Manager on the new environment. Refreshed the page and the error went away.