SqlLogin: Issues with sql-native accounts.

[jhoneill]

I'm build SQL server 2012 boxes for the a test team and the test set up is configured to use a SQL native logon with an insecure password.
We had some problems where the password was "spontaneously" changing which we traced to the xSQL DSC resource. I distilled the (complex, hundreds of settings) DSC script down to this

Configuration wibble    {
    param (
        [Parameter(Mandatory=$true)]
        [System.Management.Automation.PSCredential]$DomainCredential   
    )
    Import-DscResource -ModuleName xSQLServer  
            
    Node $AllNodes.NodeName  {
        $NodeName               =   'SQL-VM'                                    ;
        $SQLInstanceName        =   'MSSQLServer'     

        $SQLLogin   = "AppAdmin" 
        $SQLPwd     = $SQLLogin | ConvertTo-SecureString -AsPlainText -Force        
        $SQLCred    = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $SQLLogin, $SQLPWd 
        $name       = $SQLLogin -replace "\W",""
        xSQLServerLogin               $name {
                Ensure                         = 'Present'
                LoginType                      = 'SqlLogin'
                Name                           = $SQLLogin
                LoginCredential                = $SQLCred 
                LoginMustChangePassword        = $false
                LoginPasswordExpirationEnabled = $false
                LoginPasswordPolicyEnforced    = $false  
                SQLServer                      = $NodeName
                SQLInstanceName                = $SQLInstanceName
                PsDscRunAsCredential           = $DomainCredential
        }
    }
}
    $configurationData =   @{  AllNodes =   @( 
        @{NodeName                    =    "*"     
          PSDscAllowPlainTextPassword =    $true   
          PSDscAllowDomainUser        =    $true   
         }
         @{NodeName = "LocalHost"}
         )
         }

If I run wibble with config data and a valid credential the first time it creates the account exactly as required. I can logon in SQL Management studio , and connect from SQL native client using the creds.

If I run it as second time, a message is displayed saying the "Password Validation failed for the login ...", set runs and thereafter the logon is broken.

Now... Set-SQLServerLoginPassword calls $login.changePassword() with a secure string. On MSDN the documentation here https://msdn.microsoft.com/en-us/library/mt697759.aspx it says that the method takes a secure string. But on my sql 2012 box the method reports that it takes a string, which makes me think that password is being changed to a secure-string representation of the password.

Further in the test-target resource if the loginType is 'SQLLogin' it will check if the password has changed. THIS IS BAD because the login should be created but shouldn't be forced back to its initial password if the password is changed later. It is worse because the check is by calling Connect-SQL which does not set the $sql.connectionContext.secureLogin and so ONLY works with Windows logins, not SQL ones . So the test will always report the SQL native login has the wrong password, meaning the SET part will always be called, and if a password is included SET will always invoke Set-SQLServerLoginPassword.

For now I have removed the reset password behaviour and the test for a changed password from my own version. That may not be best for the public version.

[johlju]

Thanks for a detailed issue description! I labeled this as a bug even if some are an enhancement.

In regards to the behavior to it changing password back to the initial password. Maybe we should add a Force parameter and if set to $true then the password is changed. If set to $false then password is not changed.

[claudiospizzi]

I think this is related to issue #1048. I guess it was never possible, at least with SQL Server 2016, to validate the SQL Login password correctly.

[jhoneill]

It looks like Claudio has fixed the logon test, I think the setting the password wrongly part needs a check for SQL versions (pass a secure string in 2016 and a string in older versions). I'd add a "Let the password change" switch so it doesn't change existing behaviour (having "force" assumed to be true isn't nice).

[johlju]

@jhoneill Yes, I think part of this issue was resolved by issue #1048 and the merged PR #1049.