SqlDatabaseObjectPermission: Unable to correctly set permissions via resourcename SqlDatabaseObjectPermission

[nagten]

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

Unable to correctly set permissions via resourcename SqlDatabaseObjectPermission

Reproduction steps:

1. Create a DSC configuration to set permission on a table
2. Execute DSC configuration (we do this via the win_dsc Ansible module)

Expected result: Successful setting of the permission and proper return value.

Result: We receive following error message: "Failed to invoke DSC Set method: Method invocation failed because [Microsoft.Management.Infrastructure.CimInstance] does not contain a method named 'Where'."

Verbose logs showing the problem

Full verbose error message in Ansible:

fatal: [INFRATSTSERVER_42040dd6-bdca-ff2e-eadc-da9d5b9155c5]: FAILED! => {
"changed": false,
"invocation": {
"module_args": {
"DatabaseName": "msdb",
"DependsOn": null,
"InstanceName": "INSTANCE_TST",
"Name": "DOMAIN\SA-TESTACCOUNT",
"ObjectName": "sysjobs",
"ObjectType": "Table",
"Permission": [
{
"Ensure": null,
"Permission": [
"Select"
],
"State": "Grant"
}
],
"PsDscRunAsCredential_password": null,
"PsDscRunAsCredential_username": null,
"SchemaName": "dbo",
"ServerName": "INFRATSTSERVER",
"module_version": "latest",
"resource_name": "SqlDatabaseObjectPermission"
}
},
"module_version": "14.1.0",
"msg": "Failed to invoke DSC Set method: Method invocation failed because [Microsoft.Management.Infrastructure.CimInstance] does not contain a method named 'Where'.",
"reboot_required": false,
"verbose_set": [
"Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = ResourceSet,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.",
"An LCM method call arrived from computer INFRATSTSERVER with user sid S-1-5-21-1458491821-1856443079-1469108721-500.",
"[INFRATSTSERVER]: LCM: [ Start Set ] [[SqlDatabaseObjectPermission]DirectResourceAccess]",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Getting the current state of the permissions for the database object 'dbo.sysjobs' of type 'Table' in the database 'msdb' for the instance 'INSTANCE_TST' on the server 'INFRATSTSERVER'. (SDOP0001)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Found PowerShell module SQLPS already imported in the session. (SQLCOMMON0026)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Connected to SQL instance 'INFRATSTSERVER\INSTANCE_TST'. (SQLCOMMON0018)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Evaluating the state of the property 'Permission'. (SQLCOMMON0059)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Testing CIM instance 'DSC_DatabaseObjectPermission' with the key properties 'State="Grant"'. (SQLCOMMON0071)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] String value does not match. Current value is 'Absent', but expected the value 'Present'. (SQLCOMMON0063)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] The parameter 'Permission' is not in desired state. (SQLCOMMON0061)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Setting the desired permissions for the database object 'dbo.sysjobs'. (SDOP0009)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Found PowerShell module SQLPS already imported in the session. (SQLCOMMON0026)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Connected to SQL instance 'INFRATSTSERVER\INSTANCE_TST'. (SQLCOMMON0018)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Setting permissions 'Select' for the user 'DOMAIN\SA-TESTACCOUNT' with the state 'Grant' for the database object 'dbo.sysjobs' of type 'Table' in the database 'msdb' (SDOP0007)",
"[INFRATSTSERVER]: LCM: [ End Set ] [[SqlDatabaseObjectPermission]DirectResourceAccess] in 0.2030 seconds.",
"Operation 'Invoke CimMethod' complete.",
"Time taken for configuration job to complete is 0.459 seconds"
],
"verbose_test": [
"Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = ResourceTest,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.",
"An LCM method call arrived from computer INFRATSTSERVER with user sid S-1-5-21-1458491821-1856443079-1469108721-500.",
"[INFRATSTSERVER]: LCM: [ Start Test ] [[SqlDatabaseObjectPermission]DirectResourceAccess]",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Determining the current state of the permissions for the database object 'dbo.sysjobs' of type 'Table' in the database 'msdb' for the instance 'INSTANCE_TST' on the server 'INFRATSTSERVER'. (SDOP0002)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Getting the current state of the permissions for the database object 'dbo.sysjobs' of type 'Table' in the database 'msdb' for the instance 'INSTANCE_TST' on the server 'INFRATSTSERVER'. (SDOP0001)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Found PowerShell module SQLPS already imported in the session. (SQLCOMMON0026)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Connected to SQL instance 'INFRATSTSERVER\INSTANCE_TST'. (SQLCOMMON0018)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Evaluating the state of the property 'Permission'. (SQLCOMMON0059)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Testing CIM instance 'DSC_DatabaseObjectPermission' with the key properties 'State="Grant"'. (SQLCOMMON0071)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] String value does not match. Current value is 'Absent', but expected the value 'Present'. (SQLCOMMON0063)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] The parameter 'Permission' is not in desired state. (SQLCOMMON0061)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] The permissions for the database object 'dbo.sysjobs' is not in desired state. (SDOP0003)",
"[INFRATSTSERVER]: LCM: [ End Test ] [[SqlDatabaseObjectPermission]DirectResourceAccess] False in 0.4530 seconds.",
"[INFRATSTSERVER]: LCM: [ End Set ] in 0.5150 seconds.",
"Operation 'Invoke CimMethod' complete.",
"Time taken for configuration job to complete is 0.701 seconds"
]
}

Suggested solution to the issue

See pull request.

Change
$currentPermissionState = $permissionProperty.Actual.Where({ $_.State -eq $desiredPermissionState.State })
to
$currentPermissionState = $permissionProperty.Actual | Where-Object -FilterScript {$permissionProperty.Actual.State -eq $desiredPermissionState.State}

Because $permissionProperty.Actual has no Where method:

DBG]: [Process:6136]: [RemoteHost]: PS C:\Windows\system32>> $permissionProperty.Actual | Get-Member -MemberType Method

TypeName: Microsoft.Management.Infrastructure.CimInstance#root/microsoft/Windows/DesiredStateConfiguration/DSC_Databa
seObjectPermission

Name MemberType Definition

---

Clone Method System.Object ICloneable.Clone()
Dispose Method void Dispose(), void IDisposable.Dispose()
Equals Method bool Equals(System.Object obj)
GetCimSessionComputerName Method string GetCimSessionComputerName()
GetCimSessionInstanceId Method guid GetCimSessionInstanceId()
GetHashCode Method int GetHashCode()
GetObjectData Method void GetObjectData(System.Runtime.Serialization.SerializationInfo info, System.R...
GetType Method type GetType()
ToString Method string ToString()

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

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlDatabaseObjectPermission 'INFRATSTSERVERTablepermissions'
        {
            ServerName           = 'INFRATSTSERVER'
            InstanceName         = 'INSTANCE_TST'
            DatabaseName         = 'msdb'
            SchemaName           = 'dbo'
            ObjectName           = 'sysjobs'
            ObjectType           = 'Table'
            Name                 = 'DOMAIN\\SA-TESTACCOUNT'
            Permission           = @(
                DSC_DatabaseObjectPermission
                {
                    State      = 'Grant'
                    Permission = @('Select')
                }
            )

            PSDscRunAsCredential = $SqlAdministratorCredential
        }
    }

SQL Server edition and version the target node is running

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor)

SQL Server PowerShell modules present on the target node

Name Version Path

---

SQLPS 15.0 C:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1
sqlserver 21.1.18226 C:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules\sqlserver\sqlserver.psd1

The operating system the target node is running

OsName : Microsoft Windows Server 2019 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
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

Name Value

---

PSVersion 5.1.17763.1007
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.17763.1007
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

Version of the DSC module that was used

Name Version Path

---

SqlServerDsc 14.1.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\14.1.0\SqlServerDsc.psd1

[johlju]

Thanks for reporting this! I have reproduced this by making an integration test that test changing only one permission (adding multiple permissions does not give this error).

It seems there are two issues here. First the method Where() seems to mess up the types, and it starts on the first use of Where(). Secondly it seems Compare-ResourcePropertyState has a bug when the property InDesiredState is $false the wrong type is returned in the property Actual.

When using Compare-ResourcePropertyState that returns the property in desired state ($true) we get an array:

PS > $propertyState  = Compare-ResourcePropertyState -CurrentValues @{ Permission = $cimInstancePermissionCollection } -DesiredValues @{ Permission = $cimInstancePermissionCollection } -CimInstanceKeyProperties @{ Permission = @('State') }
PS > $propertyState

Name                           Value
----                           -----
Expected                       {DSC_DatabaseObjectPermission}
ParameterName                  Permission
InDesiredState                 True
Actual                         {DSC_DatabaseObjectPermission}

PS > $propertyState.Actual.GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     Object[]                                 System.Array

When using Compare-ResourcePropertyState that returns the property not in desired state ($false) we do not get an array:

PS > $propertyState  = Compare-ResourcePropertyState -CurrentValues @{ Permission = $cimInstancePermissionCollectionActual } -DesiredValues @{ Permission = $cimInstancePermissionCollection } -CimInstanceKeyProperties @{ Permission = @('State') }
PS > $propertyState

Name                           Value
----                           -----
Expected                       {DSC_DatabaseObjectPermission}
ParameterName                  Permission
InDesiredState                 False
Actual                         DSC_DatabaseObjectPermission


PS > $propertyState.Actual.GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     CimInstance                              System.Object

Then if we continue with the $propertyState that returned the correct array above we can see that the first Where() method that the code uses changes the type for the property Actual (I modified it slightly to mock the problem). Using Where-Object returns the expected type (an array).

PS > $propertyState  = Compare-ResourcePropertyState -CurrentValues @{ Permission = $cimInstancePermissionCollection } -DesiredValues @{ Permission = $cimInstancePermissionCollection } -CimInstanceKeyProperties @{ Permission = @('State') }
PS > $propertyState

Name                           Value
----                           -----
Expected                       {DSC_DatabaseObjectPermission}
ParameterName                  Permission
InDesiredState                 True
Actual                         {DSC_DatabaseObjectPermission}


PS > $propertyState.Actual.gettype()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     Object[]                                 System.Array


PS > $propertiesNotInDesiredState = $propertyState.Where( { $_.InDesiredState })
PS > $propertiesNotInDesiredState.Actual.GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     CimInstance                              System.Object


PS > $propertiesNotInDesiredState = $propertyState | Where-Object -FilterScript{ $_.InDesiredState }
PS > $propertiesNotInDesiredState.Actual.GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     Object[]                                 System.Array

Any subsequent use of method Where() will again change the type.

PS > $permissionProperty = $propertiesNotInDesiredState.Where( { $_.ParameterName -eq 'Permission' })
PS > $permissionProperty

Name                           Value
----                           -----
Expected                       {DSC_DatabaseObjectPermission}
ParameterName                  Permission
InDesiredState                 True
Actual                         {DSC_DatabaseObjectPermission}


PS > $permissionProperty.Actual.GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     CimInstance                              System.Object

[johlju]

So to solve this we should:

• fix the bug in Compare-ResourcePropertyState in the dependent module DscResource.Common.
• fix all uses of method Where() in the code.