dsccommunity
SqlRS: Error with URL Reservation when more than one instance of reporting services
Details of the scenario you tried and the problem that is occurring
I'm trying to install 3 instances of Reporting services on the same sql database instance. The 3 instances are configured to use the same virtual directories and the same Urls (Web Service URL and Web Portal Urls). When using the GUI configuration tool to do this, it works fine. Each instance is configured of after the other, and when configuring URLs, we can see in the GUI that the URLs are removed before beeing reserved, for each instance (this is important). The DSC ressource is not working that way. It checks if the actual URL reservation configuration of the instance is correct, if not, it removes the URL ONLY if they're already reserved for the actual instance (line 634 & 673 SqlRS.psm1).
The problem is that the URLs are not reserved for this instance, but they're reserved for the first instance installed, so, when installing the second instance, the ressource tries to reserve a URL that's already reserved.
Verbose logs showing the problem
"Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = ResourceSet,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.",
"An LCM method call arrived from computer SERVER1 with user sid S-1-5-21-2571584662-2423331039-2763446113-1867.",
"[SERVER1]: LCM: [ Start Set ] [[SqlRS]DirectResourceAccess]",
"[SERVER1]: [[SqlRS]DirectResourceAccess] Perform operation 'Enumerate CimInstances' with following parameters, ''namespaceName' = root\\Microsoft\\SQLServer\\ReportServer\\RS_INSTANCE2\\v13\\Admin,'className' = MSReportServer_ConfigurationSetting'.",
"[SERVER1]: [[SqlRS]DirectResourceAccess] Operation 'Enumerate CimInstances' complete.",
"[SERVER1]: [[SqlRS]DirectResourceAccess] Perform operation 'Enumerate CimInstances' with following parameters, ''namespaceName' = root/cimv2,'className' = Win32_OperatingSystem'.",
"[SERVER1]: [[SqlRS]DirectResourceAccess] Operation 'Enumerate CimInstances' complete.",
"[SERVER1]: [[SqlRS]DirectResourceAccess] Initializing Reporting Services on SERVER.domain.com,7433\\SSRS.",
"[SERVER1]: [[SqlRS]DirectResourceAccess] Setting report server virtual directory on SERVER.domain.com,7433\\SSRS to ReportServer.",
"[SERVER1]: [[SqlRS]DirectResourceAccess] Perform operation 'Invoke CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = \"INSTANCE2\"),'methodName' = SetVirtualDirectory,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_INSTANCE2/v13/Admin'.",
"[SERVER1]: [[SqlRS]DirectResourceAccess] Operation 'Invoke CimMethod' complete.",
"[SERVER1]: [[SqlRS]DirectResourceAccess] Adding report server URL reservation on SERVER.domain.com,7433\\SSRS: http://+:80.",
"[SERVER1]: [[SqlRS]DirectResourceAccess] Perform operation 'Invoke CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = \"INSTANCE2\"),'methodName' = ReserveUrl,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_INSTANCE2/v13/Admin'.",
"[SERVER1]: [[SqlRS]DirectResourceAccess] Operation 'Invoke CimMethod' complete.",
"[SERVER1]: LCM: [ End Set ] [[SqlRS]DirectResourceAccess] in 2.5050 seconds.",
"Operation 'Invoke CimMethod' complete.",
"Time taken for configuration job to complete is 2.63 seconds"
],
"msg": "Failed to invoke DSC Set method: PowerShell DSC resource DSC_SqlRS failed to execute Set-TargetResource functionality with error message: Method ReserveUrl() failed with an error. Error: The Url has already been reserved. (HRESULT:-2147220932) ",
Suggested solution to the issue
For what I need to do, I modified the resource to always remove the URLs before adding them (as the Reporting Services Configuration GUI tool does)
A better whay to do it more dynamically (get-set-test), maybe the DSC resource should loop around all reporting instances and check every URL reservation, and if a URL reservation is already done in another instance, then, the remove URL method should be called?
Line 634, replaced current config check:
$currentConfig.ReportServerReservedUrl | ForEach-Object -Process {
with urls that we wants to reserve:
$ReportServerReservedUrl | ForEach-Object -Process {
It gives :
if ( ($null -ne $ReportServerReservedUrl) -and ($null -ne (Compare-Object @compareParameters)) )
{
$restartReportingService = $true
#$currentConfig.ReportServerReservedUrl | ForEach-Object -Process {
# changed by:
$ReportServerReservedUrl | ForEach-Object -Process {
$invokeRsCimMethodParameters = @{
CimInstance = $reportingServicesData.Configuration
MethodName = 'RemoveURL'
Arguments = @{
Application = 'ReportServerWebService'
UrlString = $_
Lcid = $language
}
}
Invoke-RsCimMethod @invokeRsCimMethodParameters
}
Same thing, line 673 replaced:
$currentConfig.ReportsReservedUrl | ForEach-Object -Process {
with:
$ReportsReservedUrl | ForEach-Object -Process {
It gives:
if ( ($null -ne $ReportsReservedUrl) -and ($null -ne (Compare-Object @compareParameters)) )
{
$restartReportingService = $true
#$currentConfig.ReportsReservedUrl | ForEach-Object -Process {
# changed by:
$ReportsReservedUrl | ForEach-Object -Process {
$invokeRsCimMethodParameters = @{
CimInstance = $reportingServicesData.Configuration
MethodName = 'RemoveURL'
Arguments = @{
Application = $reportingServicesData.ReportsApplicationName
UrlString = $_
Lcid = $language
}
}
Invoke-RsCimMethod @invokeRsCimMethodParameters
}
The DSC configuration that is used to reproduce the issue (as detailed as possible)
"module_args": {
"ReportsVirtualDirectory": "Reports",
"ReportServerVirtualDirectory": "ReportServer",
"ReportsReservedUrl": [
"http://+:80",
"https://test.domain.com:443"
],
"InstanceName": "INSTANCE2",
"DatabaseInstanceName": "SSRS",
"ReportServerReservedUrl": [
"http://+:80",
"https://test.domain.com:443"
],
"DatabaseServerName": "SERVER.domain.com,7433",
"PsDscRunAsCredential_username": "domain\\account",
"ReportingServicesDatabaseName": "ReportServer_INSTANCE2",
"resource_name": "SqlRS",
"UseSsl": null,
"DependsOn": null,
"PsDscRunAsCredential_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
"SuppressRestart": null,
"module_version": "14.2.1"
That whay, the RemoveURL method is launched before the ReserveURL method, and, if the URL is already reserved, which is the problem if you have multiple instances with same URLs.
SQL Server edition and version the target node is running
Microsoft SQL Server 2016 (SP2-CU15) (KB4577775) - 13.0.5850.14 (X64) Sep 17 2020 22:12:45 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
SQL Server PowerShell modules present on the target node
The operating system the target node is running
OsName : Microsoft Windows Server 2016 Standard OsOperatingSystemSKU : StandardServerEdition OsArchitecture : 64-bit WindowsBuildLabEx : 14393.3630.amd64fre.rs1_release.200407-1730 OsLanguage : en-US OsMuiLanguages : {en-US, fr-CA}
Version and build of PowerShell the target node is running
Name Value
PSVersion 5.1.14393.3471
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14393.3471
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
Version of the DSC module that was used
Name Version Path
SqlServerDsc 14.2.1 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\14.2.1\SqlServerDsc.psd1 SqlServerDsc 13.3.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\13.3.0\SqlServerDsc.psd1 SqlServerDsc 12.2.0.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\12.2.0.0\SqlServerDsc.psd1 SqlServerDsc 11.3.0.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\11.3.0.0\SqlServerDsc.psd1 SqlServerDsc 11.1.0.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\11.1.0.0\SqlServerDsc.psd1
Sounds like it is a bug. But fixing this we should really need an integration test that configures this (and fails before a change) so we don't get regressions in future changes.
