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

We are creating logins for an AlwaysOn Failover Cluster using Sql Logins. The logins created on each node have different SIDs which means they don't function after a failover.

Verbose logs showing the problem

On Node 1:

select SUSER_SID('test')

0xF899F3553DC7DE45960EA46444243042

On Node 2:

select SUSER_SID('test')

0x43FA0951BF257D4EA24956B4D89A6930

Suggested solution to the issue

Two ideas:

1. Automatically allocate an Sid and ensure the same allocated Sid is used by all nodes when creating the Sql Login (Not sure if this is even possible?)
2. allow the configuration to supply an Sid Property, letting the developer pre-allocate an Sid and pass it into the resource (I'm assuming this would be simpler).

https://blog.sqlauthority.com/2015/04/18/sql-server-create-login-with-sid-way-to-synchronize-logins-on-secondary-server/ has a more detailed description of the phenomenon, but the solution is not Dsc oriented and requires dropping and creating the login with a pre-allocated Sid.

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

            SqlServerLogin "TestLogin"
            {
                Ensure                         = 'Present'
                Name                           = 'test'
                LoginCredential                = $testUserCredential
                LoginType                      = 'SqlLogin'
                ServerName                     = $Node.NodeName
                InstanceName                   = $Node.SQLInstanceName
                LoginMustChangePassword        = $false
                LoginPasswordExpirationEnabled = $false
                PsDscRunAsCredential           = $RunAsCreds
                DependsOn                      = $BaseDepends
            }

SQL Server edition and version the target node is running

Microsoft SQL Server 2017 (RTM-CU18) (KB4527377) - 14.0.3257.3 (X64) Nov 16 2019 01:14:50 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (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 2016 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
OsArchitecture       : 64-bit
WindowsBuildLabEx    : 14393.3383.amd64fre.rs1_release.191125-1816
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

Version and build of PowerShell the target node is running

Name                           Value
----                           -----
PSVersion                      5.1.14393.3383
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.14393.3383
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 13.0.0.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\13.0.0.0\SqlServerDsc.psd1