[Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL 2005 Reporting Services Encryption Key issue

Posted on 2010-11-29
Medium Priority
Last Modified: 2012-05-10
Our SQL Reporting Services was installed and created in 2007.  We have an SSRS encryption key backup made in 2007 (an SNK file) and we are almost certain of the password that was used to create the key backup (it's 1 of a possible 3).

Since 2007 we have changed the password of the domain account that runs the SQL Reporting Service service.  It has been changed 3 times.  It was changed by selecting the password in AD and selecting "Reset password...".  Each time, the logon properties for the service were updated with the new password and the service was restarted.  The only other SSRS item that needed to be updated was the Execution Account password details in the Configuration Tool.

In the Reporting Services Configuration manager, if you try to update the password of the Windows account that runs the service, you are prompted for the location of the backed up file and "Password" (presumably this is the password used to create the snk file).  

What would happen if you entered this information?  What would happen if you entered the wrong password (we know it's 1 of a possible 3, but we might not get it right first time)?
If you change the password of the account that runs the service, and restart the service, and the servive works, then do you need to do anything to the key?  Why are you prompted for the key password - what's going to occur when it's entered?  Why bother changing the password using the Configuration manager?
(Dont know why this is in the Exchange zone, but I can't deselect that zone, my apologies)

Question by:bungeecork
  • 4

Accepted Solution

adamsjs earned 500 total points
ID: 34236109
A password is required on the symmetric key backup file to prevent storing the key unscrambled in the backup.  Likewise, you must provide the password to unscramble the key when restoring it from the backup file.

If you provide an invalid password when restoring the symmetric key, the restore of the key would fail.

This article has good information regarding backup and restore of the encryption keys for Reporting Services:  http://msdn.microsoft.com/en-us/library/ms157275%28v=SQL.90%29.aspx

From that documentation, you need to restore the key backup for the following operations:

Changing the Report Server Windows service account name or resetting the password. When you use the Reporting Services Configuration tool, backing up the key is part of a service account name change operation.
Renaming the computer or instance that hosts the report server (a report server instance is based on a SQL Server instance name).
Migrating a report server installation or configuring a report server to use a different report server database.
Recovering a report server installation due to hardware failure.

Furthermore, in regards to changing the service account name or resetting its password:

Note: Resetting the password is not the same as changing the password. A password reset requires permission to overwrite account information on the domain controller. Password resets are performed by a system administrator when you forget or do not know a particular password. Only password resets require symmetric key restoration. Periodically changing an account password does not require you to reset the symmetric key.

Author Comment

ID: 34242373
Hi adamsjs - looks like we are on the right track here.

Now, are we talking about 2 different passwords?  
1 - I RESET the password of the account that runs the Reporting Services service.  
2 - A password is required by the symmetric key backup file process.

Can these be different, and if yes, does the second need to be changed if the first one is RESET?

The documentation you mention says a restore of the key backup is required when "Changing the Report Server Windows service account name or resetting the password".  Which password?  The one on the service account or the one used to scramble the key backup?

I'm not changing the service account name.  I only reset the service account password.  It's a domain account so the RESET operation wasn't performed on the SQL server.  I then updated the password details on the properties of the service and restarted the service.

And again, we know the password used at the time of the original scrambling was 1 of a possible 3.  If, as you say, "you provide an invalid password when restoring the symmetric key, the restore of the key would fail", then would this fail cause a problem?  Could you just retry with a different password?  Or would you have broken something by using the wrong password?

I reset the password a few days ago.  

Expert Comment

ID: 34244577
You are correct that there are two different passwords, and they can be different.  One password is, as you realize, the service account password, which you have reset.

The other is the password that was used to scramble the backup file when the key was backed up.   This is the password that you need to specify when restoring the key from the backup.  This password is independent of the service account password (though the same value could have been used).

You say that you know "the password used at the time of the original scrambling was 1 of a possible 3".  Are you referring to the password used to scramble/encrypt the key backup, or the password of the service account?  Just want to clarify.

If you provide an invalid password for the key backup, it will prevent the restore of key from succeeding.  In testing on my system, attempting to restore the key with an invalid password caused no harm (though I did not perform a password change/reset for the service account as part of this).  All that resulted was an error message.

If all is working well, I suspect that you won't have any issues due to the change of the service account password.  My recommendation at this point is make a new backup (to a new file) of the encryption key from your SSRS instance, making sure you document the password used in the process, and secure for future use if needed.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 34244609
Just had a thought.  Make sure you get the backup of the current key before you try anything.

Let's say that the key has been changed for some reason since the original backup was made.  And let's say you specify the correct password for the key backup when you restore it.  The key will be restored, but it is not the current key.  This would prevent access to sensitive data that the SSRS instance has stored.  Without a backup of the correct key to restore, you would have to delete and recreate the key, which leads to this (from Help in Reporting Services Configuration):

Deleting and recreating the symmetric key is a non-reversible action that can have important ramifications on your current installation. If you delete the key, any existing data that is encrypted by the symmetric key will be deleted along with the key. Deleted data includes connection strings to external report data sources, stored connection strings, and some subscription information.

So, make that new backup.

Expert Comment

ID: 34275927
Did this information answer your question and help you resolve your issue?
LVL 101

Expert Comment

ID: 34770157
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have come across a situation where you need to find some EDB mailbox recovery techniques, then here you will find the same. In this article, we will take you through three techniques using which you will be able to perform EDB recovery. You …
Mailbox Corruption is a nightmare every Exchange DBA wishes he never has. Recovering from it can be super-hectic if not entirely futile. And though techniques like the New-MailboxRepairRequest cmdlet have been designed to help with fixing minor corr…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question