Connect Azure MySQL to Private Endpoint with Terraform

To connect an Azure MySQL Database, or other services in Azure, one of the most secure methods to do this is with a Private Endpoint. Microsoft document the architecture they recommend using an App Service connecting to a MySQL Server, which is good if you are using the Azure Portal, but there are some missing components if you are using Terraform.

Content

Design

Microsoft do give some more simple method to allow access to the MySQL Server, by whitelisting IP Addresses or allowing Azure Services Access. However, you can only whitelist with confidence if you have a fixed IP range and the allowing all access would open it up to all services within all subscriptions. You can see these methods on the Microsoft Documentation: https://docs.microsoft.com/en-us/azure/mysql/howto-connect-webapp

A better method is to lock the server off from the internet and allow access via a Private Endpoint in a Virtual Network. This design is references in the documentation here: https://docs.microsoft.com/en-us/azure/architecture/example-scenario/private-web-app/private-web-app. As you can see it is very simple and have few components, however, when you use the Portal to create these most are dynamically created through simple entries.

App Service 
Web App 
Configuration 
WEBSITE VNET ROUTE ALL = 1 
WEBSITE DNS SERVER 
Internet 
App Service 
Regional 
VNet 
Integration 
AppSvcSubnet 
(10.1.2.0/24) 
Vlrtual Network (10.1.0.0/16) 
Private Endpoint 
(10.1.1.4/32) 
PrivateLinkSubnet 
(10.1.1.0/24) 
SQL 
= 168.63.129.16 
DNS 
Private 
DNS Zones

I have then depicted the connectivity of these in a slightly different view that shows all the components and how we are going to connection them via Terraform.

App Service Plan 
App Service 
Virtual Network 
Application 
Subnet 
10120/24 
•o 
DNS Private zone 
privatelink.mysql.dataöase.azure.com 
Private Endpoint 
Subnet 
Private Endpoint 
MySQL 
Server 
Network Interface

Building with Terraform

In each section I will highlight any particular code, but the full example is at the end.

Virtual Network

This is the centre feature to the design that we will create first. You can also create the subnets at this time, but to break it down I am only going to create the VNet itself. With the VNet we are keeping it simple so we are only entering the name and the DNS IP Address, which we will use some hardcoded values that you can always change.

DNS

address_space       = ["10.1.0.0/16"]
dns_servers         = ["10.0.0.4", "10.0.0.5"]

MySQL Server

You probably can do this in another order, but I followed the same process in creating them in the Portal. Within this I have included dynamically creating the MySQL password. A key part is the ‘mysql_server_sku’ version as this requires to be a General Purpose version or above, which is what I have set the default to. You can also see where I have enforced the firewall to disallow public access with ‘public_network_access_enabled’.

MySQL SKU

variable "mysql_server_sku" {
  type        = string
  description = "MySQL Server SKU"
  default     = "GP_Gen5_2"
}

Dynamic Password

resource "random_password" "password" {
  length      = 20
  min_upper   = 2
  min_lower   = 2
  min_numeric = 2
  min_special = 2
}
 administrator_login_password      = var.mysql_server_password == "" ? random_password.password.result : var.mysql_server_password

MySQL Firewall Settings

variable "mysql_server_settings" {
  type = object({
    auto_grow_enabled                 = bool
    backup_retention_days             = number
    geo_redundant_backup_enabled      = bool
    infrastructure_encryption_enabled = bool
    public_network_access_enabled     = bool
    ssl_enforcement_enabled           = bool
    ssl_minimal_tls_version_enforced  = string
  })
  description = "MySQL Server Configuration"
  default = {
    auto_grow_enabled                 = true
    backup_retention_days             = 7
    geo_redundant_backup_enabled      = false
    infrastructure_encryption_enabled = false
    public_network_access_enabled     = false
    ssl_enforcement_enabled           = true
    ssl_minimal_tls_version_enforced  = "TLS1_2"
  }
}

Subnet

As the subnet will be used more then once, I have made this a custom Module. This can then be called for creating the Private Endpoint subnet and the Applications Subnet. The Private Endpoint Subnet requires there to be no Delegation set, but the Application Subnet does need it. Therefore, I have made that section of the module dynamic. In this example you can also see the subnets I am giving the Private Endpoint and Application.

Delegation

 dynamic "delegation" {
    for_each = var.subnet_delegation_name == "" ? [] : [1]
    content {
      name = var.subnet_delegation_name
      service_delegation {
        name    = var.subnet_delegation_type
        actions = var.subnet_delegation_actions
      }
    }
  }

Subnets

variable "private_endpoint_subnet" {
  type        = string
  description = "Azure Private Endpoint VNet Subnet Address"
  default     = "10.1.1.0/24"
}
variable "app_subnet" {
  type        = string
  description = "Azure Application Subnet Address"
  default     = "10.1.2.0/24"
}

Private Endpoint

As this contains a few components to link everything up, I have also put this into a custom Module. You can see the order of creation below, so you can get an idea of how they are built. I try to keep things dynamic so the connecting of the MySQL Server is part of an array, you can add more resources to this endpoint. Something that caught me out was the DNS Zone name in Terraform. Most every resource that has a ‘name’ can be anything you like within the boundaries of validation as it just gives a title to the resource, but the DNS Zone must be a valid link from the Azure Documentation, which in the Terraform variable below.

Creation Order

  1. Create Private DNS Zone
  2. Create Private Endpoint
  3. Create a Private Endpoint Connection
  4. Link the DNS Zone to the Virtual Network

Dynamic Resources

private_endpoint_service_connections = [
    {
      name                           = "${var.mysql_server_name}.privateEndpoint"
      private_connection_resource_id = azurerm_mysql_server.mysql_server.id
      subresource_names              = ["mysqlServer"]
      is_manual_connection           = false
    }
  ]

DNS Zone name

variable "dnszone_private_link" {
  type        = string
  description = "Validate Private Link URL https://docs.microsoft.com/en-us/azure/private-link/private-endpoint-dns"
}
resource "azurerm_private_dns_zone" "private-endpoint-dns-private-zone" {
  name                = var.dnszone_private_link
  resource_group_name = var.resource_group_name
}
dnszone_private_link = "privatelink.mysql.database.azure.com"

App Service

We can now add the connecting App Service by creating an App Plan with an App Service connected to a subnet within the same VNet. This has all the standard values and settings, which do not have much requirements to this solution. Two factors that do is the App Plan SKU, which just needs to be Standard or above and the other is the App Settings. Again making thing dynamic, I allow a variable to pass in custom App Settings, but we also need the Subnet settings added. These include the ‘WEBSITE_VNET_ROUTE_ALL’ and the ‘WEBSITE_DNS_SERVER which is set to the Azure DNS IP Address unless you have private DNS Server.

App Settings

locals {
  app_settings_subnet = {
    WEBSITE_VNET_ROUTE_ALL = 1
    WEBSITE_DNS_SERVER     = "168.63.129.16"
  }
  app_settings = merge(var.webapp_app_settings, local.app_settings_subnet)
}

End-to-end Code

You can view the full code on my GitHub Repository PureRandom

These are also two sites where I drew a lot of knowledge from, so I thought they deserved a mention.

If you do find any issues with the code, please message me. This was taken from a larger project so I might have missed one or two things 🙂

Reference Sites:

Create User on Azure MySQL Database with Terraform

After you create you Azure MySQL Server, you would like to create a Database, but for security you would like to have a new custom user added with permission. Unfortunately the Azure Providers do not provide this, so we need to use some other skills to get these users in.

First we can go ahead and create the MySQL Server and Database. We use the Random provider to generate a random password for the MySQL password, with the rest of the details as default. There are a few places I have put variables in to make it more flexible, which can be expanded on or reduced.

resource "random_password" "server_pwd" {
  length      = 20
  min_upper   = 2
  min_lower   = 2
  min_numeric = 2
  min_special = 2
}
resource "azurerm_mysql_server" "mysql_server" {
  name                = var.mysql_server_name
  location            = var.location
  resource_group_name = var.resource_group_name
  administrator_login          = "mysqladminun"
  administrator_login_password = random_password.server_pwd.result
  sku_name   = "B_Gen5_2"
  storage_mb = 5120
  version    = "5.7"
  auto_grow_enabled                 = true
  backup_retention_days             = 7
  geo_redundant_backup_enabled      = false
  infrastructure_encryption_enabled = false
  public_network_access_enabled     = true
  ssl_enforcement_enabled           = true
  ssl_minimal_tls_version_enforced  = "TLS1_2"
}
resource "azurerm_mysql_database" "mysql_database" {
  name                = var.database_name
  resource_group_name = var.resource_group_name
  server_name         = var.server_name
  charset             = var.database_charset
  collation           = var.database_collation
}

When creating the user it uses standard MySQL queries, therefore we not only need connections to the Database we also need to allow the running service through the MySQL Firewall. To do this we can update the Firewall with our IP Address. You can pass in the IP manually or through a different method, but here I am using a HTTP request to ‘http://ifconfig.me/ip‘ which returns the current IP of what is running the Terraform. We then update the Firewall settings with this IP Address.

data "http" "myip" {
  url = "http://ifconfig.me/ip"
}
resource "azurerm_mysql_firewall_rule" "mysql_firewall_clientip" {
  name                = "ClientIpAddress"
  resource_group_name = var.resource_group_nameE
  server_name         = var.mysql_server_name
  start_ip_address    = chomp(data.http.myip.body)
  end_ip_address      = chomp(data.http.myip.body)
}

At this point we would have an MySQL Server, MySQL Database and our service IP Address allowed on the Firewall. From here we can then use the MySQL provider in Terraform. https://www.terraform.io/docs/providers/mysql/index.html

First you need to setup the provider with you MySQL details. We assume the suffix of the endpoint URL as it is standard from Azure, but if you have a custom domain and/or port then you will need to update this. When I have used this it is in a different Terraform execution and therefore I get the details from Variables, Data Resources and from the Key Vault for the password. You will need to update these as per your setup.

provider "mysql" {
  endpoint = "${var.mysqlserver_name}.mysql.database.azure.com:3306"
  username = "${var.mysql_server_username}@${var.mysql_server_name}"
  password = var.mysql_server_password
  tls      = true
}

You can either pass in your own Username and Password for the details being added in the MySQL Server, or I use the Random Provider to generate these details.

resource "random_password" "database_pwd" {
  length      = 20
  min_upper   = 2
  min_lower   = 2
  min_numeric = 2
  min_special = 2
}
resource "random_string" "username" {
  length           = 5
  special          = false
  override_special = "/@£$"
}
locals {
  database_username = "dbuser_${random_string.username.result}"
  database_password = random_password.database_pwd.result
}

With them you can then create the User in the MySQL Server.

resource "mysql_user" "dbuser" {
  user               = var.database_user_name == "" ? local.database_username : var.database_user_name
  host               = "%"
  plaintext_password = local.database_password
}

And finally Grant the user permissions to the MySQL Database.

resource "mysql_grant" "useraccess" {
  user       = mysql_user.dbuser.user
  host       = mysql_user.dbuser.host
  database   = var.database_name
  privileges = ["SELECT", "UPDATE", "DELETE", "EXECUTE", "INSERT"]
}

There are a few places where you can increase flexibly by using more variables/logic and there are places you can remove them if it is going to be state. These are some of the variables I have used to configure the resources, which below is the variables.tf content.

Server

variable "resource_group_name" {
  type        = string
  description = "Resource Group Name"
}
variable "location" {
  type        = string
  description = "Resource Location"
}

variable "mysql_server_name" {
  type        = string
  description = "MySQL Server Name"
}
variable "mysql_server_username" {
  type        = string
  description = "MySQL Server Username"
  default     = "mysqladmin"
}
variable "mysql_server_password" {
  type        = string
  description = "MySQL Server Password. (If empty auto generated password will be set)"
  default     = ""
}
variable "mysql_server_sku" {
  type        = string
  description = "MySQL Server SKU"
  default     = "B_Gen5_2"
}
variable "mysql_server_storage" {
  type        = number
  description = "MySQL Server Storage in MB"
  default     = 5120
}
variable "mysql_server_version" {
  type        = string
  description = "MySQL Server Version"
  default     = "8.0"
}
variable "mysql_server_settings" {
  type = object({
    auto_grow_enabled                 = bool
    backup_retention_days             = number
    geo_redundant_backup_enabled      = bool
    infrastructure_encryption_enabled = bool
    public_network_access_enabled     = bool
    ssl_enforcement_enabled           = bool
    ssl_minimal_tls_version_enforced  = string
  })
  description = "MySQL Server Configuration"
  default = {
    auto_grow_enabled                 = true
    backup_retention_days             = 7
    geo_redundant_backup_enabled      = false
    infrastructure_encryption_enabled = false
    public_network_access_enabled     = false
    ssl_enforcement_enabled           = true
    ssl_minimal_tls_version_enforced  = "TLS1_2"
  }
}

Database

variable "resource_group_name" {
  type        = string
  description = "Resource Group Name"
}
variable "mysql_server_name" {
  type        = string
  description = "MySQL server Name"
}
variable "database_name" {
  type        = string
  description = "Database Name"
}
variable "database_user_name" {
  type        = string
  description = "MySQL Database User username"
  default     = ""
}
variable "database_user_permissions" {
  type        = list(string)
  description = "MySQL Database User Privileges"
  default     = ["SELECT", "UPDATE", "DELETE", "EXECUTE", "INSERT"]
}
variable "database_charset" {
  type        = string
  description = "Database Charset"
  default     = "utf8"
}
variable "database_collation" {
  type        = string
  description = "Database Collation"
  default     = "utf8_unicode_ci"
}

Unable to delete Azure Subnet due to Resources

With Azure Subnets there is an order to deleting the resources. You must disconnect the Virtual Network Subnets before deleting the resources, or you can’t delete the Subnet. This caused myself some issue, while using Terraform as I kicked a Destroy command and did it in the wrong order. However, I found a method on how to correct it.

If you have this issue then you will be presented with an error like this:

Failed to delete subnet
Failed to delete subnet ‘app_subnet’. Error: Subnet app_subnet is in use by cp-rg/providers/Microsoft.Network/virtualNetworks/cp-vnet/subnets/app_subnet/serviceAssociationLinks/AppServiceLink’>cp-vnet/app_subnet/AppServiceLink and cannot be deleted. In order to delete the subnet, delete all the resources within the subnet. See aka.ms/deletesubnet.

The standard method to correct this is to recreate the resource. If you can recreate the resource that was in the Subnet before, then you can reattach it to the subnet. This then brings the resources back to the state they were before. From here you can then Disconnect the Resource and then delete the Subnet.

However, in my case I did not remember what the original resources name was, which means I cannot create it like for like to fix the issue. You can still find the name or names out by using the AZ CLI, which you might also be able to use the API as well.

$rgname = "cp-rg"
$subnet = "app_subnet"
$vnet = "cp-vnet"
az network vnet subnet show --resource-group $rgname --name $subnet --vnet $vnet

This then returns all the details of the subnet, including the ‘serviceAssociationLinks’ like below inlcuding the names of the services.

{
 "serviceAssociationLinks": [
    {
      "allowDelete": false,
      "etag": "W/\"00000000-0000-0000-0000-000000000000\"",
      "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/cp-rg/providers/Microsoft.Network/virtualNetworks/cp-vnet/subnets/app_subnet/serviceAssociationLinks/AppServiceLink",
      "link": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/cp-rg/providers/Microsoft.Web/serverfarms/cp-asp-666",
      "linkedResourceType": "Microsoft.Web/serverfarms",
      "locations": [],
      "name": "AppServiceLink",
      "provisioningState": "Succeeded",
      "resourceGroup": "cp-rg",
      "type": "Microsoft.Network/virtualNetworks/subnets/serviceAssociationLinks"
    }
  ]
}

You can now recreate the resource and delete the subnet.

DotNet User Secrets Feature

A little unknown feature of dotnet is User Secrets. This is used to create local App Setting overrides to set values for local usage. This can be a handy and powerful tool for keeping your local setup separate from checking in code.

You can find more details on the Microsoft Documentation here https://docs.microsoft.com/en-us/aspnet/core/security/app-secrets

The goal of this feature is to overwrite your App Settings with local values. For example if you have a connection string within your JSON, you are not going to want your local Username/Password stored in there to be checked. You also don’t want to be pulling other peoples settings down and having to keep changing them. Therefore, with this feature you set your values on your local machine in a different file and they get overridden, not overwritten, so they will work for your but never be checked in.

Within your project location you can run the ‘init’ command, which will create a new node in the project file. This is called ‘UserSecretId’, which contains the ID for this project. When this runs it will use the ID to match up with where the secrets are stored.

The secrets are stored in the folder ‘C:/Users/[UserName]/AppData/Roaming/Microsoft/UserSecrets’ then in a directory with the ID as the directory name. Within this folder there is then a file called ‘secrets.json’ where you will store all the secrets in a json format. You can get more detail on how to format the name properties of your App Settings on the documentation.

When you run the ‘init’ command it doesn’t create this directory and file for you, so I whipped together a script below to generate the User Secret ID and to also create the required directory/file. Before I talk about that I will also show have to use User Secrets with Dotnet Core Console Apps.

This could be something I have done wrong, but when I create a Web Application and use the feature it just works with no extra effort. However, when I created a Console App it did not just work out the box. I found I needed to do a few things to get it working, which Stafford Williams talks about here

One part he missed was when using Dependency Injection to inject where to find the User Secrets ID in the Builder as per:

varbuilder=newConfigurationBuilder()
.AddJsonFile("appsettings.json",optional:false,reloadOnChange:true)
.AddJsonFile(envJson,optional:false,reloadOnChange:true)
.AddEnvironmentVariables()
.AddUserSecrets<Program>();

Create User Secrets

In the below code it accepts a single project path and a directory with many projects. It will find the project files and check if they have the User Secrets ID in the project.

If it doesn’t then it will go to the directory, run the ‘init’ command and then get the ID as well.

From there it can check/create the folders and files for the User Secrets.

Param (
    [string]$projectPath
)
$projects;
$filesCount = 0
if ($projectPath.EndsWith('.csproj')) {
    $projects = Get-ChildItem -Path $projectPath
    $filesCount = 1
}
else {
    
    if ($projectPath.EndsWith('/') -eq $false -or $projectPath.EndsWith('\') -eq $false) {
        $projectPath += "/";
    }
    $projects = Get-ChildItem -Path "$projectPath*.csproj" -Recurse -Force
    $filesCount = $projects.Length
}
Write-Host("Files Found $filesCount")
if ($filesCount -gt 0) {
    $userSecretsPath = "$ENV:UserProfile/AppData/Roaming/Microsoft/UserSecrets"
    if (!(Test-Path $userSecretsPath)) { 
        Write-Host("Create User Secrets Path")
        New-Item -ItemType directory -Path $userSecretsPath
    }
    $currentDir = [System.IO.Path]::GetDirectoryName($myInvocation.MyCommand.Definition)
    foreach ($project in $projects) {
        Write-Host(" ")
        Write-Host("Current Project $project")
        [xml]$fileContents = Get-Content -Path $project
        if ($null -eq $fileContents.Project.PropertyGroup.UserSecretsId) { 
            Write-Host("User Secret ID node not found in project file")
            Set-Location $project.DirectoryName
            dotnet user-secrets init
            Set-Location $currentDir
            Write-Host("User Secret Create")
            [xml]$fileContents = Get-Content -Path $project
        }
        $userSecretId = $fileContents.Project.PropertyGroup.UserSecretsId
        Write-Host("User Secret ID $userSecretId")
        if ($userSecretId -ne ""){
            $userSecretPath = "$userSecretsPath/$userSecretId"
            if (!(Test-Path $userSecretPath)) { 
                New-Item -ItemType directory -Path $userSecretPath
                Write-Host("User Secret ID $userSecretId Path Created")
            }
            $secretFileName = "secrets.json"
            $secretPath = "$userSecretsPath/$userSecretId/$secretFileName"
            if (!(Test-Path $secretPath)) {   
                New-Item -path $userSecretPath -name $secretFileName -type "file" -value "{}"
                Write-Host("User Secret ID $userSecretId secrets file Created")
            }
            Write-Host("User Secrets path $secretPath")
        }
    }
}

Automatic Change Checking on Pipeline Stages

Azure DevOps has the ability to add multiple stage approval styles like human intervention, Azure Monitoring and schedules. However, there is not the ability to value if a source has changed before triggering a Stage. This can be done on the Pipeline level, but not the Stage level where it can be helpful to have sometimes. I have a PowerShell solution on how this can be done.

The problem to solve came when I had a Pipeline that build multiple Nuget Packages. Without having a human intervention approval button on each of the stages to publish the Nuget Package, they would Publish each time even if there was not changes. This would then cause the version number in the Artifacts to keep increasing for no reason.

Therefore, we wanted a method to automatically check if there has been a change in a particular source location, before allowing it to publish the Package. This has been done using PowerShell, to get the requested branch changes through Git. It can get all the names of the changed files, which included the full path, then we can check what it contains.

The code below gets the Diff files, looping through each of them while checking if it matches the provided path. If it does then it can set the global variable to say there has been changes. I did add an option to break on the first found item, but you could leave it to keep going and leave a log of all the files changed. In my case, I just wanted to know if there has or has not been a change.

$files = $(git diff HEAD HEAD~ --name-only)
$changedFiles = $files -split ' '
$changedCount = $changedFiles.Length
Write-Host("Total changed $changedCount")
$hasBeenChanged = $false
For ($i = 0; $i -lt $count; $i++) {
    $changedFile = $changedFiles[$i]
    if ($changedFile -like "${{parameters.projectPath}}") {
        Write-Host("CHANGED: $changedFile")
        $hasBeenChanged = $true
        if (${{parameters.breakOnChange}} -eq 'true')
            break
    }
}

You can then set the outcome as an output variable of the task for usage later as per this Template below:

- name: 'projectPath'
    type: string
  - name: 'name'
    type: string
  - name: 'breakOnChange'
    type: string
    default: 'true'
steps:
  - task: PowerShell@2
    name: ${{parameters.name}}
    displayName: 'Code change check for ${{parameters.name}}'
    inputs:
      targetType: 'inline'
      script: |
        $files = $(git diff HEAD HEAD~ --name-only)
        $changedFiles = $files -split ' '
        $changedCount = $changedFiles.Length
        Write-Host("Total changed $changedCount")
        $hasBeenChanged = $false
        For ($i = 0; $i -lt $count; $i++) {
            $changedFile = $changedFiles[$i]
            if ($changedFile -like "${{parameters.projectPath}}") {
                Write-Host("CHANGED: $changedFile")
                $hasBeenChanged = $true
                if (${{parameters.breakOnChange}} -eq 'true')
                break
            }
        }
        if ($hasBeenChanged -eq $true) {
            Write-Host "##vso[task.setvariable variable=IsContainFile;isOutput=true]True"
            break
        }
        else {
            Write-Host "##vso[task.setvariable variable=IsContainFile;isOutput=true]False"
        }

Once you have that output variable, it can be used throughout your pipeline as a condition on Stages, Jobs and Task, plus more. Here I use it on a Stage to check before it is run:

- stage: '${{parameters.projectExtension}}Build'
    displayName: ' Nuget Stage'
    condition: eq(variables['${{parameters.name}}.IsContainFile'], 'true')