1

I'm trying to securely let an Azure App Service connect to an Azure SQL Server Database.

Before, to 'just make it work', I had the SQL Server firewall open to internet. The App Service gets a connection string from the app service, and uses that to connect to .database.windows.net.

So far, I can only see two possibilities:

1) Specifically let the outbound IPs from the App Service through the firewall of the SQL Server. This is risky, as the IPs may change and I have to change them every time it happens.

2) Put SQL Server and App Service in a vnet.

I tried 2), and it kinda failed. I added my vnet to the allowed vnets on the SQL Server firewall's settings, and denied internet connection.
Then I upgraded the App Service from B1 to S1 because apparently my 11€ App Service isn't allowed on a vnet (this is also a reason I'm looking for another solution here).
This worked before, but now I changed some things around to try stuff, and after changing back it doesn't work anymore. I'm not sure why, the settings are the same.

So my problem from the beginning is still here:

How to properly, securely connect an App Service to a SQL Server DB?

Update:

So I'm going with the vnet/endpoint solution for now, but it won't work.

Current architecture

  • I have the WebAPI in a vnet subnet without NSGs, I can access that from the internet (swagger).
  • The vnet has the Service Endpoint Microsoft.Sql activated for that subnet.
  • The WebAPI tries to connect to the SQL Serverwith a connection string. I will change this to Azure AD once I get around to learn that. Gotta make the networking work for now.
  • The SQL Server Firewall:
    • denies public access
    • allows azure resource access
    • allows access via the subnet. Service Endpoint Status shows enabled.

SQL Server firewall setup:

Firewall settings

Metallkiller
  • 171
  • 3
  • 8

1 Answers1

2

You've really already hit the options you have for this:

  1. Allow access using the "allow access to all Azure services" checkbox, not recommended
  2. Allow access to the set of outbound IP's for your web app
  3. Join your web app to a vNet and use service endpoints to lock down SQL to that vNet

Unless you want to look at an App Service Environment, which is a single tenanted solution deployed in your vNet (and very expensive) then those are your options.

I would also recommend you look at using Managed Identity to authenticate the web app against the SQL database rather than using a connection string.

Sam Cogan
  • 38,736
  • 6
  • 78
  • 114
  • 1. Doesn't actually work for me. I have public access denied, and azure access allowed, but my app service doesn't get access. I'm jsut trying this one to better understand. 2. Seems cumbersome since I always have to update the IPs? 3. I can't get my private endpoint deployed, getting InternalServerError with message 'Bad Request' from the portal. Ideas where I can learn how to do that one properly? – Metallkiller Apr 04 '20 at 14:24
  • A couple of points. First, your web app IPs should be static for the life of your web applicaiton. Second, when you say private endpoint, are you trying to deploy private endpoint for SQL? – Sam Cogan Apr 04 '20 at 14:25
  • Yes, I go to the SQL Server -> Security/Private endpoint connection. You sound like I'm doing it wrong? This web app is gonna be used very sparsely, will the IPs change as soon it is shut down due to inactivity? – Metallkiller Apr 04 '20 at 14:27
  • 1
    No, the web app IP's wont change if the web app worker shuts down, only if you deleted and recreate the web app. Using private link for SQL should work, and can be used for what you are talking about, however you might want to start simple and just use service endpoints to lock down SQL, get it working then try private link – Sam Cogan Apr 04 '20 at 14:29
  • So I built what I thought I'd learned in an MS learn module about vnet and service endpoints. It seems there is something still missing. I described my setup in an update to the post. Any idea what's wrong? If we can't get this to work I guess I could live with allowing the IPs from the App Service, although that seems like the weakest solution and I don't learn anything. – Metallkiller Apr 05 '20 at 08:40
  • Can you post a pic of your sql firewall setup please. If that doesn’t show anything then I’ll look at replicating your setup to test but it won’t be till later today – Sam Cogan Apr 05 '20 at 08:45
  • Added my firewall setup, hope it helps. Don't worry about the time, I'm already surprised about your basically instant responses :D – Metallkiller Apr 05 '20 at 09:00
  • Ah ok, it’s the “deny public access” flag that’s the problem. Service endpoints still count as public access, it’s just locked down to tour vNet via the virtual networks section, so you need to turn that off. This setting is for when your using private link, when access from your vNet is counted as private. – Sam Cogan Apr 05 '20 at 09:17
  • Oh my, that was indeed it! I thought that switch basically gave the whole internet access to my SQL Server. I flipped it, WebAPI got access, local PC doesn't have access. Thanks so much for your help! Next stops: private endpoints and AD. – Metallkiller Apr 05 '20 at 10:26
  • 1
    Yeah that switch is just to turn on or off external access at all, then it’s further filtered by the firewall/service endpoints. If you want to lock it down further look at using private link, then you can turn this on. Glad it’s all working! – Sam Cogan Apr 05 '20 at 10:27
  • Yeah I'm already fighting with a private endpoint, since I can apparently either connect the private endpoint or the WebAPI to a subnet, but not both. Back to MS learn I guess. – Metallkiller Apr 05 '20 at 10:32
  • Yeah I think you’d need to create a second subnet for the web app, but that should be simple. – Sam Cogan Apr 05 '20 at 16:02
  • Do I understand that right, I have to do my own DNS? Even after removing all service endpoints form the vnet? Right now, even the `.privatelink.database.windows.net` resolves to the public IP-adress instead of the private one. I'd expect azure to point me to the private endpoint first from inside the vnet. – Metallkiller Apr 05 '20 at 17:46
  • 1
    You either need to add an entry to your own dns or use the azure private dns option that you get when setting up the private endpoint – Sam Cogan Apr 05 '20 at 20:33
  • Seemed simple enough - until I got "Private DNS for the currently chosen resource configuration is not supported." on the disabled private dns integration field and can't actually find that on google. I feel like tihs is evolving to a whole now question. – Metallkiller Apr 05 '20 at 20:43