dsccommunity / dsccommunity/SqlServerDsc

SqlWindowsFirewall: Rules aren't created for RS feature with 2017 standalone

Open
#1,412 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug help wanted
Dominant language
PowerShell
Stars
385
Forks
224
PR merge metrics
No merged PRs in 30d

Description

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

Tried using the SqlWindowsFirewall resource to create firewall rules for RS feature (2017 version standalone).

Set task fails with message "Firewall rules are not in desired state" and rules are not created.

Verbose logs showing the problem
VERBOSE: [REB4WIN201902]:                            [[SqlWindowsFirewall]Create_firewall_rules] The SQL Server major version is '14'.
VERBOSE: [REB4WIN201902]:                            [[SqlWindowsFirewall]Create_firewall_rules] The firewall rules are not in desired state.
VERBOSE: [REB4WIN201902]: LCM:  [ End    Set      ]  [[SqlWindowsFirewall]Create_firewall_rules]  in 0.4800 seconds.
PowerShell DSC resource MSFT_SqlWindowsFirewall  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        : REB4WIN201902
Suggested solution to the issue

Since RS became a standalone (or specific to 2017 version) the service name has changed to :

Name                : SQLServerReportingServices
DisplayName         : SQL Server Reporting Services
Status              : Running

In the psm1 of the resource, the RS service name checked for are :

$reportServiceName = 'ReportServer'
else
$reportServiceName = 'ReportServer${0}' -f $InstanceName

I think the problem is here, new service name does not match.
Please find below the SqlRSSetup and SqlWindowsFirewall config I used (works with any other Firewall feature : SQLENGINE, AS, IS)

The DSC configuration that is used to reproduce the issue (as detailed as possible)
      SqlRSSetup "Install_reporting_services"
      {
        InstanceName         = "SSRS" # Can only be set to SSRS judging by the github doc
        IAcceptLicenseTerms  = "Yes"
        SourcePath           = "D:\tmp\SQLServerReportingServices_2017.exe"
        Edition              = "Development"
        SuppressRestart      = $true
        ForceRestart         = $false
      }
    [....]
    SqlWindowsFirewall "Create_firewall_rules"
    {
      Ensure           = "Present"
      Features         = "RS"
      InstanceName     = $env:COMPUTERNAME
      SourcePath       = $Node.SourcePath
      SourceCredential = $SourceCredential
    }
SQL Server edition and version the target node is running

2017, RS as a standalone

SQL Server PowerShell modules present on the target node

Only SqlServerDsc version 13

The operating system the target node is running

OsName : Microsoft Windows Server 2019 Standard
OsOperatingSystemSKU : StandardServerEdition
OsArchitecture : 64-bit
WindowsVersion : 1809
WindowsBuildLabEx : 17763.1.amd64fre.rs5_release.180914-1434
OsLanguage : en-US
OsMuiLanguages : {en-US}

Version and build of PowerShell the target node is running

PSVersion 5.1.17763.316
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.17763.316
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)

Version 13

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start in the SqlWindowsFirewall resource's psm1 file and inspect how the RS reporting service name is selected for SQL Server 2017 standalone installations. Reproduce the configuration with the SQLServerReportingServices service, then verify that firewall rules are created and Test-TargetResource succeeds.

Written by the indexing model from the issue text.

Assessment

Tech stack
powershell, sql
Domain
databases, devops
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.