SqlRs: Reporting Services 2019 Configuration SQLRS Error

[andig1]

Details of the scenario you tried and the problem that is occurring

Installation and Configuration of Reporting Services 2019.
During installation (default, see code) i do not get any error.
 Configuration with SQLRS shows and error at the end, see follwing DSC log.
In SSRS logs i am just see these errors, but after service restart/Server reboot
the same will be disappear:
FailedToDecryptConfigInformation: Dsn
ERROR: Throwing 
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorExcept
ion: No DSN present in configuration file, 
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorExcept
ion: The report server has encountered a configuration error. ;
As mentioned, after Reboot of Server i do not see any errors in SSRS logs and i 
am not able to see any issues problems, **hence the question? why do i get the 
errors in DSC log below??**
**DSC LOG of SQLRS**
VERBOSE: [sql2019tst4]:                            [[SqlRS]DefaultConfiguration]
Operation 'Invoke CimMethod' complete.
VERBOSE: [sql2019tst4]: LCM:  [ End    Set      ]  [[SqlRS]DefaultConfiguration]
in 38.6310 seconds.
PowerShell DSC resource MSFT_SqlRS  failed to execute Set-TargetResource 
functionality with error message: Method InitializeReportServer() failed with an
error. Error: 
The report server has encountered a configuration error.  
(rsServerConfigurationError) (HRESULT:-2146233088) 
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : localhost
 
VERBOSE: [sql2019tst4]: LCM:  [ End    Set      ]
The SendConfigurationApply function did not succeed.
    + CategoryInfo          : NotSpecified: 
(root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : MI RESULT 1
    + PSComputerName        : localhost
 
VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 40.324 seconds

Verbose logs showing the problem

Suggested solution to the issue

The DSC configuration that is used to reproduce the issue (as detailed as possible)

  Configuration InstallRS
    {
    Import-DscResource -ModuleName 'SqlServerDsc'
        node localhost
        {
            SqlRSSetup 'InstallDefaultInstance'
            {
                InstanceName         = 'SSRS'
                IAcceptLicenseTerms   = 'Yes'
                SourcePath           = 'C:\sql2019\ReportingServices\
    SQLServerReportingServices.exe'
                ProductKey           = "xxxxxxxxxxxxxxxx"
            }
        }
    }
    InstallRS
    Start-DscConfiguration -Path C:\windows\system32\InstallRS -Wait -force -Verbose
    Configuration ConfigureRS
    {
    Import-DscResource -ModuleName 'SqlServerDsc'
        node localhost
        {
            SqlRS 'DefaultConfiguration'
            {
                InstanceName         = 'SSRS'
                DatabaseServerName   = 'localhost'
                DatabaseInstanceName = 'MSSQLSERVER'
            }
        }
    }
    ConfigureRS
    Start-DscConfiguration -Path C:\windows\system32\ConfigureRS -Wait -Force -
    Verbose

SQL Server edition and version the target node is running

Microsoft SQL Server 2019 (RTM-CU4) (KB4548597) - 15.0.4033.1 (X64) Mar 14
2020 16:10:35 Copyright (C) 2019 Microsoft Corporation Enterprise Edition:
Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0
(Build 14393: ) (Hypervisor)

SQL Server PowerShell modules present on the target node

SQLPS 15.0    C:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\
Modules\SQLPS\SQLPS.psd1

The operating system the target node is running

OsName               : Microsoft Windows Server 2016 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
OsArchitecture       : 64-bit
WindowsBuildLabEx    : 14393.3564.amd64fre.rs1_release.200303-1942
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

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 13.2.0.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\
13.2.0.0\SqlServerDsc.psd1

[johlju]

I personally haven't tested Windows 2019 and Sql 2019 yet so not sure if this something else that needs to happen in the configuration.

Is the server restarted between SqlRSSetup and SqlRS? If not, does it do any difference?
Secondly, does the Reporting Services error logs say anything what might be missing?

[andig1]

I personally haven't tested Windows 2019 and Sql 2019 yet so not sure if this something else that needs to happen in the configuration.
Is the server restarted between SqlRSSetup and SqlRS? If not, does it do any difference?
Secondly, does the Reporting Services error logs say anything what might be missing?

Windows is Version is 2016, not 2019...
Yes, i have restarted the Server between SQLRSSetup and SQLRS, no difference. Additionally i have tested the same on 3 different Test Server (identical Windows Version, Configuration etc).
The Reporting Service error log do not show any "critical" things as far as i can see (RS logs are complex to read sometimes in my point of view)
The only error which i can see and will disappear as mentioned in my description after restart are:
1.
FailedToDecryptConfigInformation: Dsn
2.
ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorExcept
ion: No DSN present in configuration file,
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorExcept
ion: The report server has encountered a configuration error. ;

[johlju]

Looks like the DSN is encrypted in the configuration if you look at DSN here.

"Specifies the connection string to the database server that hosts the report server database. This value is encrypted and is added to the configuration file when you create the report server database. "
https://docs.microsoft.com/en-us/sql/reporting-services/report-server/rsreportserver-config-configuration-file?view=sql-server-ver15

So I wonder if the when SqlRs has configured the dsn but some service need to be restarted for it to read the encryption key. I'm just guessing here. Wonder what happens with the method InitializeReportServer() 🤔 Something else might need to happen before running this command. We run this in the integration test with SQL RS 2017 and it seems to work. So might be something new in SQL RS 2019.

[johlju]

This explains that the error thrown in the DSC resource is a generic one, and the actual error is probably that it cannot decrypt the DSN in the configuration file. But after restart it can decrypt it.

https://docs.microsoft.com/en-us/sql/reporting-services/troubleshooting/rsserverconfigurationerror-reporting-services-error?view=sql-server-ver15

[andig1]

This explains that the error thrown in the DSC resource is a generic one, and the actual error is probably that it cannot decrypt the DSN in the configuration file. But after restart it can decrypt it.
https://docs.microsoft.com/en-us/sql/reporting-services/troubleshooting/rsserverconfigurationerror-reporting-services-error?view=sql-server-ver15

just now i have done a new installation and configuration.
i have looked into the RS logs very detailed and it seems that "FailedToDecryptConfigInformation: Dsn" is really the relevant issue here. see logs as follows:

--SQLRSSetup--
Reoccuring error:
RSManagement_2020_04_23_10_09_50.log
FailedToDecryptConfigInformation: Dsn

one time errors:
ReportingServicesService_2020_04_23_10_09_52.log
ERROR: Failed to get url configuration section.

ERROR: Configuration file element UrlReservation has a invalid VirtualRoot. It will be ignored.

ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: No DSN present in configuration file, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;
ERROR: ServiceStartThread: Exception caught while starting service. Error: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error.
ERROR: ServiceStartThread: Attempting to start service again...
INFO: RPC Server started.

RSPortal_2020_04_23_10_09_52.log
Unable to load RS Configuration, error: System.NullReferenceException: Object reference not set to an instance of an object.

--Reboot Server---
still reoccuring errors:
RSManagement_2020_04_23_10_19_18.log
FailedToDecryptConfigInformation: Dsn

one time errors:
ReportingServicesService_2020_04_23_10_19_22.log
ERROR: Failed to get url configuration section.

ERROR: Configuration file element UrlReservation has a invalid VirtualRoot. It will be ignored.

ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: No DSN present in configuration file, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;
ERROR: ServiceStartThread: Exception caught while starting service. Error: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error.
ERROR: ServiceStartThread: Attempting to start service again...
INFO: RPC Server started.

RSPortal_2020_04_23_10_19_20.log
Unable to load RS Configuration, error: System.NullReferenceException: Object reference not set to an instance of an object.

----SQLRS---
i got the usual error at the end of DSC config

As you an see the InitializeReportServer()
ReportingServicesWMI_2020_04_23_10_30_11.log
ReportingServicesWMI!8292!8328!2020/04/23-10:30:51!SYSTEM!W!InitializeReportServer() with hr = 0x80131500

still reoccuring errors:
RSManagement_2020_04_23_10_19_18.log
dauerhaft
FailedToDecryptConfigInformation: Dsn

After restart of RS Service no further errors and everything works as expected.

[johlju]

Thank you for the detailed drill down. Since the problem is when SqlRs is calling the Reporting Services method InitializeReportServer() it is either a problem how it is called, or something not happening in that call, or (most likely) some other method or step should be called or done prior to calling the method InitializeReportServer().

Looking at the remarks section here it tells what is suppose to happen. One of those steps might not occur.

https://docs.microsoft.com/en-us/sql/reporting-services/wmi-provider-library-reference/configurationsetting-method-initializereportserver?view=sql-server-ver15#remarks

[bozho]

Hi @johlju ,

I can confirm I have the exact same problem. The same DSC config (SqlRSSetup + SqlRS) runs fine for SQL + SSRS 2017, but fails with this error for SQL + SSRS 2019.

Initialising SSRS 2019 using the GUI (configuring the DB connection is enough) works fine. Both 2017 and 2019 GUIs run the same steps (at least that's what get reported in the GUI):

• Verifying database sku
• Generating database script
• Running database script
• Generating rights script
• Applying connection rights
• Setting DSN

Using SqlRS to configure SSRS 2019 after it's been initialised seems to work fine.

I've compared CIM classes' methods on both versions and they are the same.

[Fiander]

Because report server and Power bi report server are not in the SqlServer setup anymore, and are independed resources with a own release timeframe, wouldn't it be better to strip support from SQLServerDsc, and create a ReportServerDSC instead?

[brunogr84]

Is there any update on this issue? Have someone found a work around it?

Thanks

[matteu31400]

Hello,
I'm trying to do silent configuration of SQL report 2019 and same issue for me :)
I hope someone can find solution soon

[Francois-Rousseau]

Hello,
FYI:
I have the same issue, but, before I had this issue, I have the code worked for me.
If using exactly the same "DatabaseInstanceName" and "InstanceName", it works. (so is it really related to DSN?)
As soon as I change "InstanceName" to use different names for database instance and reporting instance, it stops working.

Somehow the code works... but not for different instances names

And, I'm with SQL2016 on Windows 2016. So, not sure that the SQL Version is the problem.

[andig1]

HI all,
it's been almost a year that i opened that issue.
Hence i am really suprised that it is obviously still not fixed. Does anyone really use "SqlRs" for Reporting Services 2019 and it works ?!
By the way:
i use a workaround in my deployment.

• For installation i use "SqlRSSetup"
• For configuration i use a powershell script (example see link below) and start the custom Powershell Script in DSC with "SetScript"
  https://blog.aelterman.com/2018/01/03/complete-automated-configuration-of-sql-server-2017-reporting-services/

[johlju]

No one in the community have picked up the issue and sent in a PR to resolve this, so most likely it will not work for SQL2019. But with the help with the script you provided someone in the community maybe get inspired by it and resolve the resource. Thank you for providing a link to working script.

[bozho]

@andig1 We use SQL 2016-2019, and SQL2019 is the only one for which the resource does not initialise SSRS server after an install.

Thank you for the script, I'll have a look and see if it can be incorporated into the resource.

[bozho]

@andig1 Nope, when I run your script on an uninitialised SSRS 2019 instance, the call to InitializeReportServer() fails with the OP's error.

[bozho]

@johlju I've even resorted to comparing ReportServer DBs created when initialising SSRS using Reporting management and when using WMI. Both schemas and data matches (apart from GUID IDs and creation/modification datetimes).

Is there any way we could get information from relevant SSRS people from Microsoft?

[bozho]

Progress!

It looks like (at least for SSRS 2019) after setting up DB stuff and reserved URLs for the first time, it is enough to restart SSRS service without executing InitializeReportServer() to initialise SSRS.

I've confirmed this on my test server with SQL2019, with a hacked SqlRS resource. After applying my DSC configuration, SSRS appears to be fully functional, including the web portal.

I'll do some more testing and check behaviour on SQL 2016 and 2017 and come up with a PR.

[johlju]

Because report server and Power bi report server are not in the SqlServer setup anymore, and are independed resources with a own release timeframe, wouldn't it be better to strip support from SQLServerDsc, and create a ReportServerDSC instead?

@Fiander sorry for late answer. We could add a new repo for those resources that handles those separate products, but then we need a maintainer for that new repo (module). It would also be a breaking change when we remove functionality to only keep functionality for SSRS 2016 and below.

If someone raises there hand to be a maintainer for a new module I happy to set up the project so the community can build on it.

[johlju]

Thanks to @bozho fixes for this is now merged and also integration tests so we know that it actually works. Thank you @bozho for finding the fix!