Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

SqlRs: SQL Server 2016 SSRS init fails on AWS #1258

Closed
bozho opened this issue Dec 14, 2018 · 14 comments · Fixed by #1335
Closed

SqlRs: SQL Server 2016 SSRS init fails on AWS #1258

bozho opened this issue Dec 14, 2018 · 14 comments · Fixed by #1335
Labels
bug The issue is a bug.

Comments

@bozho
Copy link
Contributor

bozho commented Dec 14, 2018 •
edited
Loading

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

We have a configuration that we run against a provisioned AWS VM Windows Server 2016 + SQL Server 2016 SP1 Standard. (We use the latest AMI that matches the name pattern Windows_Server-2016-English-Full-SQL_2016_SP1_Standard*)

Our set of partial configurations is not trivial - we have a workflow that compiles and pushes these configurations to the target server. One of the first things it does is set the machine name, which causes a reboot. Our workflow then waits for the machine to boot back up and then waits for the configuration run to finish.

Recently, we noticed that after a while the workflow starts reporting that DSC has encountered an error, but there's nothing in the Event Log. However, there is an entry in the log file:

Verbose logs showing the problem

  {"time": "2018-12-14T15:26:07.040+00:00", "type": "verbose", "message": "[HOSTING-TEST]: LCM:  [ Start  Resource ]  [[SqlRS]SSRSConfig]  "},
  {"time": "2018-12-14T15:26:07.040+00:00", "type": "verbose", "message": "[HOSTING-TEST]: LCM:  [ Start  Test     ]  [[SqlRS]SSRSConfig]  "},
  {"time": "2018-12-14T15:26:07.103+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Perform operation 'Enumerate CimInstances' with following parameters, ''namespaceName' = root\\Microsoft\\SQLServer\\ReportServer\\RS_MSSQLSERVER\\v13\\Admin,'className' = MSReportServer_ConfigurationSetting'."},
  {"time": "2018-12-14T15:26:07.930+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Operation 'Enumerate CimInstances' complete."},
  {"time": "2018-12-14T15:26:07.952+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] 2018-12-14_15-26-07: Reporting services localhost\\MSSQLSERVER are not initialized."},
  {"time": "2018-12-14T15:26:07.957+00:00", "type": "verbose", "message": "[HOSTING-TEST]: LCM:  [ End    Test     ]  [[SqlRS]SSRSConfig]  in 0.9170 seconds."},
  {"time": "2018-12-14T15:26:07.958+00:00", "type": "verbose", "message": "[HOSTING-TEST]: LCM:  [ Start  Set      ]  [[SqlRS]SSRSConfig]  "},
  {"time": "2018-12-14T15:26:08.007+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Perform operation 'Enumerate CimInstances' with following parameters, ''namespaceName' = root\\Microsoft\\SQLServer\\ReportServer\\RS_MSSQLSERVER\\v13\\Admin,'className' = MSReportServer_ConfigurationSetting'."},
  {"time": "2018-12-14T15:26:08.031+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Operation 'Enumerate CimInstances' complete."},
  {"time": "2018-12-14T15:26:08.034+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Perform operation 'Enumerate CimInstances' with following parameters, ''namespaceName' = root/cimv2,'className' = Win32_OperatingSystem'."},
  {"time": "2018-12-14T15:26:08.060+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Operation 'Enumerate CimInstances' complete."},
  {"time": "2018-12-14T15:26:08.064+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] 2018-12-14_15-26-08: Initializing Reporting Services on localhost\\MSSQLSERVER."},
  {"time": "2018-12-14T15:26:08.068+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] 2018-12-14_15-26-08: Setting report server virtual directory on localhost\\MSSQLSERVER to ReportServer."},
  {"time": "2018-12-14T15:26:08.090+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Perform operation 'Invoke CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = \"MSSQLSERVER\"),'methodName' = SetVirtualDirectory,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_MSSQLSERVER/v13/Admin'."},
  {"time": "2018-12-14T15:26:12.634+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Operation 'Invoke CimMethod' complete."},
  {"time": "2018-12-14T15:26:12.647+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] 2018-12-14_15-26-12: Adding report server URL reservation on localhost\\MSSQLSERVER: http://+:8888."},
  {"time": "2018-12-14T15:26:12.648+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Perform operation 'Invoke CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = \"MSSQLSERVER\"),'methodName' = ReserveUrl,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_MSSQLSERVER/v13/Admin'."},
  {"time": "2018-12-14T15:26:17.700+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Operation 'Invoke CimMethod' complete."},
  {"time": "2018-12-14T15:26:17.701+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] 2018-12-14_15-26-17: Adding report server URL reservation on localhost\\MSSQLSERVER: https://hosting-test.elements.symplectic.org:443."},
  {"time": "2018-12-14T15:26:17.702+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Perform operation 'Invoke CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = \"MSSQLSERVER\"),'methodName' = ReserveUrl,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_MSSQLSERVER/v13/Admin'."},
  {"time": "2018-12-14T15:26:23.254+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Operation 'Invoke CimMethod' complete."},
  {"time": "2018-12-14T15:26:23.256+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] 2018-12-14_15-26-23: Setting reports virtual directory on localhost\\MSSQLSERVER to ReportServer."},
  {"time": "2018-12-14T15:26:23.259+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Perform operation 'Invoke CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = \"MSSQLSERVER\"),'methodName' = SetVirtualDirectory,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_MSSQLSERVER/v13/Admin'."},
  {"time": "2018-12-14T15:27:03.371+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Operation 'Invoke CimMethod' complete."},
  {"time": "2018-12-14T15:27:03.381+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] 2018-12-14_15-27-03: Adding reports URL reservation on localhost\\MSSQLSERVER: http://+:8888."},
  {"time": "2018-12-14T15:27:03.395+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Perform operation 'Invoke CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = \"MSSQLSERVER\"),'methodName' = ReserveUrl,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_MSSQLSERVER/v13/Admin'."},
  {"time": "2018-12-14T15:27:08.437+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Operation 'Invoke CimMethod' complete."},
  {"time": "2018-12-14T15:27:08.438+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] 2018-12-14_15-27-08: Adding reports URL reservation on localhost\\MSSQLSERVER: https://hosting-test.elements.symplectic.org:443."},
  {"time": "2018-12-14T15:27:08.440+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Perform operation 'Invoke CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = \"MSSQLSERVER\"),'methodName' = ReserveUrl,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_MSSQLSERVER/v13/Admin'."},
  {"time": "2018-12-14T15:27:13.992+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Operation 'Invoke CimMethod' complete."},
  {"time": "2018-12-14T15:27:13.994+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Perform operation 'Invoke CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = \"MSSQLSERVER\"),'methodName' = GenerateDatabaseCreationScript,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_MSSQLSERVER/v13/Admin'."},
  {"time": "2018-12-14T15:27:14.910+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Operation 'Invoke CimMethod' complete."},
  {"time": "2018-12-14T15:27:15.024+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Perform operation 'Enumerate CimInstances' with following parameters, ''namespaceName' = root\\cimv2,'className' = Win32_Service'."},
  {"time": "2018-12-14T15:27:15.269+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Operation 'Enumerate CimInstances' complete."},
  {"time": "2018-12-14T15:27:15.270+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Perform operation 'Invoke CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = \"MSSQLSERVER\"),'methodName' = GenerateDatabaseRightsScript,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_MSSQLSERVER/v13/Admin'."},
  {"time": "2018-12-14T15:27:15.289+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Operation 'Invoke CimMethod' complete."},
  {"time": "2018-12-14T15:27:15.371+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Preferred module SqlServer found."},
  {"time": "2018-12-14T15:27:15.375+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Importing PowerShell module 'SqlServer' with version '21.0.17240' from path 'C:\\Program Files\\WindowsPowerShell\\Modules\\SqlServer\\21.0.17240\\SqlServer.psm1'."},
  {"time": "2018-12-14T15:27:16.429+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'ReportServer'."},
  {"time": "2018-12-14T15:27:16.479+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'msdb'."},
  {"time": "2018-12-14T15:27:16.523+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'master'."},
  {"time": "2018-12-14T15:27:16.554+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'ReportServerTempDB'."},
  {"time": "2018-12-14T15:27:16.581+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'ReportServerTempDB'."},
  {"time": "2018-12-14T15:27:16.687+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'ReportServer'."},
  {"time": "2018-12-14T15:27:18.524+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'master'."},
  {"time": "2018-12-14T15:27:18.535+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'msdb'."},
  {"time": "2018-12-14T15:27:18.729+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'ReportServerTempDB'."},
  {"time": "2018-12-14T15:27:18.765+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'ReportServerTempDB'."},
  {"time": "2018-12-14T15:27:18.765+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'ReportServerTempDB'."},
  {"time": "2018-12-14T15:27:18.842+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'ReportServerTempDB'."},
  {"time": "2018-12-14T15:27:19.112+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Caution: Changing any part of an object name could break scripts and stored procedures."},
  {"time": "2018-12-14T15:27:19.207+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'ReportServerTempDB'."},
  {"time": "2018-12-14T15:27:19.306+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'ReportServer'."},
  {"time": "2018-12-14T15:27:19.309+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'ReportServer'."},
  {"time": "2018-12-14T15:27:19.390+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'ReportServer'."},
  {"time": "2018-12-14T15:27:19.645+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Caution: Changing any part of an object name could break scripts and stored procedures."},
  {"time": "2018-12-14T15:27:19.688+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'ReportServer'."},
  {"time": "2018-12-14T15:27:19.775+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'ReportServer'."},
  {"time": "2018-12-14T15:27:19.875+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'ReportServer'."},
  {"time": "2018-12-14T15:27:21.241+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] The module 'CreateRdlChunk' depends on the missing object 'CreateSegmentedChunk'. The module will still be created; however, it cannot run successfully until the object exists."},
  {"time": "2018-12-14T15:27:21.241+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] The module 'CreateRdlChunk' depends on the missing object 'CreateChunkSegment'. The module will still be created; however, it cannot run successfully until the object exists."},
  {"time": "2018-12-14T15:27:21.627+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'ReportServerTempDB'."},
  {"time": "2018-12-14T15:27:21.636+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'ReportServer'."},
  {"time": "2018-12-14T15:27:21.657+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'master'."},
  {"time": "2018-12-14T15:27:21.701+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'ReportServer'."},
  {"time": "2018-12-14T15:27:21.732+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'ReportServerTempDB'."},
  {"time": "2018-12-14T15:27:21.757+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'msdb'."},
  {"time": "2018-12-14T15:27:21.782+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'master'."},
  {"time": "2018-12-14T15:27:21.814+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Changed database context to 'ReportServer'."},
  {"time": "2018-12-14T15:27:21.816+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Perform operation 'Invoke CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = \"MSSQLSERVER\"),'methodName' = SetDatabaseConnection,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_MSSQLSERVER/v13/Admin'."},
  {"time": "2018-12-14T15:27:28.863+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Operation 'Invoke CimMethod' complete."},
  {"time": "2018-12-14T15:27:28.866+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Perform operation 'Invoke CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = \"MSSQLSERVER\"),'methodName' = InitializeReportServer,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_MSSQLSERVER/v13/Admin'."},
  {"time": "2018-12-14T15:27:29.008+00:00", "type": "verbose", "message": "[HOSTING-TEST]:                            [[SqlRS]SSRSConfig] Operation 'Invoke CimMethod' complete."},
  {"time": "2018-12-14T15:27:29.055+00:00", "type": "verbose", "message": "[HOSTING-TEST]: LCM:  [ End    Set      ]  [[SqlRS]SSRSConfig]  in 81.0980 seconds."},
  {"time": "2018-12-14T15:27:29.055+00:00", "type": "error", "message": "PowerShell DSC resource MSFT_SqlRS  failed to execute Set-TargetResource functionality with error message: Method InitializeReportServer() failed with an error. Error: The feature: \"Scale-out deployment\" is not supported in this edition of Reporting Services. (rsOperationNotSupported) (HRESULT:-2146233088) "},

It looks like the initialisation only partially fails - after this has run, Reporting Services Configuration Manager looks fine, but logging into the web GUI gets you a 503, until we manually restart the SSRS service.

After the restart, SSRS seems to be working fine, our custom roles/policies resources to their job, we can deploy reports, etc.

Suggested solution to the issue

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

I'll see if I can produce a minimal configuration to replicate this problem.

SQL Server edition and version the target node is running

Microsoft SQL Server 2016 (SP1-CU8) (KB4077064) - 13.0.4474.0 (X64)   Feb 24 2018 13:53:17   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

I'll see if I can find a Win2016+SQL2016 AMI that doesn't exhibit this problem.

SQL Server PowerShell modules present on the target node

Name         Version    Path                                                                                           
----         -------    ----                                                                                           
SqlServer    21.0.17240 C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17240\SqlServer.psd1                 
SqlServer    20.0       C:\Program Files\WindowsPowerShell\Modules\SqlServer\SqlServer.psd1                            
SQLASCMDLETS 2.0        C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLASCMDLETS\SQLASC...
SQLPS        1.0        C:\Program Files (x86)\Microsoft SQL Server\130\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.2608.amd64fre.rs1_release.181024-1742
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

Version and build of PowerShell the target node is running

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

Version of the DSC module that was used ('dev' if using current dev branch)

Name         Version  Path                                                                              
----         -------  ----                                                                              
SqlServerDsc 12.1.0.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\12.1.0.0\SqlServerDsc.psd1
@bozho
Copy link
Contributor Author

bozho commented Dec 17, 2018

I've now tested with two different AWS AMIs, with our production configuration, which uses xSqlServer 9.0.0.0, and our "test" configuration, where the only difference is an upgrade to SqlServerDsc module v12.1.0.0 (with appropriately renamed resources and parameters).

The production configuration succeeds on both AMIs and the test configuration fails on both with the above error.

Is the CIM class used in SqlServerDsc trying to configure this "Scale Out Deployment" with InitializeReportServer() method?

@johlju
Copy link
Member

johlju commented Dec 18, 2018 •
edited
Loading

I wonder if it fails on this - it would suggest that.

https://github.com/PowerShell/SqlServerDsc/blob/457082c151ee5d2fe992a9dc0a2ed4389594fe37/DSCResources/MSFT_SqlRS/MSFT_SqlRS.psm1#L428-L436

So it can never get to the point that it restarts the service

https://github.com/PowerShell/SqlServerDsc/blob/457082c151ee5d2fe992a9dc0a2ed4389594fe37/DSCResources/MSFT_SqlRS/MSFT_SqlRS.psm1#L453

We should really add more verbose messages in this resource so it's easier to track how far it gets.
Anyway, I don't think scale out deployment is a supported scenario as of yet, so not sure why it tries to configure it that way.

Can you please provide the parts of configuration script that configures the Reporting Services? So it's is possible to reproduce this.

@johlju johlju added the needs more information The issue needs more information from the author or the community. label Dec 18, 2018
@bozho
Copy link
Contributor Author

bozho commented Dec 18, 2018

Hi,

I've managed to replicate the problem with absolutely minimal configuration. I tested with the following AWS AMI (eu-west-01 region, the ID will be different in different regions):

Windows_Server-2016-English-Full-SQL_2016_SP1_Standard-2018.12.12 (ami-0cdf848d0b03c0b61)

On a clean install, first install SqlServerDsc:

Install-Module -Name SqlServerDsc -RequiredVersion 12.1.0.0 -SkipPublisherCheck -Force

Then, save this to a .ps1 script and run it from PowerShell prompt:

configuration ConfigureSsrs {

    Import-DscResource -ModuleName SqlServerDsc

    $reservedUrls = @("http://+:8888")

    Node "localhost"{
        SqlRS SSRSConfig {
            InstanceName = "MSSQLSERVER"
            DatabaseServerName = "localhost"
            DatabaseInstanceName = "MSSQLSERVER"
            ReportServerReservedUrl = $reservedUrls
            ReportsReservedUrl = $reservedUrls
        }
    }
}

ConfigureSsrs

Start-DscConfiguration -Path .\ConfigureSsrs -Wait -Force -Verbose

You'll get the same error:

PowerShell DSC resource MSFT_SqlRS  failed to execute Set-TargetResource functionality with error message: Method
InitializeReportServer() failed with an error. Error: The feature: "Scale-out deployment" is not supported in this
edition of Reporting Services. (rsOperationNotSupported) (HRESULT:-2146233088)
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : localhost

@bozho
Copy link
Contributor Author

bozho commented Dec 18, 2018

It also looks that all that is needed after the initialisation fails is to restart SSRS service. I have a few custom SSRS resources (setting policies and roles). They keep failing until I restart SSRS service.

@johlju
Copy link
Member

johlju commented Dec 19, 2018 •
edited
Loading

@bozho Wonder if they way it configures it is the same way to configure a scale out deployment, and since the Standard version does not support it (see Scale out deployment (Web farms) under Reporting Services Features Supported by the Editions of SQL Server , then it fails. This probably works if you use Enterprise edition of SQL 2016.

So the problem is that this should be able to correctly initialize SSRS 2016 Standard as well.

@johlju johlju added bug The issue is a bug. help wanted The issue is up for grabs for anyone in the community. and removed needs more information The issue needs more information from the author or the community. labels Dec 19, 2018
@bozho
Copy link
Contributor Author

bozho commented Dec 19, 2018

The strange thing is that if I use the xSqlServer module and its xSQLServerRSConfig resource to configure SSRS 2016 on the same AMI, it succeeds. As far as I can tell, xSQLServerRSConfig also calls InitializeReportServer, only through WMI not CMI.

And why would InitializeReportServer even try to configure scale-out deployment?

Are there two different versions of InitializeReportServer being executed?

@bozho
Copy link
Contributor Author

bozho commented Dec 20, 2018 •
edited
Loading

@johlju Ok, found out why the xSqlServer is working - WMI method calls are not checking return values. The The feature: "Scale-out deployment" is not supported in this edition of Reporting Services. (rsOperationNotSupported) error still happens, but it's ignored.

The code in SqlServerDsc checks return values on CIM method calls, and fails.

You write better code and you're worse off... :-)

@bozho
Copy link
Contributor Author

bozho commented Apr 24, 2019

@johlju I can confirm this is still happening. Would it make sense to treat this as a warning? I see no way to control the "scale-out deployment" option on SSRS...

@johlju
Copy link
Member

johlju commented Apr 24, 2019

But if you get this error, aren’t the wrong edition used if you get this error? 🤔 Or is this just a warning reported back from the CIM call, but we treat it as an error? If so we can just output a warning for this, but an error in all other cases.

The integration test for SqlRS does not report this error, at least the tests doesn’t fail. Is it because we use the Evaluation (equals the Enterprise) version for the tests?

@bozho
Copy link
Contributor Author

bozho commented Apr 24, 2019

But if you get this error, aren’t the wrong edition used if you get this error?

No idea, we use SQL Standard on AWS (this is SQL 2016), so SSRS is bundled with it. AWS image contains a default install of SQL Server, with SSRS just installed, not configured/initialised (there's an option in SQL Setup for that).

SqlRS sets up the virtual directories, reserves URLs, and simply calls the Initialise method. Older versions of the resource would simply ignore any errors - I would bet SSRS Configuration GUI also ignores this error, since I've found no mention of that option/setting.

@johlju
Copy link
Member

johlju commented Apr 24, 2019

I’m good with outputting a warning for now, when this error is reported, until we know if this is a bug in the CIM method, or any other reason it is reporting this error. :)

@bozho
Copy link
Contributor Author

bozho commented Apr 24, 2019

Ok, I'll work on the patch

@bozho
Copy link
Contributor Author

bozho commented Apr 24, 2019

@johlju Heh, I had a hunch and did a bit more testing by executing Set-TargetResource manually line by line...

It seems that executing CIM method SetDatabaseConnection initialises SSRS. Calling Get-ReportingServicesData -InstanceName $InstanceName right after executing SetDatabaseConnection, but before executing InitializeReportServer returns SSRS configuration where IsInitialized it True.

I suppose by executing InitializeReportServer when IsInitialized, SSRS thinks we're doing the scale-out deployment and returns an error on SQL Standard. Older versions of the module were ignoring return values from CIM methods and we never caught the error.

So, the suggested patch here would be not to execute InitializeReportServer CIM method when initialising SSRS.

@johlju
Copy link
Member

johlju commented Apr 24, 2019

Awesome detective work on this one too! 😃

bozho added a commit to bozho/SqlServerDsc that referenced this issue Apr 25, 2019
@bozho
[dsccommunity#1258] A fix for Reporting Services initialization on SQ…
bca5594 
…L Standard and lower editions
bozho added a commit to bozho/SqlServerDsc that referenced this issue Apr 25, 2019
@bozho
[dsccommunity#1258] SqlRS unit test making sure InitializeReportServe…
e3ca536 
…r CIM method is never called
@bozho bozho mentioned this issue Apr 25, 2019
Merged
9 tasks
bozho added a commit to bozho/SqlServerDsc that referenced this issue Apr 25, 2019
@bozho
[dsccommunity#1258] A fix for Reporting Services initialization on SQ…
f4c9e52 
…L Standard and lower editions
bozho added a commit to bozho/SqlServerDsc that referenced this issue Apr 25, 2019
@bozho
[dsccommunity#1258] SqlRS unit test making sure InitializeReportServe…
e6fdb8d 
…r CIM method is never called
bozho added a commit to bozho/SqlServerDsc that referenced this issue Apr 26, 2019
@bozho
[dsccommunity#1258] A fix for Reporting Services initialization on SQ…
25cdf70 
…L Standard and lower editions
bozho added a commit to bozho/SqlServerDsc that referenced this issue Apr 26, 2019
@bozho
[dsccommunity#1258] SqlRS unit test making sure InitializeReportServe…
d8102cc 
…r CIM method is never called
bozho added a commit to bozho/SqlServerDsc that referenced this issue Apr 26, 2019
@bozho
[dsccommunity#1258] Added conditional execution of InitializeReportSe…
4b2c53a 
…rver
bozho added a commit to bozho/SqlServerDsc that referenced this issue Apr 26, 2019
@bozho
[dsccommunity#1258] Fixed unit tests
34e1f9f
bozho added a commit to bozho/SqlServerDsc that referenced this issue Apr 26, 2019
@bozho
[dsccommunity#1258] A fix for Reporting Services initialization on SQ…
d762f72 
…L Standard and lower editions
bozho added a commit to bozho/SqlServerDsc that referenced this issue Apr 26, 2019
@bozho
[dsccommunity#1258] SqlRS unit test making sure InitializeReportServe…
8ab79e3 
…r CIM method is never called
bozho added a commit to bozho/SqlServerDsc that referenced this issue Apr 26, 2019
@bozho
[dsccommunity#1258] Added conditional execution of InitializeReportSe…
8618453 
…rver
bozho added a commit to bozho/SqlServerDsc that referenced this issue Apr 26, 2019
@bozho
[dsccommunity#1258] Fixed unit tests
ecb64cd
bozho added a commit to bozho/SqlServerDsc that referenced this issue Apr 26, 2019
@bozho
[dsccommunity#1258] A fix for Reporting Services initialization on SQ…
7e1e8ec 
…L Standard and lower editions
bozho added a commit to bozho/SqlServerDsc that referenced this issue Apr 26, 2019
@bozho
[dsccommunity#1258] SqlRS unit test making sure InitializeReportServe…
4c249f5 
…r CIM method is never called
bozho added a commit to bozho/SqlServerDsc that referenced this issue Apr 26, 2019
@bozho
[dsccommunity#1258] Added conditional execution of InitializeReportSe…
98f0beb 
…rver
bozho added a commit to bozho/SqlServerDsc that referenced this issue Apr 26, 2019
@bozho
[dsccommunity#1258] Fixed unit tests
7904856
bozho added a commit to bozho/SqlServerDsc that referenced this issue May 6, 2019
@bozho
[dsccommunity#1258] A fix for Reporting Services initialization on SQ…
928f8a9 
…L Standard and lower editions
bozho added a commit to bozho/SqlServerDsc that referenced this issue May 6, 2019
@bozho
[dsccommunity#1258] SqlRS unit test making sure InitializeReportServe…
6cf502f 
…r CIM method is never called
bozho added a commit to bozho/SqlServerDsc that referenced this issue May 6, 2019
@bozho
[dsccommunity#1258] Added conditional execution of InitializeReportSe…
bb54481 
…rver
bozho added a commit to bozho/SqlServerDsc that referenced this issue May 6, 2019
@bozho
[dsccommunity#1258] Fixed unit tests
b1c2f91
johlju pushed a commit that referenced this issue May 6, 2019
@bozho @johlju
SqlRs: Fix for Reporting Services initialization (#1335)
aee4542 
- Changes to SqlRS
  - When initializing Reporting Services, there is no need to execute `InitializeReportServer`
    CIM method, since executing `SetDatabaseConnection` CIM method initializes
    Reporting Services Fixed (issue #1258)
@kwirkykat kwirkykat removed the help wanted The issue is up for grabs for anyone in the community. label May 6, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug The issue is a bug.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

SqlRs: [#1258] Fix for Reporting Services initialization bozho/SqlServerDsc
3 participants
@bozho @kwirkykat @johlju