Integration Test Flaw: OutputType Attribute Causes Parse Errors When SMO Types Not Available

[coderabbitai]

Problem Description

The [OutputType([Microsoft.SqlServer.Management.Smo.Server])] attribute on Connect-Sql causes parse errors in scenarios where SMO types are not available when the SqlServerDsc module is imported.

We are installing the SqlServer module as part of the configuration prior to installing the instances. But at that point Pester has already imported the SqlServerDsc module which might mean SqlServerDsc will not be re-imported after SqlServer module have been installed (so SqlServerDsc imports SqlServer so it loads SMO assemblies). A real scenario would be to install SqlServer module before invoking Pester, like a normal user would do. That would probably make OutputType work with the Server type.

But there is also the scenario where a user can install SqlServerDsc, make use of its public commands to download server media and install an instance. After that install SqlServer module. Then use the Connect-public command to connect to that newly installed instance. Without having import call inside Connect-Sql that would fail because in this scenario SMO would not be available, and the OutputType would fail in this scenario.

Root Cause

PowerShell parses and validates the [OutputType()] attribute when a function is invoked/executed. This means the SMO type must exist in the current session when the command is used, or the function definition will fail.

Affected Scenarios

Scenario 1: Integration Tests (Current Issue)

Current flow:

1. Pester runs and imports SqlServerDsc module
2. suffix.ps1 attempts to run Import-SqlDscPreferredModule (may succeed or fail depending on environment)
3. Prerequisites.Integration.Tests.ps1 installs SqlServer module (version 21.1.18256 or 22.2.0)
4. SqlServerDsc is NOT re-imported after SqlServer module installation
5. When integration tests call Connect-Sql, the OutputType validation fails because SMO types weren't loaded when the module was first imported

Error observed:

RuntimeException: Unable to find type [Microsoft.SqlServer.Management.Smo.Server].

Scenario 2: User Workflow

User flow:

1. User installs SqlServerDsc module
2. User uses public commands (e.g., Save-SqlDscSqlServerMediaFile) to download SQL Server media
3. User installs SQL Server instance
4. User installs SqlServer PowerShell module
5. User attempts to use Connect-Sql or other commands
6. Without Import-SqlDscPreferredModule being called inside Connect-Sql, SMO types may not be available and OutputType validation fails

Evidence from Codebase

suffix.ps1 (lines 11-28)

• Runs Import-SqlDscPreferredModule on module import
• Only runs if $env:SqlServerDscCI is not set (to avoid conflicts with unit test stubs)
• Cannot guarantee SMO types are available at module import time

Connect-Sql.ps1 (line 58)

• Has [OutputType([Microsoft.SqlServer.Management.Smo.Server])] attribute
• Also calls Import-SqlDscPreferredModule inside the function (line 96)
• The internal call happens too late—OutputType is already parsed at function definition time

Prerequisites.Integration.Tests.ps1 (lines 208-227)

• Installs SqlServer module as part of integration test setup
• Happens after SqlServerDsc module is already imported by Pester
• Module is not re-imported after SqlServer is installed

Potential Solutions

Option 1: Change OutputType to System.Object (Quickest Fix)

[OutputType([System.Object])]

Pros: Immediate fix, no breaking changes
Cons: Less specific type information for IDEs

Option 2: Remove OutputType Attribute

Pros: Eliminates the problem entirely
Cons: Loses IDE intellisense benefits

Option 3: Restructure Integration Tests

• Install SqlServer module before importing SqlServerDsc in integration tests
• Ensure proper module load order

Pros: Mirrors real-world usage better
Cons: May require significant test refactoring

Option 4: Force Module Re-import

• After SqlServer module installation in Prerequisites tests, force SqlServerDsc re-import
• Could use Import-Module -Name SqlServerDsc -Force

Pros: Ensures SMO types are loaded
Cons: May cause issues with PowerShell class type identities

Option 5: Conditional OutputType (Advanced)

Guard the OutputType with a runtime check, though this may not be feasible with PowerShell's parsing behavior.

Related Comments

PR #2433 discussion:
#2433

Recommended Approach

Short-term: Change [OutputType([Microsoft.SqlServer.Management.Smo.Server])] to [OutputType([System.Object])] to unblock current work.

Long-term: Investigate restructuring integration tests to install SqlServer module before importing SqlServerDsc, ensuring the module load order reflects real-world usage patterns.

Additional Context

• This issue was discovered while working on PR Sql Connection Optimization #2433 (SQL Connection Optimization)
• The problem manifests when using PSDscRunAsCredential in integration tests
• Related to environment variable $env:SqlServerDscCI used to control SMO loading in CI

[johlju]

Option 6 would be to move out Inport-* from inside Connect-Sql and let the user or resources/commands always call Import-* prior to calling Connect-*. That would solve OutputType problem as well 🤔

[johlju]

Option 7 would be if we finally can package SqlServer with the module, it is then imported in prefix.ps1 unless overridden by an environment variable (to use SQLPS or dbatools). That would make the SMO types always available and would be the best option. It is possible we could add SqlServer as a dependent module I module manifest if it is not possible to package (and pin it) inside the module.

UPDATE: After further investigation, Option 7 doesn't seem feasible. Any input appreciated on this.

Alternative 1 - Packaging SqlServer with SqlServerDsc:
Looking at the license I interpret section 2(a)(i) as we can package it, but section 2(a)(ii) that states "require distributors and external end users to agree to terms that protect it at least as much as this agreement" which would be a problem with MIT license that SqlServerDsc uses. Also it says in section 2(a)(iii) "You may not ... distribute Distributable Code to run on a platform other than the Windows platform" which will further restrict SqlServerDsc.

Alternativ 2 - add SqlServer as dependent module in SqlServerDsc module manifest:
The user would be forced into section 2, subsection (a), clause (ii) and clause (iii) with no option to opt-out.

[johlju]

Option 8 would be to build are own SQL Management Objects (SMO) assemblies from upstream which uses MIT license. We need to handle that the SMO don't interfere with any other SMO assemblies that might be loaded similar to dbatools has solved with dbatools.library (using, what I think, runspace).

Option 9 would be if we could incorporate dbatools.library so we don't need to invent the wheel (mentioned in option 8).