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

-> On one of my SQL Server Reporting Services server, I could see below warnings in Event Viewer.

Blog76_1.png

Blog76_2.png

Log Name: Application
Source: Report Server Windows Service
Date: 18/09/2019 9:44:16 AM
Event ID: 110
Task Category: Management
Level: Warning
Keywords: Classic
User: N/A
Computer:
Description:
The value for UrlRoot in RSReportServer.config is not valid. The default value will be used instead.
Event Xml:
<Event xmlns=”http://schemas.microsoft.com/win/2004/08/events/event”&gt;
<System>
<Provider Name=”Report Server Windows Service” />
<EventID Qualifiers=”0″>110</EventID>
<Level>3</Level>
<Task>5</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime=”2019-09-17T23:44:16.691430500Z” />
<EventRecordID>6489</EventRecordID>
<Channel>Application</Channel>
<Computer></Computer>
<Security />
</System>
<EventData>
<Data>UrlRoot</Data>
<Data>RSReportServer.config</Data>
</EventData>
</Event>

-> Edit file RSReportServer.config from location D:\Program Files\Microsoft SQL Server\MSRS<Version>.<Instance>\Reporting Services\ReportServer folder.

-> Search for <UrlRoot> in that file. In my case, it was blank,

Blog76_3.png

-> Open Reporting Services Configuration Manager. Navigate to “Web Portal URL” or “Report Manager URL”. Copy the entry in “URLs:” and paste it in RSReportServer.config file between <UrlRoot> </UrlRoot> as shown below,

Blog76_4.png

-> Restarting reporting service after the above change stopped the warning.

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.

SQL Server Reporting Services Encryption Key Backup

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

Blog75_1

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,

Get-WmiObject -namespace root\Microsoft\SqlServer\ReportServer -class __Namespace -ComputerName . | select Name

Blog75_2.png

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

Blog75_3.png

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

Blog75_4.png

Blog75_5

Blog75_6.png

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

Blog75_7.png

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.