SQL 2005 Reporting Services Encryption Key issue

Posted on 2010-11-29
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 125 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:

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.
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.


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 100

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
script to trace the email in Office365 4 49
T-SQL Query 9 35
Creating Scalar Function 3 17
Exchange 2010 3 26
This article demonstrates probably the easiest way to configure domain-wide tier isolation within Active Directory. If you do not know tier isolation read…
Active Directory security has been a hot topic of late, and for good reason. With 90% of the world’s organization using this system to manage access to all parts of their IT infrastructure, knowing how to protect against threats and keep vulnerabil…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

732 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