In this post we will explore how we can connect from a container / pod running in an Azure Kubernetes Service cluster (configured with Calico policies from earlier article) to a SQL Server database running in Azure SQL Server PAAS through a private endpoint.

Prerequisites

• An Azure account and a valid subscription.
• An AKS cluster deployed with Azure AD authentication, Azure CNI and Calico.
• An Azure SQL Server database.
• A Docker image containing SQL Server tools for the tests. We will use the official Mssql Tools image from Microsoft.

Setup AKS cluster

Create the development namespace in the AKS cluster.

apiVersion: v1
kind: Namespace
metadata:
  name: development
  labels:
    env: dev

Apply the deployment YAML file.

1

kubectl apply -f development-namespace.yaml

Now apply Calico policies to restrict the communications in the development namespace.

calicoctl apply -f 01-deny-all.yaml
calicoctl apply -f 02-allow-dns-egress.yaml
calicoctl apply -f 03-allow-all-development-ns.yaml

Setup for Azure SQL Server private endpoint

The Azure SQL Server server will be assigned a private endpoint in a dedicated subnet of the virtual network used to host the AKS cluster.

Creating a private endpoint will make our Azure SQL Server server available through an IP address in the space of the corresponding subnet of the virtual network used to host our AKS cluster nodes / pods.

We must also take into account the fact that most of PAAS services do use TLS certificates to secure communications. Those certificates are issued for the PAAS public endpoint domain name. For Azure SQL Server it’s <yourservername>.database.windows.net. Trying to access our database server with the private IP address will fail because of certificate mismatch. To be able to use the same name to access our database server, we configure DNS integration for our private endpoint using the Terraform code below.

# Create a Private DNS Zone for the database private endpoint
# in the AKS resource group
resource "azurerm_private_dns_zone" "dbpe_dns_zone" {
  name                = "privatelink.database.windows.net"
  resource_group_name = azurerm_resource_group.this.name
}

# Link the DNS Zone to the AKS VNET
resource "azurerm_private_dns_zone_virtual_network_link" "dns_zone_vnet_link" {
  name                  = "vnet_link"
  resource_group_name   = azurerm_resource_group.this.name
  private_dns_zone_name = azurerm_private_dns_zone.dbpe_dns_zone.name
  virtual_network_id    = azurerm_virtual_network.this.id
  registration_enabled  = "false"
}

# Create a Private Endpoint for the Azure SQL Server server
resource "azurerm_private_endpoint" "pesql" {
  name                = "sqlsrv-ep-dev"
  location            = "West Europe"
  resource_group_name = azurerm_resource_group.this.name
  subnet_id           = azurerm_subnet.paas_pe.id
  private_service_connection {
    name                           = "sqlsrv-psc-dev"
    is_manual_connection           = false
    private_connection_resource_id = azurerm_sql_server.this.id
    subresource_names              = ["sqlServer"]
  }
  private_dns_zone_group {
    name                 = "private-dns-zone-group"
    private_dns_zone_ids = [azurerm_private_dns_zone.dbpe_dns_zone.id]
  }
}

Since Terraform Azure Resource Provider version 2.0, we can associate a private dns zone to a private endpoint using the private_dns_zone_group block. This will register a DNS A record for the resource designed by the private endpoint in the corresponding private DNS zone. This DNS zone must also be linked to the virtual network of the AKS cluster to enable name resolution to work correctly.

Note: this is only for testing purpose. Going to production would require a carefully planned / designed networking strategy for the creation of PAAS related Private Endpoints.

Setup Calico network policy to allow SQL Server traffic

The last step is to create and apply a new Calico network policy allowing traffic from pods in our development namespace to the IP address of our Azure SQL Server private endpoint. We will also restrict the traffic to TCP port 1433 which is the standard communication port for SQL Server connections.

apiVersion: projectcalico.org/v3
kind: NetworkPolicy
metadata:
  name: sqlserver-allow
  namespace: development
spec:
  order: 3000
# Applies to all endpoints in the namespace
  selector: all()
  types:
  - Egress
  
  egress:
  - action: Allow
    protocol: TCP
    destination:
      nets:
      - 10.144.240.4/32
      ports:
      - 1433

Apply the policy

calicoctl apply -f 05-allow-sqlserver-egress.yaml

Tests

Start a pod with the SQL Server Tools image from Microsoft and get to interactive bash.

kubectl run --rm -it --image=mcr.microsoft.com/mssql-tools sqltools -n development

Run the following command to test the connectivity to your SQL Server database

sqlcmd -S <yoursqlservername>.database.windows.net -U <SQLadminuser> -P <SQLadminpassword>

1> select name from sys.databases;
2> go
name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                          
seb-aks-db-dev                                                                                                                  

(2 rows affected)

Test is successful !

Conclusion

We have thus successfully configured a private connection from one pod running in our AKS cluster to an Azure SQL Server database through a private endpoint. This has the advantage to not expose the traffic between our pod and the SQL Server database to the public internet.

As already mentioned, this setup is ok for a test but when designing such a solution for production, we would have to carefully design the implementation of private endpoints for PAAS services like Azure SQL Server or Azure BLOB Storage. The private endpoints are available to all virtual networks peered with the one they are defined into. Working in hub/spoke scenario enables to take advantage of your hub to define private endpoints once that are available for all connected spokes and also configure DNS private zone more easily but this is another story.