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.
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.


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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

If you troubleshoot Outlook for clients, you may want to know a bit more about the OST file before doing your next job. IMAP can cause a lot of drama if removed in the accounts without backing up.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Suggested Courses

765 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