You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

Azure Pipeline托管代理连接Azure SQL数据库单元测试失败排查

avatar
开发者阿泽
2026-8-19
问题描述

我用Azure Pipeline部署数据库到Azure SQL Server，部署流程正常。为测试存储过程（USP），新增了临时数据库（Pipeline结束自动删除）。通过SSDT创建单元测试.dll，配置app.config后本地Visual Studio测试全过，用app.config里的凭据在SSMS也能成功连服务器。但Pipeline的托管代理任务没法在内部连数据库，已经试过把代理IP加服务器白名单、勾选“允许Azure服务访问数据库”，都没解决。

报错信息：

Initialization method BuilEnDeployTest_Unittest.SqlServerUnitTest001.TestInitialize threw exception. System.Data.SqlClient.SqlException: System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) ---> System.ComponentModel.Win32Exception: An existing connection was forcibly closed by the remote host.

对应的Pipeline YAML配置：

trigger:
- test

name: $(TeamProject)_$(Date:yyyyMMdd)$(Rev:_r)

variables:
- group: "DatabaseDeploy"
- name:  databaseName
  value: sqldb-AzureDevOps_$(Build.BuildNumber)
#- name: ExecutionContext.__Database__
#  value: $(variables.databaseName)

stages:

### Building the code in the Pull-Request

- stage: Build
  displayName: Build the project

  jobs:
  - job: Build
    displayName: Build DB project
    pool:
      vmImage: windows-latest

    steps:


    - script: echo '$(Build.BuildNumber)'
      displayName: BuildName

    #- powershell: 
    #    Get-ChildItem -Path $(Agent.WorkFolder)\1\s\BuilEnDeployTest_Unittest -recurse
    #  displayName: Folder structure

    #- script: echo '$(Agent.WorkFolder)\1\s\BuilEnDeployTest_Unittest\app.config'
    #  displayName: app.config

    - powershell: 
         Get-Content -path $(Agent.WorkFolder)/1/s/BuilEnDeployTest_Unittest/app.config
      displayName: app.config

    - powershell: >
         (Get-Content -path $(Agent.WorkFolder)/1/s/BuilEnDeployTest_Unittest/app.config -Raw) 
         -replace 'MyDatabase', '$(databaseName)' 
         -replace 'MyServer', '$(ServerName_unittst).database.windows.net'
         -replace 'MyUser', '$(DatabaseUser_unittst)'
         -replace 'MyPassword', '$(DatabasePassword_unittst)'| Set-Content -Force -Path $(Agent.WorkFolder)/1/s/BuilEnDeployTest_Unittest/app.config

    - powershell: 
         Get-Content -path $(Agent.WorkFolder)/1/s/BuilEnDeployTest_Unittest/app.config
      displayName: app.config
    
    - task: VSBuild@1
      displayName: Building the database project
      inputs:
        solution: '**\*.sln'

    - task: CopyFiles@2
      displayName: Copy Build Artifacts
      inputs:
        SourceFolder: '$(agent.builddirectory)\s'
        Contents: '**'
        TargetFolder: '$(build.artifactstagingdirectory)'

          
        
   ### In the commands below, 'pipeline' is used instead of 'Container', allthough
   ### 'Container' is often seen in the online documentation. 
   ### I have noticed that changing 'pipeline' to 'Container' breaks my code
   ### I haven't spend time in figuring out why. It could be because of the path I use 
   ### down the line. 

    - task: PublishPipelineArtifact@1
      displayName: Publish Build Artifacts
      inputs:
        PathtoPublish: '$(Build.ArtifactStagingDirectory)'
        ArtifactName: 'database'
        publishLocation: 'pipeline'

#### Unit testing the Pull-Request

- stage: Unit_testing
  displayName: Database UnitTest
  condition: and(succeeded(), eq(variables['Build.Reason'], 'PullRequest'))


  jobs:
  - job: Deploy_to_temporary_database
    displayName: Deploy to temporary database
    pool:
      vmImage: windows-latest

 

    steps:

    ############ Create the Azure Resources as defined in the AzureSQLARMTemplate.json

    ## When the Azure SQL server does not exist it is also created by the ARM deployment.
    
    ## NOTE: I experienced errors when deleting and recreating the Azure SQL server. This could be 
    ## because of the little time between deletion and creation. 
    ## Deployment through the ARM template can deal with an existing server. In that case
    ## the server creation is skipped. So best to not delete the Azure SQL server untill the project
    ## is abandoned. 

    - task: AzurePowerShell@5
      displayName: 'Create unittest DB'
      inputs:
        azureSubscription: $(connectedServiceNameARM)
        ScriptType: 'FilePath'
        ScriptPath: './ARMtemplate/CreateAzureSQLAndDB.ps1'
        ScriptArguments: -azuresqlserverName $(ServerName_unittst) -sqlserverAdminLogin $(DatabaseUser_unittst) -sqlserverAdminPassword $(DatabasePassword_unittst) -databaseName $(databaseName) -Location 'northeurope' -ResourceGroupName 'rg-temp' -TemplateFile ARMtemplate\AzureSQLARMTemplate.json
        azurePowerShellVersion: 'LatestVersion'




    ############ Download the build artifacts from the build stage

    - task: DownloadPipelineArtifact@2
      displayName: 'Download artifacts'
      inputs: 
        buildType: 'current'
        artifactName: 'database'
        targetPath: '$(Pipeline.Workspace)/database'

    - powershell: 
        Get-ChildItem -Path $(Pipeline.Workspace)/database -recurse
      displayName: Folder structure

    - powershell: 
         Get-Content -path $(Pipeline.Workspace)/database/a/BuilEnDeployTest_Unittest/bin/Debug/BuilEnDeployTest_Unittest.dll.config
      displayName: BuilEnDeployTest_Unittest.dll.config

    ############ Deploy the database dacpac file to the newly created database

    - task: SqlAzureDacpacDeployment@1
      displayName: 'Deploy unittest DB'
      inputs:
        connectedServiceNameARM: $(connectedServiceNameARM)
        ServerName: $(ServerName_unittst).database.windows.net
        DatabaseName: $(databaseName)
        SqlUsername: $(DatabaseUser_unittst)
        SqlPassword: $(DatabasePassword_unittst)
        DacpacFile: $(Pipeline.Workspace)/database/a/$(dacpacfile_location)
        PublishProfile: $(Pipeline.Workspace)/database/a/$(publishprofile_location)

    ## Create a firewall rule for the IP adress of the build agent

    - task: AzurePowerShell@5
      displayName: 'Add buildserver public ip'
      inputs:
        azureSubscription: $(connectedServiceNameARM)
        ScriptType: InlineScript
        Inline: |
          $ip = (Invoke-WebRequest -uri "http://ifconfig.me/ip").Content
          $AzureSQLFirewallRule = Get-AzSqlServerFirewallRule -ResourceGroupName 'rg-temp' -ServerName $(ServerName_unittst) -FirewallRuleName "azuredevops"
          if ($AzureSQLFirewallRule -eq $null) {New-AzSqlServerFirewallRule -ResourceGroupName 'rg-temp' -ServerName $(ServerName_unittst) -FirewallRuleName "azuredevops" -StartIpAddress $ip -EndIpAddress $ip}
        azurePowerShellVersion: 'LatestVersion'     

    ############ Run the unittest

    - task: VSTest@2
      inputs:
        testAssemblyVer2: $(Pipeline.Workspace)/database/a/$(Unittestdll_location)
        testConfiguration: 'Debug'
        runOnlyImpactedTests: true
        runInParallel: false

    ############ Delete the newly created database
         
    - task: AzurePowerShell@5
      displayName: 'Delete unittest DB'
      inputs:
        azureSubscription: $(connectedServiceNameARM)
        ScriptType: 'FilePath'
        ScriptPath: './ARMtemplate/CleanupAzureSQLAndDB.ps1'
        ScriptArguments: -azuresqlserverName $(ServerName_unittst) -databaseName $(databaseName) -ResourceGroupName 'rg-temp'
        azurePowerShellVersion: 'LatestVersion'

     ## Delete a firewall rule for the IP adress of the build agent

    - task: AzurePowerShell@5
      displayName: 'remove buildserver public ip'
      inputs:
        azureSubscription: $(connectedServiceNameARM)
        ScriptType: InlineScript
        Inline: |
          $ip = (Invoke-WebRequest -uri "http://ifconfig.me/ip").Content
          Remove-AzSqlServerFirewallRule -ResourceGroupName 'rg-temp' -ServerName $(ServerName_unittst) -FirewallRuleName "azuredevops"
        azurePowerShellVersion: 'LatestVersion'


## Deploying the merged result to the database

- stage: Deploy_to_tst
  displayName: Deploy
  condition: and(succeeded(), ne(variables['Build.Reason'], 'PullRequest'))

  jobs:
  - job: Deploy
    displayName: Deploy
    pool:
      vmImage: windows-latest
  
    steps:
    - task: DownloadPipelineArtifact@2
      inputs: 
        buildType: 'current'
        artifactName: 'database'
        targetPath: '$(Pipeline.Workspace)/database'

    - powershell: 
        #Get-ChildItem -Path $(Pipeline.Workspace)/database -recurse
        #Get-ChildItem -Path $(Agent.Workspace) -recurse

    - task: SqlAzureDacpacDeployment@1
      displayName: 'Deploying the database to Azure'
      inputs:
        connectedServiceNameARM: $(connectedServiceNameARM)
        ServerName: $(Server_tst)
        DatabaseName: $(DatabaseName_tst)
        SqlUsername: $(DatabaseUser_tst)
        SqlPassword: $(DatabasePassword_tst)
        DacpacFile: $(Pipeline.Workspace)/database/a/$(dacpacfile_location)
        PublishProfile: $(Pipeline.Workspace)/database/a/$(publishprofile_location)
可能的解决方案

  • 修正防火墙规则的IP获取与更新逻辑
    当前用http://ifconfig.me/ip获取代理IP可能不稳定，换成更可靠的https://api.ipify.org，同时不要只判断规则是否存在，直接更新规则避免旧IP残留：

    $ip = (Invoke-WebRequest -uri "https://api.ipify.org" -UseBasicParsing).Content.Trim()
$AzureSQLFirewallRule = Get-AzSqlServerFirewallRule -ResourceGroupName 'rg-temp' -ServerName $(ServerName_unittst) -FirewallRuleName "azuredevops" -ErrorAction SilentlyContinue
if ($AzureSQLFirewallRule) {
    Set-AzSqlServerFirewallRule -ResourceGroupName 'rg-temp' -ServerName $(ServerName_unittst) -FirewallRuleName "azuredevops" -StartIpAddress $ip -EndIpAddress $ip
} else {
    New-AzSqlServerFirewallRule -ResourceGroupName 'rg-temp' -ServerName $(ServerName_unittst) -FirewallRuleName "azuredevops" -StartIpAddress $ip -EndIpAddress $ip
}

  • 确认单元测试配置文件替换是否完整
    检查Pipeline中BuilEnDeployTest_Unittest.dll.config的输出日志，确认MyServer、MyDatabase等占位符都被正确替换成变量值，尤其是服务器名称是否带.database.windows.net后缀。

  • 强制启用加密连接
    Azure SQL Server要求加密连接，在单元测试的连接字符串中添加Encrypt=True参数：

    <add name="YourConnectionString" connectionString="Server=...;Database=...;User Id=...;Password=...;Encrypt=True;" providerName="System.Data.SqlClient" />

  • 验证数据库部署后的可用性
    在VSTest任务前加一个PowerShell任务，用sqlcmd测试连接，提前排查连接问题：

    sqlcmd -S $(ServerName_unittst).database.windows.net -d $(databaseName) -U $(DatabaseUser_unittst) -P $(DatabasePassword_unittst) -Q "SELECT 1"

  • 检查VSTest任务的文件上下文
    确保测试dll和对应的.config文件在同一目录下，在VSTest任务前添加打印目录文件列表的步骤，确认配置文件存在。

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.08.19 18:30:59