-> On one of my SQL Server Reporting Services server, I could see below warnings in Event Viewer.
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”>
<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>
-> Search for <UrlRoot> in that file. In my case, it was blank,
-> 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,
-> 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.
-> 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,
-> 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:
“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'
-> 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.