SqlServerLogin: When LoginMode is 'Integrated' there is no error thrown

[johlju]

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

When adding an SqlLogin, and the $serverObject.LoginMode is 'Integrated' there is no error thrown.

I don't think Integrated should be in this regex expression. 🤔

https://github.com/PowerShell/SqlServerDsc/blob/024ce324018e847b134cfffd6355a035e1086e2b/DSCResources/MSFT_SqlServerLogin/MSFT_SqlServerLogin.psm1#L233-L236
But rather it should be 'Mixed|Normal' according to this list.

https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.serverloginmode?view=sqlserver-2016

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

        SqlServerLogin 'AddSqlLogin'
        {
            Ensure                         = 'Present'
            Name                           = $DatabaseCredential.UserName
            LoginType                      = 'SqlLogin'
            ServerName                     = '.'
            InstanceName                   = 'SQLEXPRESS'
            LoginCredential                = $DatabaseCredential
            LoginMustChangePassword        = $false
            LoginPasswordExpirationEnabled = $false
            LoginPasswordPolicyEnforced    = $true
            #PsDscRunAsCredential           = $SqlAdministratorCredential

            DependsOn = '[xScript]InstallSqlExpress'
        }

Version of the operating system and PowerShell the target node is running

Caption                             OSArchitecture Version    MUILanguages $PSVersionTable.PSVersion
-------                             -------------- -------    ------------ -------------------------
Microsoft Windows Server Datacenter 64-bit         10.0.16299 {en-US}      5.1.16299.492

SQL Server edition and version the target node is running

Edition  : Express Edition
Version  : 14.0.1000.169
Language : 1033

What SQL Server PowerShell modules, and which version, are 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

Version of the DSC module you're using, or write 'dev' if you're using current dev branch

Dev

[mjcarrabine]

I was going to open a new issue, but I think my issue is the same as this one, so I second this request.

I ran into this today, and spent several hours trying to identify the root cause because I thought I was dealing with "Password failed validation for login" instead of more quickly realizing that the server itself was simply in Windows Authentication (Integrated) mode.

Now that I identified my issue, hopefully the details below might save someone else some time.

Issue

I have 2 SQL Servers both running SQL Server 2016 using version 11.2.0.0 of the SqlServerDsc module. I am using this code on both servers:

SqlServerLogin Add_Login_ssrs{
	Ensure =                         "Present"
	Name =                           "ssrslogin"
	LoginType =                      "SqlLogin"
	ServerName =                     "localhost"
	InstanceName =                   "MSSQLSERVER"
	LoginCredential =                [pscredential]::new("ssrslogin", (ConvertTo-SecureString -force -AsPlainText "Password1@"))
	LoginMustChangePassword =        $false
	LoginPasswordExpirationEnabled = $false
	LoginPasswordPolicyEnforced =    $true
}

On server 1, the code above adds the SqlLogin and results in Compliant.

On server 2, the same code adds the SqlLogin, but results in:

• Compliant only the first time after deployment
• Not compliant 15 minutes later on the second and all subsequent checks

Running Get-DscConfigurationStatus | Get-XDscConfigurationDetail provided this

2019-01-10T15:16:41.957-5:00 verbose [DEV-04]: LCM:  [ Start  Resource ]  [[SqlServerLogin]Add_Login_ssrs]
2019-01-10T15:16:41.957-5:00 verbose [DEV-04]: LCM:  [ Start  Test     ]  [[SqlServerLogin]Add_Login_ssrs]
2019-01-10T15:16:41.982-5:00 verbose [DEV-04]:                            [[SqlServerLogin]Add_Login_ssrs] Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
2019-01-10T15:16:42.057-5:00 verbose [DEV-04]:                            [[SqlServerLogin]Add_Login_ssrs] Importing PowerShell module SQLPS.
2019-01-10T15:16:42.077-5:00 verbose [DEV-04]:                            [[SqlServerLogin]Add_Login_ssrs] Connected to SQL instance 'localhost'.
2019-01-10T15:16:42.077-5:00 verbose [DEV-04]:                            [[SqlServerLogin]Add_Login_ssrs] Getting SQL logins
2019-01-10T15:16:42.077-5:00 verbose [DEV-04]:                            [[SqlServerLogin]Add_Login_ssrs] 2019-01-10_15-16-42: Getting the login 'ssrslogin' from 'localhost\MSSQLSERVER'
2019-01-10T15:16:42.077-5:00 verbose [DEV-04]:                            [[SqlServerLogin]Add_Login_ssrs] 2019-01-10_15-16-42: The login 'ssrslogin' is Present from the 'localhost\MSSQLSERVER' instance.
2019-01-10T15:16:42.127-5:00 verbose [DEV-04]:                            [[SqlServerLogin]Add_Login_ssrs] Information: PowerShell module SqlServer not found, trying to use older SQLPS module.
2019-01-10T15:16:42.192-5:00 verbose [DEV-04]:                            [[SqlServerLogin]Add_Login_ssrs] Importing PowerShell module SQLPS.
2019-01-10T15:16:42.227-5:00 verbose [DEV-04]:                            [[SqlServerLogin]Add_Login_ssrs] 2019-01-10_15-16-42: Password validation failed for the login 'ssrslogin'.
2019-01-10T15:16:42.227-5:00 verbose [DEV-04]: LCM:  [ End    Test     ]  [[SqlServerLogin]Add_Login_ssrs] False in 0.2700 seconds.
2019-01-10T15:16:42.227-5:00 verbose [DEV-04]: LCM:  [ End    Resource ]  [[SqlServerLogin]Add_Login_ssrs]

Cause

Since I was unable to login to server 2 using ssrslogin even when it showed Compliant, I assumed it had something to do with #1048. However, that bug is fixed in version 11.2.0.0 of SqlServerDsc (Many thanks to @claudiospizzi for resolving it.)

In the end, the issue was:

• Server 1 is configured with LoginType = Mixed
• Server 2 is configured with LoginType = Integrated

[johlju]

@mjcarrabine indeed sounds like the same issue.

I labeled this as a good first issue since this this should be an easy change for a new contributor. Looks like it "just" needs adding a regression test and then changing the regex.

[johlju]

I will run with this - I need something to test issue #1260 on.