How to whitelist the Function app in Azure SQL Database

Question

I need to configure Azure SQL Database firewall settings so that it can only be accessed by my Azure Function app. The problem is I'm running the app in a consumption plan, and as far as I know, the outbound IP address(es) can change even when I don't take any actions.

Is there a way to whitelist the app so that I protect the database from unwanted connections?

I thought of whitelisting by Azure region since everything is hosted in the same region, but then how do I guard against other apps in the same region? That's why I'm thinking of using specific IP addresses. My only concern with this approach is, I don't know if other function apps can share the same outbound IP addresses as my own.

P.S. Currently, my firewall settings deny public network access and allow Azure services to connect only.

Answer 1

There are several ways to achieve this.

You may want to integrate VNet or get static IP addresses for your Azure Functions

Image from: https://docs.microsoft.com/en-us/azure/azure-functions/functions-networking-options

However, what I've seen from your comments you don't want to go the premium plan.

The last thing I can suggest you implement Managed Service Identity.

The idea behind this, instead of connecting the database with connection string, you connect to the database with the access token that you granted. You can't get the access token if you are not in the same Identity.

This tutorial explains the general idea with App Service: https://docs.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi

and this tutorial pretty much covers what do you really want to achieve.

https://www.azurecorner.com/using-managed-service-identity-in-azure-functions-to-access-azure-sql-database/

Good luck!

Answer 2

One thing you can do is assign a managed identity to your function. It will retrieve a token from Azure AD, and it will be used to connect to Azure SQL:

if (accessToken != null) {
    string connectionString = "Data Source=<AZURE-SQL-SERVERNAME>; Initial Catalog=<DATABASE>;";
    SqlConnection conn = new SqlConnection(connectionString);
    conn.AccessToken = accessToken;
    conn.Open();
}

https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/tutorial-windows-vm-access-sql

Answer 3

You can do this by assigning a static IP to the function app and whitelist at the SQL Server -INbound Networking side and Deny all requests. However you have to change the consumption plan to Appservice to assign a static IP.

https://docs.microsoft.com/en-us/azure/azure-functions/ip-addresses#dedicated-ip-addresses

Also, you can try creating a Vnet peering for those services and block other requests.

Answer 4

I can't find where i got the answer (so not my answer but sharing it here), your Azure Functions have a list of outboundIpAddresses and possibleoutboundIpAddresses that you can add to your Azure SQL firewall rules (mine had about 10).

You can find them by...

1. Go to https://resources.azure.com
2. Expand Subscriptions -> [Expand your Subscription] -> Providers -> Microsoft.Web -> Sites
3. Find your Azure Function Site in the JSON, and locate the outboundIpAddresses and possibleoutboundIpAddresses, these will contain a list of IP addresses.
4. Add all of them to your SQL server's firewall.

While I'm not positive if these will ever change, so far they haven't for me and the person who originally posted this solution also noted that they haven't run into issues with this.

Answer 5

I had the same issue but managed identity didn't make much difference.

In the firewall setting for the SQL server there is an option to allow azure resources to access the server. For me this was set to no, but needed to be set to yes.

Answer 6

Virtual networks do not work on Azure the same way as they work on premises

If you create a vnet, add your Azure function in a subnet and in sql server you allow this subnet to access it will unfortunately not work.

If it is ok for you to allow "public access" and/or "azure resources access" then things are simple. You log in with sql credentials and you have access.

If you block public access I am not sure that your resources would be able to access your database, because all your connection go to SQL server from the internet not from your internal network.

Solution that worked for me is

1. Create a vnet
2. Create a private endpoint for Sql server in this vnet (custom DNS records were created by the IT-OPS people).
3. Azure function uses a subnet of this vnet.

Now, you can close public and azure resources access in your database.

All your calls will go through your virtual network (not through the internet anymore) and only applications that use a subnet of this vnet would be able to connect to the database.