SqlServerRole: Conflict in Composite Resources

[petr-stupka]

Hi, i have two Composite resources, each with own members in specific SQL role (securityadmin) and those two composites are used in one parent configuration document.
Unfortunately it is not possible to have two composites with SqlServerRole config. See the error message below.

Composite_A

    SqlServerRole A-securityadmin {
        ServerRoleName   = "securityadmin"
        ServerName       = $nodeName
        InstanceName     = $sqlInstanceName
        Ensure           = "Present"
        MembersToInclude = "$nodeName\LOCAL_A_Group"
    }

Composite_B

    SqlServerRole B-securityadmin {
        ServerRoleName   = "securityadmin"
        ServerName       = $nodeName
        InstanceName     = $sqlInstanceName
        Ensure           = "Present"
        MembersToInclude = "$nodeName\LOCAL_B_Group"
    }

Error:

Test-ConflictingResources : A conflict was detected between resources '[SqlServerRole]A-securityadmin::[Composite_A]SQLConfig (C:\Program Files\WindowsPowerShell\Modules...::SqlServerRole)' and
'[SqlServerRole]B-securityadmin::[Composite_B]SQLConfig (C:\Program
Files\WindowsPowerShell\Modules...::SqlServerRole)' in node 'mycomputer'.
Resources have identical key properties but there are differences in the following non-key properties: 'MembersToInclude'. Values 'mycomputer\LOCAL_A_Group' don't match values
'mycomputer\LOCAL_B_Group'. Please update these property values so that they are identical in both cases.

The only way how it is possible right now is to move the config in parent Configuration document.
This solution is making the configuration chaotic.

    SqlServerRole securityadmin {
        ServerRoleName   = "securityadmin"
        ServerName       = $nodeName
        InstanceName     = $sqlInstanceName
        Ensure           = "Present"
        MembersToInclude = "$nodeName\LOCAL_A_Group","$nodeName\LOCAL_B_Group"
    }

Possible Solution:
Please change the key properties so we can separate those configuration in composite resources

System / Module Info

• SQLServerDsc\10.0.0.0
• Powershell 5.1 / Server 2012 R2

[johlju]

Just to make sure we validate this, I updated one of the examples for SqlServerRole to have to server roles in the same configuration, and that works.

See configuration here:
https://github.com/PowerShell/SqlServerDsc/pull/1004/files#diff-4701906418b84e136aaa8c09a1aa9bb3

And compiled successfully here:
https://ci.appveyor.com/project/PowerShell/sqlserverdsc/build/9.0.151.0#L1410

[johlju]

The error message you are receiving feels like you trying to update the same server role, for example 'securityadmin', in both composite resources.

The two resource configurations you submitted above should work together without a problem, since they update two different server roles they should not conflict, just as the example in my previous comment.
If you in fact only have those two SqlServerRole resource configurations in your composites then I maybe you found a problem using composite resources. I can't see that this is a problem with the resource SqlServerRole.

[johlju]

By design (of Desired State Configuration) you will not be able to use the same key properties for the same resource in the same (compiled) configuration. If that was possible it would become a ping-pong behavior on each run.

[petr-stupka]

Hi! Thanks for the feedback. Sorry for the typo mistake. Both composites are updating securityadmin role. I fixed it in the text.
The issue i see is that i can not have more composites which update the same role. So if i have multiple application configs in composites, i can not use SqlServerRole in each of it for specific users, but need to include it in parent document and specify all the different users in one section. I would say SqlServerRole is not Composite friendly.

[petr-stupka]

Just thinking about the SqlServerRole design. Current state make sense if you specify fix memebers otherwise as you said it will do ping pong in case of multiple different configurations. But in my case i’m using includeMembers and in this case i should be able to use the resource multiple times.

[johlju]

It is not possible to use a property as key if that property takes a array of strings. So we can’t make IncludeMembers a key. Making the IncludeMembers just take a single user is not an option I see either.

I don’t think it is possible to do what you are asking for. I think you have to re-evaluate how you use the resource.

[petr-stupka]

Thanks Johan, understand. Would suggest to keep this issue in mind when creating such nice DSCResources like this one so we can be flexible as much as we can. Many thanks again!

[johlju]

Yes I'm sure we will. And thanks for submitting this issue, it was a good discussion! Sadly we are limited to what DSC/LCM is capable of today. Partial configurations and composite resource are powerful but at the end it's limited of the final compiled configuration which has it's limitations. But I bet that will improve in the future..

Closing this as I don't see us being able to change the design of SqlServerRole to handle this scenario.

[randomnote1]

I'm going to suggest a potential solution that I really hate...

The MembersToInclude property could be made a string of comma-separated values. This is the way the features are passed into SqlSetup. I really don't like this for several reasons:

• A comma is a legal character for SQL logins
• It's not very Powershell-like
• The CSV must be parsed into an array whenever the resource processes

[johlju]

I don't like that suggestion either. 😄 I rather then that we make a new resource beside the current one, that "pivots the parameters" and uses the concept as the resource SqlDatabaseRole. That is, single user parameter as key and a required parameter for which roles (one or more) to add or remove the user from. Ensure parameter is set to 'Present' och 'Absent' depending if the user should be added or removed from the group. This resource would not create new server roles.

[ClassVersion("1.0.0.0"), FriendlyName("SqlServerRoleMember")]
class MSFT_SqlServerRoleMember : OMI_BaseResource
{
    [Key, Description("The name of the login that will become a member, or removed as a member, of the server role(s).")] String Name;
    [Key, Description("The name of the SQL instance to be configured.")] String InstanceName;
    [Required, Description("One or more server roles to which the login (user) will be added or removed.")] String ServerRoleName[];
    [Write, Description("The host name of the SQL Server to be configured. Defaults to $env:COMPUTERNAME.")] String ServerName;
    [Write, Description("If set to 'Present' then the login (user) will be added to the server role(s). If 'Absent' then the login (user) will be removed from the server role(s). Default value is 'Present'."), ValueMap{"Present","Absent"}, Values{"Present","Absent"}] String Ensure;
};

One problem that can occur if the same configuration has both SqlServerRole and SqlServerRoleMember and both sets memebers. Then we would get a ping pong behavior in some scenarios.

Update: Updated the order of the parameters, so they begin with keys, and follows by required and write.

[randomnote1]

Thinking out loud here....

If the SqlServerRoleMember resource is created, then SqlServerRole could become a composite resource that consumes SqlServerRoleMember for defining role members. I have zero experience with composite resources at this point, so I don't know if that's a good idea or not.

[johlju]

I'm not seeing any of the two resource being a composite resource, but both should be normal mof resources. Both resource has different behavior, but both will end upp adding members to a server role.
First resource 'SqlServerRole' can create a server role, and add members to it, where the Members property is the main thing, removing all other members to add only those logins in the configuration.
Second resource 'SqlServerRoleMember' cannot create server roles, but can add a login to one or more server roles.

Simple mockup:

# Adds server role 'MyRole'.
SqlServerRole
{
    Ensure         = 'Present'
    ServerRoleName = 'MyRole'

    ServerName     = 'sqltest.company.local'
    InstanceName   = 'DSC'
}

# Adds login to server roles 'MyRole' and 'dbcreator'.
SqlServerRoleMember
{
    Ensure = 'Present'
    Name   = 'MyLogin'
    ServerRoleName = @(
        'MyRole'
        'dbcreator'
    )

    ServerName     = 'sqltest.company.local'
    InstanceName   = 'DSC'
}

@schwaizi can use either or both in their own composite resources, where the new resource would solve the problem they having with their composite resources.

A composite resource is basically a bunch of resources in a configuration, and that configuration is made to be seen as a resource which can be used as a resource in a second configuration. See http://duffney.io/UsingDscCompositeResources which is a good source for this.

I'm a making sense? Or is it a totalyl bad idea? 😄

[randomnote1]

You're making sense, however I think we will have duplicated code. The use of helper functions will help to reduce that though.

[johlju]

Yes, helper functions would be needed to not having duplicated code.

[johlju]

@schwaizi What are your thoughts?

[petr-stupka]

Hi @johlju. Thanks to you for taking time with this issue. I think your proposal SqlServerRoleMember will work pretty well!

I would suggest to add ServerName as Key property as well to minimize conflict in case of two role configuration with the same instance name (just thinking possible scenarios). More unique is better.

I remember in one of my custom made mof resources i created dummy key to avoid this conflict, but it was dirty and quick solution. I believe your option is much cleaner and also minimize the risk of ping-pong config or conflict between configs.

But still there is possibility if someone is using same account in two composites will conflict as well.
For Example:

• two composites for applications in dev stage on one sever - conflict - both composites in one configuration with same key values
• in production separated into two servers - no conflict - each composite in separate configuration.

But using unique groups like i have may be option to avoid it.

So a pretty good solution you proposed!

[johlju]

Can't think of a reason why ServerName should be key. 🤔 The unique for that resource is Name, you will never be able to set the same Name twice. For example it will not be possible to use the same Name in two composite resources (which will conflict in the final compiled configuration).
InstanceName is a key so that the same Name can be set in two instances on the same server, but the ServerName will still be the same for both instances, which means it does not need to be key.

Is there a scenarion you see where ServerName can differ in the below configuration? I'm submitted an issue a while back discussing this, see issue #319.

# Add login 'MyLogin' to server role 'securityadmin' in instance DSC1.
SqlServerRoleMember
{
    Name           = 'MyLogin'
    ServerName     = 'sqltest.company.local'
    InstanceName   = 'DSC1'
    ServerRoleName = @(
        'securityadmin'
    )
}

# Add login 'MyLogin' to server role 'securityadmin' in instance DSC2.
SqlServerRoleMember
{
    Name           = 'MyLogin'
    ServerName     = 'sqltest.company.local'
    InstanceName   = 'DSC2'
    ServerRoleName = @(
        'securityadmin'
    )
}

[johlju]

But still there is possibility if someone is using same account in two composites will conflict as well.
For Example:

• two composites for applications in dev stage on one sever - conflict - both composites in one
  configuration with same key values
• in production separated into two servers - no conflict - each composite in separate configuration.

But using unique groups like i have may be option to avoid it.

I'm not following you here. Could you explain in more detail what you mean?

[johlju]

Labeling this as a resource proposal and help wanted so that someone can take a look at it and send in a PR.

[petr-stupka]

Hi @johlju, i know scenarios where in DEV Stage are all and multiple Application placed on one server and the same instance. Those application are then split in production servers on separate nodes.

So you can have scenario with one login on one server in multiple Composites/Resources:

# Add login 'MyLogin' to server role 'securityadmin' in instance DSC1 for Application A.
SqlServerRoleMember
{
    Name           = 'MyLogin'
    ServerName     = 'sqltest.company.local'
    InstanceName   = 'DSC1'
    ServerRoleName = @(
        'securityadmin'
    )
}

# Add login 'MyLogin' to server role 'securityadmin' in instance DSC1 for Application B.
SqlServerRoleMember
{
    Name           = 'MyLogin'
    ServerName     = 'sqltest.company.local'
    InstanceName   = 'DSC1'
    ServerRoleName = @(
        'securityadmin'
    )
}

And in Production Stage it will look like this:

# Add login 'MyLogin' to server role 'securityadmin' in instance DSC1 for Application A.
SqlServerRoleMember
{
    Name           = 'MyLogin'
    ServerName     = 'sqlprod1.company.local'
    InstanceName   = 'DSC1'
    ServerRoleName = @(
        'securityadmin'
    )
}

# Add login 'MyLogin' to server role 'securityadmin' in instance DSC1 for Application B.
SqlServerRoleMember
{
    Name           = 'MyLogin'
    ServerName     = 'sqlprod2.company.local'
    InstanceName   = 'DSC1'
    ServerRoleName = @(
        'securityadmin'
    )
}

As i said, using Groups can be a solution for the DEV Stage

# Add login 'MyLogin' to server role 'securityadmin' in instance DSC1 for Application A.
SqlServerRoleMember
{
    Name           = 'LOCAL_A_Group'
    ServerName     = 'sqltest.company.local'
    InstanceName   = 'DSC1'
    ServerRoleName = @(
        'securityadmin'
    )
}

# Add login 'MyLogin' to server role 'securityadmin' in instance DSC1 for Application B.
SqlServerRoleMember
{
    Name           = 'LOCAL_B_Group'
    ServerName     = 'sqltest.company.local'
    InstanceName   = 'DSC1'
    ServerRoleName = @(
        'securityadmin'
    )
}

And ServerName as key because of you can have multiple Resources with the same group on different servers like in Production Stage example above.

[johlju]

Do you mean that in your scenario the application server configures both the SQL Server target nodes?

I would target the the SQL Server nodes by them self. I think you should split these up so the final configuration does only contain the configuration for that specific target node. So I would have changed the above configuration so it target each servers individually, like this.

Final configuration for SQLPROD1

# Add login 'MyLogin' to server role 'securityadmin' in instance DSC1 for Application A.
SqlServerRoleMember
{
    Name           = 'MyLogin'
    ServerName     = 'sqlprod1.company.local'
    InstanceName   = 'DSC1'
    ServerRoleName = @(
        'securityadmin'
    )
}

Final configuration for SQLPROD2

# Add login 'MyLogin' to server role 'securityadmin' in instance DSC1 for Application B.
SqlServerRoleMember
{
    Name           = 'MyLogin'
    ServerName     = 'sqlprod2.company.local'
    InstanceName   = 'DSC1'
    ServerRoleName = @(
        'securityadmin'
    )
}

[petr-stupka]

Hi, sorry for late response.
I'm using one configuration for all the nodes, but using separate configuration data for each environment.
The prod environment will work fine with the proposal, but it will not work in Dev stage where is only one target node hosting both applications and both application using the same login 'MyLogin' as i can not add it twice (reason why i opened this issue).
But i'm fine with the proposal as i can separate the logins by using groups 'LOCAL_A_Group' and 'LOCAL_B_Group' or using different login for each application. Then i assume it will work.

[johlju]

I think I'm following you know. Using the proposed new resource SqlServerRoleMember, the Name (which is the login name) and InstanceName is a key, which means that if both applications in the Dev environment are using different instances you can add the SqlServerRoleMember resource twice with the difference of instance name. If both applications are using the same instance, then I think I would have added and if-statement in the configuration so it does not run the second resource if the configuration data is the same for both resource.

[petr-stupka]

Hi again. I agree it will do the job as you described.

Production > using different instances i can add the SqlServerRoleMember resource twice
Dev > The if-statement can do the job in case of both applications are using the same instance

You doing a pretty good job here! Many thanks!
The last question. Do you know already if and when can be this proposal implemented?