SqlSetup : $SecurityMode parameter of 'Windows' causes FAILURE of SQL instance installation/setup

[SphenicPaul]

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

When applying/setting a MOF file generated from a PowerShell DSC configuration that uses the "SqlSetup" ("MSFT_SqlSetup") DSC Resource...

... although the SqlServerDsc documentation seems to state that the SecurityMode can be set to Windows or SQL (i.e. SQL is effectively "Mixed Mode")...

....when I have provided a SecurityMode value of Windows, this fails to install SQL Server 2016 Enterpise Core (although I'd possibly expect to similar behaviour with other editions/versions).

MOF File Feedback

The feedback from the applying/setting of the MOF file shows the following configuration switch/parameter value was used on the (presumably) command line call...

/SECURITYMODE="Windows"

It also highlighted a feedback/non-terminating error message and suggested viewing a Summary.txt file in the Setup Bootstrap\Log directory within the SQL Server shared installation folders (in this case within the 130 subdirectory).

"Summary.txt" log file contents

The error message in the SQL setup Summary.txt file clearly shows an exception has been throw. This reads as...

The only supported value for Authentication Mode is "SQL".

Supported command line switches

The following page...

Install SQL Server from the Command Prompt

...also shows that SQL is the only option allowed for this switch/parameter so providing a value of Windows would appear to be incorrect, and, I presume, this behaviour is undesired (i.e. a bug).

Other notes/points

I also tried this with a $null value (i.e. I set SQLSecurityMode to $null) and this also seemed to try to use Windows in the installation command line switch/parameter (perhaps as a default value).

I'd still like to be able to provide/set a value of Windows or $null to this property/parameter and still have the installation work (and setup only Windows authentication on the SQL instance). I would suspect that passing the (presumably, default) Windows value to the \SECURITYMODE switch/parameter needs to be reemoved.

I'm unsure if a command line \SECURITYMODE switch/parameter value of "Windows" was allowed in earlier SQL Server versions but has since been removed?

Also, I suppose you can now install certain SQL Server versions on Linux also.

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

   $SQLSecurityMode = 'Windows'

   ...<snip>...

    SQLSetup 'DefaultSQLInstance'
    {   
               ...<snip>...

		SecurityMode = $SQLSecurityMode    

               ...<snip>...
    }

   ...<snip>...

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

Windows 2016 Core

SQL Server edition and version the target node is running

SQL Server 2016 Core

What SQL Server PowerShell modules, and which version, are present on the target node.

N/A

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

SqlServerDsc - 11.2.0.0

[SphenicPaul]

Shout if you've any questions.

[johlju]

Thank you for the detailed summary! I see the problem here, and agree that this property should probably not have the option to be set to Windows. I say probably because I haven’t seen the code, if there are anything saying this should have Windows option.
If the property is left out of the configuration it will default to Windows authentication only.

Labeling it as a bug, and help wanted so that anyone in the community can run with it.

[SphenicPaul]

Again, relating to #1186 , it would be nice to assign this variable/parameter with a $null value and have it install in Windows-only security mode. In that way, The Windows option may/could still need to be left as a parameter option (to avoid breaking changes and provide some compatibility with existing configurations that use it).

Having said that, I can't see how specifying Windows would have previously worked?!

I suspect it may just a case of removing use of the /SECURITYMODE switch in the call to the command line installation if it isn't "SQL" but am unsure if earlier SQL Version installations allowed "Windows" as an option.

[mdaniou]

@johlju This parameter should probably be a boolean like EnableSecurityModeSQL = $True

[johlju]

@mdaniou I could potentially be another authentication method in the future that is enabled using this property, so unless setup.exe argument is a boolean, I think we keep it a string as well? Otherwise we need to do a breaking change now, and another breaking change if that day comes.

[mdaniou]

@johlju ok so I guess that we only need to fix the behavior when Windows is chosen

[johlju]

LoginMode is returned by Get-DscConfiguration, that is probably the reason the validate set have the value 'Windows'.

https://github.com/PowerShell/SqlServerDsc/blob/77a40f5417e24d6ed7aeede17ec6b8921d0faa3d/DSCResources/MSFT_SqlSetup/MSFT_SqlSetup.psm1#L201-L208

When using Windows and SQL authentication, the LoginMode property is set to 'Mixed' , and set to 'Integrated' when using Windows only authentication, but returned by the resource as either 'SQL' or 'Windows' (to be similar to what is shown in the SSMS GUI).

So, I think we should either

1. Remove 'Windows' from the validate set, and update the parameter description to mention that this property should be left out to use Windows only authentication. Get-DscConfiguration should return $null when SecurityMode not equals 'Mixed' (since we don't have a value in the validate set to return).
2. Change so that when SecurityMode is set to 'Windows' in the configuration, the SecurityMode argument is removed from the setup.exe argument list. Update the parameter description to mention that this property should be be either left out, or set to 'Windows', to use Windows only authentication.

Although I like alternative 2, that could potentially become a problem if the setup.exe argument SecurityMode some day adds a 'Windows' value with a different purpose than what this resource uses it for.
To align with setup.exe, maybe alternative 1 is the best?
Any other alternatives?

[WobblyDave]

I am writing the DSC code for my unattended install & hit the same problem. SQL Setup complains

Exception type: Microsoft.SqlServer.Chainer.Infrastructure.InputSettingValidationException
Message:
The only supported value for Authentication Mode is "SQL".
HResult : 0x851a0011
FacilityCode : 1306 (51a)
ErrorCode : 17 (0011)

I cannot leave the value empty & Windows, whilst a valid DSC entry does not carry properly

" /SQLTEMPDBLOGDIR="E:\MSSQL15.SQLMGMT\MSSQL\Data" /SQLUSERDBDIR="S:\MSSQL15.SQLMGMT\MSSQL\Data" /IACCEPTSQLSERVERLICENSETERMS="True" /SECURITYMODE="Windows" /INSTALLSHAREDWOWDIR="C:
\Program Files (x86)\Microsoft SQL Server" /QUIET="True" /INSTANCENAME="SQLMGMT" /INSTANCEDIR="C:\Program Files\Microsoft SQL Server" /SQLSYSADMINACCOUNTS="xxxx" /SQLTEMPDBDIR="E:\MSSQL15.SQLMGMT\MSSQL\Data" /SQLCOLLATION="Latin1_General_CI_AS" /FEATURES=SQLENGINE /INSTALLSQLDATADIR=S:\ /INSTALLSHAREDDIR="C:\Progra
m Files\Microsoft SQL Server" /UPDATEENABLED="False" /BROWSERSVCSTARTUPTYPE="Automatic"

The only way forward I can see is to reloop the data section after the install has concluded to set the value for the node as required, which is counter-intuitive.

[WobblyDave]

My workaround after a few days of consideration: I am using a post install registry hack using a registry DSC resource

    Registry REG_LoginMode{
        DependsOn           = '[SqlSetup]InstallSQL'
        Key                 = 'HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer'
        ValueName           = 'LoginMode'
        ValueType           = 'REG_DWORD'
        ValueData           =  $Node.LoginMode
        PsDscRunAsCredential = $SQLInstallCredential
    }

LoginMode for the node in question is either set to 1 (Windows Only) or 2 (Mixed Authentication)

[martenrune]

New to DSC and SQL Install, but otherwise senior, this is a charming workaround. Thanx for sharing
:-)

[martenrune]

Here I am commeting on the same post.
like WobblyDave (who contributed a nice regEdit solution), I did not understand.
When looking att successful install from cmd prompt where windows authentication only is the end result, I finaly figured out that OMITTING this setting = Windows Authentication
And then you can have a simple day 2 change if SQL account should be used.
Regards Marten

[johlju]

We should remove this:

SqlServerDsc/source/DSCResources/DSC_SqlSetup/DSC_SqlSetup.psm1

Lines 2691 to 2694 in 47e02f2

	else
	{
	$securityMode = 'Windows'
	}

And then switch the ValidateSet to only contain SQL.

And update unit test accordingly.