How to connect to a SQL Azure DB from a hosted build server for running tests

0
votes

We wish to implement CI using a TFS / Visual Studio Online-hosted build server. To run our unit/integration tests the build server needs to connect to a SQL Azure DB.

We've hit a stumbling block here because SQL Azure DBs use an IP address whitelist.

My understanding is that the hosted build agent is a VM which is spun-up on demand, which almost certainly means that we can't determine its IP address beforehand, or guarantee that it will be the same for each build agent.

So how can we have our hosted build agent run tests which connect to our IP-address-whitelisted SQL DB? Is it possible to programmatically add an IP to the whitelist and then remove it at the end of testing?

3
azurecontinuous-integrationintegration-testingazure-sql-databaseazure-devops
You can try with creating a powershell (or cross platform cli) script that adds ip address to sql server then another powershell script to remove it .. – Milen
That's partly what I'm asking - does Azure expose a method of programmatically (code or script) modifying a whitelist? I'm unable to find any documentation for such a feature. – awj
one more way is to add subnet total range – TheGameiswar

3 Answers

1
votes

After little research found this (sample uses PowerShell):

Login to your azure account

Select relevant subscription

Then:

New-AzureRmSqlServerFirewallRule -EndIpAddress 1.0.0.1 -FirewallRuleName test1 -ResourceGroupName testrg-11 -ServerName mytestserver111 -StartIpAddress 1.0.0.0

To remove it:

Remove-AzureRmSqlServerFirewallRule -FirewallRuleName test1 -ServerName mytestserver111 -ResourceGroupName testrg-11 -Force

Found in Powershell ISE for windows. Alternatively there should be something similar using cross platform cli if not running on windows machine

0
votes

There is the task/step of Azure PowerShell that you can call azure powershell (e.g. New-AzureRmSqlServerFirewallRule)

On the other hand, you can manage server-level firewall rules through REST API, so you can custom build/release task to get necessary information (e.g. authentication) of selected Azure Service Endpoint, then send the REST API to add new or remove firewall rules.

The SqlAzureDacpacDeployment task has the source code to add firewall rules through REST API that you can refer to. Part SqlAzureDacpacDeployment source code, VstsAzureRestHelpers_.psm1 source code.

0
votes

There now is a "Azure SQL InlineSqlTask" build task which u can use to automatically set firewall rules on the Azure server. Just make sure "Delete Rule After Task Ends" is not checked. And just add some dummy query like "select top 1 * from...." as "Inline SQL Script"