SqlRole resource incorrect documentation or bug

[since81]

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

I am trying to add new users to the role, but got:
Failed to invoke DSC Test method: Cannot validate argument on parameter 'ServerName'. The argument is null or empty. Provide an argument that is not null or empty and then try the command again.

Verbose logs showing the problem

Suggested solution to the issue

in documentation ServerName is not mandatory

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

{
    "changed": false,
    "invocation": {
        "module_args": {
            "PsDscRunAsCredential_password": null,
            "ServerRoleName": "sysadmin",
            "module_version": "latest",
            "MembersToExclude": null,
            "DependsOn": null,
            "InstanceName": "TEST",
            "resource_name": "SqlRole",
            "Ensure": "Present",
            "ServerName": null,
            "PsDscRunAsCredential_username": null,
            "Members": null,
            "MembersToInclude": [
                "DOMAIN\\Administrator",
                "DOMAIN\\SQLACL-SysAdmin"
            ]
        }
    }
}

SQL Server edition and version the target node is running

Microsoft SQL Server 2017 (RTM-CU20) (KB4541283) - 14.0.3294.2 (X64) Mar 13 2020 14:53:45 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

SQL Server PowerShell modules present on the target node

Name Version Path

---

SQLPS 14.0 C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

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

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.0.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\14.0.0\SqlServerDsc.psd1
SqlServerDsc 13.0.0.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\13.0.0.0\SqlServerDsc.psd1

[johlju]

ServerName is not mandatory but if used in the configuration it must be set to a valid value. If you don't need to set a value for ServerName then the parameter should be left out of the configuration.

[cmcapellan]

I am having the exact same problem using the 14.1 version, in my case I have not specified ServerName and I am getting the same error.

Here is the relevant part of my code:

        {
            Name                 = 'BUILTIN\Administrators'
            LoginType            = 'WindowsUser'
            InstanceName         = 'MSSQLSERVER'
        }
        SqlRole 'Grant_sysadmin_for_LocalAdmins'
        {
            ServerRoleName       = 'sysadmin'
            MembersToInclude     = 'BUILTIN\Administrators'
            InstanceName         = 'MSSQLSERVER'
            DependsOn            = '[SqlLogin]AllowLoginForLocalAdmins'
        }

And I am getting the result:

"Exception":  {
                      "Message":  "The PowerShell DSC resource \u0027[SqlRole]Grant_sysadmin_for_LocalAdmins\u0027 with SourceInfo \u0027::35::9::SqlRole\u0027 threw one or more non-terminating errors while running the Test-TargetResource functionality. These errors are logged to the ETW channel called Microsoft-Windows-DSC/Operational. Refer to this channel for more details.",
                      "Data":  {

                               },
                      "InnerException":  {
                                             "Message":  "Cannot validate argument on parameter \u0027ServerName\u0027. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again.",
                                             "ParameterName":  "ServerName",
                                             "ParameterType":  "string",
                                             "TypeSpecified":  "string",
                                             "ErrorId":  "ParameterArgumentValidationError",
                                             "Line":  432,
                                             "Offset":  51,
                                             "CommandInvocation":  "System.Management.Automation.InvocationInfo",
                                             "ErrorRecord":  "Cannot validate argument on parameter \u0027ServerName\u0027. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again.",
                                             "WasThrownFromThrowStatement":  false,
                                             "Data":  "System.Collections.ListDictionaryInternal",

[cmcapellan]

One more note, I tried adding "$env:COMPUTERNAME" (with both single and double quotes), but as I am using Azure DSC, it gets populated with "CLIENT" in the compile step. So unforunately not sure if I can find a workaround. Have been using a bunch of other modules, including SqlLogin, SqlDatabaseDefaultLocation, SqlMemory, SqlConfiguraton with no issues so far, just SqlRole having this ServerName issue.

[cmcapellan]

I see the problem, SqlRole uses $PSBoundParameters which doesn't work with default values, I see this was fixed in other places in issue #1503 but it seems like this one was missed.

[johlju]

@cmcapellan Great catch!

[johlju]

I will release a new version tomorrow that will fix this.