issue with sqlserverdsc 16.5.0 sqlpermission parameter problem

[rismoney]

• Name: sqlserverdsc
• Version: 16.5.0

Here is a stack trace of the sqlpermission resource that is not working. Puppet 8.x Windows 2022, SQL 2022.

Debug: dsc_sqlpermission: Collecting data from the DSC Resource
Debug: dsc_sqlpermission: retrieving {:name=>"SQLConfigureServerPermission-sec_sqlserver_dev_adm", :dsc_instancename=>"MSSQLSERVER", :dsc_name=>"myco\\sec_sqlserver_dev_adm"}
Debug: dsc_sqlpermission: invocable_resource: {:parameters=>{:dsc_instancename=>{:value=>"MSSQLSERVER", :mof_type=>"String", :mof_is_embedded=>false}, :dsc_name=>{:value=>"myco\\sec_sqlserver_dev_adm", :mof_type=>"String", :mof_is_embedded=>false}}, :name=>"dsc_sqlpermission", :dscmeta_resource_friendly_name=>"SqlPermission", :dscmeta_resource_name=>"SqlPermission", :dscmeta_resource_implementation=>"Class", :dscmeta_module_name=>"SqlServerDsc", :dscmeta_module_version=>"16.5.0", :dsc_invoke_method=>"get", :vendored_modules_path=>"C:/ProgramData/PuppetLabs/puppet/cache/lib/puppet_x/sqlserverdsc/dsc_resources", :attributes=>nil}
Debug: dsc_sqlpermission: Script:
 function new-pscredential {
    [CmdletBinding()]
    param (
        [parameter(Mandatory = $true,
            ValueFromPipelineByPropertyName = $true)]
        [string]
        $user,

        [parameter(Mandatory = $true,
            ValueFromPipelineByPropertyName = $true)]
        [string]
        $password
    )

    $secpasswd = ConvertTo-SecureString $password -AsPlainText -Force
    $credentials = New-Object System.Management.Automation.PSCredential ($user, $secpasswd)
    return $credentials
}

Function ConvertTo-CanonicalResult {
  [CmdletBinding()]
  param(
      [Parameter(Mandatory, Position = 1)]
      [psobject]
      $Result,

      [Parameter(DontShow)]
      [string]
      $PropertyPath,

      [Parameter(DontShow)]
      [int]
      $RecursionLevel = 0
  )

  $MaxDepth = 5
  $CimInstancePropertyFilter = { $_.Definition -match 'CimInstance' -and $_.Name -ne 'PSDscRunAsCredential' }

  # Get the properties which are/aren't Cim instances
  $ResultObject = @{ }
  $ResultPropertyList = $Result | Get-Member -MemberType Property | Where-Object { $_.Name -ne 'PSComputerName' }
  $CimInstanceProperties = $ResultPropertyList | Where-Object -FilterScript $CimInstancePropertyFilter

  foreach ($Property in $ResultPropertyList) {
      $PropertyName = $Property.Name
      if ($Property -notin $CimInstanceProperties) {
          $Value = $Result.$PropertyName
          if ($PropertyName -eq 'Ensure' -and [string]::IsNullOrEmpty($Result.$PropertyName)) {
              # Just set 'Present' since it was found /shrug
              # If the value IS listed as absent, don't update it unless you want flapping
              $Value = 'Present'
          }
          else {
              if ([string]::IsNullOrEmpty($value)) {
                  # While PowerShell can happily treat empty strings as valid for returning
                  # an undefined enum, Puppet expects undefined values to be nil.
                  $Value = $null
              }

              if ($Value.Count -eq 1 -and $Property.Definition -match '\\[\\]') {
                  $Value = @($Value)
              }
          }
      }
      elseif ($null -eq $Result.$PropertyName) {
          if ($Property -match 'InstanceArray') {
              $Value = @()
          }
          else {
              $Value = $null
          }
      }
      elseif ($Result.$PropertyName.GetType().Name -match 'DateTime') {
          # Handle DateTimes especially since they're an edge case
          $Value = Get-Date $Result.$PropertyName -UFormat "%Y-%m-%dT%H:%M:%S%Z"
      }
      else {
          # Looks like a nested CIM instance, recurse if we're not too deep in already.
          $RecursionLevel++

          if ($PropertyPath -eq [string]::Empty) {
              $PropertyPath = $PropertyName
          }
          else {
              $PropertyPath = "$PropertyPath.$PropertyName"
          }

          if ($RecursionLevel -gt $MaxDepth) {
              # Give up recursing more than this
              return $Result.ToString()
          }

          $Value = foreach ($item in $Result.$PropertyName) {
              ConvertTo-CanonicalResult -Result $item -PropertyPath $PropertyPath -RecursionLevel ($RecursionLevel + 1) -WarningAction Continue
          }

          # The cim instance type is the last component of the type Name
          # We need to return this for ruby to compare the result hashes
          # We do NOT need it for the top-level properties as those are defined in the type
          If ($RecursionLevel -gt 1 -and ![string]::IsNullOrEmpty($Value) ) {
              # If there's multiple instances, you need to add the type to each one, but you
              # need to specify only *one* name, otherwise things end up *very* broken.
              if ($Value.GetType().Name -match '\[\]') {
                  $Value | ForEach-Object -Process {
                      $_.cim_instance_type = $Result.$PropertyName.CimClass.CimClassName[0]
                  }
              } else {
                  $Value.cim_instance_type = $Result.$PropertyName.CimClass.CimClassName
                  # Ensure that, if it should be an array, it is
                  if ($Result.$PropertyName.GetType().Name -match '\[\]') {
                      $Value = @($Value)
                  }
              }
          }
      }

      if ($Property.Definition -match 'InstanceArray') {
          If ($null -eq $Value -or $Value.GetType().Name -notmatch '\[\]') { $Value = @($Value) }
      }

      $ResultObject.$PropertyName = $Value
  }

  # Output the final result
  $ResultObject
}
$script:ErrorActionPreference = 'Stop'
$script:WarningPreference = 'SilentlyContinue'

$response = @{
    indesiredstate = $false
    rebootrequired = $false
    errormessage   = ''
}

$UnmungedPSModulePath = [System.Environment]::GetEnvironmentVariable('PSModulePath','machine')
$MungedPSModulePath = $env:PSModulePath + ';C:\ProgramData\PuppetLabs\puppet\cache\lib\puppet_x\sqlserverdsc\dsc_resources'
[System.Environment]::SetEnvironmentVariable('PSModulePath', $MungedPSModulePath, [System.EnvironmentVariableTarget]::Machine)
$env:PSModulePath = [System.Environment]::GetEnvironmentVariable('PSModulePath','machine')

$InvokeParams = @{Name = 'SqlPermission'; Method = 'get'; Property = @{instancename = 'MSSQLSERVER'; name = 'myco\sec_sqlserver_dev_adm'}; ModuleName = @{ModuleName = 'SqlServerDsc'; RequiredVersion = '16.5.0'}}
Try {
  $Result = Invoke-DscResource @InvokeParams
} catch {
  $Response.errormessage   = $_.Exception.Message
  return ($Response | ConvertTo-Json -Compress)
} Finally {
  If (![string]::IsNullOrEmpty($UnmungedPSModulePath)) {
    # Reset the PSModulePath
    [System.Environment]::SetEnvironmentVariable('PSModulePath', $UnmungedPSModulePath, [System.EnvironmentVariableTarget]::Machine)
    $env:PSModulePath = [System.Environment]::GetEnvironmentVariable('PSModulePath', 'machine')
  }
}

# keep the switch for when Test passes back changed properties
Switch ($invokeParams.Method) {
  'Test' {
    $Response.indesiredstate = $Result.InDesiredState
    return ($Response | ConvertTo-Json -Compress)
  }
  'Set' {
    $Response.indesiredstate = $true
    $Response.rebootrequired = $Result.RebootRequired
    return ($Response | ConvertTo-Json -Compress)
  }
  'Get' {
    $CanonicalizedResult = ConvertTo-CanonicalResult -Result $Result
    return ($CanonicalizedResult | ConvertTo-Json -Compress -Depth 10)
  }
}

Debug: dsc_sqlpermission: raw data received: {"rebootrequired"=>false, "indesiredstate"=>false, "errormessage"=>"PowerShell DSC resource SqlServerDsc  failed to execute Get functionality with error message: At least one of the properties 'Permission', 'PermissionToInclude', or 'PermissionToExclude' must be specified. (SP0009)\r\nParameter name: Permission, PermissionToInclude, PermissionToExclude "}
Error: dsc_sqlpermission: PowerShell DSC resource SqlServerDsc  failed to execute Get functionality with error message: At least one of the properties 'Permission', 'PermissionToInclude', or 'PermissionToExclude' must be specified. (SP0009)
Parameter name: Permission, PermissionToInclude, PermissionToExclude
Error: /Stage[main]/Sql::Logons/Dsc_sqlpermission[SQLConfigureServerPermission-sec_sqlserver_dev_adm]: Could not evaluate: undefined method `[]' for nil:NilClass
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_gems/gems/puppet-resource_api-1.8.14/lib/puppet/resource_api.rb:292:in `block in namevar_match?'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_gems/gems/puppet-resource_api-1.8.14/lib/puppet/resource_api.rb:291:in `all?'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_gems/gems/puppet-resource_api-1.8.14/lib/puppet/resource_api.rb:291:in `namevar_match?'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_gems/gems/puppet-resource_api-1.8.14/lib/puppet/resource_api.rb:254:in `block in refresh_current_state'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_gems/gems/puppet-resource_api-1.8.14/lib/puppet/resource_api.rb:254:in `each'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_gems/gems/puppet-resource_api-1.8.14/lib/puppet/resource_api.rb:254:in `find'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_gems/gems/puppet-resource_api-1.8.14/lib/puppet/resource_api.rb:254:in `refresh_current_state'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_gems/gems/puppet-resource_api-1.8.14/lib/puppet/resource_api.rb:140:in `rsapi_current_state'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_gems/gems/puppet-resource_api-1.8.14/lib/puppet/resource_api.rb:279:in `retrieve'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/type.rb:1120:in `retrieve_resource'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction/resource_harness.rb:307:in `from_resource'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction/resource_harness.rb:20:in `evaluate'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:267:in `apply'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:288:in `eval_resource'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:191:in `call'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:191:in `block (2 levels) in evaluate'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util.rb:556:in `block in thinmark'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/2.7.0/benchmark.rb:308:in `realtime'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util.rb:555:in `thinmark'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:191:in `block in evaluate'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/graph/relationship_graph.rb:122:in `traverse'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:178:in `evaluate'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/resource/catalog.rb:238:in `block (2 levels) in apply'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util.rb:556:in `block in thinmark'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/2.7.0/benchmark.rb:308:in `realtime'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util.rb:555:in `thinmark'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/resource/catalog.rb:237:in `block in apply'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util/log.rb:165:in `with_destination'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction/report.rb:150:in `as_logging_destination'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/resource/catalog.rb:236:in `apply'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/configurer.rb:286:in `block (2 levels) in apply_catalog'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util.rb:556:in `block in thinmark'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/2.7.0/benchmark.rb:308:in `realtime'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util.rb:555:in `thinmark'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/configurer.rb:285:in `block in apply_catalog'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util.rb:234:in `block in benchmark'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/2.7.0/benchmark.rb:308:in `realtime'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util.rb:233:in `benchmark'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/configurer.rb:284:in `apply_catalog'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/configurer.rb:500:in `run_internal'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/configurer.rb:339:in `run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/agent.rb:69:in `block (6 levels) in run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/context.rb:62:in `override'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet.rb:289:in `override'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/agent.rb:68:in `block (5 levels) in run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/2.7.0/timeout.rb:95:in `block in timeout'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/2.7.0/timeout.rb:105:in `timeout'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/agent.rb:67:in `block (4 levels) in run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/agent/locker.rb:21:in `lock'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/agent.rb:61:in `block (3 levels) in run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/agent.rb:148:in `with_client'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/agent.rb:57:in `block (2 levels) in run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/agent.rb:113:in `run_in_fork'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/agent.rb:56:in `block in run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/application.rb:172:in `controlled_run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/agent.rb:47:in `run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/application/agent.rb:433:in `onetime'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/application/agent.rb:393:in `block in run_command'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/context.rb:62:in `override'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet.rb:289:in `override'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/application/agent.rb:390:in `run_command'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/application.rb:421:in `block in run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util.rb:745:in `exit_on_fail'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/application.rb:421:in `run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util/command_line.rb:143:in `run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util/command_line.rb:77:in `execute'
C:/Program Files/Puppet Labs/Puppet/puppet/bin/puppet:4:in `<main>'
Debug: Class[Sql::Logons]: Resource is being skipped, unscheduling all events

[rismoney]

This is a class based resource, not sure if that matters...

[jordanbreen28]

This is a class based resource, not sure if that matters...

Thanks for raising this @rismony. I'll try look at this when I can, Could you supply a sample manifest to replicate this?

[rismoney]

class sql::logons {

  dsc_sqlpermission { 'SQLConfigureServerPermission-sec_sqlserver_dev_adm':
    dsc_instancename => 'MSSQLSERVER',
    dsc_name         => 'myco\\sec_sqlserver_dev_adm',
    dsc_permission   => [
      {
        'permission' => ['ViewServerState', 'ViewAnyDefinition'],
        'state'      => 'Grant',
      }
    ],
  }
}

[rismoney]

this was previously recorded here - https://github.com/dsccommunity/SqlServerDsc/issues/1951

[rismoney]

Anything that can be done to expedite this, let me know. I found a bug in the puppet sql module as well, that is causing a blocker in managing this either through that module or via dsc.

[pmcmaw]

Closing as this is an issue in https://github.com/dsccommunity/SqlServerDsc/issues/1951 and not in the builder.

[rismoney]

I don't think the issue is in the dsc module. I think it has to do with the use of class based resource. I stopped using DSC with puppet because it is wildly inefficient in its' catalogs bloating