Add encryption support for SQL Server instance DSCSQLTEST in CI environment

[coderabbitai]

Problem Statement

Currently, the CI environment does not support encrypted connections to the SQL Server instance DSCSQLTEST. This limitation prevents us from testing encryption-related functionality and requires skipping tests that use the -Encrypt parameter.

Affected Areas

• Integration tests that require encrypted SQL Server connections
• Currently identified skipped test: Invoke-SqlDscQuery.Integration.Tests.ps1 line 165-169
• Additional tests across the project that may require encryption support

Technical Requirements

SQL Server Configuration

1. SSL Certificate Setup

   • Install a valid SSL certificate on the SQL Server instance DSCSQLTEST
   • Configure SQL Server to use the certificate for encrypted connections
   • Options:
     • Self-signed certificate (for CI testing purposes)
     • CA-signed certificate (more secure, but may require additional setup)

2. SQL Server Instance Configuration

   • Enable Force Encryption setting in SQL Server Configuration Manager
   • Configure certificate binding in SQL Server Network Configuration
   • Restart SQL Server service to apply changes

CI Worker Configuration

1. Certificate Trust

   • If using self-signed certificates, add them to the trusted certificate store
   • Configure certificate chain validation for CI environment

2. Network Security

   • Ensure TLS protocols are properly configured
   • Verify firewall settings allow encrypted connections

Implementation Suggestions

Phase 1: Certificate Generation and Installation

# Example PowerShell script for CI setup
$cert = New-SelfSignedCertificate -DnsName "$(hostname)" -CertStoreLocation "Cert:\LocalMachine\My" -KeySpec KeyExchange
$thumbprint = $cert.Thumbprint

# Export certificate for SQL Server configuration
Export-Certificate -Cert $cert -FilePath "C:\temp\sqlserver.cer"

# Import to trusted root (for self-signed certificates)
Import-Certificate -FilePath "C:\temp\sqlserver.cer" -CertStoreLocation "Cert:\LocalMachine\Root"

Phase 2: SQL Server Configuration

• Use SQL Server Configuration Manager or PowerShell/WMI to configure certificate
• Update registry settings for certificate binding
• Restart SQL Server services

Phase 3: Test Enablement

• Remove -Skip attribute from encryption-dependent tests
• Add comprehensive encryption testing scenarios
• Verify all parameter sets work with encrypted connections

Success Criteria

• SSL certificate properly configured for DSCSQLTEST instance
• Encrypted connections work in CI environment
• All previously skipped encryption tests are enabled and passing
• No regression in existing non-encrypted connection tests

References

• PR: Add integration test for Invoke-SqlDscQuery command #2256
• Comment: Add integration test for Invoke-SqlDscQuery command #2256 (comment)
• Requested by: @johlju

Additional Notes

This enhancement will improve test coverage and ensure encryption-related functionality works correctly across different SQL Server versions (2017, 2019, 2022) as indicated by the integration test tags.

[johlju]

This can be integration tested using the command Connect-SqlDscDatabaseEngine with the parameter -Encrypt, that will enable property EncryptConnection on the ConnectionContext object of the Server object.

    $sqlServerObject = New-Object -TypeName 'Microsoft.SqlServer.Management.Smo.Server'
    $sqlConnectionContext = $sqlServerObject.ConnectionContext

    if ($Encrypt.IsPresent)
    {
        $sqlConnectionContext.EncryptConnection = $true
    }

[johlju]

There are additional information in URL: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-sql-server-encryption