PowerShell中Invoke-SQLCmd执行失败：解析错误与证书信任问题

我是PowerShell新手，写了一段执行数据库查询的脚本，但一开始就失败了。语法看起来没问题，但遇到两个错误：

• 主要错误：SQL解析错误，提示“解析空字符串时遇到语法错误”
• 次要错误：登录时SSL证书链不受信任（我怀疑解析错误不是证书问题导致的）

报错信息 #

PS C:\Admin> .\Cancel.ps1 -Client  MyClient
================ Define Cancel Type ================
cmdlet Get-Credential at command pipeline position 1
Supply values for the following parameters:
Credential
Verifying Client...
Invoke-SQLCmd : A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The
certificate chain was issued by an authority that is not trusted.)
At C:\Admin\Cancel.ps1:36 char:15
+     $Output = Invoke-SQLCmd -Query $Query -Database 'MyDB' | Write-Ou ...
+               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Invoke-SQLCmd : Incorrect syntax was encountered while parsing ''.
At C:\Admin\Cancel.ps1:36 char:15
+     $Output = Invoke-SQLCmd -Query $Query -Database 'MyDB' | Write-Ou ...
+               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ParserError: (:) [Invoke-Sqlcmd], BatchParserException
    + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
PS C:\Admin>

脚本开头代码 #

[CmdletBinding()]

param( [string][Parameter(Mandatory=$true, Position=0, HelpMessage="Missing defined client")]
       [ValidateNotNullOrEmpty()]$Client,
       
       [string][Parameter(Position=1)]$Secondary
)

Install-Module sqlserver
Update-Module sqlserver
Import-Module "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\SQLPS.ps1" –DisableNameChecking

Function Verify-Client {Param([string]$Client)
    $Query = "SELECT * FROM DBO.MyTable WHERE Name = '$Client'"
    $Output = Invoke-SQLCmd -Query $Query -Database 'MyDB' | Write-Output
    
    If ($Output -eq $null) {
        Write-Error 'FATAL ERROR: Client does not exist!'
        Break
    }
    Else {
        ## Build Client Array
        [ARRAY]$Script:ClientDetails = $Output
        Write-Host '  Client Found!'
    }
}

PowerShell版本 #

Major  Minor  Build  Revision
------------------------------
5      1      17763  4720

1. 解析空字符串错误修复 #

问题根源 #

调用Invoke-SQLCmd时未指定SQL服务器地址，命令无法定位数据库，引发空字符串解析错误；同时管道后的Write-Output属于冗余操作，Invoke-SQLCmd本身会返回查询结果。

修复代码 #

Function Verify-Client {Param([string]$Client)
    $Query = "SELECT * FROM DBO.MyTable WHERE Name = '$Client'"
    # 替换为你的实际SQL服务器地址，比如localhost或远程服务器IP/域名
    $Output = Invoke-SQLCmd -Query $Query -Database 'MyDB' -ServerInstance "你的SQL服务器地址"
    
    If (-not $Output) {
        Write-Error 'FATAL ERROR: Client does not exist!'
        return # 用return代替Break，避免终止整个脚本
    }
    Else {
        [ARRAY]$Script:ClientDetails = $Output
        Write-Host '  Client Found!'
    }
}

2. SSL证书链不受信任错误修复 #

临时方案（测试环境） #

添加-TrustServerCertificate $true参数跳过证书验证：

$Output = Invoke-SQLCmd -Query $Query -Database 'MyDB' -ServerInstance "你的SQL服务器地址" -TrustServerCertificate $true

永久方案（生产环境） #

将SQL服务器的证书导入本地信任的根证书颁发机构，或使用受信任CA颁发的正规证书。

额外优化建议 #

• 移除脚本开头的Install-Module sqlserver和Update-Module sqlserver，手动执行一次即可，避免每次运行脚本重复安装/更新；替换旧的SQLPS模块为更现代的sqlserver模块，脚本里仅保留Import-Module sqlserver。
• 避免直接拼接用户输入到SQL语句，存在注入风险，改用参数化查询：

$Query = "SELECT * FROM DBO.MyTable WHERE Name = @Client"
$Output = Invoke-SQLCmd -Query $Query -Database 'MyDB' -ServerInstance "你的SQL服务器地址" -Parameter @{ Client = $Client }

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