SqlConfiguration: Performing DSC tests against certain configuration names/items does NOT work consistently

[SphenicPaul]

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

The SqlServerConfiguration (MSFT_SqlServerConfiguration) DSCResource is being used within a number of configuration(s) (function(s)) I've setup to set SQL Server configuration options.

I'd expect, in normal usage/functionality, that any configuration item listed here...

Server Configuration Options (SQL Server)

... should be able to be set and re-tested using DSC via this method.

As part of the application/actioning of the generated MOF files, "Set"-ing the configuration works OK with the few options that have been tried so far, but "Test"-ing the configuration again straight after the "Set" is failing with an error of...

Specified option '<OptionName>' could not be found.

...where <OptionName> is the particular option that can't be found.

At present, the two examples I've found that allow the DSC "Set" but fail on a DSC "Test" are...

• remote login timeout
• remote query timeout

---

...Meanwhile, within SQL Server...

It seems that within SQL Server, you can actually set and retrieve each of the following configuration options/values (using sp_configure) ...Note the extra values with the (s)...

• remote login timeout
• remote login timeout (s)
• remote query timeout
• remote query timeout (s)

...and running sp_configure in SQL Server actually lists the configuration option name with the additional "(s)" (not without it), even though both input parameters are different.

Also note that the use of "sp_configure" also seems to use a T-SQL "LIKE" behind the scenes as the following query/command that is using a partial string also works (seemingly as long as a unique record/option is returned)...

---

...back to SQLServerDsc...

As a result, the defect/inconsistency I'm seeing is that "SqlServerConfiguration" ("MSFT_SqlServerConfiguration") DSCResource...

• ALLOWS the non-"(s)" version to "Set" the configuration option without error (I'm not actually sure it's setting it correctly at present, but it doesn't error or raise an exception)
• DOESN'T allow the "Test" of the DSC resource to run if the non-"(s)" version is used. It seems that it wants the EXACT configuration option name.

I have been able to work around this by using the configuration option names with an (s) to get the desired behaviour but suspect this inconsistency will apply to a number of these configuration options and people/developers are going to keep hitting this problem if they don't use the exact names. This may also be exacerbated by the fact that the Server Configuration Options (SQL Server) page configuration option names don't have the "(s)".

The DSC configuration that is using the resource (as detailed as possible)

...<snip>...

    SqlServerConfiguration 'SQLConfig_RemoteLoginTimeout'
    {
        Servername = $NodeName
        InstanceName = $SQLInstanceName
        OptionName = 'remote login timeout'
        OptionValue = $SQLConfigRemoteLoginTimeout
        RestartService = $false       
		
        DependsOn = "[SQLSetup]DefaultSQLInstance"
    }

...<snip>...

Version of the operating system and PowerShell the target node is running

Windows Server 2016 Core

SQL Server edition and version the target node is running

SQL Server 2016 Enterprise Core

What SQL Server PowerShell modules, and which version, are present on the target node.

N/A

Version of the DSC module you're using, or write 'dev' if you're using current dev branch

SqlServerDsc version '11.2.0.0'

[SphenicPaul]

Shout if you need any more information.

[johlju]

Thanks for the detailed info. Labeling this as a bug, and help wanted so anyone in the community can run with it.