I am deploying a database to an Azure SQL Server with Azure Pipelines. The deployment is working really well. So I added the creation and deployment of a temporary database (one that is removed at the end of the pipeline) for unit testing of my USP's.
I created a unit test .dll with SSDT. I configured the app.config and tested it on my laptop in Visual Studio. On my laptop it works, the unit tests are run an all pass.
I tried to connect to the server with the credentials from the app.config in SSMS. The connection is established.
But, I cant get the pipeline (or hosted agent) task to connect to the database from within the pipeline.
I tried adding the IP of the agent to the server whitelist. I checked the "allow azure services to access the database". These did not solve the problem.
The error I get is this:
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 yml:
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)