Backing up SSRS Encryption Key with Powershell

A few months back I wrote a post about how I wanted a way to automate a backup of the encryption key used by our SQL 2008 reporting servers. I thought that Powershell would be the way to go, but I ran into a few issues. First, I couldn’t see a way to use Powershell to access the key. I could always use rsKeyMgmt from a command line, but I’d have to feed in a Y since I couldn’t suppress a prompt. I found a way to do that, but I didn’t want the password to be saved inside a batch file. And I wanted to copy the key file to a second location, in case I needed to use it to restore a report server. You can read my earlier post here.

One day, while I had my twitter feed open on my desktop, I saw a tweet from Laerte Junior (Blog | Twitter). In it he mentioned a post he wrote about accessing the report server WMI objects. And finally everything clicked into place.

The first thing I did was to install the SQLPSX modules on our report servers. The modules are available on Codeplex and you can download them here. Next I created a profile for all users to load the SQL Server snapins. I use Invoke-Sqlcmd to query a database.


Add-PsSnapin SqlServerCmdletSnapin100
Add-PsSnapin SqlServerProviderSnapin100

The final step was to create a table inside a database to store a password to use for the encryption key file. I figured I could easily control access to that password to only those who needed to know it. Now I was ready to create a script to do everything I wanted.

Getting the password out and storing it in a variable was easy:


$results = Invoke-Sqlcmd -Query "SELECT pwd FROM AdminDB.dbo.SSRS" -ServerInstance "Java"

Thanks to Laerte Junior I know what namespace, class and method to use. I simply need to pass in the password stored in my variable.


$SSRSClass = Get-Wmiobject -namespace "root\microsoft\sqlserver\reportserver\rs_mssqlserver\v10\admin" -class "MSReportServer_ConfigurationSetting"

$key = $SSRSClass.BackupEncryptionKey($results.pwd)

One thing to note. The server in my example is the default instance and is named “JAVA”. So when I reference the namespace I use the default instance of “rs_mssqlserver”. If you’re using a named instance just change it to rs_YourInstanceName.

Now that I’ve backed up the key I need to create the file.


$stream = [System.IO.File]::Create("c:\\SSRS.snk", $key.KeyFile.Length)
$stream.Write($key.KeyFile, 0, $key.KeyFile.Length)
$stream.Close()

The last thing I do is to copy the file to a second, safe location.


Copy-Item "C:\SSRS.snk" "\\DEVSERVER\SQLBackups"

And that’s all there is to it. I can use the Report Server Configuration Manager to restore the key when I need to.

image

Now I have this script on all of our report servers, and it runs after we promote new reports. There’s a few things I’ve been meaning to add, like deleting the old file before I create the new one. And I should put in error handling. I’ll be putting that into v2.

m4s0n501