SSRS Archives - JBs Wiki

-> Backup of SQL Server Reporting Services Encryption keys plays an important role in Disaster Recovery involving SQL Server Reporting Services.

-> I have seen scenarios where there were no backups of SSRS encryption keys during restore, DBA’s had to delete all encrypted contents and had to create connection string, login, password etc manually.

-> SSRS encryption key backups can be performed following below article,

https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/ssrs-encryption-keys-back-up-and-restore-encryption-keys?view=sql-server-2017

https://www.powershellgallery.com/packages/ReportingServicesTools/0.0.0.2/Content/Functions%5CAdmin%5CBackup-RsEncryptionKey.ps1

-> In one of my recent Project, I used the second link and tried to perform the SSRS encryption key backup using powershell and got below error,

PS C:\> Backup-RSEncryptionKey -SqlServerInstance ‘SQL16S_TOPR_GIS’ -SqlServerVersion ’13’ -Password ‘Test1234$3$’ -KeyPath ‘C:\temp\PlayingGround\Default.snk’
Get-WmiObject : Invalid namespace “root\Microsoft\SqlServer\ReportServer\RS_SQL16S_TOPR_GIS\v13\Admin”
At C:\Users\vjanakiraman\Documents\WindowsPowerShell\Modules\ReportingServicesTools\ReportingServicesTools\Functions\Utilities\New-RsConfigurationSettingObject.ps1:100 char:19
+     $wmiObjects = Get-WmiObject @getWmiObjectParameters
+                   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidArgument: (:) [Get-WmiObject], ManagementException + FullyQualifiedErrorId : GetWMIManagementException,Microsoft.PowerShell.Commands.GetWmiObjectCommand

-> I tries below command to list the encoded instance names of my report server instance in the WMI namespace path,

[code language=”powershell”]
Get-WmiObject -namespace root\Microsoft\SqlServer\ReportServer -class __Namespace -ComputerName . | select Name
[/code]

-> This explains why we are experiencing the error previously. Compare the name in above screenshot and instance name. Could see extra 5f added in
encoded instance name of report server instance in the WMI namespace path.

-> Lets check the WMI namespace using wmimgmt.msc.

-> Right Click “WMI Control (Local)” and click properties,

-> The powershell script expects “root\Microsoft\SqlServer\ReportServer\RS_SQL16S_TOPR_GIS\v13\Admin”. But the server has entries, root\Microsoft\SqlServer\ReportServer\RS_5fSQL16S_5fTOPR_GIS\v13\Admin and this is causing issue.

-> I tried rerunning the powershell command to backup SSRS encryption keys by changing the instancename to what we see in WMI namespace,

PS C:\> Backup-RSEncryptionKey -SqlServerInstance ‘SQL16S_5fTOPR_5fGIS’ -SqlServerVersion ’13’ -Password ‘Test1234$3$’ -KeyPath ‘C:\temp\PlayingGround\Default.snk’
You cannot call a method on a null-valued expression.
At C:\Users\vjanakiraman\Documents\WindowsPowerShell\Modules\ReportingServicesTools\ReportingServicesTools\Functions\Admin\Backup-RsEncryptionKey.ps1:86 char:9
+         $encryptionKeyResult = $rsWmiObject.BackupEncryptionKey($Pass …
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

Failed to create backup of the encryption key. Errors:
At C:\Users\vjanakiraman\Documents\WindowsPowerShell\Modules\ReportingServicesTools\ReportingServicesTools\Functions\Admin\Backup-RsEncryptionKey.ps1:94 char:13
+             throw “Failed to create backup of the encryption key. Err …
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : OperationStopped: (Failed to creat…n key. Errors: :String) [], RuntimeException
+ FullyQualifiedErrorId : Failed to create backup of the encryption key. Errors:

-> As per article https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/ssrs-encryption-keys-back-up-and-restore-encryption-keys?view=sql-server-2017 we need to backup the keys only once. Please find the details from that article below,

“You only need to back up one copy of the symmetric key. There is a one-to-one correspondence between a report server database and a symmetric key. Although you only need to back up one copy, you might need to restore the key multiple times if you are running multiple report servers in a scale-out deployment model. Each report server instance will need its copy of the symmetric key to lock and unlock data in the report server database.”

-> I have developed a solution for backing up the SSRS encryption key every day and maintain the backups for last last 10 days using below scripts.

-> Below scripts are for creating credential and Proxy to allow the powershell script to be executed in SQL Server Agent,

CREATE CREDENTIAL SQLAgentPowershellCredential
WITH IDENTITY = 'JBS\SQLAgentProxy'
, SECRET = 'Passw0rd@#'

USE msdb;
EXEC dbo.sp_add_proxy @proxy_name = N'SQLAgentPowershellProxy'
, @enabled=1
, @description = N'Proxy used to run PowerShell as an administrator'
, @credential_name = N'SQLAgentPowershellCredential';

USE msdb;
EXEC dbo.sp_grant_proxy_to_subsystem @proxy_name = N'SQLAgentPowershellProxy'
, @subsystem_name = N'PowerShell'

-> Save below file as c:\temp\Backup_SSRS_Key.ps1

$ComputerName = "JBSERVER1"
$InstanceName = "IN01"
$KeyFolder = "\\JBUNC1\Prod_Backup\SSRS_Encryption_Key_Backup\"
$KeyPassword = 'Passw0rd@#'
$TimeStamp = Get-Date -Format "-yyyyMMdd-HHmmss"

Get-WmiObject -Namespace "Root\Microsoft\SqlServer\ReportServer" -Class "__Namespace" -ComputerName $ComputerName|
Select-Object -ExpandProperty Name |
% {
$NameSpaceRS = $_
$KeyFileName = Join-Path -Path $KeyFolder -ChildPath ($ComputerName + "_" + $InstanceName + $Timestamp + ".snk")
"Found Reporting Services in instance '$($InstanceName)' on $($ComputerName); will save key to '$($KeyFileName)' ..."
$SQLVersion = (Get-WmiObject -Namespace "Root\Microsoft\SqlServer\ReportServer\$($NameSpaceRS)" -Class "__Namespace" -ComputerName $ComputerName).Name
$SSRSClass = Get-WmiObject -Namespace "Root\Microsoft\SqlServer\ReportServer\$($NameSpaceRS)\$($SQLVersion)\Admin" -Query "SELECT * FROM MSReportServer_ConfigurationSetting WHERE InstanceName='$($InstanceName)'" -ComputerName $ComputerName
$Key = $SSRSClass.BackupEncryptionKey($KeyPassword)
If ($Key.HRESULT -ne 0) {
$Key.ExtendedErrors -join "`r`n" | Write-Errorts
} Else {
$Stream = [System.IO.File]::Create($KeyFileName, $Key.KeyFile.Length)
$Stream.Write($Key.KeyFile, 0, $Key.KeyFile.Length)
$Stream.Close()
}
}

-> Save below file as c:\temp\Delete_SSRS_Key_10days_Old.ps1

New-PSDrive -Name "P" -PSProvider FileSystem -Root "\\JBUNC1\Prod_Backup\"
Get-ChildItem –Path "P:\SSRS_Encryption_Key_Backup\" -Recurse | Where-Object {($_.LastWriteTime -lt (Get-Date).AddDays(-10))} | Remove-Item
Remove-PSDrive -Name "P"

-> Create a SQL Server agent job to execute the created powershell scripts,

USE [msdb]
GO

/****** Object: Job [SSRS - EncryptionKey - Backup] Script Date: 17/09/2019 5:52:38 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 17/09/2019 5:52:38 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR != 0 OR @ReturnCode != 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'SSRS - EncryptionKey - 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'Database Maintenance',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR != 0 OR @ReturnCode != 0) GOTO QuitWithRollback
/****** Object: Step [Backup_SSRS_Encryption_key] Script Date: 17/09/2019 5:52:38 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup_SSRS_Encryption_key',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@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'C:\temp\Backup_SSRS_Key.ps1
',
@database_name=N'master',
@flags=0,
@proxy_name=N'SQLAgentPowershellProxy'
IF (@@ERROR != 0 OR @ReturnCode != 0) GOTO QuitWithRollback
/****** Object: Step [Delete_Files_Older_Than_10days] Script Date: 17/09/2019 5:52:38 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete_Files_Older_Than_10days',
@step_id=2,
@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'C:\temp\Delete_SSRS_Key_10days_Old.ps1',
@database_name=N'master',
@flags=0,
@proxy_name=N'SQLAgentPowershellProxy'
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'1',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20190917,
@active_end_date=99991231,
@active_start_time=164800,
@active_end_time=235959,
@schedule_uid=N'e45a59ec-7444-4838-ba15-a563f161c46b'
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

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

JBs Wiki

Fall in love with SQL

Category Archives: SSRS

SQL Server Reporting Services – The value for UrlRoot in RSReportServer.config is not valid. The default value will be used instead.

SQL Server Reporting Services Encryption Key Backup