PowerShell通过GitHub Secret访问仓库执行SQL脚本遇连接错误求助

我的GitHub仓库里有个Database文件夹，存着SQL脚本，仓库只能通过GitHub Secret访问。我想用PowerShell的Invoke-WebRequest下载这些脚本，再用Invoke-Sqlcmd在本地数据库执行，写了如下脚本：

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
$GitRepository = ("https://github.com/XYZ/ABC/tree/ABCD/XYZXYZ/Database/")
$GitManifestArray = @("Database.sql","XYZ.sql","ABC.sql","SP.sql","RSP.sql","FN.sql","E.sql")
$SQLInstances = ".\SQLEXPRESS"
$DefaultDatabase = "MYDEFDATABASE"
ForEach ($SQLInstance in $SQLInstances.Split(","))
 {
     echo "Running Scripts on " $SQLInstance
     #foreach ($File In $Manifest)
     ForEach ($file in $GitManifestArray)
     {
         $ScriptURL = ($GitRepository+$file.Replace("/blob/","/"))
        echo "Running " $ScriptURL
        $Headers = @{
           accept = "application/vnd.github.v3.raw"
           authorization = '${{secrets.ABC_XYZ_ABC_TOKEN}}'
           }
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls,  [Net.SecurityProtocolType]::Tls11, [Net.SecurityProtocolType]::Tls12, [Net.SecurityProtocolType]::Ssl3

         $ScriptFromGit = Invoke-WebRequest -Uri $ScriptURL  -Headers $Headers -UseBasicParsing
         Invoke-Sqlcmd -ServerInstance $SQLInstance  -Query $ScriptFromGit.Content
     }
 }

执行时遇到错误：

Invoke-WebRequest : The request was aborted: The connection was closed unexpectedly.
At line:18 char:27

• $ScriptFromGit = Invoke-WebRequest -Uri $ScriptURL  -Headers $Headers - ...*
  

我尝试把授权头改成以下格式，仍出现相同错误：

authorization = 'token ${{secrets.ABC_XYZ_ABC_TOKEN}}'

1. 修正GitHub文件URL格式 #

你当前使用的是仓库网页路径（含tree），无法直接获取原始文件内容。正确的原始文件URL格式应为：
https://raw.githubusercontent.com/[用户名]/[仓库名]/[分支名]/[文件路径]
修改$GitRepository变量：

$GitRepository = "https://raw.githubusercontent.com/XYZ/ABC/ABCD/XYZXYZ/Database/"

拼接URL时无需替换/blob/（原路径中不存在该字符串），直接用Join-Path拼接更规范。

2. 修复授权头配置 #

• 若在GitHub Actions环境中，${{secrets.ABC_XYZ_ABC_TOKEN}}会自动替换，但授权头必须是token 令牌值的格式，需确保变量拼接正确：

  $Headers = @{
      accept = "application/vnd.github.v3.raw"
      authorization = "token ${{secrets.ABC_XYZ_ABC_TOKEN}}"
  }
  

• 若本地运行PowerShell，${{secrets.xxx}}语法不生效，需手动传入令牌值（注意不要将令牌硬编码到代码中）：

  $githubToken = "你的GitHub Personal Access Token"
  $Headers = @{
      accept = "application/vnd.github.v3.raw"
      authorization = "token $githubToken"
  }
  

3. 移除不安全的SSL协议 #

SSL3已被废弃，会导致连接异常，仅保留安全的TLS版本：

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 -bor [Net.SecurityProtocolType]::Tls13

4. 补充数据库指定参数 #

脚本中定义了$DefaultDatabase但未使用，执行Invoke-Sqlcmd时需加上该参数，避免默认数据库错误：

Invoke-Sqlcmd -ServerInstance $SQLInstance -Database $DefaultDatabase -Query $ScriptFromGit.Content

修正后的完整脚本 #

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

# 替换为正确的原始文件根URL
$GitRepository = "https://raw.githubusercontent.com/XYZ/ABC/ABCD/XYZXYZ/Database/"
$GitManifestArray = @("Database.sql","XYZ.sql","ABC.sql","SP.sql","RSP.sql","FN.sql","E.sql")
$SQLInstances = ".\SQLEXPRESS"
$DefaultDatabase = "MYDEFDATABASE"
# 本地运行时替换为实际令牌，GitHub Actions中保留${{secrets.ABC_XYZ_ABC_TOKEN}}
$githubToken = "${{secrets.ABC_XYZ_ABC_TOKEN}}"

# 启用安全的TLS版本
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 -bor [Net.SecurityProtocolType]::Tls13

ForEach ($SQLInstance in $SQLInstances.Split(","))
{
    Write-Host "Running Scripts on $SQLInstance"
    ForEach ($file in $GitManifestArray)
    {
        $ScriptURL = Join-Path -Path $GitRepository -ChildPath $file
        Write-Host "Running $ScriptURL"
        
        $Headers = @{
            accept = "application/vnd.github.v3.raw"
            authorization = "token $githubToken"
        }

        try {
            $ScriptFromGit = Invoke-WebRequest -Uri $ScriptURL -Headers $Headers -UseBasicParsing -ErrorAction Stop
            Invoke-Sqlcmd -ServerInstance $SQLInstance -Database $DefaultDatabase -Query $ScriptFromGit.Content -ErrorAction Stop
            Write-Host "Successfully executed $file on $SQLInstance"
        }
        catch {
            Write-Error "Failed to process $file on $SQLInstance : $_"
        }
    }
}

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