azure web app + sql database private endpoint

Question

I am trying to have a better understand how a web app and a sql can play together inside a vnet and different subnets, but Microsoft documentation is confusing me quiet a lot.

To start working on this, I got this terraform script and changed some stuff inside it.

terraform {
  required_providers {
    azurerm = {
      source = "hashicorp/azurerm"
      version = "~>2.0"
    }
  }
}
provider "azurerm" {
  features {}
}

resource "azurerm_resource_group" "rg-hri-private-link" {
  name     = "appservice-rg"
  location = "westeurope"
}

resource "azurerm_virtual_network" "vnet" {
  name                = "vnet"
  location            = azurerm_resource_group.rg-hri-private-link.location
  resource_group_name = azurerm_resource_group.rg-hri-private-link.name
  address_space       = ["10.1.0.0/16"]
}

resource "azurerm_subnet" "integrationsubnet" {
  name                 = "integrationsubnet"
  resource_group_name  = azurerm_resource_group.rg-hri-private-link.name
  virtual_network_name = azurerm_virtual_network.vnet.name
  address_prefixes     = ["10.1.1.0/24"]
  delegation {
    name = "delegation"
    service_delegation {
      name = "Microsoft.Web/serverFarms"
    }
  }
}

resource "azurerm_subnet" "endpointsubnet" {
  name                 = "endpointsubnet"
  resource_group_name  = azurerm_resource_group.rg-hri-private-link.name
  virtual_network_name = azurerm_virtual_network.vnet.name
  address_prefixes     = ["10.1.2.0/24"]
  enforce_private_link_endpoint_network_policies = true
  service_endpoints = [
  "Microsoft.Sql"
  ]
  delegation {
    name = "delegation"
    service_delegation {
      actions = [
        "Microsoft.Network/virtualNetworks/subnets/join/action",
        "Microsoft.Network/virtualNetworks/subnets/prepareNetworkPolicies/action",
        "Microsoft.Network/virtualNetworks/subnets/unprepareNetworkPolicies/action",
      ]
      name = "Microsoft.Sql/managedInstances"
    }
  }
}

resource "azurerm_app_service_plan" "appserviceplan" {
  name                = "appserviceplan"
  location            = azurerm_resource_group.rg-hri-private-link.location
  resource_group_name = azurerm_resource_group.rg-hri-private-link.name

  sku {
    tier = "Premiumv2"
    size = "P1v2"
  }
}

resource "azurerm_app_service" "frontwebapp" {
  name                = "frontweba"
  location            = azurerm_resource_group.rg-hri-private-link.location
  resource_group_name = azurerm_resource_group.rg-hri-private-link.name
  app_service_plan_id = azurerm_app_service_plan.appserviceplan.id

  app_settings = {
    "WEBSITE_DNS_SERVER": "168.63.129.16",
    "WEBSITE_VNET_ROUTE_ALL": "1"
  }
}

resource "azurerm_app_service_virtual_network_swift_connection" "vnetintegrationconnection" {
  app_service_id  = azurerm_app_service.frontwebapp.id
  subnet_id       = azurerm_subnet.integrationsubnet.id
}

resource "azurerm_sql_server" "srv-backend" {
  administrator_login          = "username"
  administrator_login_password = "password!"
  location                     = azurerm_resource_group.rg-hri-private-link.location
  name                         = "serverbackend"
  resource_group_name          = azurerm_resource_group.rg-hri-private-link.name
  version                      = "12.0"
}

resource "azurerm_sql_virtual_network_rule" "privatesql" {
  name                = "azuresqldatabaseendpoint"
  resource_group_name = azurerm_resource_group.rg-hri-private-link.name
  server_name         = azurerm_sql_server.srv-backend.name
  subnet_id           = azurerm_subnet.endpointsubnet.id
  ignore_missing_vnet_service_endpoint = true
}

resource "azurerm_sql_database" "sqlprivatedatabase" {
  location            = azurerm_resource_group.rg-hri-private-link.location
  name                = "sqlprivatedatabase"
  resource_group_name = azurerm_resource_group.rg-hri-private-link.name
  server_name         = azurerm_sql_server.srv-backend.name
}

resource "azurerm_private_dns_zone" "dnsprivatezone" {
  name                = "privatelink.azurewebsites.net"
  resource_group_name = azurerm_resource_group.rg-hri-private-link.name
}

resource "azurerm_private_dns_zone_virtual_network_link" "dnszonelink" {
  name = "dnszonelink"
  resource_group_name = azurerm_resource_group.rg-hri-private-link.name
  private_dns_zone_name = azurerm_private_dns_zone.dnsprivatezone.name
  virtual_network_id = azurerm_virtual_network.vnet.id
}

resource "azurerm_private_endpoint" "privateendpoint" {
  name                = "backwebappprivateendpoint"
  location            = azurerm_resource_group.rg-hri-private-link.location
  resource_group_name = azurerm_resource_group.rg-hri-private-link.name
  subnet_id           = azurerm_subnet.endpointsubnet.id

  private_dns_zone_group {
    name = "privatednszonegroup"
    private_dns_zone_ids = [azurerm_private_dns_zone.dnsprivatezone.id]
  }

  private_service_connection {
    name = "privateendpointconnection"
    private_connection_resource_id = azurerm_sql_server.srv-backend.id
    subresource_names = ["sqlServer"]
    is_manual_connection = false
  }
}

I create the following resources:

• Vnet
• integration subnet
• private endpoint subnet
• Web app configuration to consume private dns zone
• Connect the front end app with the integration subnet
• Create a backend sql server and database
• Create a private dns zone
• Link the zone to the vnet
• create a private endpoint backend to the endpoint subnet

The script run just fine. I deployed a web app template to test the connectivity between the the web app and the sql database in the backend and it works just fine.

But here where I am starting to get confused. My web app has its default url .azurewebsites.net and my sql database is still accessible from its hostname. Its seems that the dns zone is not doing anything in particular.

How can I test this.

My idea is to have a zero trust architecture to allow only and exclusively the fron end app to access the sql database in the inbound traffic, and in the outbound traffic of the sql database to allow it only to the front end web app.

How can I achieve this?