SqlSetup: Missing Configuration Feature/Options : Network Configurations

[mdaniou]

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

This issue is created to bring some improvements to the SqlSetup DSC Configuration. The idea is to report all the missing options in the resource to be allow the installation of all the features and to use all the options available in the SQL Server installer.

Options to add to the DSC Resource :

/NPENABLED : Specifies the state of the Named Pipes protocol for the SQL Server service. Supported values:
/TCPENABLED : Specifies the state of the TCP protocol for the SQL Server service. Supported values:

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

SqlSetup

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

Windows Server 2012/2012R2/2016

SQL Server edition and version the target node is running

SQL Server 2012/2014/2016/2017

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

Most recent version

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

Most recent version

[JoeAlanis]

I solved the ability to Enable Named Pipes for the Server Protocol using the code snip below. I don't know if there is a proper way to load the assembly, but it seems to work in this scenario. I hope this can benefit someone who looking to include this functionality via DSC.

Script 'EnableNamedPipes'
{
GetScript = {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
$wmi = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer')$env:COMPUTERNAME
$np = $wmi.ServerInstances.ServerProtocols | ?{$.displayName -eq "Named Pipes"}
$result = $np.IsEnabled
return @{ 'Result' = "$result" }
}
TestScript = {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
$wmi = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer')$env:COMPUTERNAME
$np = $wmi.ServerInstances.ServerProtocols | ?{$.displayName -eq "Named Pipes"}
$result = $np.IsEnabled
return $result;
}
SetScript = {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
$wmi = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer')$env:COMPUTERNAME
$wmi.ServerInstances.ServerProtocols | Select DisplayName, State, IsEnabled
$np = $wmi.ServerInstances.ServerProtocols | ?{$_.displayName -eq "Named Pipes"}
$np.IsEnabled = $True
$np.Alter()
}
DependsOn = '[SqlSetup]InstallDefaultInstance'

    }

[johlju]

Thanks for the snippet, that would simplify for someone to add this, but I think it should be implemented in SqlServerNetwork (issue #14), since SqlSetup will just add the required arguments to SqlSetup. It will be up to SqlServerNetwork to make sure the settings is kept in desired state.

A suggestion is that it should use Import-Module (either SQLPS or SqlServer) to load the correct assemblies into the session instead of using LoadWithPartialName, which will load the first dll it encounters in the GAC regardless of version of SQL Server. If you only have one instance on the target node, then that is probably not a problem. But if you for example is using SqlServer module together with other resource modules, that Script resource would end up loading the wrong assembly making the SqlServer module not working.

[NReilingh]

Would enabling the setting of the "Force Encryption" flag be in scope for this issue?

[johlju]

@NReilingh Not sure what force encryption flag you a referencing, but If SQL Server setup.exe can enable it, then yes it would be the scope for the SqlSetup resource, otherwise no - then it should be part of another resource.

[SQLHorizons]

Hi @johlju, Enforced Encryption and Hide Instance are properties in the DB instances ietwork properties and found via SQL Server Configuration Manager, see sample code:-

##  apply surface area configuration control 2.12
$WmiObject = @{
    ComputerName = $SQLServer.NetName
    Namespace    = "root\Microsoft\SqlServer\ComputerManagement$($SQLServer.VersionMajor)"
    Class        = "ServerSettingsGeneralFlag"
    Filter       = "FlagName = 'HideInstance'"
}
$HideInstance = Get-WmiObject @WmiObject

if ($HideInstance.FlagValue -eq $false) {
    Write-Verbose "Setting HideInstance to True."
    $HideInstance.SetValue($true) | Out-Null
    Write-Verbose "HideInstance set to True"
}

##  apply surface area configuration control 8.2
$WmiObject = @{
    ComputerName = $SQLServer.NetName
    Namespace    = "root\Microsoft\SqlServer\ComputerManagement$($SQLServer.VersionMajor)"
    Class        = "ServerSettingsGeneralFlag"
    Filter       = "FlagName = 'ForceEncryption'"
}
$ForceEncryption = Get-WmiObject @WmiObject

if ($ForceEncryption.FlagValue -eq $false) {
    Write-Verbose "Setting ForceEncryption to True."
    $ForceEncryption.SetValue($true) | Out-Null
    Write-Verbose "ForceEncryption set to True"
}

Note $SQLServer is the SMO object

However this would only be half a solution as some client may wish to apply the certificate with this option.

[johlju]

Thanks for the information @SQLHorizons!

The new resource https://github.com/PowerShell/SqlServerDsc#sqlserversecureconnection introduce ForceEncryption and Thumbprint, although it is using the registry to set this, so that resource could be changed to use the CimInstance instead. The HideInstance is not supported in any resource yet, please submit a new issue suggesting where it could fit, since this is not related to this issue.

[johlju]

I solved the ability to Enable Named Pipes for the Server Protocol using the code snip below. I don't know if there is a proper way to load the assembly, but it seems to work in this scenario. I hope this can benefit someone who looking to include this functionality via DSC.

The new resource SqlServerProtocol will solve this (currently in PR). But does not fix this issue to make SqlSetup support this.

[johlju]

The properties ForceEncryption and HideInstanceare now tracked in issue #1519 and issue #1520.