tf-aks-sql-aad

This Terraform template enables authentication to Azure SQL Database using AAD Pod Identity from an AKS Cluster. The template is loosely based on this document with the following differences;

• We use PowerShell sqlserver module instead of sqlcmd utility. When applying the template from an Azure DevOps pipeline, PowerShell already exists on all hosted agents.
• We set the Service Principal under which Terraform is running, as the Azure AD Admin for SQL Server.
• When granting database roles to the managed identity, we create the external user via following T-SQL statement;

CREATE USER [<identity-name>] WITH default_schema=[dbo], SID=[<identity-sid>], TYPE=E;

instead of

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;

The later T-SQL statement requires [<identity-name>] to be a user principal and if a service principal/managed identity is specified, it will fail with the following error;

Principal 'abc' could not be found at this time. Please try again later.

Many thanks to my colleague Noel Bundick for pointing out to this solution based on the pseudo-documented SID hack.

Requirements

• Azure CLI
• kubectl
• PowerShell
• Terraform
• Terraform authenticated via Service Principal

Note: This template performs Azure AD role assignments required by AAD Pod Identity. Therefore the Service Principal used for Terraform authentication must be created with Owner privileges.

Azure resources

• Azure SQL Database
• User-Assigned Managed Identity
• AKS Cluster

Smoke Test

Once terraform apply has successfully completed, fill the following variables from the Terraform output;

export aad_pod_id_binding_selector="aad-pod-id-binding-selector"
export aks_cluster_name="aks-xxxxxx"
export rg_name="rg-xxxxxx"
export sql_db_name="sqldb-test"
export sql_server_fqdn="sql-xxxxxx"

Alternatively, you can execute the following;

eval $(terraform output | sed 's/^/export /; s/ = /="/g; s/$/"/')

Then;

./smoke_test.sh

The smoke test will create a test pod in the newly provisioned AKS cluster and will attempt to authenticate to the SQL DB from the pod using managed identity. Once authentication is successful it will perform DDL and CRUD operations to validate the database roles.