search

dsccommunity / SqlServerDsc

This module contains DSC resources for deployment and configuration of Microsoft SQL Server.
MIT License
358 stars 224 forks source link

SqlRs Fails to Access Using a Machine Account for SQL 2017 RS and Newer #1878

Closed shurick81 closed 1 year ago

shurick81 commented 1 year ago

Problem description

Set up looks working for me when I install SQL Server 2016 Reporting Services. However, when I start configuring SQL 2017 I get an error Login failed for user 'C0NT0S00\SWAZSERVER00$', where SWAZSERVER00 is the machine where I run reporting services. My service is running under dedicated C0NT0S00\_ssrs service account in AD. And I use PsDscRunAsCredential DSC parameter for using administrative account that have full permissions in SQL DBENGINE instance (SWAZDB00\SQLInstance01).

Verbose logs

VERBOSE: [SWAZSERVER00]: LCM:  [ Start  Resource ]  [[SqlRS]ReportingServicesConfig]
VERBOSE: [SWAZSERVER00]: LCM:  [ Start  Test     ]  [[SqlRS]ReportingServicesConfig]
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Get the current reporting services
 configuration for the instance 'SSRS'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Enumerate 
CimInstances' with following parameters, ''namespaceName' = 
root\Microsoft\SQLServer\ReportServer\RS_SSRS\v14\Admin,'className' = MSReportServer_ConfigurationSetting'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Enumerate CimInstances'
 complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Reporting services 
SWAZDB00\SQLInstance01 are not initialized.
VERBOSE: [SWAZSERVER00]: LCM:  [ End    Test     ]  [[SqlRS]ReportingServicesConfig]  in 1.5800 seconds.
VERBOSE: [SWAZSERVER00]: LCM:  [ Start  Set      ]  [[SqlRS]ReportingServicesConfig]
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Enumerate 
CimInstances' with following parameters, ''namespaceName' = 
root\Microsoft\SQLServer\ReportServer\RS_SSRS\v14\Admin,'className' = MSReportServer_ConfigurationSetting'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Enumerate CimInstances'
 complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Enumerate 
CimInstances' with following parameters, ''namespaceName' = root/cimv2,'className' = Win32_OperatingSystem'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Enumerate CimInstances'
 complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Initializing Reporting Services on
 SWAZDB00\SQLInstance01.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Setting report server virtual 
directory on SWAZDB00\SQLInstance01 to ReportServer.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke 
CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = 
"SSRS"),'methodName' = SetVirtualDirectory,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_SSRS/v14/Admin'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' 
complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Adding report server URL 
reservation on SWAZDB00\SQLInstance01: http://+:80.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke 
CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = 
"SSRS"),'methodName' = ReserveUrl,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_SSRS/v14/Admin'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' 
complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Setting reports virtual directory 
on SWAZDB00\SQLInstance01 to ReportServer.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke 
CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = 
"SSRS"),'methodName' = SetVirtualDirectory,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_SSRS/v14/Admin'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' 
complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Adding reports URL reservation on 
SWAZDB00\SQLInstance01: http://+:80.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke 
CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = 
"SSRS"),'methodName' = ReserveUrl,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_SSRS/v14/Admin'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' 
complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke 
CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = 
"SSRS"),'methodName' = GenerateDatabaseCreationScript,'namespaceName' = 
root/Microsoft/SQLServer/ReportServer/RS_SSRS/v14/Admin'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' 
complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Enumerate 
CimInstances' with following parameters, ''namespaceName' = root\cimv2,'className' = Win32_Service'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Enumerate CimInstances'
 complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke 
CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = 
"SSRS"),'methodName' = GenerateDatabaseRightsScript,'namespaceName' = 
root/Microsoft/SQLServer/ReportServer/RS_SSRS/v14/Admin'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' 
complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Preferred module SqlServer found. 
(SQLCOMMON0023)
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Importing PowerShell module 
'SqlServer' with version '21.1.18256' from path 'C:\Program 
Files\WindowsPowerShell\Modules\SqlServer\21.1.18256\SqlServer.psm1'. (SQLCOMMON0025)
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'msdb'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'master'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServerTempDB'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServerTempDB'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'master'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'msdb'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServerTempDB'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServerTempDB'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServerTempDB'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServerTempDB'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Caution: Changing any part of an 
object name could break scripts and stored procedures.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServerTempDB'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Caution: Changing any part of an 
object name could break scripts and stored procedures.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] The module 'CreateRdlChunk' 
depends on the missing object 'CreateSegmentedChunk'. The module will still be created; however, it cannot run 
successfully until the object exists.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] The module 'CreateRdlChunk' 
depends on the missing object 'CreateChunkSegment'. The module will still be created; however, it cannot run 
successfully until the object exists.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServerTempDB'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'master'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServerTempDB'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'msdb'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'master'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke 
CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = 
"SSRS"),'methodName' = SetDatabaseConnection,'namespaceName' = 
root/Microsoft/SQLServer/ReportServer/RS_SSRS/v14/Admin'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' 
complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Enumerate 
CimInstances' with following parameters, ''namespaceName' = 
root\Microsoft\SQLServer\ReportServer\RS_SSRS\v14\Admin,'className' = MSReportServer_ConfigurationSetting'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Enumerate CimInstances'
 complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke 
CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = 
"SSRS"),'methodName' = InitializeReportServer,'namespaceName' = 
root/Microsoft/SQLServer/ReportServer/RS_SSRS/v14/Admin'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' 
complete.
VERBOSE: [SWAZSERVER00]: LCM:  [ End    Set      ]  [[SqlRS]ReportingServicesConfig]  in 74.0560 seconds.
PowerShell DSC resource DSC_SqlRS  failed to execute Set-TargetResource functionality with error message: Method 
InitializeReportServer() failed with an error. Error: The report server cannot open a connection to the report server 
database. A connection to the database is required for all requests and processing. 
(rsReportServerDatabaseUnavailable);Login failed for user 'C0NT0S00\SWAZSERVER00$'. (HRESULT:-2146233088) 
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : SWAZSERVER00.c0nt0s00.local

DSC configuration

SqlRS ReportingServicesConfig
        {
            InstanceName                    = 'SSRS'
            DatabaseServerName              = $env:RS_DB_INSTANCE.Split( "\" )[0]
            DatabaseInstanceName            = if ($env:RS_DB_INSTANCE.Split( "\" )[1] -eq $null) { 'MSSQLSERVER' } else { $env:RS_DB_INSTANCE.Split( "\" )[1] }
            ReportServerVirtualDirectory    = 'ReportServer'
            ReportsVirtualDirectory         = 'Reports'
            ReportServerReservedUrl         = @( 'http://+:80' )
            ReportsReservedUrl              = @( 'http://+:80' )
            # Encrypt                         = "Optional" #Not available in SqlServerDsc -ModuleVersion 15.2.0
            PsDscRunAsCredential            = $CRMInstallAccountCredential
            DependsOn                       = "[xService]RSService"
        }

Suggested solution

I guess it is C0NT0S00\_ssrs account that should be used for accessing the dababase from SSRS context.

SQL Server edition and version

Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64)   Oct  8 2022 05:58:25   Copyright (C) 2022 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2022 Datacenter 10.0 <X64> (Build 20348: ) (Hypervisor)

SQL Server PowerShell modules

Name      Version    Path
----      -------    ----
SqlServer 21.1.18256 C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18256\SqlServer.psd1

Operating system

OsName               : Microsoft Windows Server 2022 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
OsArchitecture       : 64-bit
WindowsVersion       : 2009
WindowsBuildLabEx    : 20348.1.amd64fre.fe_release.210507-1500
OsLanguage           : en-US
OsMuiLanguages       : {en-US}
PSComputerName       : SWAZSERVER00
RunspaceId           : 099d1021-69e0-46d0-a33a-0ef8444d2d77

PowerShell version

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

SqlServerDsc version

Name         Version Path
----         ------- ----
SqlServerDsc 15.2.0  C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\15.2.0\SqlServerDsc.psd1
shurick81 commented 1 year ago

When I use SqlServerDsc 16.1.0, the output is a bit different but failure anyway:

VERBOSE: [SWAZSERVER00]: LCM:  [ Start  Resource ]  [[SqlRS]ReportingServicesConfig]
VERBOSE: [SWAZSERVER00]: LCM:  [ Start  Test     ]  [[SqlRS]ReportingServicesConfig]
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Get the current reporting services
 configuration for the instance 'SSRS'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Enumerate
CimInstances' with following parameters, ''namespaceName' =
root\Microsoft\SQLServer\ReportServer\RS_SSRS\v14\Admin,'className' = MSReportServer_ConfigurationSetting'.                                                                                                                                  VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Enumerate CimInstances' complete.                                                                                                            VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Reporting services SWAZDB00\SQLInstance01 are not initialized.                                                                                          VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Report server virtual directory on SWAZDB00\SQLInstance01 is , should be ReportServer.                                                                  VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Reports virtual directory on SWAZDB00\SQLInstance01 is , should be Reports.                                                                             VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Report server reserved URLs on SWAZDB00\SQLInstance01 are missing, should be http://+:80.                                                               VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Reports reserved URLs on SWAZDB00\SQLInstance01 are missing, should be http://+:80.                                                                     VERBOSE: [SWAZSERVER00]: LCM:  [ End    Test     ]  [[SqlRS]ReportingServicesConfig]  in 42.4400 seconds.                                                                                                                                    VERBOSE: [SWAZSERVER00]: LCM:  [ Start  Set      ]  [[SqlRS]ReportingServicesConfig]                                                                                                                                                         VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Enumerate CimInstances' with following parameters, ''namespaceName' =                                                                root\Microsoft\SQLServer\ReportServer\RS_SSRS\v14\Admin,'className' = MSReportServer_ConfigurationSetting'.                                                                                                                                  VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Enumerate CimInstances' complete.                                                                                                            VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Enumerate CimInstances' with following parameters, ''namespaceName' = root/cimv2,'className' = Win32_OperatingSystem'.               VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Enumerate CimInstances' complete.                                                                                                            VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Initializing Reporting Services on SWAZDB00\SQLInstance01.                                                                                              VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Setting report server virtual directory on SWAZDB00\SQLInstance01 to 'ReportServer'.                                                                    VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = "SSRS"),'methodName'  = SetVirtualDirectory,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_SSRS/v14/Admin'.                                                                                                                                            VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' complete.                                                                                                                  VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Adding report server URL reservation on SWAZDB00\SQLInstance01: http://+:80.                                                                            VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = "SSRS"),'methodName'  = ReserveUrl,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_SSRS/v14/Admin'.                                                                                                                                                     VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' complete.                                                                                                                  VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Setting reports virtual directory on SWAZDB00\SQLInstance01 to 'ReportServer'.                                                                          VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = "SSRS"),'methodName'  = SetVirtualDirectory,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_SSRS/v14/Admin'.                                                                                                                                            VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' complete.                                                                                                                  VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Adding reports URL reservation on SWAZDB00\SQLInstance01: http://+:80.                                                                                  VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = "SSRS"),'methodName'  = ReserveUrl,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_SSRS/v14/Admin'.                                                                                                                                                     VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' complete.                                                                                                                  VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Generate database creation script on SWAZDB00\SQLInstance01 for database 'ReportServer'.                                                                VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = "SSRS"),'methodName'  = GenerateDatabaseCreationScript,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_SSRS/v14/Admin'.                                                                                                                                 VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' complete.                                                                                                                  VERBOSE: [SWAZSERVER00]: LCM:  [ End    Set      ]  [[SqlRS]ReportingServicesConfig]  in 206.7040 seconds.                                                                                                                                   PowerShell DSC resource DSC_SqlRS  failed to execute Set-TargetResource functionality with error message: Method GenerateDatabaseCreationScript() failed with an error. Error:  (HRESULT:-2147023181)                                            + CategoryInfo          : InvalidOperation: (:) [], CimException                                                                                                                                                                             + FullyQualifiedErrorId : ProviderOperationExecutionFailure                                                                                                                                                                                  + PSComputerName        : SWAZSERVER00.c0nt0s00.local                                                                                                                                                                                                                                                                                                                                                                                                                                 VERBOSE: [SWAZSERVER00]: LCM:  [ End    Set      ]                                                                                                                                                                                           The SendConfigurationApply function did not succeed.                                                                                                                                                                                             + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException                                                                                                                                          + FullyQualifiedErrorId : MI RESULT 1                                                                                                                                                                                                        + PSComputerName        : SWAZSERVER00.c0nt0s00.local
image
shurick81 commented 1 year ago

When I explicitly give the computer account access,

                SqlLogin "RS_Machine_$instance"
                {
                    ServerName              = $instance.Split( "\" )[0]
                    InstanceName            = $instance.Split( "\" )[1]
                    Name                    = "C0NT0S00\SWAZSERVER00`$"
                    LoginType               = 'WindowsUser'
                    PsDscRunAsCredential    = $CRMInstallAccountCredential
                }

                SqlRole "Sysadmins_$instance"
                {
                    ServerName              = $instance.Split( "\" )[0]
                    InstanceName            = $instance.Split( "\" )[1]
                    ServerRoleName          = "sysadmin"
                    MembersToInclude        = "C0NT0S00\SWAZSERVER00`$"
                    PsDscRunAsCredential    = $CRMInstallAccountCredential
                    DependsOn               = "[SqlLogin]RS_Machine_$instance"
                }

I get another issue with SqlServerDsc 15.2.0:

VERBOSE: [SWAZSERVER00]: LCM:  [ Start  Resource ]  [[SqlRS]ReportingServicesConfig]
VERBOSE: [SWAZSERVER00]: LCM:  [ Start  Test     ]  [[SqlRS]ReportingServicesConfig]
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Get the current reporting services
 configuration for the instance 'SSRS'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Enumerate 
CimInstances' with following parameters, ''namespaceName' = 
root\Microsoft\SQLServer\ReportServer\RS_SSRS\v14\Admin,'className' = MSReportServer_ConfigurationSetting'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Enumerate CimInstances'
 complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Reporting services 
SWAZDB00\SQLInstance01 are not initialized.
VERBOSE: [SWAZSERVER00]: LCM:  [ End    Test     ]  [[SqlRS]ReportingServicesConfig]  in 0.5780 seconds.
VERBOSE: [SWAZSERVER00]: LCM:  [ Start  Set      ]  [[SqlRS]ReportingServicesConfig]
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Enumerate 
CimInstances' with following parameters, ''namespaceName' = 
root\Microsoft\SQLServer\ReportServer\RS_SSRS\v14\Admin,'className' = MSReportServer_ConfigurationSetting'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Enumerate CimInstances'
 complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Enumerate 
CimInstances' with following parameters, ''namespaceName' = root/cimv2,'className' = Win32_OperatingSystem'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Enumerate CimInstances'
 complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Initializing Reporting Services on
 SWAZDB00\SQLInstance01.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Setting report server virtual 
directory on SWAZDB00\SQLInstance01 to ReportServer.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke 
CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = 
"SSRS"),'methodName' = SetVirtualDirectory,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_SSRS/v14/Admin'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' 
complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Adding report server URL 
reservation on SWAZDB00\SQLInstance01: http://+:80.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke 
CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = 
"SSRS"),'methodName' = ReserveUrl,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_SSRS/v14/Admin'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' 
complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Setting reports virtual directory 
on SWAZDB00\SQLInstance01 to ReportServer.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke 
CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = 
"SSRS"),'methodName' = SetVirtualDirectory,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_SSRS/v14/Admin'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' 
complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Adding reports URL reservation on 
SWAZDB00\SQLInstance01: http://+:80.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke 
CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = 
"SSRS"),'methodName' = ReserveUrl,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_SSRS/v14/Admin'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' 
complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke 
CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = 
"SSRS"),'methodName' = GenerateDatabaseCreationScript,'namespaceName' = 
root/Microsoft/SQLServer/ReportServer/RS_SSRS/v14/Admin'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' 
complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Enumerate 
CimInstances' with following parameters, ''namespaceName' = root\cimv2,'className' = Win32_Service'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Enumerate CimInstances'
 complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke 
CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = 
"SSRS"),'methodName' = GenerateDatabaseRightsScript,'namespaceName' = 
root/Microsoft/SQLServer/ReportServer/RS_SSRS/v14/Admin'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' 
complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Found PowerShell module SqlServer 
already imported in the session. (SQLCOMMON0026)
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'msdb'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'master'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServerTempDB'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServerTempDB'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'master'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'msdb'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServerTempDB'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServerTempDB'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServerTempDB'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServerTempDB'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Caution: Changing any part of an 
object name could break scripts and stored procedures.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServerTempDB'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Caution: Changing any part of an 
object name could break scripts and stored procedures.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] The module 'CreateRdlChunk' 
depends on the missing object 'CreateSegmentedChunk'. The module will still be created; however, it cannot run 
successfully until the object exists.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] The module 'CreateRdlChunk' 
depends on the missing object 'CreateChunkSegment'. The module will still be created; however, it cannot run 
successfully until the object exists.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServerTempDB'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'master'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServerTempDB'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'msdb'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'master'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 
'ReportServer'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke 
CimMethod' with following parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = 
"SSRS"),'methodName' = SetDatabaseConnection,'namespaceName' = 
root/Microsoft/SQLServer/ReportServer/RS_SSRS/v14/Admin'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' 
complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Enumerate 
CimInstances' with following parameters, ''namespaceName' = 
root\Microsoft\SQLServer\ReportServer\RS_SSRS\v14\Admin,'className' = MSReportServer_ConfigurationSetting'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Enumerate CimInstances'
 complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Restarting Reporting Services.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Getting information about service 
'SQLServerReportingServices'. (SQLCOMMON0037)
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] 'SQL Server Reporting Services' 
service is restarting. (SQLCOMMON0038)
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] 'SQL Server Reporting Services' 
service is stopping. (SQLCOMMON0039)
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Waiting 30 seconds before starting
 service 'SQL Server Reporting Services'. (SQLCOMMON0041)
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] 'SQL Server Reporting Services' 
service is starting. (SQLCOMMON0040)
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Get the current reporting services
 configuration for the instance 'SSRS'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Enumerate 
CimInstances' with following parameters, ''namespaceName' = 
root\Microsoft\SQLServer\ReportServer\RS_SSRS\v14\Admin,'className' = MSReportServer_ConfigurationSetting'.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Operation 'Enumerate CimInstances'
 complete.
VERBOSE: [SWAZSERVER00]:                            [[SqlRS]ReportingServicesConfig] Reporting services 
SWAZDB00\SQLInstance01 are not initialized.
VERBOSE: [SWAZSERVER00]: LCM:  [ End    Set      ]  [[SqlRS]ReportingServicesConfig]  in 142.1850 seconds.
PowerShell DSC resource DSC_SqlRS  failed to execute Set-TargetResource functionality with error message: 
System.Exception: Test-TargetResource function returned false when Set-TargetResource function verified the desired 
state. This indicates that the Set-TargetResource did not correctly set set the desired state, or that the function 
Test-TargetResource does not correctly evaluate the desired state. 
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : SWAZSERVER00.c0nt0s00.local
shurick81 commented 1 year ago

Here's the difference highlighted between the logs when machine account did not have admin permissions and the logs when the machine account had admin permissions: https://gist.github.com/shurick81/d304870c88e3c7b25e9b90fc68d05de9/revisions

johlju commented 1 year ago

I could see several things I would try. Looking at the code this is called setting the database connection.

https://github.com/dsccommunity/SqlServerDsc/blob/5efa82dac1e5811b795512f1961a4c9dedd2e9d2/source/DSCResources/DSC_SqlRS/DSC_SqlRS.psm1#L495-L523

This will sometimes initialize the report services when it is restarted

https://github.com/dsccommunity/SqlServerDsc/blob/5efa82dac1e5811b795512f1961a4c9dedd2e9d2/source/DSCResources/DSC_SqlRS/DSC_SqlRS.psm1#L538-L542

But if not, in that case the CIM method InitializeReportServer() is called to initialize reporting services.

https://github.com/dsccommunity/SqlServerDsc/blob/5efa82dac1e5811b795512f1961a4c9dedd2e9d2/source/DSCResources/DSC_SqlRS/DSC_SqlRS.psm1#L544-L567

I wonder, since the reporting services is restarted and the variable $reportingServicesData is re-set, maybe we need to call the CIM method SetDatabaseConnection() again using the CredentialsType set to 2.

When the CredentialsType parameter is set to 2 (Windows Service), the report server uses integrated security to connect to the report server database and the UserName and Password parameters are ignored. The Reporting Server Web service will use either the ASP.NET account or an application pool's account and the Windows service account to access the report server database. https://learn.microsoft.com/en-us/sql/reporting-services/wmi-provider-library-reference/configurationsetting-method-setdatabaseconnection?view=sql-server-ver16

But, in your case you want to use the the credential pass intio the resource - in that case the CredentialsType must be set to 0, and we need to set username and password to the credentials passed, none of that is supported in the resource currently.

When the CredentialsType parameter is set to 0 (Windows), the UserName and Password parameters must be set. The UserName parameter must be in the form "domain\username", and the value must represent a valid Windows logon. https://learn.microsoft.com/en-us/sql/reporting-services/wmi-provider-library-reference/configurationsetting-method-setdatabaseconnection?view=sql-server-ver16

I would test to copy the following block before the CIM method InitializeReportServer() is called: https://github.com/dsccommunity/SqlServerDsc/blob/5efa82dac1e5811b795512f1961a4c9dedd2e9d2/source/DSCResources/DSC_SqlRS/DSC_SqlRS.psm1#L495-L523

If that doesn't work I would check that it uses the correct credentials from the apppool och windows service. If that checks out and it still doesn't work I would experiment with different credential types of the CIM method SetDatabaseConnection() call.

shurick81 commented 1 year ago

I think it is related to the way how I change the RS service account name from a default virtual account to a real domain account. When I change it manually, and then use the SqlRs DSC resource for provisioning, everything seems fine. However, if I don't change the account manually but using something like this:

            xService RSService
            {
                Name        = "SQLServerReportingServices"
                Credential  = $SqlRSAccountCredential
                DependsOn   = "[UserRightsAssignment]LogonAsAService"
            }

Then it fails to provision reporting services, even regardless whether I use SqlRs or try to provision using a wizard. So I cannot see how this might be related to the SqlServerDsc module. Let's close it?

Anyway, I will put a few more findings here in case someone bumps into the same issue. The issue is not occurring for me when the RS service account is a full admin on the RS machine. I noticed these errors in event logs:

Application: RSManagement.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception.
Exception Info: System.Net.HttpListenerException
   at System.Net.HttpListener.AddAllPrefixes()
   at System.Net.HttpListener.Start()
   at Microsoft.Owin.Host.HttpListener.OwinHttpListener.Start(System.Net.HttpListener, System.Func`2<System.Collections.Generic.IDictionary`2<System.String,System.Object>,System.Threading.Tasks.Task>, System.Collections.Generic.IList`1<System.Collections.Generic.IDictionary`2<System.String,System.Object>>, System.Collections.Generic.IDictionary`2<System.String,System.Object>, System.Func`2<System.String,System.Func`6<System.Diagnostics.TraceEventType,Int32,System.Object,System.Exception,System.Func`3<System.Object,System.Exception,System.String>,Boolean>>)
   at Microsoft.Owin.Host.HttpListener.OwinServerFactory.Create(System.Func`2<System.Collections.Generic.IDictionary`2<System.String,System.Object>,System.Threading.Tasks.Task>, System.Collections.Generic.IDictionary`2<System.String,System.Object>)

Exception Info: System.Reflection.TargetInvocationException
   at System.RuntimeMethodHandle.InvokeMethod(System.Object, System.Object[], System.Signature, Boolean)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(System.Object, System.Object[], System.Object[])
   at System.Reflection.RuntimeMethodInfo.Invoke(System.Object, System.Reflection.BindingFlags, System.Reflection.Binder, System.Object[], System.Globalization.CultureInfo)
   at Microsoft.Owin.Hosting.ServerFactory.ServerFactoryAdapter.Create(Owin.IAppBuilder)
   at Microsoft.Owin.Hosting.Engine.HostingEngine.Start(Microsoft.Owin.Hosting.Engine.StartContext)
   at Microsoft.BIServer.Management.WebHost.Program.Main(System.String[])

I guess the issue might be related to netsh http urlacl: when the service account is a full admin, it does not even require explicit acl for the URL to establish the listening.

johlju commented 1 year ago

I think you cannot use xService, but instead we need to have a resource that supports this CIM method (but just guessing here): https://learn.microsoft.com/en-us/sql/reporting-services/wmi-provider-library-reference/configurationsetting-method-setwindowsserviceidentity?view=sql-server-ver16

Then it fails to provision reporting services, even regardless whether I use SqlRs or try to provision using a wizard. So I cannot see how this might be related to the SqlServerDsc module. Let's close it?

If you feel this is outside of what we can resolve in this module (no bug or enhancement) the I suggest you close the issue. 🙂

shurick81 commented 1 year ago

probably but xService seems working for RS 2016, and not for RS 2017

johlju commented 1 year ago

It is clearly state in the documentation that one should not change the account directly on the Service. Mentioned in Best practices for changing the service account for the report server in SQL Server Reporting Services and Changing the Service Account.

The resource SqlServiceAccount would probably not even work as I think it should call the method SetWindowsServiceIdentity() if the service is Reporting Services.

https://learn.microsoft.com/en-us/sql/reporting-services/wmi-provider-library-reference/configurationsetting-method-setwindowsserviceidentity?view=sql-server-ver16

johlju commented 1 year ago

Closing this issue as it seems the root cause is replacing service account in a wrong way.