Azure pipeline for Web App, SQL Database, Private Endpoint
Published: 2022-01-04 04:11:10

# 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](https://github.com/koheikawata/network-sample-app) demonstrate build and deployment through Azure Pipelines.

# TOC
- [Key points](#key-points)
  - [Deployment dependencies](#deployment-dependencies)
  - [App Service](#web)
  - [SQL Database](#sql)
  - [Key Vault](#kv)

# Key points <a name="key-points"></a>
Here are two architectures, [TravelApi](https://github.com/koheikawata/network-sample-app/tree/main/src/TravelApi) with Application Gateway and Web App, [TravelApiSql](https://github.com/koheikawata/network-sample-app/tree/main/src/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](https://dev.to/koheikawata/deployment-pipeline-of-application-gateway-with-app-service-1011). I would like to discuss the key points when you add SQL Database and Private Endpoint to the first architecture (TravelApi to TravelApiSql).

**TravelApi**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pfdgsyb686dyjhqydylz.png)
 
**TravelApiSql**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r6n4bljnr9yhp1yjk8vr.png)

## Deployment dependencies <a name="deployment-dependencies"></a>
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](https://github.com/koheikawata/network-sample-app/blob/main/arm-templates/azuredeploy-travelapi.json), the deployment dependencies are described like below.

**TravelApi deployment dependencies**
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/raatnifx2hupte9jdqal.png)

For [TravelApiSql deployment template](https://github.com/koheikawata/network-sample-app/blob/main/arm-templates/azuredeploy-travelapisql.json), 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**
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i2c46okhmsrigjv2nf3e.png)
  
## App Service <a name="web"></a>
- **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](https://docs.microsoft.com/en-us/azure/virtual-network/vnet-integration-for-azure-services#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](https://docs.microsoft.com/en-us/azure/app-service/overview-vnet-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 <a name="sql"></a>
Private Endpoint for SQL Database requires four resources, `Network/privateEndpoints`, `Network/privateDnsZones`, `Network/privateDnsZones/A`, `Network/privateDnsZones/virtualNetworkLinks`.

**ARM template example** from [TravelApiSql](https://github.com/koheikawata/network-sample-app/blob/main/arm-templates/azuredeploy-travelapisql.json)
```
{
  "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](https://docs.microsoft.com/en-us/azure/devops/pipelines/agents/agents?view=azure-devops&tabs=browser#install).

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

## Key Vault <a name="kv"></a>
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](https://github.com/koheikawata/network-sample-app/blob/main/arm-templates/azuredeploy-travelapisql.json)
```
{
  "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](https://github.com/koheikawata/network-sample-app/blob/main/azure-devops/dacpac/dacpac-pipeline.yml) 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](https://docs.microsoft.com/en-us/azure/devops/pipelines/agents/agents?view=azure-devops&tabs=browser#install) to execute the DACPAC deployment.