One of the tasks I perform on a regular basis is to backup the Encryption Key on our report servers which are running SQL 2008. I perform the backup twice a month, after our reports have been promoted or updated. What I want to do is to automate the process. At first I thought this would be a natural for Powershell, but I’m finding a few issues. If anyone has found a different solution I’d love to hear it.
My first attempt was to use Powershell inside SQL to access the Report Server. But while I can connect to the SSRS I don’t see a method to backup the key.
I next tried to just call the RSKeyMgmt command-line utility directly from Powershell. This I can do, but there’s a problem; when you run the utility you’re prompted to confirm that you are indeed extracting the key to a file, and Powershell will wait for a Y or N reply. And Powershell doesn’t allow input redirection, at least that I’ve found.
I did find a superb workaround, written by Greg Milner here. I modified his script slightly, since our report servers are named instances and I had to add the -i option for RSKeyMgmt. And I also added code to copy the key file to a secondary location. It works just as I need it to. But I’m hard-coding the password for the key file inside the Powershell script, and I’d like to change that.
What I’m looking for is a way to be able to specify a password for the key that’s not easily readable. It has to be available for the script to use since there won’t be any interaction. I’m planning to execute the script as a bi-monthly job.