SQL Confessions 02 SSRS Encryption

Comments: No Comments
Published on: January 25, 2011

In December 2010, I started a little series called SQL Confessions.  The idea of this series is as a learning exercise when I come across something that I either did wrong or couldn’t find a better way of doing it at the time.  In that first episode, I admitted to the use of a cursor (drat).  I should go back and update that posting because I got a recommendation of an alternative way to do that.  As of yet, I haven’t tested and it was a one time run (phwew).

This time around, the consequences were a bit bigger.  This time it was a failure and it is related to SSRS.  I came away from the experience with a few options that I want to test and see which method will work best in the event the same thing happens.

In this case, the ReportServer databases were backed up and the backups were good (I used them to restore a secondary database).  We even had SSRS installed on the failover server.  From that one can deduce that a recent failover was required for SSRS – and this was not a test.  Well, if you have the database backed up and a standby server ready to go – what is missing?

What was missing was the encryption key backup.  Life in recovery is a lot easier if that backup key exists.  I could not find it.  This did not prevent me from being able to recover SSRS though – it just meant a little more work.

If you lose your encryption key, then you have an issue with any data that may be encrypted.  Some of the things that are encrypted in SSRS 2008 are:  Connections and Subscriptions.

When you lose the encryption key you lose those items too.  If you have good documentation you can most likely recover them.  In my case I was able to recreate the Connections (DSNs) and bring the reports back up.  I don’t want to reveal the process I used just yet because I do want to test this a bunch more and find the better method to use.

As for the recommended method, here are some articles for reference:

1. Move to a new Server SSRS 2005 (useful even for 2008).

2.  MSDN – Backup the Encryption Key

I hope you find this useful, and the followup should be useful.

No Comments - Leave a comment

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

January 2011
« Dec   Feb »


  • @GEEQL: #sqlhelp question from daughter: "why is it 'more optimized' to list 6 tables in a single from statement than it is to do JOINs?"
  • @BrentO: @markmurphynyc This is kinda beyond 140 - best to put the full queries & plans on a Q&A web site. #sqlhelp
  • @markmurphynyc: #SQLHELP But if I filter by S_DATE_ID in the D_DATE dim, it does use partition elimination. This is in 2012.
  • @markmurphynyc: #SQLHELP Queries filtering by calendar date in d_date joined to the fact table by s_date_id aren't using partition elim. Any way to force?
  • @markmurphynyc: #SQLHELP (1/2) Fact table has s_date_id as YYYYMMDD and is partitioned monthly by this key. D_Date has same numeric key, plus calendar date
  • @jlangdon: @SQLSoldier Also, when hyperthreading is turned on each thread is licensed which sucks. Not applicable with SA @YetAnotherSQL #sqlhelp
  • @jlangdon: @SQLSoldier Correct. Question asked was about EE which is what we have too.@YetAnotherSQL #sqlhelp
  • @databasedave: Any additional info on wait types "se repl commit ack" or "resource governor idle"? Cant find anything on the interwebs. #sqlhelp #azure
  • @SQLHammer: #sqlhelp why does my SSMS open and recovery all of my query windows after I close it explicitly? It's like it thought it crashed.
  • @SQLSoldier: @jlangdon @YetAnotherSQL Only if you oversubscribe the CPUs which I recommend never doing, and need to use Ent. Ed. #sqlhelp

Welcome , today is Tuesday, September 16, 2014