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.
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.