SqlServerLogin: fails test for Disabled sql logins everytime

[Matticusau]

Details of the scenario you tried and the problem that is occurring:
I have found that when you set the Disabled property for a login, in my case a SQL Login, the logic of the test case means that it will attempt to authenticate as that login and thus fail.

The problem is with the following code logic:

if ( $LoginType -eq 'SqlLogin' )
{
    if ( $LoginPasswordExpirationEnabled -ne $loginInfo.LoginPasswordExpirationEnabled )
    {
        New-VerboseMessage -Message "The login '$Name' on the instance '$SQLServer\$SQLInstanceName' has PasswordExpirationEnabled set to $($loginInfo.LoginPasswordExpirationEnabled) rather than $LoginPasswordExpirationEnabled"
        $testPassed = $false
    }

    if ( $LoginPasswordPolicyEnforced -ne $loginInfo.LoginPasswordPolicyEnforced )
    {
        New-VerboseMessage -Message "The login '$Name' on the instance '$SQLServer\$SQLInstanceName' has PasswordPolicyEnforced set to $($loginInfo.LoginPasswordPolicyEnforced) rather than $LoginPasswordPolicyEnforced"
        $testPassed = $false
    }

    # If testPassed is still true and a login credential was specified, test the password
    if ( $testPassed -and $LoginCredential )
    {
        $userCred = [System.Management.Automation.PSCredential]::new($Name, $LoginCredential.Password)

        try
        {
            $serverObject = Connect-SQL -SQLServer $SQLServer -SQLInstanceName $SQLInstanceName -SetupCredential $userCred
        }
        catch
        {
            New-VerboseMessage -Message "Password validation failed for the login '$Name'."
            $testPassed = $false
        }
    }
}

Based on this logic, if the login is a SQL Login then it will always attempt to authenticate to validate the password and this will fail. In my situation we wanted to disable the 'sa' account, but i could have been disabling any other SQL login.

The logic needs to have a condition before the attempt to connect that checks if the Disabled flag is set. There is already logic earlier in the test function to check if the disabled flag matches the login configuration.

The following is the extract from the Test-DscConfiguration -Verbose run on the target machine having the issue.

VERBOSE: [MYAZUREVM01]: LCM:  [ Start  Resource ]  [[xSQLServerLogin]DisableSaAccount::[HPWSqlServer]hpwSqlServer]
VERBOSE: [MYAZUREVM01]: LCM:  [ Start  Test     ]  [[xSQLServerLogin]DisableSaAccount::[HPWSqlServer]hpwSqlServer]
VERBOSE: [MYAZUREVM01]:                            [[xSQLServerLogin]DisableSaAccount::[HPWSqlServer]hpwSqlServer]
Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [MYAZUREVM01]:                            [[xSQLServerLogin]DisableSaAccount::[HPWSqlServer]hpwSqlServer] Importing
 PowerShell module SQLPS.
VERBOSE: [MYAZUREVM01]:                            [[xSQLServerLogin]DisableSaAccount::[HPWSqlServer]hpwSqlServer] Connected
 to SQL instance 'localhost'.
VERBOSE: [MYAZUREVM01]:                            [[xSQLServerLogin]DisableSaAccount::[HPWSqlServer]hpwSqlServer] Getting
SQL logins
VERBOSE: [MYAZUREVM01]:                            [[xSQLServerLogin]DisableSaAccount::[HPWSqlServer]hpwSqlServer]
2017-11-24_13-51-06: Getting the login 'sa' from 'localhost\MSSQLSERVER'
VERBOSE: [MYAZUREVM01]:                            [[xSQLServerLogin]DisableSaAccount::[HPWSqlServer]hpwSqlServer]
2017-11-24_13-51-06: The login 'sa' is Present from the 'localhost\MSSQLSERVER' instance.
VERBOSE: [MYAZUREVM01]:                            [[xSQLServerLogin]DisableSaAccount::[HPWSqlServer]hpwSqlServer]
Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
VERBOSE: [MYAZUREVM01]:                            [[xSQLServerLogin]DisableSaAccount::[HPWSqlServer]hpwSqlServer] Importing
 PowerShell module SQLPS.
VERBOSE: [MYAZUREVM01]:                            [[xSQLServerLogin]DisableSaAccount::[HPWSqlServer]hpwSqlServer]
2017-11-24_13-51-11: Password validation failed for the login 'sa'.
VERBOSE: [MYAZUREVM01]: LCM:  [ End    Test     ]  [[xSQLServerLogin]DisableSaAccount::[HPWSqlServer]hpwSqlServer] False in
5.3400 seconds.
VERBOSE: [MYAZUREVM01]: LCM:  [ End    Resource ]  [[xSQLServerLogin]DisableSaAccount::[HPWSqlServer]hpwSqlServer]

The specific line where the problem occurs is 2017-11-24_13-51-11: Password validation failed for the login 'sa'.

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

The following is an extract of the configuration.

xSQLServerLogin DisableSaAccount
{
	SQLServer = 'localhost'
	SQLInstanceName = $SqlInstanceName
	Name = 'sa'
	LoginCredential = $SaAccount
	Ensure = 'Present'
	Disabled = $true
	LoginType = 'SqlLogin'
	LoginPasswordExpirationEnabled = $false
	LoginMustChangePassword = $false
	LoginPasswordPolicyEnforced = $false
	DependsOn = '[xSQLServerSetup]ReInstallSQL'
}

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:
Windows Server 2016
PowerShell 5.1
SQL Server 2016 SP1

What module (SqlServer or SQLPS) and which version of the module the DSC Target Node is running:
xSqlServerLogin (with modified name to xSqlLogin to work around path issues in Azure Automation)

Version of the DSC module you're using, or 'dev' if you're using current dev branch:
xSqlServer 8.2.0.0 (but have checked the source for 9.0.0.0 and same logic)

[johlju]

Thanks for the detailed bug report! Sounds like an easy fix, but a regression test should be written for this, so it doesn’t happen again. Labeling this one as ‘help wanted’ so someone that is up for resolving this can take it on.