How to secure access from App Service To Azure Sql Database using virtual network?

Question

Scenario

I want to use virtual network in order to limit access to Azure Database only from my App Service, so that I can turn of "Allow access to App Services" in firewall settings

What I have done:

1. I went to App Service -> Networking -> VNET Integration -> Setup -> Create New Virtual Network
2. I've created new VNET with default settings.
3. When VNET was created I went to App Service -> Networking -> VNET Integration and ensured that the VNET is connected
4. I went to SQL Firewall settigs -> Virtual Network -> Add existing Virtual Newtork and selected my VNET. I've left default subnet and address space: "default / 10.0.0.0/24" and I've left IgnoreMissingServiceEndpoint flag unchecked.

I can now see Microsoft.Sql service endpoint in my VNET:

Question

However, I'm still getting

SqlException: Cannot open server 'my-sqlserver' requested by the login. Client with IP address '52.233..' is not allowed to access the server.:

What am I missing?

Cockaleekie

DavidBrowne anwsered here that it is possible, but i had also had no succes. any advice? – Dobson

To save everyone time from reading the answers below, know that this still isn't reasonably straight-forward to do through Azure. You can use service endpoints, but only if your app services are in the same region as your SQL DB. – Dialyse

Answer 1

Unfortunately, you cannot achieve this scenario for now.

Why:

1. When you use VNET Integration with your Web App, your web App can communicate with the VNET like Web App <---> VNET.

2. When you use SQL firewall to allow traffic from a specific VNet, so the traffic from the VNET address space can communicate with your SQL like VNet <---> SQL server.

3. When all configurations are setup, your scenario should be Web App <---> VNET <---> SQL server. The important thing is that the traffic from Web App cannot go trough the VNET to the SQL server, because the web App is not within the VNET, it's out side the VNET! So,When you use Web App try to access the SQL server, the traffic is not from the VNET.

Solution:

The solution is that put your Web App into the VNet. So, you can use Azure ASE to achieve this.

Reference:

VNet Integration gives your web app access to resources in your virtual network but does not grant private access to your web app from the virtual network. Private site access refers to making your app only accessible from a private network such as from within an Azure virtual network. Private site access is only available with an ASE configured with an Internal Load Balancer (ILB). For details on using an ILB ASE, start with the article here: Creating and using an ILB ASE.

Similar case:App Service VNet Integration with Azure Storage Service Endpoint

Duralumin

So you are saying it will cost me more than 1000USD every moths (monthly fee for ASE v2)? For app service that does nothing just access SQL Database? – Cockaleekie

Hi @Cockaleekie . I understand what you mean. But it's only the solution for this scenario. This depends on product features. ASE can do more things than this. The main idea I want to say is VNet intergration cannot let your Web App be within the VNET. :-) – Duralumin

Ok, thanks. Is there any plan to change it in future? I mean restrict SQL access to single app service on network level? – Cockaleekie

@Cockaleekie I don’t know that.But I will feedback this issue to Azure PG team. – Duralumin

Just another reason Azure multitenant web apps are borderline worthless.No info sec professional will allow a SQL server (managed or not) to be accessible from the public internet. – Turino

This is no longer the correct answer. It is possible now to VNet-integrate the App Service and then using either a Private Endpoint (in which case the SQL public endpoint can be disabled) or service endpoint to reach the SQL Database. See learn.microsoft.com/en-us/azure/architecture/example-scenario/…. Second bullet under Alternatives indicates how to do it without private endpoint. – Interweave

Answer 2

If you want to lock down access from your app to a service such as Azure SQL, Storage, Cosmos DB, etc with service endpoints, you can use the new VNet Integration feature. The earlier form of the feature requires a gateway and builds on point to site VPN. The source IP from your app will be one of the point to site addresses. Service endpoints only works if your source IP is one of the VNet addresses.

With the new VNet Integration, your app backend will make calls with IPs in the selected subnet. That means you can set service endpoints on that subnet.

This is not to be confused with service endpoints TO your app. That is the recently released capability to lock traffic to your app down to selected VNet/subnets.

Implement

I just created a brand new env with new app service and plan, new vnet and subnet with sql service endpoint, new app service vnet integration (preview), new sql server with advanced firewall. still, when i connect to the azure sql server from my appservice, I get the public IP, and I get error when "allow acess from azure resources is disabled" – Gilberte

Doesn't work with sql because you need to set up a private dns endpoint and Azure web app vnet integration can't use private dns. – Turino

@PeterMoore You no longer need to use a private endpoint. See bullet #2 under Alternatives here: learn.microsoft.com/en-us/azure/architecture/example-scenario/… – Interweave

Answer 3

I believe Azure has now included support for your scenario, allowing connections like this one Web App <---> VNET <---> SQL server, contrary to @Waine Yang's response. I've just configured my Azure Functions to talk through a private VNET to a SQL Server only accessible via that VNET.

Now, when you try to set up VNET integration in your App Service, Azure displays the following message (notice it also says or through your Azure VNET):

Here is an example on how to create it using Terraform:

Ably

Is this the "Private Link" feature, or something else? – Cockaleekie

@Cockaleekie This feature can be used with Private Link/Private Endpoints, in which case you can complete disable the SQL public endpoint, but it can also be used with service endpoints. See bullet #2 under Alternatives: learn.microsoft.com/en-us/azure/architecture/example-scenario/… – Interweave

This answer is vaguely "correct", but lacking enough information to really be helpful IMO. Service endpoints @Interweave mentioned can only be used if your SQL DB and App services are in the same region, so only useful for very simple cases. – Dialyse

Answer 4

One option to consider that would allow you to remove the firewall setting "Allow access to App Services" is to add firewall rules for the outbound IP addresses of your specific App Service(s).

You can find the current list in the Properties section of the App Service, or with PowerShell...

$outboundIps = (Get-AzureRmResource -ResourceGroupName $rg -ResourceType Microsoft.Web/sites -ResourceName $appService).Properties.outboundIpAddresses

A couple of important caveats.

1. The outbound IPs can change, but do so predictably (see link below). I run PowerShell scripts as part of a VSTS release process that updates the firewall rules. (If you also manage your resources and their scale through your release process rather than the Azure portal, this approach seems reliable.)

2. I haven't been able to find any information to confirm whether these IPs are unique to your App Service. This is a risk to consider.

https://learn.microsoft.com/en-us/azure/app-service/app-service-ip-addresses

Graceless

My understanding based on the App Service architecture is that those IP addresses are /not/ unique to any one customer. Unless you have the $1000/mo+ ASE, you share those external IP addresses with many others. – Picked

@Picked You may well be right but the alternative (other than an ASE) is to open to all Azure services - this approach at least reduces the set of IPs with access significantly. – Graceless

Yup! That's right. Indeed the situation is pretty bad until some improvements are made. – Picked

@SteveS: would you mind sharing the powershell, that updates the firewall rules? – Cockaleekie