Azure pipeline for Web App, SQL Database, Private Endpoint

Summary

This article shares about important points when you deploy Azure Application Gateway, Azure App Service, Azure SQL Database, Azure Key Vault within Virtual Network linked through Private Endpoints. The code examples demonstrate build and deployment through Azure Pipelines.

Key points

Here are two architectures, TravelApi with Application Gateway and Web App, TravelApiSql additionally with Key Vault and SQL Database linked through Private Endpoint. The first architecture is already discussed in the previous article Deployment pipeline of Application Gateway with App Service. I would like to discuss the key points when you add SQL Database and Private Endpoint to the first architecture (TravelApi to TravelApiSql).

TravelApi

TravelApiSql

Deployment dependencies

You need to plan the order of Azure resource deployment in the deployment pipeline. For example, KeyVault/vaults/accessPolicies should follow Web/sites because it cannot assign the object ID before it is created.

Deployment dependency example

{
  "type": "Microsoft.Web/sites",
  "name": "[variables('appsrv_name')]",
  "identity": {
      "type": "SystemAssigned"
  }
},
{
  "type": "Microsoft.KeyVault/vaults/accessPolicies",
  "dependsOn": [
    "[resourceId('Microsoft.Web/sites', variables('appsrv_name'))]"
  ],
  "properties": {
    "accessPolicies": [
      {
        "objectId": "[reference(resourceId('Microsoft.Web/sites', variables('appsrv_name')), '2021-03-01', 'Full').identity.principalId]"
      }
    ]
  }
},

For TravelApi deployment template, the deployment dependencies are described like below.

TravelApi deployment dependencies

For TravelApiSql deployment template, it gets more complex and you need to strategize it. Here are some key points.

Application Gateway needs KeyVault/vaults/accessPolicies beforehand because it has to access a SSL certificate.
Web/sites needs Network/virtualNetworks beforehand because of App Service Virtual Network integration.

TravelApiSql deployment dependencies

App Service

Inbound: you can choose from Service Endpoint or Private Endpoint. The difference between Service Endpoint and Private Endpoint is described here Compare Private Endpoints and Service Endpoints. The important points are below. TravelApi and TravelApiSql use Service Endpoint.

Service Endpoint vs Private Endpoint for App Service

	Service Endpoint	Private Endpoint
Available to all tiers	Yes	No (PremiumV2, PremiumV3, IsolatedV2 required)
Outbound traffic	No (Vnet integration required)	No (Vnet integration required)
DNS configuration	No	Yes
Azure backbone network	Yes	Yes
Disable Public IP address	No	Yes

Service Endpoint for App Service configuration example

{
  "type": "Microsoft.Web/sites",
  "properties": {
    "siteConfig": {
      "ipSecurityRestrictions": [
        {
          "vnetSubnetResourceId": "[resourceId('Microsoft.Network/virtualNetworks/subnets/', variables('vnet_name'), variables('subnet_name_agw'))]",
          "action": "Allow",
          "priority": 100,
          "name": "AllowAppGatewaySubnet"
        }
      ]
    }
  }
}

Outbound: Virtual Network Integration that requires a dedicated subnet.

{
  "type": "Microsoft.Web/sites",
  "properties": {
    "virtualNetworkSubnetId": "[resourceId('Microsoft.Network/virtualNetworks/subnets/', variables('vnet_name'), variables('subnet_name_web'))]",
  }
},

SCM site (Kudu console) In order to restrict the web deploy endpoint and Kudu console, it has to have scmIpSecurityRestrictions configuration.

{
  "type": "Microsoft.Web/sites",
  "properties": {
    "siteConfig": {
      "scmIpSecurityRestrictions":[
        {
          "ipAddress": "Any",
          "action": "Deny",
          "priority": 2147483647,
          "name": "Deny all",
          "description": "Deny all access"
        }
      ]
    }
  }
}

Once you restrict all inbound to SCM, it would cause problems when a web app code is deployed. You have to open the firewall IP restriction rule, deploy a web app code, and close the firewall again. In the code example of this article, it does not restrict SCM inbound.

SQL Database

Private Endpoint for SQL Database requires four resources, Network/privateEndpoints, Network/privateDnsZones, Network/privateDnsZones/A, Network/privateDnsZones/virtualNetworkLinks.

ARM template example from TravelApiSql

{
  "type": "Microsoft.Network/privateEndpoints",
  "apiVersion": "2021-03-01",
  "name": "[variables('pe_name_sql')]",
  "location": "[variables('location')]",
  "dependsOn": [
    "[resourceId('Microsoft.Network/virtualNetworks', variables('vnet_name'))]",
    "[resourceId('Microsoft.Sql/servers', variables('sqlserver_name'))]"
  ],
  "properties": {
    "privateLinkServiceConnections": [
      {
        "name": "[variables('pe_name_sql')]",
        "properties": {
          "privateLinkServiceId": "[resourceId('Microsoft.Sql/servers', variables('sqlserver_name'))]",
          "groupIds": [
            "sqlServer"
          ]
        }
      }
    ],
    "subnet": {
      "id": "[resourceId('Microsoft.Network/virtualNetworks/subnets', variables('vnet_name'), variables('subnet_name_sql'))]"
    }
  }
},
{
  "type": "Microsoft.Network/privateDnsZones",
  "apiVersion": "2020-06-01",
  "name": "[variables('dns_name_sql')]",
  "location": "global"
},
{
  "type": "Microsoft.Network/privateDnsZones/A",
  "apiVersion": "2020-06-01",
  "name": "[concat(variables('dns_name_sql'), '/', variables('sqlserver_name'))]",
  "dependsOn": [
    "[resourceId('Microsoft.Network/privateEndpoints',variables('pe_name_sql'))]",
    "[resourceId('Microsoft.Network/privateDnsZones', variables('dns_name_sql'))]"
  ],
  "properties": {
    "ttl": 3600,
    "aRecords": [
      {
        "ipv4Address": "[reference(resourceId('Microsoft.Network/privateEndpoints',variables('pe_name_sql')), '2021-03-01', 'Full').properties.customDnsConfigs[0].ipAddresses[0]]"
      }
    ]
  }
},
{
  "type": "Microsoft.Network/privateDnsZones/virtualNetworkLinks",
  "apiVersion": "2020-06-01",
  "name": "[concat(variables('dns_name_sql'), '/', variables('vnetlink_name_sql'))]",
  "location": "global",
  "dependsOn": [
    "[resourceId('Microsoft.Network/privateDnsZones', variables('dns_name_sql'))]",
    "[resourceId('Microsoft.Network/virtualNetworks', variables('vnet_name'))]"
  ],
  "properties": {
    "registrationEnabled": false,
    "virtualNetwork": {
      "id": "[resourceId('Microsoft.Network/virtualNetworks', variables('vnet_name'))]"
    }
  }
}

Another consideration is whether the public network access shouldbe denied. If publicNetworkAccess is disabled, it would cause problems when deploying a schema such as DACPAC to the SQL Database. You would need a VM inside the virtual network to manage the SQL Database. For Azure Pipelines, you would have to manage Self-hosted agent.

{
  "type": "Microsoft.Sql/servers",
  "properties": {
    "publicNetworkAccess": "Disabled"
  }
}

Key Vault

The same as SQL Database, Private Endpoint for Key Vault requires four resources, Network/privateEndpoints, Network/privateDnsZones, Network/privateDnsZones/A, Network/privateDnsZones/virtualNetworkLinks.

ARM template example from TravelApiSql

{
  "type": "Microsoft.Network/privateEndpoints",
  "apiVersion": "2021-03-01",
  "name": "[variables('pe_name_kv')]",
  "location": "[variables('location')]",
  "dependsOn": [
    "[resourceId('Microsoft.Network/virtualNetworks', variables('vnet_name'))]",
    "[resourceId('Microsoft.KeyVault/vaults', variables('keyvault_name'))]"
  ],
  "properties": {
    "privateLinkServiceConnections": [
      {
        "name": "[variables('pe_name_kv')]",
        "properties": {
          "privateLinkServiceId": "[resourceId('Microsoft.KeyVault/vaults', variables('keyvault_name'))]",
          "groupIds": [
            "vault"
          ]
        }
      }
    ],
    "subnet": {
      "id": "[resourceId('Microsoft.Network/virtualNetworks/subnets', variables('vnet_name'), variables('subnet_name_kv'))]"
    }
  }
},
{
  "type": "Microsoft.Network/privateDnsZones",
  "apiVersion": "2020-06-01",
  "name": "[variables('dns_name_kv')]",
  "location": "global"
},
{
  "type": "Microsoft.Network/privateDnsZones/A",
  "apiVersion": "2020-06-01",
  "name": "[concat(variables('dns_name_kv'), '/', variables('keyvault_name'))]",
  "dependsOn": [
    "[resourceId('Microsoft.Network/privateEndpoints',variables('pe_name_kv'))]",
    "[resourceId('Microsoft.Network/privateDnsZones', variables('dns_name_kv'))]"
  ],
  "properties": {
    "ttl": 3600,
    "aRecords": [
      {
        "ipv4Address": "[reference(resourceId('Microsoft.Network/privateEndpoints',variables('pe_name_kv')), '2021-03-01', 'Full').properties.customDnsConfigs[0].ipAddresses[0]]"
      }
    ]
  }
}, 
{
  "type": "Microsoft.Network/privateDnsZones/virtualNetworkLinks",
  "apiVersion": "2020-06-01",
  "name": "[concat(variables('dns_name_kv'), '/', variables('vnetlink_name_kv'))]",
  "location": "global",
  "dependsOn": [
    "[resourceId('Microsoft.Network/privateDnsZones', variables('dns_name_kv'))]",
    "[resourceId('Microsoft.Network/virtualNetworks', variables('vnet_name'))]"
  ],
  "properties": {
    "registrationEnabled": false,
    "virtualNetwork": {
      "id": "[resourceId('Microsoft.Network/virtualNetworks', variables('vnet_name'))]"
    }
  }
}

Similar to SQL Database, the private endpoint gives access endpoint from a Virtual Network but does not remove other public access. To make sure the access is only from the private endpoint, you have to enable the key vault firewall.

ARM template example

{
  "type": "Microsoft.KeyVault/vaults",
  "properties": {
    "networkAcls": {
      "bypass": "AzureServices",
      "defaultAction": "Deny",
      "ipRules": [],
      "virtualNetworkRules": []
    }
  }
}

If all public access is denied, it would cause problems downloading secret tasks in Azure Pipelines. For example, the code example of DACPAC deployment uses AzureKeyVault@2 task to retrieve a SQL login password. This task would not work when the all public access is denied. You would need Self-hosted agent to execute the DACPAC deployment.