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.

SQL Confessions

Comments: 1 Comment
Published on: December 13, 2010

I had a brainstorm of an idea for a group of articles on my blog a couple of weeks ago and am finally getting around to putting it together.  The idea came about while I was struggling with a problem at work and trying to meet the requirements in the code.  As the title states, this group is going to be about things done in SQL server that I would normally avoid.

The first article came about while trying to find a solution to some business requirements.  The requirements had an average to be calculated and then a revised average if certain criteria were met.  Then it was required to move things from one group to another group depending on a series of criteria.  When I first looked at these requirements, I thought I would need several cursors.  As it turns out, I had to implement just one cursor.  I could find no way of getting away from using a cursor for this one requirement.  The essence of it was that I had two variables.  One variable was the number of items to move and the second variable was the person to whom the items needed to be moved.  Not only did it require those two variables, but there was the additional requirement that it be randomized.

First, let’s take a look at how I did the randomizing.  I took care of this requirement by dumping all of my prep data into a temp table that had a default on a column explicitly to hold  a random value.  Here is the code for that table.

[codesyntax lang=”tsql”]

[/codesyntax]

Using this table, I can just run a set-based insert to populate the table and thus end up with random values in the “Randomizer” column due to the default constraint.

[codesyntax lang=”tsql”]

[/codesyntax]

That piece of the puzzle was fixed rather easily.  Now for the next part – the confession part.  I had to write a cursor.  Next part of that confession – I had to look up how to write the cursor.  Without going into a lot of details, this is the code of how I used a cursor to solve this particular problem.

[codesyntax lang=”tsql”]

[/codesyntax]

Note that this cursor is a little hybrid.  I am using this cursor to perform some set based actions.  I am fine with that.  I am happy with the random distribution and the fact that this performs well.  This was done for a one off requirement that will not be used again (or at least not planned to be used again) and it met all of the requirements.  I was blocked on this particular requirement by the required variables and couldn’t get past how to do this in a 100% set-based fashion.  If you have any input, let me know.

Do you have any such stories to share?

page 1 of 1








Calendar
December 2017
M T W T F S S
« Nov    
 123
45678910
11121314151617
18192021222324
25262728293031
Content
SQLHelp

SQLHelp


Welcome , today is Tuesday, December 12, 2017