xSQLServerServiceAccount: The ReportingServices service could not be found

[rdavisunr]

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

I have a two server setup:

• Server One running "SQLENGINE" and "SSMS" features
• Server Two running only "RS" feature

I am using xSQLSeverSetup to install on both machines (which works fine). I am not setting the RSSvcAccount property on xSQLServerSetup. Rather, I am attempting to use xSQLServerServiceAccount to set the account for the Server running RS.

However, this fails with the following error message:

PowerShell DSC resource MSFT_xSQLServerServiceAccount  failed to execute Test-TargetResource functionality with error
message: System.Exception: The ReportingServices service on SSRS\MSSQLSERVER could not be found.
    + CategoryInfo          : InvalidOperation: (:) [], CimException  

Reporting Services is installed and can be accessed via the Configuration Manager with the Server Name (SSRS) and instance (MSSQLSERVER) listed in the error message. For example:

The error looks like it originates from the Get-ServiceObject function...

When I manually execute this (and substitute the values) it does not find the the ReportServer service. However, if I remove the -and clause from the line that searches through $managedComputer.Services, then it does work and returns the ReportServer service.

# Get the Service object for the specified instance/type
function Get-ServiceObject 
{
...
# Connect to SQL WMI
$managedComputer = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $SQLServer

# Change the regex pattern for a default instance
if ($SQLInstanceName -ieq 'MSSQLServer')
{
    $serviceNamePattern = '^MSSQLServer$'
}
else
{
    $serviceNamePattern = ('\${0}$' -f $SQLInstanceName)
}

# Get the proper enum value
$serviceTypeFilter = ConvertTo-ManagedServiceType -ServiceType $ServiceType

# Get the Service object for the specified instance/type
$serviceObject = $managedComputer.Services | Where-Object -FilterScript {
    ($_.Type -eq $serviceTypeFilter) -and ($_.Name -imatch $serviceNamePattern)
}
...

So, perhaps something up with the regex? Or, is it possible I am not passing the right value to SQLInstanceName? Or, could it be something unexpected with my install?

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

# by default, reporting services can't create databases on other machines, to work
# around that we can change it to use either Network Serivce or a Domain Account
# https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/install-reporting-services

$dummyPass = ConvertTo-SecureString "dummy" -AsPlainText -Force
$rsServiceCreds = New-Object System.Management.Automation.PSCredential ("NT AUTHORITY\NETWORK SERVICE", $dummyPass)

xSQLServerServiceAccount SetRSServiceAcccount
{
    SQLServer = $NodeName
    SQLInstanceName = $SqlData.InstanceName
    ServiceType = "ReportingServices"
    ServiceAccount = $rsServiceCreds
    DependsOn = "[xSqlServerSetup]InstallSql", "[File]InstallSqlServerModule"
}
# NOTE 
# $NodeName evaluates to "SSRS"
# $SqlData.InstanceName evaluates to "MSSQLSERVER"

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:
Windows Server 2012R2, SQL Server 2014 SP2, PowerShell5.1

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

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

[johlju]

Curious why you just don't set the RSSvcAccount to the same account used for SQL Server service? Is there a reason for not setting the RSSvcAccount parameter?

[nabrond]

I found the problem. There's an assumption that a default service will always be called "MSSQLSERVER". However, when RS is installed alone, it will default to "ReportServer". I have some code written up to work around this and better identify the service name for all service types. @johlju can you drop the "help wanted" tag? I will take care of this issue.

[johlju]

@nabrond done! Thank you!

[rdavisunr]

Thank you @johlju and @nabrond!

I will look forward to trying this out in a future release.