SqlProtocol: KeepAlive parameter/property using incorrect data type

[SphenicPaul]

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

It appears that if you use the SqlProtocol, DSC resource and specify a KeepAlive parameter value that is different to the value already set/present, the resource will throw the following error:

Exception setting "Value": "Type mismatch when trying to assign value to property KeepAlive. Received System.UInt16 instead of System.Int32.".

The SqlProtocol resource currently uses a UInt16 datatype but when the resource tries to update/set the relevant value by calling...

$serverProtocolProperties.ProtocolProperties['KeepAlive'].Value = $KeepAlive

... currently line 304, in the Set-TargetResource method in DSC_SqlProtocol.psm1, it seems to be expecting an Int32 data type instead.

Note that if the KeepAlive value is set to the same value as it was previously, no error/exception will be thrown because the update statement call is skipped.

Verbose logs showing the problem

N/A

Suggested solution to the issue

I believe the DSC resource KeepAlive parameter/property of the SqlProtocol, DSC resource needs to be updated from a UInt16 to an Int32 data type.

The DSC configuration that is used to reproduce the issue (as detailed as possible)

N/A - No config, but example PowerShell around the specific line of code is below.

NOTE: The assignment of $serverProtocolProperties was performed using example script from #1628

$serverProtocolProperties # Assigned by other script in issue #1628

<...snip...>

# 'UInt16' Example
$currentKeepAlive = $serverProtocolProperties.ProtocolProperties['KeepAlive'].Value
Write-Output "Current: $currentKeepAlive"

[UInt16]$KeepAlive = $currentKeepAlive + 1  # Current data type (UInt16)
$serverProtocolProperties.ProtocolProperties['KeepAlive'].Value = $KeepAlive  # !!! EXCEPTION THROWN !!!

$currentKeepAlive = $serverProtocolProperties.ProtocolProperties['KeepAlive'].Value
Write-Output "PostUpdate: $currentKeepAlive"




# 'Int32' Example
$currentKeepAlive = $serverProtocolProperties.ProtocolProperties['KeepAlive'].Value
Write-Output "Current: $currentKeepAlive"

[Int32]$KeepAlive = $currentKeepAlive + 1 # Required data type (Int32)
$serverProtocolProperties.ProtocolProperties['KeepAlive'].Value = $KeepAlive  # Updates without exception

$currentKeepAlive = $serverProtocolProperties.ProtocolProperties['KeepAlive'].Value
Write-Output "PostUpdate: $currentKeepAlive"

SQL Server edition and version the target node is running

Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

SQL Server PowerShell modules present on the target node

SqlServer - 21.1.18229
SQLPS - 14.0

The operating system the target node is running

OsName : Microsoft Windows Server 2012 R2 Standard
OsOperatingSystemSKU : StandardServerEdition
OsArchitecture : 64-bit
WindowsBuildLabEx : 9600.19846.amd64fre.winblue_ltsb_escrow.200923-1735
OsLanguage : en-US
OsMuiLanguages : {en-US}

Version and build of PowerShell the target node is running

PSVersion 5.1.14409.1018
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14409.1018
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

Version of the DSC module that was used

14.2.1

[SphenicPaul]

Shout if you've any questions

[Fiander]

I can confirm that KeepAlive should be a Int32.

`
$ServerName = "SQL2019-00"
$InstanceName = "INST00"

$newObjectParameters = @{
TypeName = 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer'
ArgumentList = @($ServerName)
}

$managedComputerObject = New-Object @newObjectParameters
$serverInstance = $managedComputerObject.ServerInstances[$InstanceName]
$serverInstance.ServerProtocols['TCP'].ProtocolProperties['KeepAlive'].Type.Name
`
gives as response: Int32

[johlju]

Happy to see a PR that fixes this.

[johlju]

@SphenicPaul this was just merged and a new preview release will be published shortly thanks to @Fiander 🙂