CODEWITHSUNDEEP
azurecontinuous-integrationintegration-testingazure-sql-databaseazure-devops
awj
awj

Reputation: 7959

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

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?

Upvotes: 0

Views: 627

Answers (3)

Robert Pouleijn
Robert Pouleijn

Reputation: 509

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"

Upvotes: 0

starian chen-MSFT
starian chen-MSFT

Reputation: 33728

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.

Upvotes: 0

Milen
Milen

Reputation: 8877

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

Upvotes: 1

Related Questions