☁️ Azure SQL Server Security Alert Policy Terraform Module

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

• 🛡️ Turns on Advanced Threat Protection for an Azure SQL logical server.
• 🔑 state is required by the provider, so this suite's secure-by-default rule cannot apply — the module compensates rather than pretending otherwise.
• 🚫 Reads disabled_alerts for what it is — a deny list — and emits its complement positively.
• 🔗 Its id is what the vulnerability-assessment module consumes, not the server's, and that policy must be Enabled or the sibling refuses to configure.
• ⚖️ Names the differences from the managed-instance twin: five alert types, not six, and a differently-spelled admin-email argument.

💡 Why it matters: two states here look nothing alike in configuration and identical in effect. state = "Disabled" and a deny list containing all five alert types are both fully-formed records that detect absolutely nothing, and neither produces a warning. A third — terraform destroy — leaves the first of those behind rather than removing anything.

If this module saved you time:

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

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

    SRV -->|"name and resource_group_name"| THIS
    THIS -->|"id, and it must be Enabled"| VA
    SA -->|"primary_blob_endpoint"| THIS
    SA -->|"holds the container"| SC
    SC -->|"url"| VA
    MIP -.->|"six alert types there, five here"| THIS

    style THIS fill:#0078D4,stroke:#004578,color:#ffffff
    style SRV 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 MIP fill:#F3F2F1,stroke:#8A8886,color:#201F1E

The edge from this node to the vulnerability assessment carries this policy's id, not the server's — that resource's parent really is the policy, and the provider refuses to configure it unless this policy is Enabled. The dotted edge is the difference to hold onto when moving between the server and managed-instance resources: six alert types there, five here, and a deny list copied across fails on Brute_Force.

flowchart TB
    PARENT["server_name plus resource_group_name (force-new)"]
    STATE["state (REQUIRED, Enabled or Disabled)"]
    DA["disabled_alerts (a DENY list, FIVE types)"]
    ST["storage_endpoint plus access key (mutually required)"]

    POL["azurerm_mssql_server_security_alert_policy.this"]

    OID["id, consumed by the vulnerability assessment"]
    OON["threat_detection_is_on"]
    OEN["enabled_alert_types"]
    ONO["this_policy_detects_nothing"]

    PARENT --> POL
    STATE --> POL
    DA --> POL
    ST --> POL
    POL --> OID
    POL --> OON
    POL --> OEN
    POL --> ONO

    style POL fill:#0078D4,stroke:#004578,color:#ffffff
    style STATE fill:#004578,stroke:#004578,color:#ffffff
    style PARENT 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 OON fill:#F3F2F1,stroke:#8A8886,color:#201F1E
    style OEN fill:#F3F2F1,stroke:#8A8886,color:#201F1E
    style ONO fill:#F3F2F1,stroke:#8A8886,color:#201F1E

Schema notes that bite:

• 🔴 state is REQUIRED, and it is a case-sensitive string enum (Enabled / Disabled) rather than the managed instance's optional bool. There is no empty call to make safe.
• 🔴 There are FIVE alert types here, not six. Brute_Force is accepted by the managed-instance resource and refused by this one — so a deny list copied between them fails on exactly that value, loudly, at parse time.
• 🔴 storage_endpoint and storage_account_access_key are mutually RequiredWith — both or neither. That constraint is invisible in the binary schema, and it differs from the managed-instance resource where a key alone was merely useless.
• 🔴 There is no credential-free storage form. Unlike the SQL auditing policies, this resource has no managed-identity fallback: storage is configured with an access key or not at all.
• 🔴 terraform destroy does not delete this policy — it disables it. The 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 requires-import guard on create. Applying over a server that already carries an alert policy silently overwrites it.
• ⚠️ The argument is email_account_admins, not email_account_admins_enabled as on the managed instance.
• ⚠️ storage_endpoint carries only a non-empty check in the provider — no URL validation. The https and not-a-Resource-ID checks here are the module's.
• ℹ️ resource_group_name comes from the provider's shared schema, which carries force-new and normalisation.
• ℹ️ retention_days defaults to 0, governs storage only, and has no documented upper bound — so none is invented here.
• ℹ️ No tags, no location. All four timeouts are honoured (30/5/30/30).

🔴 Disabling this policy breaks the sibling. The vulnerability-assessment resource re-reads this policy on every update and refuses unless it is Enabled — so write access here is effectively control over whether that resource can be changed at all.

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

• The Microsoft.Sql resource provider registered on the subscription.
• An existing Azure SQL logical server.
• 💳 A Microsoft Defender for SQL entitlement, billed per server. state = "Enabled" is a purchase covering every database on it.
• For storage-backed records: a storage account whose blob endpoint and access key are both supplied — the provider requires each with the other.
• The caller configures provider "azurerm" { features {} }, auth and subscription.

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

provider "azurerm" {
  features {}
}

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

  server_name         = module.sql_server.name
  resource_group_name = "rg-data-platform"
  state               = "Enabled"
}

There is no shorter call — state is required by the provider. All five detections run, nobody is emailed, and no storage copy is written. Applying it starts a per-server Defender for SQL charge.

Consumes

Emits

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

  server_name         = module.sql_server.name
  resource_group_name = "rg-data-platform"
  state               = "Enabled"
}

output "on" {
  value = module.sql_threat_protection.threat_detection_is_on # true
}

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

  server_name         = module.sql_server.name
  resource_group_name = "rg-data-platform"
  state               = "Disabled"
}

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

  server_name         = module.sql_server.name
  resource_group_name = "rg-data-platform"
  state               = "Enabled"

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

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

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

  server_name         = module.sql_server.name
  resource_group_name = "rg-data-platform"
  state               = "Enabled"

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

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

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

  server_name         = module.sql_server.name
  resource_group_name = "rg-data-platform"
  state               = "Enabled"

  email_addresses      = ["secops@example.com"]
  email_account_admins = true # NOT email_account_admins_enabled -- see example 9
}

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

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

  server_name         = module.sql_server.name
  resource_group_name = "rg-data-platform"
  state               = "Enabled"

  storage_endpoint           = module.threat_storage.primary_blob_endpoint
  storage_account_access_key = var.threat_storage_key
  retention_days             = 365
}

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

  server_name         = module.sql_server.name
  resource_group_name = "rg-data-platform"
  state               = "Enabled" # REQUIRED by the sibling below
}

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

  # THIS POLICY'S id -- not module.sql_server.id.
  server_security_alert_policy_id = module.sql_threat_protection.id
  storage_container_path          = module.va_results.url
}

output "server" {
  value = module.sql_threat_protection.server_id
}

output "renames_to_make" {
  # {
  #   state                = "enabled (a bool, and optional there)"
  #   email_account_admins = "email_account_admins_enabled"
  #   server_name          = "managed_instance_name"
  # }
  value = module.sql_threat_protection.the_argument_names_differ_from_the_managed_instance_resource
}

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

  server_name         = module.sql_server.name
  resource_group_name = "rg-data-platform"
  state               = "Enabled"
}

module "sql_threat_protection" {
  source   = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-server-security-alert-policy.git?ref=v1.0.0"
  for_each = module.sql_servers

  server_name         = each.value.name
  resource_group_name = "rg-data-platform"
  state               = "Enabled"

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

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

output "policy_ids_for_assessments" {
  value = { for k, m in module.sql_threat_protection : k => m.id }
}

# Removing this module block does NOT remove the policy.

provider "azurerm" {
  features {}
}

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

  name                = "sql-platform-eus2"
  resource_group_name = "rg-data-platform"
  location            = "eastus2"

  azuread_administrator = {
    login_username = "sql-admins"
    object_id      = var.sql_admin_group_object_id
  }
}

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

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

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.threat_storage.id
}

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

  server_name         = module.sql_server.name
  resource_group_name = "rg-data-platform"
  state               = "Enabled"

  disabled_alerts      = [] # all five detections on, stated explicitly
  email_addresses      = ["secops@example.com"]
  email_account_admins = false

  # Both or neither -- the provider requires each with the other.
  storage_endpoint           = module.threat_storage.primary_blob_endpoint
  storage_account_access_key = var.threat_storage_key
  retention_days             = 365
}

# The other half of Defender for SQL, and it depends on the policy above
# being Enabled -- which is why it takes the POLICY's id.
module "sql_vulnerability_assessment" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-server-vulnerability-assessment.git?ref=v1.0.0"

  server_security_alert_policy_id = module.sql_threat_protection.id
  storage_container_path          = module.va_results.url

  recurring_scans = {
    enabled = true
    emails  = ["secops@example.com"]
  }
}

output "sql_security_posture" {
  value = {
    detecting       = module.sql_threat_protection.enabled_alert_types
    detects_nothing = module.sql_threat_protection.this_policy_detects_nothing
    nobody_notified = module.sql_threat_protection.alerts_reach_nobody_by_email
    records_account = module.sql_threat_protection.storage_account_name
    scan_results    = module.sql_vulnerability_assessment.container_name
  }
}

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

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

The secure-by-default rule cannot apply, and saying so is better than pretending. This suite's convention is that an empty call produces the safe resource; the provider makes state required, so there is no empty call and no default for the module to pick. The three compensations available are all applied — validate the value set, name the protective value inside the error message, and emit the choice as a boolean so an audit across an estate compares true/false rather than enum spellings.

Three configurations detect nothing and look completely different. state = "Disabled"; a deny list containing all five types; and the record a terraform destroy leaves behind. One output, this_policy_detects_nothing, covers the first two, and a constant output covers the third — because a plan renders "destroy" and "removes the protection" as the same thing when only the first is true.

Its id is the load-bearing output. The sibling vulnerability assessment takes this policy's Resource ID as its parent, and the provider reads the policy on that resource's create and update. So the dependency is expressed by data flow rather than depends_on, and disabling this policy breaks the sibling's next apply — a coupling that appears nowhere in either plan.

The differences from the managed-instance twin are named, not smoothed over. Five alert types rather than six; state as a required string rather than an optional bool; email_account_admins rather than email_account_admins_enabled; a mutually-required storage pair rather than a merely-useless lone key. Every one of those fails loudly when a configuration is copied across, which is the safe direction — and the module emits the rename map rather than leaving it to be discovered.

There is no credential-free storage form here. The SQL auditing policies fall back to the server's managed identity when a key is omitted; this resource does not. If avoiding the secret matters more than the storage copy, leave storage unconfigured and read detections in Microsoft Defender for Cloud.

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.

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 server-name rule in both directions, the resource-group-not-an-ID check, the case-sensitive state enum, the five-value alert-type set (including that Brute_Force is refused), the email @ check, the non-negative whole-number retention rule, the storage endpoint's https and not-a-Resource-ID checks, and the mutual storage pairing in both directions.

Only terraform plan and apply exercise: whether the server exists, whether a Defender for SQL entitlement is present, and whether the supplied key works.

Nothing at either stage reports that an existing policy is about to be overwritten, that a destroy will disable rather than delete, or that disabling will break the sibling assessment. 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-shape mistakes Azure would reject.

id                           = "/subscriptions/.../resourceGroups/rg-data-platform/providers/Microsoft.Sql/servers/sql-platform-eus2/securityAlertPolicies/Default"
server_id                    = "/subscriptions/.../providers/Microsoft.Sql/servers/sql-platform-eus2"
state                        = "Enabled"
threat_detection_is_on       = true
enabled_alert_types          = ["Access_Anomaly", "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         = "stsqlsecurity0001"
retention_days               = 365
an_access_key_is_configured  = true
force_new_fields             = ["server_name", "resource_group_name"]

• azurerm_mssql_server_security_alert_policy
• Configure Advanced Threat Protection for Azure SQL Database
• Microsoft Defender for SQL
• Sibling modules: terraform-azurerm-mssql-server, terraform-azurerm-mssql-server-vulnerability-assessment, terraform-azurerm-mssql-managed-instance-security-alert-policy, terraform-azurerm-storage-account, terraform-azurerm-storage-container
• This module's SCOPE.md

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