You might have heard about various authentication mechanisms are used for database access management securely. Azure SQL server also has its own authentication mechanism as single administer account which has control over the entire server. Other than this super admin, you can create second administrator account as Azure AD account. You can easily add AAD (Azure Active Directory) users as admin to Azure SQL via the Azure portal. However, if you want to deploy Azure SQL server via IaC let's look at using Terraform to add an AAD user as admin to the Azure SQL in this blog.
We can create resource group, Azure Key Vault to store SQL server password, Azure SQL server, Azure AD group using Terraform script. Main.tf of the sample script as follows.
terraform {
required_providers {
azurerm = {
source = "hashicorp/azurerm"
version = "~>2.46"
}
}
}
provider "azurerm" {
subscription_id = var.AzureSubscriptionId
client_id = var.AzureSPNAppId
client_secret = var.AzureSPNPwd
tenant_id = var.AzureTenantId
features {}
}
data "azurerm_client_config" "current" {}
resource "azurerm_resource_group" "sqlrg" {
name = "database-rg"
location = "West US"
}
data "http" "myip" {
url = "http://ipv4.icanhazip.com"
}
resource "random_password" "password" {
length = 16
special = true
override_special = "_%@"
}
# #vnet
resource "azurerm_virtual_network" "vnet" {
name = "vnet-pushpa"
resource_group_name = azurerm_resource_group.sqlrg.name
location = azurerm_resource_group.sqlrg.location
address_space = ["10.1.0.0/16"]
}
# #subnet
resource "azurerm_subnet" "defualtsnet" {
name = "snet-pushpa"
resource_group_name = azurerm_resource_group.sqlrg.name
virtual_network_name = azurerm_virtual_network.vnet.name
address_prefixes = ["10.1.0.0/24"]
service_endpoints = [ "Microsoft.KeyVault" ]
}
resource "azurerm_key_vault" "kv" {
name = "kv-pushpa-dev"
location = azurerm_resource_group.sqlrg.location
resource_group_name = azurerm_resource_group.sqlrg.name
tenant_id = data.azurerm_client_config.current.tenant_id
sku_name = "standard"
soft_delete_retention_days = 7
network_acls {
default_action = "Deny"
bypass = "AzureServices"
virtual_network_subnet_ids = [azurerm_subnet.defualtsnet.id]
ip_rules = [ "${chomp(data.http.myip.body)}/32" ]
}
access_policy {
tenant_id = data.azurerm_client_config.current.tenant_id
object_id = data.azurerm_client_config.current.object_id
key_permissions = [
"create",
"get",
]
secret_permissions = [
"set",
"get",
"delete",
"purge",
"recover"
]
}
}
resource "azurerm_key_vault_secret" "kvs" {
name = "sqlpwd"
value = random_password.password.result
key_vault_id = azurerm_key_vault.kv.id
}
resource "azurerm_sql_server" "sqldb" {
name = "mssqlserverpushpa"
resource_group_name = azurerm_resource_group.sqlrg.name
location = azurerm_resource_group.sqlrg.location
version = "12.0"
administrator_login = "sqladmin"
administrator_login_password = random_password.password.result
tags = {
environment = "production"
}
}
resource "azuread_group" "adg" {
display_name = "sqladgroupsample"
}
resource "azurerm_sql_active_directory_administrator" "adadmin" {
server_name = azurerm_sql_server.sqldb.name
resource_group_name = azurerm_resource_group.sqlrg.name
login = "sampleadgroup"
tenant_id = data.azurerm_client_config.current.tenant_id
object_id = azuread_group.adg.object_id
}
Let's try to understand the sections of the script in detail.
We use Azure Key Vault to store administrator passwords. Hence, Terraform execution machine need to access the Azure Key Vault which is secured inside a virtual network(vnet) and subnet. To access the protected Key Vault via Terraform, we need to allow the execution machine public IP in the Azure Key Vault. Following script helps to get terraform execution machine public IP.
data "http" "myip" {
url = "http://ipv4.icanhazip.com"
}
Once the Azure SQL server is created, following script is used to add AAD user to it. We need to provide tenant id of the AAD and object id of the AD user or user group which going to be added as AAD admin for Azure SQL server.
resource "azurerm_sql_active_directory_administrator" "adadmin" {
server_name = azurerm_sql_server.sqldb.name
resource_group_name = azurerm_resource_group.sqlrg.name
login = "sampleadgroup"
tenant_id = data.azurerm_client_config.current.tenant_id
object_id = azuread_group.adg.object_id
}
Variable.tf of the script as follows.
# Authenticate
variable "AzureSubscriptionId" {
type = string
description = "Azure Subscription Id"
}
variable "AzureSPNAppId" {
type = string
description = "Azure Service Principle App Id"
}
variable "AzureSPNPwd" {
type = string
description = "Azure Service Principle Password"
}
variable "AzureTenantId" {
type = string
description = "Azure Tenat Id"
}
Once you execute the script, you would be able to see Azure SQL server added with AAD user as shown in the following image.
We have discussed how to deploy Azure SQL server with Active Directory user as Admin using Terraform in this post.
No comments:
Post a Comment