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 *










Calendar
January 2011
M T W T F S S
« Dec   Feb »
 12
3456789
10111213141516
17181920212223
24252627282930
31  
Content
SQLHelp

SQLHelp

  • @Ko_Ver: @NickyvV no, no special tricks in the package :) #sqlhelp
  • @NickyvV: @Ko_Ver I've seen that last week that the Job (step) succeeded, but it actually failed with an error #sqlhelp
  • @NickyvV: @Ko_Ver Is FailParentOnFailure set to false on a task/container maybe? #sqlhelp
  • @Ko_Ver: When I start an Agent job, the "job running" pop-up says there's an error. But SSIS catalog and Agent history says jobs succeed. #sqlhelp
  • @DbaMayor: #sqlhelp I'm seeing LCK_M_U and LCK_M_RS_U waits on sec readonly replica with only reporting queries...anyone faced similar issue?..thnks
  • @TrustMeImADBA: Prob not a good question for #sqlhelp but what is the chances time-service changing the time 23 sec forward made my sql cluster failover?
  • @zippy1981: Seem to be that I need to install SSMS vNext Verifying now #sqlhelp
  • @ChrisAdkin8: @YaHoznaDBA you should also compare avg writelog waits to avg tran log write stall,the two are not necessarily foced to be the same #sqlhelp
  • @toddkleinhans: #sqlhelp Will try using lock priority. Totally forgot about that. Thanks!
  • @DBArgenis: @toddkleinhans Compatibility mode is for T-SQL constructs and Query Optimization, nothing else. #sqlhelp

Welcome , today is Tuesday, February 21, 2017