How to Deploy an Azure SQL Server instance with a Private Endpoint using Terraform
In this story, we are going to deploy a SQL Server instance with a Private Endpoint, which is a private IP address within a specific VNet and subnet.
For very secure systems, located in healthcare, insurance, or banking environments, or for regulatory reasons, we can use a Private Link to secure the traffic to our databases.
Private Link allows us to connect to Azure SQL Server instances via a private endpoint and secures the connection between endpoints in Azure by eliminating data exposure to the public internet.
Note: this code is compatible and was tested with Terraform v13.x and Azure Provider v2.x
1. Configuring the Terraform Provider File
In this step, we are going to configure the Terraform and Azure providers. Providers create, manage, and update infrastructure resources, through API calls. We create the provider-variables.tf file and add the following code to the file:
variable "azure-subscription-id" {
type = string
description = "Azure Subscription ID"
}variable "azure-client-id" {
type = string
description = "Azure Client ID"
}variable "azure-client-secret" {
type = string
description = "Azure Client Secret"
}variable "azure-tenant-id" {
type = string
description = "Azure Tenant ID"
}
Then we create the provider-main.tf file and add the following code:
# Define Terraform provider
terraform {
required_version = ">= 0.13"
}# Configure the Azure provider
provider "azurerm" {
environment = "public"
version = ">= 2.7.0"
features {}
subscription_id = var.azure-subscription-id
client_id = var.azure-client-id
client_secret = var.azure-client-secret
tenant_id = var.azure-tenant-id
}
2. Creating Network Resources
The first step is to create all network resources required to host our SQL Server instance. We create a file called network-variables.tf and we added the following code:
variable "kopi-vnet-cidr" {
type = string
description = "The CIDR of the VNET"
}variable "kopi-db-subnet-cidr" {
type = string
description = "The CIDR for the Backoffice subnet"
}variable "kopi-private-dns" {
type = string
description = "The private DNS name"
}variable "kopi-dns-privatelink" {
type = string
description = "SQL DNS Private Link"
}
Then we create a file called network-main.tf and we added the following code:
# Create a resource group
resource "azurerm_resource_group" "kopi-rg" {
name = "kopi-sql-rg"
location = var.location
}# Create the VNET
resource "azurerm_virtual_network" "kopi-vnet" {
name = "kopi-sql-vnet"
address_space = [var.kopi-vnet-cidr]
location = azurerm_resource_group.kopi-rg.location
resource_group_name = azurerm_resource_group.kopi-rg.name
}# Create a DB subnet
resource "azurerm_subnet" "kopi-db-subnet" {
name = "kopi-sql-db-subnet"
address_prefixes = [var.kopi-db-subnet-cidr]
virtual_network_name = azurerm_virtual_network.kopi-vnet.name
resource_group_name = azurerm_resource_group.kopi-rg.name
enforce_private_link_endpoint_network_policies = true
}
3. Configuring the DNS Settings
In this step, we will create the private DNS records required. Create a file called network-dns.tf and add the following code:
# Create a Private DNS Zone
resource "azurerm_private_dns_zone" "kopi-private-dns" {
name = var.kopi-private-dns
resource_group_name = azurerm_resource_group.kopi-rg.name
}# Link the Private DNS Zone with the VNET
resource "azurerm_private_dns_zone_virtual_network_link" "kopi-private-dns-link" {
name = "${var.prefix}-${var.environment}-${var.app_name}-vnet"
resource_group_name = azurerm_resource_group.kopi-rg.name
private_dns_zone_name = azurerm_private_dns_zone.kopi-private-dns.name
virtual_network_id = azurerm_virtual_network.kopi-vnet.id
}# Create a DB Private DNS Zone
resource "azurerm_private_dns_zone" "kopi-endpoint-dns-private-zone" {
name = "${var.kopi-dns-privatelink}.database.windows.net"
resource_group_name = azurerm_resource_group.kopi-rg.name
}
4. Creating the SQL Server Instance and Database
In this step, we are going to create the SQL Server instance and the SQL Server database. Create a file called sqlserver-main.tf and add the following code:
# Create the SQL Server
resource "azurerm_mssql_server" "kopi-sql-server" {
name = "kopi-sql-server-instance" #NOTE: globally unique
resource_group_name = azurerm_resource_group.kopi-rg.name
location = azurerm_resource_group.kopi-rg.location
version = "12.0"
administrator_login = "kopisqladmin"
administrator_login_password = "MySQLAdm1nP@ssw0rd"
public_network_access_enabled = false
}# Create a the SQL database
resource "azurerm_sql_database" "kopi-sql-db" {
depends_on = [azurerm_mssql_server.kopi-sql-server]
name = "kopi-db"
resource_group_name = azurerm_resource_group.kopi-rg.name
location = azurerm_resource_group.kopi-rg.location
server_name = azurerm_mssql_server.kopi-sql-server.name
edition = "Standard"
collation = "Latin1_General_CI_AS"
max_size_bytes = "10737418240"
zone_redundant = false
read_scale = false
}
5. Creating the Endpoint
In this final piece of code, we will create a file called network-endpoint.tf and add the following code to it. The code will create the endpoint for an existing SQL Server instance, so it will be available first.
# Create a DB Private Endpoint
resource "azurerm_private_endpoint" "kopi-db-endpoint" {
depends_on = [azurerm_mssql_server.kopi-sql-server]
name = "kopi-sql-db-endpoint"
location = azurerm_resource_group.kopi-rg.location
resource_group_name = azurerm_resource_group.kopi-rg.name
subnet_id = azurerm_subnet.kopi-db-subnet.id private_service_connection {
name = "kopi-sql-db-endpoint"
is_manual_connection = "false"
private_connection_resource_id = azurerm_mssql_server.kopi-sql-server.id
subresource_names = ["sqlServer"]
}
}# DB Private Endpoint Connecton
data "azurerm_private_endpoint_connection" "kopi-endpoint-connection" {
depends_on = [azurerm_private_endpoint.kopi-db-endpoint] name = azurerm_private_endpoint.kopi-db-endpoint.name
resource_group_name = azurerm_resource_group.kopi-rg.name
}# Create a DB Private DNS A Record
resource "azurerm_private_dns_a_record" "kopi-endpoint-dns-a-record" {
depends_on = [azurerm_mssql_server.kopi-sql-server] name = lower(azurerm_mssql_server.kopi-sql-server.name)
zone_name = azurerm_private_dns_zone.kopi-endpoint-dns-private-zone.name
resource_group_name = azurerm_resource_group.kopi-rg.name
ttl = 300
records = [data.azurerm_private_endpoint_connection.kopi-endpoint-connection.private_service_connection.0.private_ip_address]
}# Create a Private DNS to VNET link
resource "azurerm_private_dns_zone_virtual_network_link" "dns-zone-to-vnet-link" {
name = "kopi-sql-db-vnet-link"
resource_group_name = azurerm_resource_group.kopi-rg.name
private_dns_zone_name = azurerm_private_dns_zone.kopi-endpoint-dns-private-zone.name
virtual_network_id = azurerm_virtual_network.kopi-vnet.id
}
6. The Output File
We created the output.tf file and add the following content.
output "sql_private_link_endpoint_ip" {
description = "SQL Private Link Endpoint IP"
value = data.azurerm_private_endpoint_connection.kopi-endpoint-connection.private_service_connection.0.private_ip_address
}output "sql_db" {
description = "SQL Server DB and Database"
value = "${azurerm_sql_database.kopi-sql-db.server_name}/${azurerm_sql_database.kopi-sql-db.name}"
}
7. Creating the Input Definition Variables File
In the last step, we are going to create input definition variables file terraform.tfvars and add the following code to the file:
location = "North Europe"azure-subscription-id = "complete-me"
azure-client-id = "complete-me"
azure-client-secret = "complete-me"
azure-tenant-id = "complete-me"kopi-vnet-cidr = "10.50.0.0/16"
kopi-db-subnet-cidr = "10.50.2.0/24"
kopi-private-dns = "kopicloud.lan"
kopi-dns-privatelink = "kopidb"
The complete code to Deploy an Azure SQL Server instance with a Private Endpoint using Terraform is here → https://github.com/guillermo-musumeci/terraform-azure-sqlserver-private-endpoint
And that’s all folks. If you liked this story, please show your support by 👏 this story. Thank you for reading!
