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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.


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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Here in this article, you will get a step by step guidance on how to restore an Exchange database to a recovery database. Get a brief on Recovery Database and how it can be used to restore Exchange database in this section!
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Suggested Courses

650 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