Can I allow a VSO build hosted agent to connect to SQL Azure DB?

Category: visual studio online

Question

F C (MSFT CSG) on Thu, 07 Jul 2016 13:31:15


From Ameet #7  (@AmeetAyare) via Twitter who tweets:

“Need to get #VSO #BuildServer talk to #Azure #sqldatabase as part of #ContIntegration but how to configure firewall rule?! Any ideas?”

Customer was sent the documentation on https://azure.microsoft.com/en-us/documentation/articles/sql-database-configure-firewall-settings/, however this did not help the customer

The customer also added: “doesn't help me. I need to know how I can allow a VSO build hosted agent can connect to SQL #azure DB.”

Tweet URL: https://twitter.com/AmeetAyare/status/751042272301551616

Appreciate if you may be able to assist the customer on this matter.

Thanks,
@AzureSupport

Replies

chamindac on Fri, 08 Jul 2016 07:17:04


Hi Francis,

By talk to Azure sqldatabase, I believe @AmeetAyare means connecting to deployment purpose using a task in VS Team Services (VSO). 

I have explained here how can a .dacpac deployed to an Azure DB via Team Services Release, and how to setup the firewall rule for the task. This is done with hosted build/release agent.

http://chamindac.blogspot.com/2016/07/deploy-dacpc-to-azure-db-via-vs-team.html

In "Azure SQL Database Deployment" task in VS Team Services (VSO), you can set firewall rule as shown below. 

or you can do below. but not recommended.

Cheers!

Chaminda

Ameet Ayare on Sun, 10 Jul 2016 10:38:23


Our CI build on VSO is set up to run: 1. Environmental tests 2. Integration tests 3. Unit tests Our UAT build definition on VSO is setup to run: 1. Environmental tests 2. Unit tests 3. Publish API app Environmental tests are a collection of MSTests that connect to the DB based on buildconfiguration for that specific environment and check certain application sensitive data for presence and evaluate that they are correct. Integration tests are a collection of MSTests that connect to the BD based on buildconfiguration for that specific environment and check integration of app's data access layer by performing CRUD operations and cleaning up after the tests. Obviously, to connect to the DB on azure the firewall rule has to be set but these tests will be run on VSO, so I'm struggling to find a way where the connection can be made by setting the firewall rule dynamically, connect to the database, run tests and then remove the firewall setting. @chaminda's post above is specific to deploying the DB, in our case we need connect - run tests - disconnect - publish API app to azure.

chamindac on Mon, 11 Jul 2016 04:59:11


Hi Ameet,

Sorry, I could not help earlier, but your question was not clear. With the description you provided now , I believe you can do below steps to achieve what you want..

1. Create Azure Powershell Task to do prep activity before your steps. You should get IP address of running machine (VSTS build/release agent) with powershell, and set azure firewall rule. 

you can use the below command 

New-AzureSqlDatabaseServerFirewallRule

2. Once you are done with you activities remove the firewall rule with 

Remove-AzureSqlDatabaseServerFirewallRule

You can find useful functions in this blog https://agileramblings.com/2015/07/26/using-powershell-to-set-your-azure-sql-firewall-rule/

VSTS hosted agent has AzurePS 1.0.0 available, so you can run above commands using

either "Azure Powershell" task

or "Run Inline Azure Powershell" task comes with free extension in marketplace.


Cheers!

Chaminda