☁️ Azure SQL Managed Instance Entra Administrator Terraform Module

Names the Entra ID administrator of an Azure SQL Managed Instance and decides whether SQL authentication survives alongside it — targeting hashicorp/azurerm ~> 4.0.

• 🔐 Names the Entra ID (Azure AD) administrator of a SQL Managed Instance — a user, group or service principal.
• 🚪 Decides whether SQL authentication survives alongside it.
• 🧬 One resource, two ARM records. The administrator and the Entra-only setting are separate sub-resources; both IDs are emitted.
• 🔀 azuread_authentication_only defaults to true here and to false in the provider — and the provider's zero value actively turns the setting off.
• ⏳ Its delete timeout is three hours, six times the create.

💡 Why it matters: the default is not a no-op either way. A bare resource block sends azuread_authentication_only = false unconditionally, so it disables Entra-only authentication on an instance that may have had it on. This module sends true instead — which is the secure value and a disruptive one, because it stops every SQL login on the instance working. Both directions deserve a decision, so this README states the cost of its own default as plainly as the risk of the alternative.

If this module saved you time:

• ⭐ Star the repository
• 💼 Connect on LinkedIn
• ☕ Buy me a coffee

flowchart TB
    MI["terraform-azurerm-mssql-managed-instance"]
    THIS["terraform-azurerm-mssql-managed-instance-active-directory-administrator"]
    TDE["terraform-azurerm-mssql-managed-instance-transparent-data-encryption"]
    SAP["terraform-azurerm-mssql-managed-instance-security-alert-policy"]
    KV["terraform-azurerm-key-vault"]
    SRV["terraform-azurerm-mssql-server"]

    MI -->|"id"| THIS
    MI -->|"id"| TDE
    MI -->|"name and resource_group_name"| SAP
    KV -->|"key_ids or key_versionless_ids"| TDE
    MI -->|"identity_principal_id wraps the key"| KV
    THIS -.->|"Entra-only auth is a separate ARM record"| MI
    SRV -.->|"the logical server sets its admin inline"| THIS

    style THIS fill:#0078D4,stroke:#004578,color:#ffffff
    style MI fill:#004578,stroke:#004578,color:#ffffff
    style TDE fill:#F3F2F1,stroke:#8A8886,color:#201F1E
    style SAP fill:#F3F2F1,stroke:#8A8886,color:#201F1E
    style KV fill:#F3F2F1,stroke:#8A8886,color:#201F1E
    style SRV fill:#F3F2F1,stroke:#8A8886,color:#201F1E

The managed instance feeds its id here and to the encryption-protector module; the security alert policy takes the parent as a name instead, which is why that node's edge is labelled differently. The dotted edge back to the instance is the one to read carefully: Entra-only authentication is a separate ARM record under the instance, written by this same resource. The other dotted edge is a reminder that a logical SQL server sets its administrator inline on the server resource — there is no separate module for it.

flowchart TB
    MID["managed_instance_id (required, force-new)"]
    IDENT["login_username plus object_id plus tenant_id"]
    AAD["azuread_authentication_only (module TRUE, provider false)"]

    ADM["azurerm_mssql_managed_instance_active_directory_administrator.this"]

    OID["id"]
    OAAD["azuread_only_authentication_id"]
    OSQL["sql_authentication_still_permitted"]
    ODES["destroying_this_resource_re_enables_sql_authentication"]

    MID --> ADM
    IDENT --> ADM
    AAD --> ADM
    ADM --> OID
    ADM --> OAAD
    ADM --> OSQL
    ADM --> ODES

    style ADM fill:#0078D4,stroke:#004578,color:#ffffff
    style MID fill:#004578,stroke:#004578,color:#ffffff
    style IDENT fill:#F3F2F1,stroke:#8A8886,color:#201F1E
    style AAD fill:#F3F2F1,stroke:#8A8886,color:#201F1E
    style OID fill:#F3F2F1,stroke:#8A8886,color:#201F1E
    style OAAD fill:#F3F2F1,stroke:#8A8886,color:#201F1E
    style OSQL fill:#F3F2F1,stroke:#8A8886,color:#201F1E
    style ODES fill:#F3F2F1,stroke:#8A8886,color:#201F1E

Schema notes that bite:

• 🔴 One resource, two ARM APIs. The administrator record and the Entra-only authentication setting are separate sub-resources, written by two different clients in the same create and read back from two different APIs.
• 🔴 azuread_authentication_only has NO schema default and is sent unconditionally, so its zero value of false means a bare resource block turns Entra-only authentication off rather than leaving it alone. This module defaults it to true.
• 🔴 terraform destroy re-enables SQL authentication. The service refuses to remove an Entra administrator while Entra-only authentication is on, so the provider disables it first as a required step.
• 🔴 object_id is not force-new. Handing administration of the instance to a different principal is an ordinary in-place attribute change, rendered no more prominently in a plan than a display-name correction.
• ⚠️ The delete timeout is 180 minutes, against 30 for create and update and 5 for read. That is the provider's own value, and it is a signal about how long the service takes.
• ⚠️ login_username carries only a non-empty check and is never reconciled with object_id. A display name left stale after a group rename is accepted and simply misleads the next reader.
• ℹ️ There IS a requires-import guard on create — unlike the managed instance's security alert policy and vulnerability assessment, which silently overwrite. A clean apply here is evidence that no administrator existed.
• ℹ️ tenant_id is required here, while the logical server's azuread_administrator block defaults it to the provider's tenant. Same idea, two conventions.
• ℹ️ No tags, no location. Tag the managed instance instead.

🔴 Whoever can write this resource can hand instance administration to any principal, in place, with no replacement shown in the plan. That is a higher-privilege operation than the resource's size suggests.

⚠️ No directory permission is needed to name an administrator. Azure accepts an object ID without the Terraform principal being able to read it in Entra, so a typo in object_id is caught by nothing — it produces an administrator nobody can use.

🔒 No secret passes through this module. An administrator is identified here, never authenticated.

• The Microsoft.Sql resource provider registered on the subscription.
• An existing Azure SQL Managed Instance.
• An existing Entra ID user, group or service principal, and its object ID and tenant ID.
• 🔴 A cutover plan for SQL logins, if azuread_authentication_only stays at this module's default of true — they stop working at apply.
• The caller configures provider "azurerm" { features {} }, auth and subscription.

terraform-azurerm-mssql-managed-instance-active-directory-administrator/
├── providers.tf    # required_version + pinned azurerm; no provider block
├── variables.tf    # 6 inputs, deeply typed, with the identity and Entra-only rules
├── main.tf         # the single keystone `this`
├── outputs.tf      # id first, then both ARM record IDs, then the posture facts
├── README.md       # this file
├── SCOPE.md        # the cross-module contract
├── LICENSE         # MIT
└── .gitignore

provider "azurerm" {
  features {}
}

module "mi_entra_admin" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-managed-instance-active-directory-administrator.git?ref=v1.0.0"

  managed_instance_id = module.sqlmi.id
  login_username      = "sql-mi-admins"
  object_id           = var.sql_admin_group_object_id
  tenant_id           = var.tenant_id
}

Read example 2 before applying this. The default disables SQL authentication for every login on the instance.

Consumes

Emits

module "mi_entra_admin" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-managed-instance-active-directory-administrator.git?ref=v1.0.0"

  managed_instance_id = module.sqlmi.id
  login_username      = "sql-mi-admins"
  object_id           = var.sql_admin_group_object_id
  tenant_id           = var.tenant_id
}

module "mi_entra_admin" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-managed-instance-active-directory-administrator.git?ref=v1.0.0"

  managed_instance_id = module.sqlmi.id
  login_username      = "sql-mi-admins"
  object_id           = var.sql_admin_group_object_id
  tenant_id           = var.tenant_id

  # The default, stated explicitly so the plan reads as a decision.
  azuread_authentication_only = true
}

module "mi_entra_admin" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-managed-instance-active-directory-administrator.git?ref=v1.0.0"

  managed_instance_id = module.sqlmi.id
  login_username      = "sql-mi-admins"
  object_id           = var.sql_admin_group_object_id
  tenant_id           = var.tenant_id

  azuread_authentication_only = false
}

output "sql_logins_still_work" {
  value = module.mi_entra_admin.sql_authentication_still_permitted # true
}

output "the_two_records_this_writes" {
  value = {
    administrator = module.mi_entra_admin.id
    entra_only    = module.mi_entra_admin.azuread_only_authentication_id
  }
}

# Removing this module block does more than remove an administrator.

module "mi_entra_admin" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-managed-instance-active-directory-administrator.git?ref=v1.0.0"

  managed_instance_id = module.sqlmi.id
  login_username      = "sql-mi-admins-v2"
  object_id           = var.new_admin_group_object_id # <-- in-place change
  tenant_id           = var.tenant_id
}

module "mi_entra_admin" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-managed-instance-active-directory-administrator.git?ref=v1.0.0"

  managed_instance_id = module.sqlmi.id
  login_username      = "SQL MI Admins" # a label; spaces are fine
  object_id           = var.sql_admin_group_object_id
  tenant_id           = var.tenant_id
}

module "mi_entra_admin" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-managed-instance-active-directory-administrator.git?ref=v1.0.0"

  managed_instance_id = module.sqlmi.id
  login_username      = "sp-sqlmi-automation"
  object_id           = var.automation_sp_object_id
  tenant_id           = var.tenant_id
}

data "azurerm_client_config" "current" {}

module "mi_entra_admin" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-managed-instance-active-directory-administrator.git?ref=v1.0.0"

  managed_instance_id = module.sqlmi.id
  login_username      = "sql-mi-admins"
  object_id           = var.sql_admin_group_object_id
  tenant_id           = data.azurerm_client_config.current.tenant_id
}

module "mi_entra_admin" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-managed-instance-active-directory-administrator.git?ref=v1.0.0"

  managed_instance_id = module.sqlmi.id
  login_username      = "sql-mi-admins"
  object_id           = var.sql_admin_group_object_id
  tenant_id           = var.tenant_id

  timeouts = {
    create = "45m"
    delete = "180m" # the provider's own default -- do not shorten without a reason
  }
}

module "mi_entra_admin" {
  source   = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-managed-instance-active-directory-administrator.git?ref=v1.0.0"
  for_each = module.managed_instances

  managed_instance_id = each.value.id
  login_username      = "sql-mi-admins"
  object_id           = var.sql_admin_group_object_id
  tenant_id           = var.tenant_id
}

output "instances_where_sql_logins_still_work" {
  value = [for k, m in module.mi_entra_admin : k if m.sql_authentication_still_permitted]
}

module "mi_entra_admin" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-managed-instance-active-directory-administrator.git?ref=v1.0.0"

  managed_instance_id = module.sqlmi.id
  login_username      = "sql-mi-admins"
  object_id           = var.sql_admin_group_object_id
  tenant_id           = var.tenant_id
}

provider "azurerm" {
  features {}
}

data "azurerm_client_config" "current" {}

module "sqlmi" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-managed-instance.git?ref=v1.0.0"

  name                = "sqlmi-platform-eus2"
  resource_group_name = "rg-data-platform"
  location            = "eastus2"
  sku_name            = "GP_Gen5"
  vcores              = 8
  storage_size_in_gb  = 512
  subnet_id           = var.managed_instance_subnet_id
  license_type        = "BasePrice"
  # Both are required through this module: the provider pairs each with an
  # inline Entra administrator block that this module does not expose.
  administrator_login          = "sqladmin"
  administrator_login_password = var.mi_admin_password # out of band; never committed
}

module "mi_entra_admin" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-managed-instance-active-directory-administrator.git?ref=v1.0.0"

  managed_instance_id = module.sqlmi.id
  login_username      = "sql-mi-admins"
  object_id           = var.sql_admin_group_object_id
  tenant_id           = data.azurerm_client_config.current.tenant_id

  azuread_authentication_only = true
}

# The other identity/encryption control on the same instance.
module "mi_tde" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-managed-instance-transparent-data-encryption.git?ref=v1.0.0"

  managed_instance_id = module.sqlmi.id
  # No key -- service-managed encryption, which is already secure.
}

output "identity_posture" {
  value = {
    administrator     = module.mi_entra_admin.object_id
    entra_only_record = module.mi_entra_admin.azuread_only_authentication_id
    sql_logins_work   = module.mi_entra_admin.sql_authentication_still_permitted
    encryption_key    = module.mi_tde.key_kind
  }
}

Identity of the instance — managed_instance_id (required, force-new). Identity of the administrator — login_username, object_id, tenant_id (all required, none force-new). Authentication — azuread_authentication_only. Tail — timeouts. (There is no tags: the resource exposes none.)

🔒 No secret is accepted or emitted. An administrator is identified here, never authenticated.

One Terraform resource, two ARM records. The administrator lives under /administrators/ActiveDirectory; Entra-only authentication is a separate sub-resource under /azureADOnlyAuthentications/Default, written by a second client in the same create and read back from a different API. They can drift independently. Both Resource IDs are emitted, because only one of them is this resource's id and a review needs to name the other.

The divergence here prevents an active weakening, not just a no-op. The provider declares azuread_authentication_only with no default, so its zero value is false — and the create sends that value unconditionally. A bare resource block therefore disables Entra-only authentication on an instance that may have had it on. This module defaults to true, matching this suite's Entra-first convention for SQL and the sibling logical-server module.

And that default is disruptive, which is not a reason to hide it. Enabling Entra-only authentication stops every SQL-authenticated login working, including the built-in administrator created with the instance. The README, the variable description and an output all say so, and azuread_authentication_only = false remains a first-class choice for a migration period.

Destroy is a security change. Because the service will not remove an Entra administrator while Entra-only authentication is enabled, the provider disables it first as a required step. A terraform destroy therefore hands SQL authentication back. If that matters, revoke the SQL logins themselves — this resource's absence guarantees nothing.

Handing over administration is an in-place update. Only managed_instance_id is force-new, so a changed object_id reads in a plan like any other attribute change while being the most consequential edit this module allows.

Lifecycle. A lifecycle block is not valid inside a module block, so a caller who wants deletion protection wants a CanNotDelete management lock on the instance — which prevents deletion, not replacement.

terraform init -backend=false
terraform validate
terraform fmt -check

Pin the module with ?ref=v1.0.0 — never a branch. This module is plan-only in this repository; a human applies from CI.

terraform validate proves, offline and with no credentials: the type schemas, the anchored managed_instance_id pattern (a logical server ID and a child ID are both refused), the non-empty login_username check, the GUID-or-Resource-ID-as-display-name refusal, and the UUID shape of object_id and tenant_id.

Only terraform plan — which needs credentials — exercises: whether the instance exists, whether an administrator is already configured (the requires-import guard), and whether the object ID names anything real in the tenant.

Nothing at either stage catches an object ID that is well-formed and wrong. Azure accepts it without the Terraform principal being able to read it in Entra, and the result is an administrator nobody can use.

Note the asymmetry: a validation failure blocks terraform destroy as well as apply, which is why this module refuses only what the provider itself would refuse, plus the one display-name mistake.

id                                 = "/subscriptions/.../resourceGroups/rg-data-platform/providers/Microsoft.Sql/managedInstances/sqlmi-platform-eus2/administrators/ActiveDirectory"
azuread_only_authentication_id     = "/subscriptions/.../managedInstances/sqlmi-platform-eus2/azureADOnlyAuthentications/Default"
managed_instance_name              = "sqlmi-platform-eus2"
login_username                     = "sql-mi-admins"
object_id                          = "1a2b3c4d-5e6f-7081-92a3-b4c5d6e7f809"
azuread_authentication_only        = true
sql_authentication_still_permitted = false
force_new_fields                   = ["managed_instance_id"]

• azurerm_mssql_managed_instance_active_directory_administrator
• Microsoft Entra authentication for Azure SQL
• Configure Microsoft Entra-only authentication
• Sibling modules: terraform-azurerm-mssql-managed-instance, terraform-azurerm-mssql-managed-instance-transparent-data-encryption, terraform-azurerm-mssql-managed-instance-security-alert-policy, terraform-azurerm-mssql-server
• This module's SCOPE.md

💙 "Infrastructure as Code should be standardized, consistent, and secure."