☁️ Azure SQL Managed Instance Security Alert Policy Terraform Module

Advanced Threat Protection for an Azure SQL Managed Instance — which detections run, who hears about them, and where the records go. Targets hashicorp/azurerm ~> 4.0.

🧩 Overview

🛡️ Turns on Advanced Threat Protection for a SQL Managed Instance — SQL injection, access anomalies, data exfiltration, unsafe actions and brute force.
🔀 enabled defaults to true here, and to false in the provider. A bare resource block creates a policy that detects nothing; this module does not.
💳 It is billed per node. Defender for SQL pricing covers the whole managed instance, so the secure default is also a purchase.
🚫 Reads disabled_alerts for what it is — a deny list — and emits its complement positively.
📮 Reports when a working policy has no one to email.

💡 Why it matters: two states here look identical in a plan and behave nothing alike. A policy with enabled = false, and a policy with all six alert types in the deny list, are both fully-formed records that detect absolutely nothing — and neither produces a warning. This module defaults to the working configuration and emits this_policy_detects_nothing for the rest.

❤️ Support this project

If this module saved you time:

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

🗺️ Where this fits in the family

flowchart TB
    MI["terraform-azurerm-mssql-managed-instance"]
    THIS["terraform-azurerm-mssql-managed-instance-security-alert-policy"]
    VA["terraform-azurerm-mssql-managed-instance-vulnerability-assessment"]
    SA["terraform-azurerm-storage-account"]
    SC["terraform-azurerm-storage-container"]
    SRV["terraform-azurerm-mssql-server"]

    MI -->|"name and resource_group_name"| THIS
    MI -->|"id"| VA
    SA -->|"primary_blob_endpoint"| THIS
    SC -->|"container URL"| VA
    SA -->|"holds the container"| SC
    MI -->|"identity_principal_id writes results"| SA
    THIS -->|"managed_instance_id"| VA
    SRV -.->|"logical server equivalents are separate resources"| THIS

    style THIS fill:#0078D4,stroke:#004578,color:#ffffff
    style MI fill:#004578,stroke:#004578,color:#ffffff
    style VA fill:#F3F2F1,stroke:#8A8886,color:#201F1E
    style SA fill:#F3F2F1,stroke:#8A8886,color:#201F1E
    style SC fill:#F3F2F1,stroke:#8A8886,color:#201F1E
    style SRV fill:#F3F2F1,stroke:#8A8886,color:#201F1E

The managed instance feeds its name and resource group here — not its ID, which is what the sibling vulnerability-assessment module wants. That is why this module emits managed_instance_id: the edge from this node to the assessment carries the parent ID that the assessment needs and that a caller would otherwise assemble by hand. The dotted edge is a reminder that the logical-server equivalents are entirely separate resources, not this one pointed at a different parent.

🧬 What this module builds

flowchart TB
    MIN["managed_instance_name plus resource_group_name (force-new)"]
    EN["enabled (module default TRUE, provider default false)"]
    DA["disabled_alerts (a DENY list)"]
    ST["storage_endpoint plus access key"]

    POL["azurerm_mssql_managed_instance_security_alert_policy.this"]

    OID["id"]
    OMI["managed_instance_id"]
    OEN["enabled_alert_types"]
    ONO["this_policy_detects_nothing"]

    MIN --> POL
    EN --> POL
    DA --> POL
    ST --> POL
    POL --> OID
    POL --> OMI
    POL --> OEN
    POL --> ONO

    style POL fill:#0078D4,stroke:#004578,color:#ffffff
    style MIN fill:#004578,stroke:#004578,color:#ffffff
    style EN fill:#F3F2F1,stroke:#8A8886,color:#201F1E
    style DA fill:#F3F2F1,stroke:#8A8886,color:#201F1E
    style ST fill:#F3F2F1,stroke:#8A8886,color:#201F1E
    style OID fill:#F3F2F1,stroke:#8A8886,color:#201F1E
    style OMI fill:#F3F2F1,stroke:#8A8886,color:#201F1E
    style OEN fill:#F3F2F1,stroke:#8A8886,color:#201F1E
    style ONO fill:#F3F2F1,stroke:#8A8886,color:#201F1E

Resource	Count	Notes
`azurerm_mssql_managed_instance_security_alert_policy.this`	1	One per managed instance; there is no policy name to choose

✅ Provider / Versions

Requirement	Value
Terraform	`>= 1.12.0`
`hashicorp/azurerm`	`~> 4.0`
Provider block	None in this module — the caller configures the provider, its auth, and the mandatory `features {}` block

Schema notes that bite:

🔴 The provider declares enabled with NO default, so its zero value is false and a bare resource block creates a disabled alert policy. This module defaults it to true — see module_default_diverges_from_provider_default.
🔴 disabled_alerts is a DENY list. An entry switches that detection off. The legal values are a closed, case-sensitive set of six; sql_injection and Brute_force are both rejected.
🔴 terraform destroy does not delete this policy — it disables it. The provider's delete sends a policy carrying only the disabled state, so the endpoint, recipients and retention it had are not preserved by that call.
🔴 There is no import-already-exists guard on create. Applying over a managed instance that already carries an alert policy silently overwrites it. A clean apply is not evidence that nothing was configured before.
🔴 The access key is never read back from Azure. The provider's read re-sets the field from state rather than querying the service, so drift on it is undetectable by construction.
⚠️ storage_endpoint carries only a non-empty check in the provider — there is no URL validation at all, so not-a-url reaches Azure before anything objects. The https and not-a-Resource-ID checks here are the module's.
⚠️ The parent is a name and a resource group, while the sibling vulnerability assessment takes a single managed_instance_id. Same provider release, two conventions.
⚠️ managed_instance_name is validated with the logical SERVER name rule — lowercase letters, digits and hyphens, no leading or trailing hyphen, at most 63 characters. This module mirrors it exactly rather than guessing at a different one.
ℹ️ retention_days governs storage only, and nothing at all when no endpoint is configured. The provider's only check is that it is not negative; there is no documented upper bound, so none is invented here.
ℹ️ resource_group_name comes from the provider's shared schema, which carries force-new and normalisation, so a differently-cased value is not a diff.
ℹ️ All four timeouts are honoured here (30/5/30/30) — which is not true of the sibling assessment.
ℹ️ No tags, no location. Tag the managed instance instead.

🔑 Required Azure RBAC Roles / Permissions

Permission	Scope	Why
`Microsoft.Sql/managedInstances/securityAlertPolicies/read`	the managed instance	plan and refresh
`Microsoft.Sql/managedInstances/securityAlertPolicies/write`	the managed instance	create, update and disable the policy
`SQL Security Manager`	the managed instance	Microsoft names this as the role for enabling and managing Defender for SQL settings

🔒 The Terraform principal needs no storage permission. It supplies an endpoint and optionally a key; the service does the writing.

ℹ️ Reading this policy does not read a credential. The provider's read never queries Azure for the access key, so plan access here is not credential access.

Azure Prerequisites

The Microsoft.Sql resource provider registered on the subscription.
An existing Azure SQL Managed Instance.
💳 A Microsoft Defender for SQL entitlement, billed per node. Enabling this policy is a purchase covering the entire managed instance.
For storage-backed records: an existing storage account whose blob endpoint is passed here, reachable from the instance, plus a credential or an identity grant.
The caller configures provider "azurerm" { features {} }, auth and subscription.

📁 Module Structure

terraform-azurerm-mssql-managed-instance-security-alert-policy/
├── providers.tf    # required_version + pinned azurerm; no provider block
├── variables.tf    # 10 inputs, deeply typed, with the deny-list enum and storage rules
├── main.tf         # the single keystone `this`
├── outputs.tf      # id first, then the wiring facts, then the posture facts
├── README.md       # this file
├── SCOPE.md        # the cross-module contract
├── LICENSE         # MIT
└── .gitignore

⚙️ Quick Start

provider "azurerm" {
  features {}
}

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

  managed_instance_name = module.sqlmi.name
  resource_group_name   = "rg-data-platform"
}

All six detections on, nobody emailed, no storage copy. Two things to know before applying it: this enables a per-node Defender for SQL charge, and nobody is notified until you add recipients — see examples 2 and 3.

🔌 Cross-Module Contract

Consumes

Input	Type	Source
`managed_instance_name`	name	`terraform-azurerm-mssql-managed-instance` → `name`
`resource_group_name`	name	the caller
`storage_endpoint`	https URL	`terraform-azurerm-storage-account` → `primary_blob_endpoint`
`storage_account_access_key`	sensitive string	out of band

Emits

Output	Description
`id`	Resource ID of the policy (first)
`managed_instance_id`	the parent ID, for the sibling vulnerability-assessment module
`enabled_alert_types`	the deny list stated positively
`this_policy_detects_nothing`	true when disabled or fully denied
`alerts_reach_nobody_by_email`	true when no recipient is configured
`an_access_key_is_configured`	presence flag only — never the key

📚 Example Library

1 · The secure empty call

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

  managed_instance_name = module.sqlmi.name
  resource_group_name   = "rg-data-platform"
}

🔴 enabled is true here and false in the provider. A bare resource block would create a policy that exists, appears in the portal, and detects nothing. This module makes the caller type enabled = false for that instead.

💳 And that default is a purchase. Advanced Threat Protection is part of Microsoft Defender for SQL, priced per node — the whole instance, not per database.

2 · Telling somebody when a threat fires

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

  managed_instance_name = module.sqlmi.name
  resource_group_name   = "rg-data-platform"

  email_addresses = ["secops@example.com", "dba-oncall@example.com"]
}

output "nobody_listening" {
  value = module.mi_threat_protection.alerts_reach_nobody_by_email # false
}

⚠️ The empty call emails nobody. Detections are still recorded and still visible in Microsoft Defender for Cloud, so this is a notification gap rather than a detection gap — but it is the gap where an alert fires and nobody hears it.

ℹ️ The provider applies no check to these addresses at all. This module refuses only a value with no @ in it, and deliberately imposes no email regex that could reject a legal address.

3 · Emailing the account administrators too

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

  managed_instance_name        = module.sqlmi.name
  resource_group_name          = "rg-data-platform"
  email_account_admins_enabled = true
}

ℹ️ This default is kept at the provider's false, unlike enabled. Who should receive a security alert is an organisational decision, not a safe-versus-unsafe one — and turning this on mails whoever holds the admin role at the time, which may not be the security team.

4 · Silencing one detection, and seeing what is left

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

  managed_instance_name = module.sqlmi.name
  resource_group_name   = "rg-data-platform"

  # A DENY list -- this turns Brute_Force detection OFF.
  disabled_alerts = ["Brute_Force"]
}

output "still_detecting" {
  # ["Access_Anomaly", "Data_Exfiltration", "Sql_Injection",
  #  "Sql_Injection_Vulnerability", "Unsafe_Action"]
  value = module.mi_threat_protection.enabled_alert_types
}

🔴 Read the argument name carefully. disabled_alerts is a deny list: everything named there stops being detected. The module mirrors the provider's name — a wrong value fails loudly against a closed enum, which is the safe direction — and emits the complement so a reviewer never has to invert the list.

5 · The configuration that detects nothing

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

  managed_instance_name = module.sqlmi.name
  resource_group_name   = "rg-data-platform"

  disabled_alerts = [
    "Sql_Injection",
    "Sql_Injection_Vulnerability",
    "Access_Anomaly",
    "Data_Exfiltration",
    "Unsafe_Action",
    "Brute_Force",
  ]
}

output "detects_nothing" {
  value = module.mi_threat_protection.this_policy_detects_nothing # true
}

🔴 This is legal, applies cleanly, and produces a policy that detects absolutely nothing while still costing the per-node Defender for SQL charge. enabled = false reaches the same place by a different route. Neither produces a warning anywhere, which is why the flag exists.

6 · Building a deny list against a checked reference

output "all_types" {
  # ["Access_Anomaly", "Brute_Force", "Data_Exfiltration",
  #  "Sql_Injection", "Sql_Injection_Vulnerability", "Unsafe_Action"]
  value = module.mi_threat_protection.all_alert_types
}

⚠️ The values are case-sensitive and underscore-separated, and the provider enforces the set exactly. sql_injection, Brute_force and SQL_Injection are all rejected. all_alert_types is emitted so a list can be built against a checked reference instead of from memory.

7 · Keeping a copy of the records in your own storage

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

  managed_instance_name = module.sqlmi.name
  resource_group_name   = "rg-data-platform"

  storage_endpoint = module.audit_storage.primary_blob_endpoint
  retention_days   = 365
}

ℹ️ Omitting the endpoint does not turn threat detection off. Detections are still raised in Microsoft Defender for Cloud; what you lose is a copy of the audit records in storage you control — and retention_days then governs nothing.

⚠️ Note this wants the bare blob endpoint. The sibling vulnerability-assessment module wants a full container path. The two arguments are opposites.

8 · An access key, and the honest accounting of it

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

  managed_instance_name      = module.sqlmi.name
  resource_group_name        = "rg-data-platform"
  storage_endpoint           = module.audit_storage.primary_blob_endpoint
  storage_account_access_key = var.audit_access_key # provisioned out of band
}

output "key_in_use" {
  value = module.mi_threat_protection.an_access_key_is_configured # true
}

🔒 sensitive = true redacts plan output. It does NOT encrypt state. The key sits in the state file in plaintext, and in the state of every configuration that reads this module's state.

🔴 The read never queries Azure for this value — it re-sets the field from state. So a key rotated at the storage account is completely invisible: no plan shows it, no refresh corrects it.

⚠️ A key without a storage_endpoint is refused at plan, because it authenticates to an account this policy was never told to write to.

9 · Wiring the sibling vulnerability assessment

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

  managed_instance_name = module.sqlmi.name
  resource_group_name   = "rg-data-platform"
}

module "va_results" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-storage-container.git?ref=v1.0.0"

  name               = "vulnerability-assessment"
  storage_account_id = module.audit_storage.id
}

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

  # This resource takes a NAME; that one takes an ID.
  managed_instance_id = module.mi_threat_protection.managed_instance_id

  # And that one wants a CONTAINER url, not the bare endpoint this one wants.
  storage_container_path = module.va_results.url
}

💡 The two halves of Defender for SQL identify the same parent differently. This resource takes managed_instance_name plus resource_group_name; the assessment takes a single managed_instance_id. managed_instance_id is emitted here — derived by trimming this policy's own Resource ID — so nobody has to assemble that path by hand.

💳 Both capabilities sit inside the same per-node purchase. Enabling one and not the other does not halve the bill.

10 · Turning detection off, and what that leaves behind

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

  managed_instance_name = module.sqlmi.name
  resource_group_name   = "rg-data-platform"
  enabled               = false
}

🔴 Removing the module block reaches almost the same place. The provider's delete does not remove the ARM record — it sends a policy carrying only the disabled state. So terraform destroy disables the policy and leaves the record behind, and because the payload carries nothing else, the endpoint, recipients and retention it had are not preserved by that call.

11 · Several instances from one map

module "mi_threat_protection" {
  source   = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-managed-instance-security-alert-policy.git?ref=v1.0.0"
  for_each = module.managed_instances

  managed_instance_name = each.value.name
  resource_group_name   = "rg-data-platform"

  email_addresses = ["secops@example.com"]
}

output "instances_detecting_nothing" {
  value = [for k, m in module.mi_threat_protection : k if m.this_policy_detects_nothing]
}

output "instances_nobody_hears_about" {
  value = [for k, m in module.mi_threat_protection : k if m.alerts_reach_nobody_by_email]
}

💡 Keying by a stable name rather than by index means adding or removing an instance never re-indexes the rest. The two outputs turn "is anything silently unprotected?" into expressions rather than a review.

12 · Adopting an instance that may already have a policy

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

  managed_instance_name = module.sqlmi.name
  resource_group_name   = "rg-data-platform"

  disabled_alerts = ["Data_Exfiltration"]
}

🔴 This apply will overwrite an existing alert policy without saying so. The provider's create performs no existence check — there is no import-already-exists guard on this resource — so a policy someone configured in the portal is silently replaced by whatever this module says. Check the instance before a first apply; a clean plan is not evidence that nothing was there.

13 · 🏗️ End-to-end composition

provider "azurerm" {
  features {}
}

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 "audit_storage" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-storage-account.git?ref=v1.0.0"

  name                = "stmithreat0001"
  resource_group_name = "rg-data-platform"
  location            = "eastus2"
}

# Lets the instance write its own threat-detection records.
resource "azurerm_role_assignment" "mi_writes_threat_records" {
  scope                = module.audit_storage.id
  role_definition_name = "Storage Blob Data Contributor"
  principal_id         = module.sqlmi.identity_principal_id
}

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

  managed_instance_name = module.sqlmi.name
  resource_group_name   = "rg-data-platform"

  email_addresses = ["secops@example.com"]
  disabled_alerts = [] # every detection on, stated explicitly

  storage_endpoint = module.audit_storage.primary_blob_endpoint
  retention_days   = 365

  depends_on = [azurerm_role_assignment.mi_writes_threat_records]
}

output "threat_posture" {
  value = {
    detecting        = module.mi_threat_protection.enabled_alert_types
    detects_nothing  = module.mi_threat_protection.this_policy_detects_nothing
    nobody_notified  = module.mi_threat_protection.alerts_reach_nobody_by_email
    records_account  = module.mi_threat_protection.storage_account_name
    parent_for_va    = module.mi_threat_protection.managed_instance_id
  }
}

🔒 Every detection on, a real recipient, and a record copy the instance writes with its own identity.

⚠️ The depends_on is deliberate: nothing in the policy references the role assignment, so without it Terraform may configure the policy before the instance can write.

💳 And this composition starts a Defender for SQL charge for sqlmi-platform-eus2, covering every database on it.

📥 Inputs

Identity — managed_instance_name, resource_group_name (both required, both force-new). Detection — enabled, disabled_alerts. Notification — email_addresses, email_account_admins_enabled. Records — storage_endpoint, storage_account_access_key, retention_days. Tail — timeouts. (There is no tags: the resource exposes none.)

Full schemas

Name	Type	Default	Notes
`managed_instance_name`	`string`	—	Required, force-new. Lowercase, digits, hyphens; not leading/trailing; ≤63.
`resource_group_name`	`string`	—	Required, force-new. Normalised by the provider.
`enabled`	`bool`	`true`	Diverges from the provider's `false`. Billed per node.
`disabled_alerts`	`set(string)`	`[]`	A DENY list. Closed, case-sensitive set of six.
`email_addresses`	`set(string)`	`[]`	Only a missing `@` is refused.
`email_account_admins_enabled`	`bool`	`false`	The provider's default, kept.
`retention_days`	`number`	`0`	Storage only. Non-negative; no documented upper bound.
`storage_endpoint`	`string`	`null`	A blob endpoint, not a container path.
`storage_account_access_key`	`string` (sensitive)	`null`	Never read back. Refused without an endpoint.
`timeouts`	`object({create, read, update, delete})`	`null`	All four are real here.

🧾 Outputs

Output	Description
`id`	Resource ID of the policy (first)
`managed_instance_id`	the parent ID, for the sibling assessment module
`managed_instance_name` / `resource_group_name`	the parent, as configured
`enabled`	whether Advanced Threat Protection is on
`enabled_alert_types`	the deny list stated positively
`disabled_alert_types`	what is switched off, sorted
`all_alert_types`	the provider's closed set of six
`retention_days` / `storage_endpoint`	records configuration
`storage_account_name`	parsed from the endpoint host
`email_addresses` / `email_account_admins_enabled`	notification configuration
`this_policy_detects_nothing`	true when disabled or fully denied
`alerts_reach_nobody_by_email`	true when no recipient is configured
`an_access_key_is_configured`	presence flag — never the key
`writes_threat_records_to_storage`	true when an endpoint is configured
`force_new_fields` / `fields_that_can_change_after_creation`	lifecycle
`fields_azure_returns_on_read`	where drift is detectable — the key is absent
`module_default_diverges_from_provider_default`	constant true
`advanced_threat_protection_is_billed_per_node`	constant true
`disabled_alerts_is_a_deny_list`	constant true
`destroying_this_resource_does_not_delete_the_policy`	constant true
`creating_this_resource_overwrites_any_existing_policy`	constant true
`the_access_key_is_never_read_back_so_there_is_no_drift_detection`	constant true
`sensitive_redacts_plan_output_it_does_not_encrypt_state`	constant true
`no_access_key_is_emitted_by_this_module`	constant true
`the_provider_does_not_validate_the_storage_endpoint_as_a_url`	constant true
`retention_applies_to_storage_only`	constant true
`the_parent_is_a_name_here_and_an_id_on_the_sibling`	constant true
`the_name_validator_is_borrowed_from_the_logical_server`	constant true
`one_policy_per_managed_instance`	constant true
`all_four_timeouts_are_honoured_here`	constant true
`this_resource_supports_no_azure_resource_tags`	constant true

🔒 No secret is emitted. The access key is reported as a presence flag.

🧠 Architecture Notes

The default is the headline, and it is a divergence. The provider declares enabled with no default, so Terraform's zero value for a bool applies and a bare resource block creates a disabled alert policy. That record appears in state and in the portal, costs the same per-node Defender for SQL charge as a working one, and detects nothing. This module defaults it to true instead. A caller who wants the provider's behaviour types enabled = false, and the divergence is emitted as an output so it is never a surprise.

A working policy can still detect nothing. Putting all six alert types in disabled_alerts reaches the same place as disabling, by a different route, and applies just as cleanly. this_policy_detects_nothing covers both.

And a detecting policy can still be silent. With no email_addresses and email_account_admins_enabled false, detections are recorded and visible in Microsoft Defender for Cloud but nobody is mailed. That is a notification gap rather than a detection gap, which is precisely why it deserves an output rather than a validation failure.

The deny list is a negated field, so the module states it positively. enabled_alert_types is the complement of disabled_alerts against the provider's closed set. The argument name is mirrored rather than corrected, because a wrong value fails loudly against that closed enum — the safe direction — while a wrong reading of the name is what the output prevents.

Destroy is a disable, and create overwrites. Two behaviours to hold together: the provider's delete sends a policy carrying only the disabled state, so the ARM record survives a terraform destroy and the settings it carried are not preserved by that call; and the create performs no existence check, so an apply over an instance that already has a policy silently replaces it. Neither is visible in a plan.

The access key has no drift detection at all. The provider's read does not query Azure for it — it re-sets the field from whatever is in state. That is weaker than the usual "the API does not return it": here nothing is even asked.

Sensitivity is contagious. var.storage_account_access_key != null is a sensitive bool, which Terraform refuses to emit. The presence flag is unwrapped with nonsensitive() at the point it is derived.

Lifecycle. Both halves of the parent reference are force-new, so pointing this module at a different instance is a different record. 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.

🧱 Design Principles

Concern	Secure default (empty call)	Opt-out (caller must type it)
Threat detection	`enabled = true` — diverging from the provider's `false`	set to `false`
Detection coverage	`disabled_alerts = []` — all six on	name types to switch them off
Alert recipients	none — reported, not defaulted	supply `email_addresses`
Account-admin email	`false` — the provider's default, kept	set to `true`
Records in your storage	none configured	supply `storage_endpoint`
Credential	none	supply `storage_account_access_key`
Secret emission	none — presence flags only	not available
Cost	not zero: `enabled = true` starts a per-node charge	`enabled = false`

🚀 Runbook

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.

🧪 Testing

terraform validate proves, offline and with no credentials: the type schemas, the instance-name rule in both directions, the resource-group-not-an-ID check, the closed case-sensitive alert enum, the email @ check, the non-negative whole-number retention rule, the storage endpoint's https and not-a-Resource-ID checks, and the key-without-endpoint rule.

Only terraform plan — which needs credentials — exercises: whether the instance exists, whether a Defender for SQL entitlement is present, whether the storage account is reachable from the instance, and whether the supplied key works.

Nothing at either stage reports that an existing policy is about to be overwritten, or that a destroy will disable rather than delete. That is what the constant outputs are for.

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 two storage mistakes Azure would reject.

💬 Example Output

id                          = "/subscriptions/.../resourceGroups/rg-data-platform/providers/Microsoft.Sql/managedInstances/sqlmi-platform-eus2/securityAlertPolicies/Default"
managed_instance_id         = "/subscriptions/.../providers/Microsoft.Sql/managedInstances/sqlmi-platform-eus2"
enabled                     = true
enabled_alert_types         = ["Access_Anomaly", "Brute_Force", "Data_Exfiltration", "Sql_Injection", "Sql_Injection_Vulnerability", "Unsafe_Action"]
disabled_alert_types        = []
this_policy_detects_nothing = false
alerts_reach_nobody_by_email = false
storage_account_name        = "stmithreat0001"
retention_days              = 365
an_access_key_is_configured = false
force_new_fields            = ["managed_instance_name", "resource_group_name"]

🔍 Troubleshooting

Symptom	Cause	Fix
`managed_instance_name can contain only lowercase letters`	uppercase, an underscore, a leading/trailing hyphen, over 63 characters, or a Resource ID was passed	Pass the instance's name. This resource takes a name and a resource group, not an ID
`resource_group_name must be a resource group NAME`	a Resource ID or an empty value was passed	Pass the group's name
`a disabled_alerts entry is not a legal alert type`	wrong case or a value outside the six	Use `all_alert_types` as the reference; the set is case-sensitive
`an email_addresses entry is empty or contains no '@'`	a malformed recipient	Fix the address. Only that obvious mistake is refused
`retention_days must be a whole number`	negative or fractional	Use a whole non-negative number of days
`storage_endpoint must be an https URL`	a Resource ID, a container path, or a bare hostname	Use the storage module's `primary_blob_endpoint`. The provider has no URL check, so this one is the module's
`storage_account_access_key is set but storage_endpoint is not`	a key with nothing to authenticate to	Supply the endpoint, or drop the key
The policy exists but no threat is ever detected	it is disabled, or every type is in the deny list	Check `this_policy_detects_nothing`
A threat was detected but nobody was told	no recipients configured	Check `alerts_reach_nobody_by_email`; add `email_addresses`
An existing policy's settings vanished after an apply	create performs no existence check and overwrote it	Expected. Reconcile the portal configuration into this module's inputs
`terraform destroy` succeeded but the policy is still there, disabled	the provider's delete sends a disabled-state policy rather than removing the record	Expected. There is no way to remove it through this resource
A rotated storage key stopped the records, and no plan shows anything	the read never queries Azure for the key	Update `storage_account_access_key` in the configuration
An unexpected Defender for SQL charge appeared	`enabled = true` is this module's default	Expected; it is a per-node charge covering the whole instance

🔗 Related Docs

azurerm_mssql_managed_instance_security_alert_policy
Configure Advanced Threat Protection in Azure SQL Managed Instance
Microsoft Defender for SQL
Secure your Azure SQL Managed Instance
Sibling modules: terraform-azurerm-mssql-managed-instance, terraform-azurerm-mssql-managed-instance-vulnerability-assessment, terraform-azurerm-storage-account, terraform-azurerm-storage-container
This module's SCOPE.md

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

Navigation Menu

Repository navigation

About

Repository files navigation

☁️ Azure SQL Managed Instance Security Alert Policy Terraform Module

🧩 Overview

❤️ Support this project

🗺️ Where this fits in the family

🧬 What this module builds

✅ Provider / Versions

🔑 Required Azure RBAC Roles / Permissions

Azure Prerequisites

📁 Module Structure

⚙️ Quick Start

🔌 Cross-Module Contract

📚 Example Library

📥 Inputs

🧾 Outputs

🧠 Architecture Notes

🧱 Design Principles

🚀 Runbook

🧪 Testing

💬 Example Output

🔍 Troubleshooting

🔗 Related Docs

About

Releases

Packages

Contributors

Languages

Name		Name	Last commit message	Last commit date
.gitignore		.gitignore
LICENSE		LICENSE
README.md		README.md
SCOPE.md		SCOPE.md
main.tf		main.tf
outputs.tf		outputs.tf
providers.tf		providers.tf
variables.tf		variables.tf

Latest commit

History

Folders and files

Repository files navigation

☁️ Azure SQL Managed Instance Security Alert Policy Terraform Module

🧩 Overview

❤️ Support this project

🗺️ Where this fits in the family

🧬 What this module builds

✅ Provider / Versions

🔑 Required Azure RBAC Roles / Permissions

Azure Prerequisites

📁 Module Structure

⚙️ Quick Start

🔌 Cross-Module Contract

📚 Example Library

📥 Inputs

🧾 Outputs

🧠 Architecture Notes

🧱 Design Principles

🚀 Runbook

🧪 Testing

💬 Example Output

🔍 Troubleshooting

🔗 Related Docs

About

Topics

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages