使用PowerShell获取远程SQL Server实例，解决加密连接报错问题

原本运行正常的PowerShell代码，因SQL Server默认启用加密的破坏性更新失效，报错发生在使用Get-ChildItem访问SQLSERVER:\PS驱动器时，错误信息如下：

Invoke-Sqlcmd: 已成功与服务器建立连接，但在登录过程中发生错误。(provider: SSL Provider, error: 0 - 证书链由不受信任的机构颁发。)

核心需求：

• 如何在使用Get-ChildItem "SQLSERVER:\SQL\$server"时添加TrustServerCertificate=true或Encrypt=optional参数
• 本地执行PowerShell代码时，如何查询远程服务器上的所有SQL Server实例

原代码 #

Import-Module -Name SqlServer
Import-Module -Name ImportExcel

# 定义服务器名称列表
$servers = @("DE-SQL-03")

# 定义域管理员登录信息
$Username = "DOMAIN\badmin" 

$Password = Read-Host "Enter the password for $Username" -AsSecureString
$Credential = New-Object System.Management.Automation.PSCredential($Username, $Password)
# 获取当前日期
$currentDate = (Get-Date).ToString("yyyy-MM-dd_HH-MM")
# 定义Excel输出文件
$OutputFile = "C:\scripts\DatabaseAndLogSizes_$($servers)_$($currentDate).xlsx"

# 遍历每个服务器，获取数据库大小和填充率
foreach ($server in $servers) {
    Write-Host "Server: $($server)"
  
    # 打开远程会话
    $Session = New-PSSession -ComputerName $server -ConfigurationName "PowerShell.7" -Credential $Credential 
    
    # 获取服务器上的所有SQL Server实例
    $instances = Invoke-Command -Session $Session  -ScriptBlock {
        Import-Module -Name SqlServer
        Get-ChildItem -Path "SQLSERVER:\SQL\$($using:server)" | Select-Object -ExpandProperty Name
    }

    # 遍历每个实例，获取数据库大小和填充率
    foreach ($instance in $instances) {
        Write-Host "Instance: $($instance)"
        # 原代码后续部分省略
    }
}

已尝试的方法 #

方法1：使用ServerConnection对象 #

$serverConnection = New-Object -TypeName Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList $server
$serverConnection.LoginSecure = $true

# 添加TrustServerCertificate=True到连接字符串
$serverConnection.ConnectionString = $serverConnection.ConnectionString + ";TrustServerCertificate=True"

# 建立连接
$serverConnection.Connect()

方法2：自定义SqlConnection传递给Get-ChildItem #

$instances = Invoke-Command -Session $Session -ScriptBlock {
    param($server)
    Import-Module -Name SqlServer

    $connectionString = "Server=$($server);Integrated Security=True;Encrypt=True;TrustServerCertificate=True"
    $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $connectionString
    $connection.Open()

    # 使用自定义连接获取SQL Server实例
    Get-ChildItem -Path "SQLSERVER:\SQL\$($server)" -Connection $connection | Select-Object -ExpandProperty Name
    $connection.Close()
} -ArgumentList $server

方法3：尝试Invoke-Sqlcmd（逻辑错误待修正） #

$size = Invoke-Command -Session $Session -ScriptBlock {
    param($instance, $query)
    Import-Module -Name SqlServer
    Invoke-Sqlcmd -ServerInstance $instance -Query $query -ConnectionString "Server=$instance;Encrypt=True;TrustServerCertificate=True"
} -ArgumentList $instance, $querySize

解决方案 #

核心思路 #

SQLSERVER:\PS驱动器依赖SMO（SQL Server管理对象）连接，直接修改SMO连接的信任证书属性，或通过SMO对象直接获取实例列表，即可解决证书验证失败问题。

方案1：通过SMO Server对象获取实例列表（推荐） #

绕过SQLSERVER:\驱动器，直接用SMO对象控制连接参数，逻辑更清晰：

$instances = Invoke-Command -Session $Session -ScriptBlock {
    param($serverName)
    Import-Module SqlServer

    # 创建SMO连接并配置信任证书
    $serverConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($serverName)
    $serverConn.LoginSecure = $true
    $serverConn.TrustServerCertificate = $true # 关键设置：信任服务器证书

    # 创建SMO Server对象
    $smoServer = New-Object Microsoft.SqlServer.Management.Smo.Server($serverConn)
    
    # 获取所有实例名称
    $smoServer.Instances
} -ArgumentList $server

方案2：配置SQLSERVER:\驱动器的连接属性 #

如果坚持使用Get-ChildItem，可在访问驱动器前修改其全局连接参数：

$instances = Invoke-Command -Session $Session -ScriptBlock {
    param($serverName)
    Import-Module SqlServer

    # 构建带信任证书的连接字符串
    $connBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
    $connBuilder["Server"] = $serverName
    $connBuilder["Integrated Security"] = $true
    $connBuilder["TrustServerCertificate"] = $true
    $connBuilder["Encrypt"] = "Optional" # 若服务器强制加密，改为True

    # 注册/更新SQLSERVER:\驱动器的连接属性
    $sqlDrive = Get-PSDrive -Name SQLSERVER -ErrorAction SilentlyContinue
    if ($sqlDrive) {
        $sqlDrive.ConnectionString = $connBuilder.ConnectionString
    } else {
        New-PSDrive -Name SQLSERVER -PSProvider SqlServer -Root "SQLSERVER:\" -Scope Global -ConnectionString $connBuilder.ConnectionString
    }

    # 正常访问SQLSERVER:\路径
    Get-ChildItem -Path "SQLSERVER:\SQL\$serverName" | Select-Object -ExpandProperty Name
} -ArgumentList $server

关键说明 #

• TrustServerCertificate=true是解决证书不受信任问题的核心，必须在SMO连接或SQL连接字符串中明确指定
• 使用SMO Server对象获取实例比SQLSERVER:\驱动器更直接，也更容易排查连接问题
• 若SQL Server强制要求加密，需将Encrypt设为True，配合TrustServerCertificate=true使用

内容的提问来源于stack exchange，提问作者fpSPQR