Allow VSTS to update test database

Question

In order to run my acceptance tests I need to define a known good state on the database running on SQL Azure. I have the tests running fine locally and have set up the connection string to update my instance of SQL on Azure PaaS. The tests will run after the database is deployed using VSTS. In order for the deploying process to run my accceptance tests I need the process running Visual studio team system tests to have access to the database. VSTS apparently runs in the East US Azure zone. Given there are potentially hundreds of ip addresses I would need to whitelist, is there a more secure way of doing this, grabbing the ip address of the deploying process and then allowing this IP address access to the database as part of the deployment?

Answer 1

You can add and remove firewall rule by calling New-AzureRmSqlServerFirewallRule and Remove-AzureRmSqlServerFirewallRule powershell command.

Refer to these thread below to do it during the build/release: Deploy Dacpac packages via power shell script to Azure SQL Server

First, you need to add firewall rule in order to connect to Azure SQL Server.

1.Edit your build definition

2.Select Option tab and check Allow Scripts to Access OAuth Token

3.Add Azure PowerShell step (arguments: -RestAddress https://[account].vsdtl.visualstudio.com/DefaultCollection/_apis/vslabs/ipaddress -Token $(System.AccessToken) -RG [resource group] -Server [server name] -ruleName $(Build.BuildNumber)

Code:

param (
    [string]$RestAddress,
    [string]$Token,
    [string]$RG,
    [string]$Server
    )
$basicAuth = ("{0}:{1}" -f 'test',$Token)
$basicAuth = [System.Text.Encoding]::UTF8.GetBytes($basicAuth)
$basicAuth = [System.Convert]::ToBase64String($basicAuth)
$headers = @{Authorization=("Basic {0}" -f $basicAuth)}
$result = Invoke-RestMethod -Uri $RestAddress -headers $headers -Method Get
Write-Host $result.value
New-AzureRmSqlServerFirewallRule -ResourceGroupName $RG -ServerName $Server -FirewallRuleName "UnitTestRule" -StartIpAddress "$($result.value)" -EndIpAddress "$($result.value)"  

Update:

Allow Scripts to Access OAuth Token for release:

1. Edit release definition
2. Click Run On Agent
3. Check Allow Scripts to Access OAuth Token option