February 19, 2015 at 1:13 pm
I'm beginning the process of migrating a customer to a new SSRS server, and to save everybody work, I want to migrate their Reporting Services database.
I've done this before a year or so back, so I know the process is fairly straight-forward.
1. Backup the encryption key on the existing SSRS instance
2. Backup the Reporting Services DB (in this case I don't need the Reporting Services TempDB)
3. Configure SSRS on the new server
4. Restore the encryption key to the new SSRS instance
5. Restore the DB
6. Point the SSRS instance to the restored DB
The problem is, step 1. On both the existing QA and production SSRS servers, when I try to backup the encryption key, after entering the path, password, and hitting OK, I get "windows has encountered a critical problem and will restart." I'm trying to save the key file to my desktop on the server, so it shouldn't be an NTFS permissions issue.
I've done some Google searching, and not found this. We did just recently go through a security audit, so some GPO settings may have changed (which I would have no knowledge of or insight to)
My next step at the moment is going to be to try starting the SSRS Config Manager using the "Run As Administrator" option, although I'm not too confident in that.
Has anyone run into something like this, and if so, how did you resolve it?
I've already warned the customer that we / they may need to re-upload all their reports and schedules on the new server, as well.
Thanks,
Jason
Update 1: Before you ask, no, I don't have a copy of, or easy access to a copy of, the original encryption key backup. I could likely get one from our backup admin, but they'd need to go to the off-site tapes, plus I'd have to track down when the migration happened (one is easy, the other more work)
Update 2: A peice of good news in the MS article on migrating Reporting Services. They suggest and link to several methods to just migrate content from one instance to another, so that might make things a little less painful if I can't resolve this...
https://msdn.microsoft.com/en-us/library/ms143724(v=sql.110).aspx
February 19, 2015 at 1:24 pm
jason i only did this once, and it was a two or more years ago, for a 2005 instance.
there's away to backup the key via powershell, and avoid the gui which seems to be crashing;
i cannot find the exact article i found, but this was the first link i found that had something similar:
http://www.pertell.com/sqlservings/archive/2011/12/backing-up-ssrs-encryption-key-with-powershell/
and of course googling for "powershell backup ssrs encryption key" gets a lot of other examples.
Lowell
February 19, 2015 at 5:58 pm
Lowell, thanks for the pointer. I hadn't even thought about seeing if there was a way to get the key out with something like Powershell, I'll be giving that a try tomorrow!
February 20, 2015 at 7:00 am
So I tried tweaking the method that Lowell provided for a one-off, without the SQL DB / Table holding the password for the file. Looks like either I'm typo'ing something, or MS changed something, or they've got the systems so locked down it's preventing the method from working...
Regardless, thanks Lowell for the pointer to a possible solution!
(And I do think it's the third option above, I'm seeing audit failures in the OS Security Event log when I try to run the backupencryptionkey part, despite me being a local Admin on the box, and having done "Run As Administrator" for the Powershell session)
February 20, 2015 at 7:17 am
Jason running this specific example on my local machine, as well as on another server, this worked correctly for me:
it has to run locally, since it's effectively calling a command line to RSKeyMgmt.exe:
#####################################################################
#
# SCRIPT NAME: Backup-RSKeys.ps1
#
# PURPOSE: Generate the .bat file to backup the Reporting
# Services RSKeys and delete the old ones after 30 days.
# the .bat file creates a RSKey backup file with a filename
# like RSKey_MYSERVERNAME_200908052329.snk.
#
# Parameters:
# $RSKeysFolder -> directory to write to and delete from - will NOT be recursive!
# $DaysBackToDelete -> Files older then N days to be deleted
# $RSKeyFilePwd -> Actual RSKey encryption file password
#
# Example Usage
# to delete from 15 days back
# ./Backup-RSKeys.ps1 "\\SERVERNAME\ShareFolder\SQLBackups\RSKeys" "-15" "FlibbertyGibbet77"
#
# to run from DOS command or from Task Scheduler:
# powershell -command "& {./Backup-RSKeys.ps1 'E:\TargetFolder' '-30' 'FlibbertyGibbet77'}"
#
# Change History:
# 08/04/2009 10:33 AM - gmilner: Created.
#
######################################################################
param( $RSKeysFolder = "C:\Data" , $DaysBackToDelete = -30, $RSKeyFilePwd)
# populate the variables.
# the date before which you delete old files.
$DeleteFromDate =((Get-Date).AddDays($DaysBackToDelete))
# Get the name of our machine
$ServerName = get-content env:computername
$RSKeyFilePwd ="MyS3cr#tPa55w0rd!"
# Formats now date and time to go in a filename like 200909142304
$DateString = (get-date (get-date) -uformat %Y%m%d%H%M)
# build the target path and filename
$TargetPathAndFileName = $RSKeysFolder + "\RSKey_" + $ServerName + "_" + $DateString + ".snk"
# we need a file to feed it the "Y" when the RSKeyMgmt.exe asks for it
"Y">y.txt
#note the redirection operator to bring in the Y from y.txt ("an old DOS trick")
$DOSCommandString = "RSKeyMgmt.exe -e -f """ + $TargetPathAndFileName + """ -p"+ $RSKeyFilePwd + " < y.txt"
# write the command out to a batch file. NOTE: If you done use -encoding the batch will error
# on a unicode related error.
$DOSCommandString | out-file -file "BackupRSKeys.bat" -encoding ASCII
# Now run the batch file
.\BackupRSKeys.bat
#---------------------------------------------
# Delete files older than x days if extension = .snk
Get-ChildItem "$RSKeysFolder" -include *.snk |
where { $_.LastWriteTime -le $DeleteFromDate } |
remove-item
Lowell
February 20, 2015 at 10:47 am
Sigh.
Looked like a great solution, except it generates the same OS error as trying to backup the key in the SSRS Config manager.
I really do think one of the things that got locked down / changed for the recent security audit is the culprit...
Once again, Lowell, thanks.
Now for some good news (for me at least.) Turns out on the QA server (where I've been trying these tips) there's a backup of the key (for QA, but to simplify the Devs life I'm hoping / betting it's the same between QA and Prod) sitting on the filesystem! So I've got that out to the new server, and am working on getting things configured.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply