Reporting Service Key Backup Script

A lot of us have gone through the scenario where a Reporting Service account gets changed/deleted and then we don't have a way to get our reports working as all the encrypted content is lost.

Our organization needed an automated way to backup the Reporting Service key. Here is a simple script which will create a job on your SQL Instance. This job will run once a day taking RS Key backup with a random password. It will put the Key file and the Random password in the folder specified in the script and retain these for last 7 days. Go ahead and give it a try.

Please replace the value for variables $KeyPath(Location where key backup will be placed), $PwdFilePath(Location where password file will be placed) and $Daysback (Number of days to retain the key in the destination folder. Default is 7).


--******Start of Script*******
USE [msdb]
GO

/****** Object:  Job [Navi_SSRS_Key_Backup]    Script Date: 25/04/2018 08:23:09 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 25/04/2018 08:23:09 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'SSRS_Key_Backup', 
@enabled=1, 
@notify_level_eventlog=0, 
@notify_level_email=0, 
@notify_level_netsend=0, 
@notify_level_page=0, 
@delete_level=0, 
@description=N'No description available.', 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [SSRS Key Backup]    Script Date: 25/04/2018 08:23:09 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SSRS Key Backup', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'PowerShell', 
@command=N'Function GET-Temppassword() {

Param(

[int]$length=10,

[string[]]$sourcedata

)



For ($loop=1; $loop –le $length; $loop++) {

            $TempPassword+=($sourcedata | GET-RANDOM)

            }

return $TempPassword

}

$ascii=$NULL
For ($a=33;$a –le 126;$a++) {$ascii+=,[char][byte]$a }
$password = GET-Temppassword –length 19 –sourcedata $ascii

$postfix = Get-Date -format "yyyyMMddhhmm"
$KeyPath = ''C:\MSSQL11.MSSQLSERVER\MSSQL\SSRSEncBackup\''
$PwdFilePath = ''C:\MSSQL11.MSSQLSERVER\MSSQL\SSRSEncBackup\KeyPwd''+$postfix+''.txt''
$KeyFileName = ''SSRSDBkey'' + $postfix + ''.snk''
$KeyFile = $KeyPath + $KeyFileName
echo y | rskeymgmt -e -f $KeyFile -p $password

$file = $PwdFilePath + $FileName

(get-date).ToString() + " | " + $password | Out-File -FilePath $PwdFilePath

$Daysback = “-7”
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($Daysback)
Get-ChildItem $KeyPath -Recurse | Where-Object { $_.LastWriteTime -lt $DatetoDelete } | Remove-Item', 
@database_name=N'master', 
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'SSRSKeyBackup', 
@enabled=1, 
@freq_type=4, 
@freq_interval=1, 
@freq_subday_type=1, 
@freq_subday_interval=0, 
@freq_relative_interval=0, 
@freq_recurrence_factor=0, 
@active_start_date=20171118, 
@active_end_date=99991231, 
@active_start_time=233000, 
@active_end_time=235959, 
@schedule_uid=N'4fb8bbee-b128-4c0e-9005-2e966b872544'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO
--******End of Script*******

Hope it helps.