SMO enumeration fails on SQL Server 2005 even though the connection and raw T-SQL succeed

[howarthcd]

Summary

On a real SQL Server 2005 instance (9.00.5000.00, Express Edition with Advanced Services), the TDS connection succeeds and raw T-SQL through Invoke-DbaQuery returns results, but every command that walks SMO's object model fails. $server.Databases and $server.Logins throw 'CONNECTIONPROPERTY' is not a recognized built-in function name, and Get-DbaDatabase throws Invalid column name 'is_cdc_enabled'. Both CONNECTIONPROPERTY and is_cdc_enabled arrived in SQL Server 2008 - SMO issues queries that depend on them regardless of which version it is talking to, so no command that enumerates a database or login collection can reach 2005. VersionMajor, VersionString and Edition all come back empty on the connection too, which matters for the suggested fix below.

This surfaced while working on #10581 (open, not yet merged into development), which adds a -DataPages switch to Invoke-DbaDbDecryptObject. The repro below does not depend on that PR - Get-DbaDatabase, Get-DbaLogin and plain Connect-DbaInstance already reproduce it on current development.

This is a different finding from #9821, not a duplicate. That issue was closed as an unsupported combination of PowerShell 7, TLS 1.0 and Microsoft.Data.SqlClient, with the connection itself failing during login. Here the connection succeeds (also over TLS 1.0, from PowerShell 7) and Invoke-DbaQuery returns results normally - the failure is one layer up, inside SMO's own queries, and would happen the same way for anybody reaching a 2005 instance regardless of client stack.

Steps to Reproduce

# Any command touching SMO's Databases or Logins collection reproduces this against a SQL Server 2005 instance.
Connect-DbaInstance -SqlInstance $instance -SqlCredential $cred | Select-Object VersionMajor, VersionString, Edition
Get-DbaDatabase -SqlInstance $instance -SqlCredential $cred -EnableException
Get-DbaLogin -SqlInstance $instance -SqlCredential $cred -EnableException

# Raw T-SQL through dbatools works fine on the same instance, for contrast.
Invoke-DbaQuery -SqlInstance $instance -SqlCredential $cred -Database master -Query "SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion"

Reproduced today (not from an old session) against a live SQL Server 2005 Express Edition with Advanced Services instance, PowerShell 7, dbatools.library 2026.5.3 (Microsoft.SqlServer.Smo 18.100.0.0), connecting over TLS 1.0 via Set-DbatoolsInsecureConnection:

=== raw client, no SMO ===
opened. ServerVersion 09.00.5000
ProductVersion 9.00.5000.00
Edition        Express Edition with Advanced Services
Version        Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86)

=== Invoke-DbaQuery ===
Invoke-DbaQuery works: ProductVersion 9.00.5000.00

=== SMO via Connect-DbaInstance ===
server.VersionString = []
server.Edition = []
server.ProductLevel = []
server.Databases threw: The following exception occurred while trying to enumerate the collection: "'CONNECTIONPROPERTY' is not a recognized built-in function name.".
server.Logins threw: The following exception occurred while trying to enumerate the collection: "'CONNECTIONPROPERTY' is not a recognized built-in function name.".

=== dbatools commands ===
Get-DbaDatabase threw: An exception occurred while executing a Transact-SQL statement or batch. Invalid column name 'is_cdc_enabled'.
Get-DbaLogin threw: The following exception occurred while trying to enumerate the collection: "'CONNECTIONPROPERTY' is not a recognized built-in function name.".

VersionMajor comes back empty on this connection rather than 9, which is why Connect-DbaInstance -MinimumVersion cannot be used to refuse it - see below.

# MinimumVersion cannot enforce a floor it cannot read
Connect-DbaInstance -SqlInstance $instance -SqlCredential $cred -MinimumVersion 10 -WarningVariable minimumWarning -WarningAction SilentlyContinue
# Connects anyway. VersionMajor is empty on 2005, so Connect-DbaInstance never evaluates the check that
# would otherwise refuse it - MinimumVersion 9 and MinimumVersion 10 behave identically here.

Expected behaviour

Not asking for SQL Server 2005 support to be added - SMO's own queries already put that out of reach, and Microsoft.SqlServer.Smo in the currently pinned dbatools.library (2026.5.3, SMO 18.100.0.0) dropped 2005 well before this build. The ask is narrower: the repository's version-support guidance should describe the boundary this actually is.

Where the guidance is wrong

.github/prompts/sql-version-support.md recommends -MinimumVersion 9 as the standard pattern for "SQL 2005+ features" (lines 30-38, 84, 110), on the basis that catalog views, schemas and DMVs arrived in 2005. That is true of the T-SQL surface, but any command that also touches SMO's object model needs 2008, and -MinimumVersion 9 cannot enforce even that lower floor on an actual 2005 instance, because VersionMajor comes back empty there rather than 9 - the guard is evaluated, finds nothing to compare, and lets the connection through regardless of the number given. The result is that a command guarded by -MinimumVersion 9 looks protected against old servers in the source, but on the one version where that protection would matter it does nothing, and the failure that follows (CONNECTIONPROPERTY, is_cdc_enabled) points at SMO internals rather than at anything resembling "your SQL Server is too old."

Suggested fix

Documentation only, no code change proposed here:

• Add a note to .github/prompts/sql-version-support.md that commands walking SMO's Databases, Logins or similar collections need SQL Server 2008 (VersionMajor 10), not 2005 (VersionMajor 9), and that -MinimumVersion cannot be relied on to enforce this specifically against a 2005 instance, since VersionMajor is unreadable there.
• Optionally record, wherever the module states its supported version range, that SQL Server 2005 is unreachable through SMO-backed commands with the currently pinned dbatools.library, independent of client OS, PowerShell version or TLS configuration - which is what distinguishes this from MSSQL 2005 PowerShell 7 Error In Login Process #9821.

Test

None proposed - this instance is not part of CI, cannot be added to it as a SQL Server 2005 Express box, and the finding is about documentation rather than command behaviour. If a maintainer wants a regression test guarding against re-widening -MinimumVersion past what SMO can serve, it would need a 2008 instance in the matrix rather than a 2005 one.

---

This text was created by Claude and reviewed by Chris Howarth.