`Connect-Sql`: Control EncryptConnection and TrustServerCertificate properties

[johlju]

Problem description

Connect-Sql should support finer control for EncryptConnection and optionally for TrustServerCertificate properties on the ServerConnection object when connecting to SQL Server. Currently, if the Encrypt parameter is not specified, EncryptConnection is not explicitly disabled, and there is no way to opt in or out of TrustServerCertificate unless the property is set after object creation. Improvements should:

• Explicitly disable EncryptConnection when -Encrypt is not present, to clarify intent.
• Add an opt-in parameter to control TrustServerCertificate, defaulting to disabled if property exists and not explicitly set.
• Switch logic depending on SQL Server defaults, e.g., SQL Server 2025 has TrustServerCertificate enabled by default (secure-first). This should align with defaults based on the target SQL Server version.

Reference/example code to improve:

# localization string
    IgnoreServerCertificateTrust = Ignoring server certificate trust; server certificate trust is not enforced when connecting. (SQLCOMMON0055)

    if ($Encrypt.IsPresent)
    {
        $sqlConnectionContext.EncryptConnection = $true
    }
    else
    {
        # SQL Server 2025 defaults to having encryption enabled.
        $sqlConnectionContext.EncryptConnection = $false

        <#
            SQL Server 2025 defaults to trust server certificate.
            TODO: We need a new parameter to control this behavior. Integration tests
                DSC_SqlSecureConnection_AddSecureConnection_Config fails unless this is set.
        #>
        if ($sqlConnectionContext.PSObject.Properties.Name -contains 'TrustServerCertificate')
        {
            Write-Verbose -Message (
                $script:localizedData.IgnoreServerCertificateTrust
            )
            $sqlConnectionContext.TrustServerCertificate = $false
        }
    }

This change could use detection logic for SQL Server version (2025+) to set better defaults.

Verbose logs

n/a (enhancement/design change)

How to reproduce

1. Use Connect-Sql with and without the -Encrypt switch.
2. Attempt to control TrustServerCertificate via parameters (currently not possible).
3. Observe connection behaviors with various SQL Server versions (2022, 2025, etc.)

Expected behavior

Be able to:

• Explicitly disable or enable EncryptConnection based on the parameter.
• Opt-in to TrustServerCertificate when needed, otherwise leave disabled.
• Defaults and logic adapt to SQL Server version (secure-first approach).
• Better documentation of security properties in help and examples, including for SQL Server 2025/2022, etc.

Current behavior

Cannot explicitly disable EncryptConnection (only enable if -Encrypt is specified).
No parameter exists to control TrustServerCertificate; manual/object-property change is required.
SQL Server version defaults (e.g., 2025 secure-first approach) aren't reflected in current logic.

Suggested solution

• Add explicit disabling for EncryptConnection if -Encrypt is not present.
• Introduce an opt-in parameter (e.g. -TrustServerCertificate) to set that property, defaulting to disabled, only if available on the object.
• Implement logic to switch defaults based on SQL Server version; for 2025+, TrustServerCertificate could default to enabled.
• Enhance documentation and provide up-to-date examples showing secure defaults and options.

Operating system the target node is running

Any OS supported by SqlServerDsc

PowerShell version and build the target node is running

Any currently supported PowerShell version

Module version used

SqlServerDsc (latest + all relevant versions)