SqlPermission: Get-TargetResource expects a mandatory parameter

[wahid99]

Problem description

When doing a get on SQLPermission resource it expects the parameter 'permission' to be passed into it. If the permission parameter isn't passed through then we hit an Exception.

Verbose logs

PowerShell DSC resource DSC_SqlPermission  failed to execute Get-TargetResource functionality with error message: System.InvalidOperationException: Unexpected result when trying to get permissions for 'domain\testuser1'. ---> 
System.Management.Automation.ParameterBindingValidationException: Cannot validate argument on parameter
'Permission'. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again. --->
System.Management.Automation.ValidationMetadataException: The argument is null or empty. Provide an argument that is not
 null or empty, and then try the command again.
   at System.Management.Automation.ValidateNotNullOrEmptyAttribute.Validate(Object arguments, EngineIntrinsics engineInt
rinsics)
   at System.Management.Automation.ParameterBinderBase.BindParameter(CommandParameterInternal parameter, CompiledCommand
Parameter parameterMetadata,
ParameterBindingFlags flags)
   --- End of inner exception stack trace ---
   at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exc
eption)
   at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   --- End of inner exception stack trace ---

DSC configuration

$InvokeParams = @{

Name = 'SqlPermission';
Method = 'get';
Property = @{instancename = 'MSSQLSERVER'; principal = 'domain\testuser1'};
ModuleName = @{ModuleName = 'C:/ProgramData/PuppetLabs/puppet/cache/lib/puppet_x/sqlserverdsc/dsc_resources/SqlServerDsc/SqlServerDsc.psd1';
RequiredVersion = '15.2.0'}}
Invoke-DscResource @InvokeParams

Suggested solution

make permission to be a mandatory parameter in the sqlpermission resource. Doing this returns without any problems.

$InvokeParams = @{
Name = 'SqlPermission';
Method = 'get';
Property = @{instancename = 'MSSQLSERVER'; principal = 'domain\testuser1'; permission = 'AlterAnyAvailabilityGroup', 'ViewServerState'};
ModuleName = @{ModuleName = 'C:/ProgramData/PuppetLabs/puppet/cache/lib/puppet_x/sqlserverdsc/dsc_resources/SqlServerDsc/SqlServerDsc.psd1';
RequiredVersion = '15.2.0'}}
Invoke-DscResource @InvokeParams

ConfigurationName    :
DependsOn            :
ModuleName           : C:/ProgramData/PuppetLabs/puppet/cache/lib/puppet_x/sqlserverdsc/dsc_resources/SqlServerDsc/SqlServerDsc.psd1
ModuleVersion        : 15.2.0
PsDscRunAsCredential :
ResourceId           :
SourceInfo           :
Ensure               : Absent
InstanceName         : MSSQLSERVER
Permission           : {}
Principal            : domain\testuser1
ServerName           : SQL01
PSComputerName       : localhost

SQL Server edition and version

Microsoft SQL Server 2019 (RTM-CU16-GDR) (KB5014353) - 15.0.4236.7 (X64)   May 29 2022 15:55:47   Copyright (C) 2019 Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: )

SQL Server PowerShell modules

Name  Version Path
----  ------- ----
SQLPS 15.0    E:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

Operating system

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}

PowerShell version

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

SqlServerDsc version

15.2.0

[johlju]

Agree that it looks like the Permission should be made mandatory.

[johlju]

Happy to review a PR that fixes this. 🙂

[wahid99]

https://github.com/dsccommunity/SqlServerDsc/pull/1762

Kindly review. Thanks

[johlju]

PR looks okay. But looking at the resource as a whole I think the entire resource should be refactored to remove Ensure parameter. It should instead have the parameters Permission, PermissionToInclude, and PermissionToExclude. The PR #1769 has done such refactor on SqlDatabasePermission. I think we need to do a similar refactor on this resource. I create a new issue to track this. Outside of your PR.