Enable SQL Azure access from Azure WebJobs by adding firewall rules using PowerShell | José Quinto

Introduction

When we are developing custom Azure Web Apps or WebJobs, likely we will need access to Azure SQL database. Depends on the needs you will use Entity Framework, or direct Queries using Stored Procedures, but this is not the topic of this post. The topic today is how to allow our Azure WebJob to access to SQL Azure database, doesn’t matter what method we are using.

After some investigation, there are different ways to do that, I will share with you the different options I concluded:

The options

1. Allow access other Azure Services

   Security Note: this approach enables access to ALL AZURE SERVICES IN THE SAME REGION (even from other subscriptions).

2. Allow Outbound IP Addresses of the Azure Web App in SQL Azure using a specific firewall rule

   • Dynamic IP
     • Use TimerJob to add rules every certain time (if the IP changes)
     • Use Powershell and schedule a task every certain time (THIS IS THE SCENARIO APPLIED ON THIS POST)
   • Static IP (probably expensive, requires Premium App Service Plan)
     • Using ASE (other) and Add the Firewall rules manually only once

Read more…

Some useful references:

• Configure an Azure SQL Database server-level firewall rule using the Azure Portal)
• Reserved IP
• Azure Security fundamentals
• Create a server firewall rule in SQL Azure
• What is outgoing IP on Azure Web Apps

The approach and code

Now I will share the PowerShell script that you can configure with your Azure Web App and SQL Server details to add automatically the outbound IPs into SQL Azure firewall:

Important notes on the code:

• Change all the configuration parameters including Azure Admin, Web App and SQL info.
• Run the script and enjoy!

  Outbound Public URL aren’t Static by default, so probably they could change over the time (is not usual but can happen). So, I recommend some daily task to check every day and add the new IPs if needed. As the code is ready to update if the IPs are different. (read more here)