SqlRS: Does not work when reporting database machine is renamed

[achern]

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

I am trying to use xSQLServerRSConfig to set SSRS's database connection after the machine with the reporting database has been renamed. There are two problems:

1. xSQLServerRSConfig is running the database creation scripts even though the database is already created, resulting in errors when trying to recreate it. This is because it is checking isInitialized to determine if the DB should be created, but this variable seems to only indicate if the encryption keys have been generated, which in my case have not, probably due to the rename.

2. Immediately after setting the new database connection and initializing the report server, xSQLServerRSConfig tests the resource values again and fails because isInitialized is still $false. This value only becomes $true after 30 seconds or so in my environment. It seems like there needs to be come sort of wait here for the initialization to complete. Also, I am not sure if reinitializing a report server like this will cause encrypted data to be lost. Maybe if a rename is detected the keys should be backed up and restored?

The DSC configuration that is using the resource (as detailed as possible):

This should be reproducible with a barebones configuration where the SQL Server with the reporting database has been renamed:

Configuration RSConfig {

    Import-DscResource -ModuleName xSqlServer

    Node $Node {
        
        xSQLServerRSConfig RSConfig {
            InstanceName = 'MSSQLSERVER'
            RSSQLServer = $NewSqlServerComputerName
            RSSQLInstanceName = 'MSSQLSERVER'
        }
    }
}

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:

Windows Server 2012 R2 6.3.9600

SQL Server 2016 SP1 13.0.4001.0

PowerShell:

Name                           Value
----                           -----
PSVersion                      5.1.14409.1005
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.14409.1005
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

What module (SqlServer or SQLPS) and which version of the module the DSC Target Node is running:

SqlServer 21.0.17178

Version of the DSC module you're using, or 'dev' if you're using current dev branch:

8.2.0.0

[johlju]

Do I understand your the scenario correctly.

1. You configured Reporting Services on the first server with computer name 'A', and Reporting Services databases on a second server with the computer name 'B'. The configuration finished successfully and Reporting Services was working.
2. You renamed the computer name from 'B' to 'Y'.
3. You re-ran the same configuration with the exception of changing parameter RSSQLServer to 'Y'.
4. The configuration failed with error 1 above.

[achern]

Yes, that is pretty much the scenario, except 'A' and 'B' are actually the same computer in my environment. That is, the reporting DB and Reporting Services are both on the same computer.

After some further experimentation, it seems that in the case of a rename, all that needs to be done to fix the settings is to call SetDatabaseConnection() with the new name on the MSReportServer_ConfigurationSetting WMI object.

[johlju]

In a multiserver environment (or in a single server environment) I would typically don't use the computer name as the host name, but use a DNS host name (CNAME-record, or an A-record so Kerberos double hop works) so it easy to move the databases to a new environment.

That said. If possible, it should be possible to change the parameter if desired state is something else than the configured value.

Thanks for determine the possible solution!

Articles for reference:

• MSReportServer_ConfigurationSetting Methods
  • ConfigurationSetting Method - SetDatabaseConnection
• MSReportServer_ConfigurationSetting Members
  • ConfigurationSetting Property - DatabaseServerName