MSFT_SqlServerRole: Incorrect, conflicting, case-(in)sensitive "ServerRoleName"

[SphenicPaul]

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

When two (or multiple?) SqlServerRole DSC resources are used that have the "ServerRoleName" property assigned with the names that differ only by the casing, a conflict occurs when generating the MOF file for a SQL instance using a case-sensitive collation.

It appears that in a case-sensitive, SQL Server collation I can create/use two server roles - For example, SysAdmin and sysadmin.

In the SQL server instance these are recognised as being different/independent although the SqlServerRole DSC resource identifies these as the same (as, presumably, the key generated from them is identical within PowerShell (as case isn't being taken into account, perhaps?).

I suspect the likelihood of wanting to use two identical names that are only different by case is going to be uncommon (in this instance I only found this by accident) but I'm unsure if this has been considered in relation to other such DSC resources when used on case-sensitive collations and whether the key should be unique accross casing when applied to a case-sensitive collation.

I would see the below configuration being valid when applying to a SQL Server instance using a case-sensitive collation.

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

...<snip>...

	SqlServerRole 'SQL_SqlServerRole_sysadmin_Present'
    {
        ServerName     = $NodeName
        InstanceName   = $SQLInstanceName
        ServerRoleName = 'sysadmin'               # Note: The casing (different to below)
        Members        = $SQLSysAdminAccounts
			
        Ensure         = 'Present'

    }

	SqlServerRole 'SQL_SqlServerRole_SysAdmin_Absent'
    {
        ServerName     = $NodeName
        InstanceName   = $SQLInstanceName
        ServerRoleName = 'SysAdmin'               # Note: The casing (different to above)
			
        Ensure         = 'Absent'
	 
 	DependsOn      = "[SqlServerRole]SQL_SqlServerRole_sysadmin_Present"
    }


...<snip>...

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

PowerShell v5

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

v11.2.0.0

[SphenicPaul]

Please give me a shout if you've any questions.

Also seems a little closely related to #60 and #1002

[johlju]

I see the problem here, not sure if we can solve this one... 🤔
@TravisEz13 can you give me some insight on case-sensitivity works with mof-resources? Is it possible to make the a Key property case-sensitive when compiling the mof file?

[SphenicPaul]

I suspect there are also considerations to be made around how making the key case-sensitive would (perhaps) regress existing, working functionality.

For example, configurations/resources may nolonger conflict when they really should (i.e. only casing is different when deploying to a case-INsensitive SQL instance).

In addition, I suspect there could/may also be a requirement of multiple SQL instances (with different collations - some case-senstive, some case-insensitive) managed/setup on the same servers/hosts/nodes.

Any change to existing functionality would have to allow this to work for both SQL instances and their respective, DSC resources within the same DSC configuration (and resultant MOF files).

[johlju]

Not sure it's even possible to use case-sensitivity when compiling the .mof file - If it isn't possible then this is by design, if it is possible then configuring that would probably be per target node, meaning the user is opt-in to use case-sensitive. But most likely case-insensitiv is by design.

[stale]

This issue has been automatically marked as stale because it has not had activity from the community in the last 30 days. It will be closed if no further activity occurs within 10 days. If the issue is labelled with any of the work labels (e.g bug, enhancement, documentation, or tests) then the issue will not auto-close.

[johlju]

I asked this question internally, and no one answer that it is possible to make it case-sensitive. So closing this issue as by design.