SqlProtocol: Throws exception when used with SQL Server 2022

[johlju]

Problem description

When running the integration tests for SqlProtocol, on the build worker that have installed the SQL Server 2022 of the Database Engine, it fails with an exception. It seems it fails when getting the current state.

To reproduce, enable the Pester tag 'Integration_SQL2022' on line 39 in the integration tests of SqlProtocol and then run the pipeline (or the integration test).

This has probably something to do with the type Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer. Maybe we need to install a newer version of the SqlServer PowerShell module. But it is all a guess since I have not debugged the resource. 🤔

Verbose logs

##[error]    [-] Should compile and apply the MOF without throwing 1.9s (1.9s|8ms)
##[error]     Expected no exception to be thrown, but an exception "PowerShell DSC resource DSC_SqlProtocol  failed to execute Test-TargetResource functionality with error message: System.InvalidOperationException: Failed to obtain a SQL Server instance with name 'DSCSQLTEST' on server 'fv-az30-420'. Ensure the SQL Server instance exists on the server and that the 'SQLServer' module references a version of the 'Microsoft.SqlServer.Management.Smo.Wmi' library that supports the version of the SQL Server instance. (SQLCOMMON0070) " was thrown from D:\a\1\s\tests\Integration\DSC_SqlProtocol.Integration.Tests.ps1:74 char:17
##[error]         + ...               Start-DscConfiguration @startDscConfigurationParameters
##[error]         +                   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~.
##[error]     at } | Should -Not -Throw, D:\a\1\s\tests\Integration\DSC_SqlProtocol.Integration.Tests.ps1:75

DSC configuration

See integration tests configuration.

Suggested solution

Unknown. But seems this need to be handled differently for SQL Server 2022.

SQL Server edition and version

SQL Server 2022 (GA)

SQL Server PowerShell modules

SqlServer 21.1.18256 (as installed by integratation tests)

Operating system

Windows Server 2019 and Windows Server 2022

PowerShell version

Unknown (the version build pipeline uses)

SqlServerDsc version

Code in main branch.

[johlju]

This seems to be a problem with the PowerShell module SqlServer. It works with the PowerShell module SQLPS that comes with the installation of SQL Server 2022, but it does not work with SqlServer from PowerShell Gallery.

SQLPS:

PS > import-module sqlps
PS > $a = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer -ArgumentList localhost
PS > $a

ConnectionSettings : Microsoft.SqlServer.Management.Smo.Wmi.WmiConnectionInfo
Services           : {MSSQL$SQL2022, SQLAgent$SQL2022, SQLBrowser}
ClientProtocols    : {}
ServerInstances    : {SQL2022}
ServerAliases      : {}
Urn                : ManagedComputer[@Name='localhost']
Name               : localhost
Properties         : {}
UserData           :
State              : Existing

SqlServer:

PS > import-module sqlserver
PS > $a = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer -ArgumentList localhost
PS > $a

ConnectionSettings : Microsoft.SqlServer.Management.Smo.Wmi.WmiConnectionInfo
Services           :
ClientProtocols    :
ServerInstances    :
ServerAliases      :
Urn                : ManagedComputer[@Name='localhost']
Name               : localhost
Properties         : {}
UserData           :
State              : Existing

[johlju]

It seems also work with the PowerShell module dbatools.

PS > import-module dbatools
PS > $a = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer -ArgumentList localhost
PS > $a

ConnectionSettings : Microsoft.SqlServer.Management.Smo.Wmi.WmiConnectionInfo
Services           : {MSSQL$SQL2022, SQLAgent$SQL2022, SQLBrowser}
ClientProtocols    : {}
ServerInstances    : {SQL2022}
ServerAliases      : {}
Urn                : ManagedComputer[@Name='localhost']
Name               : localhost
Properties         : {}
UserData           :
State              : Existing

[matekubi]

@johlju were you able to solve this issue? I'm facing the same with SQL2022 and DSC module.

[johlju]

The root cause is that SQL Server team has not updated their module SqlServer with the latest SMO assemblies.

I have tried to leverage module dbatools, but couldn't get the integration tests to pass. I thought about looking into leverage dbatools.library but haven't had time, and won't have time, to work on it.

Please see if you can leverage dbatools to get this working. I started PR #1808 that suggest a change but that fails tests, so something is different. Just kicked of the build again to see what breaks.

[johlju]

This was resolved with PR #1808. To use this resource on SQL Server 2022 the SqlServer module 22.0.49-preview is needed, and also the latest preview of SqlServerDsc (building and deploying as I write this).