SqlLogin: Should support creating viable SqlLogins across a cluster

[chrissimon-au]

trafficstars

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 <X64> (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

[johlju]

I think the simplest solution is alternative 2, let the user provide a SID, if provided it is used to create the login. The each node configuration can use the pre-determined SID. A third option would be to see if the node is part of an AlwaysOn and see if the login exist on any other node that is online and use that, but the logic for that is far more harder to make.

It says here:

SQL Server login SID: a 16 byte (binary(16)) literal value based on a GUID. For example, SID = 0x14585E90117152449347750164BA00A7. https://docs.microsoft.com/en-us/sql/t-sql/statements/create-login-transact-sql?view=sql-server-ver15

Does this mean it is possible for a configuration to do this?

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

    LoginSid                       = '0x{0}' -f ((New-Guid).Guid -replace '-').ToUpper()
}

[chrissimon-au]

Yes, that should be possible. I've started a fork and will prepare a PR once tested and ready.

Note that the underlying class Microsoft.SqlServer.Management.Smo.Login has a Sid property that is a byte[] so the DSC resource will have to accept the 0x... style GUID and convert to/from the byte[].

However, I suggest it's probably clearer to accept the 0x... style GUID as an input to the resource, as this is what most users will be used to from interacting with Sid values in SQL Server. Does that sound right to you?

[Rob-S]

Maybe another possibility... use the File resource to write the SID for a specific user to a FIle using the '0x{0}' -f ((New-Guid).Guid -replace '-').ToUpper() code suggested, if the file does not already exist. Then retrieve the SID from that file for each node in the cluster. The property to do that could be named LoginSidFileName.

[chrissimon-au]

I have a fork that has implemented this, and it's working for us locally - you can see it here: https://github.com/dsccommunity/SqlServerDsc/compare/master...lanternpay:fix/allow-allocate-sid - unfortunately I haven't had enough time to create the necessary unit tests to support creating a PR back to master.

I hope to get to that soon but if anyone is interested in picking up from that fork and adding unit tests, feel free!