One of the Microsoft Defender for Cloud recommendations you will see is to enable vulnerability assessment settings on the SQL server. Periodic scans should be enabled and the scan reports email address setup. Note that turning this on incurs a monthly cost per server.

The setting is fairly well hidden, it can be found by browsing to the SQL server, and choosing ‘Microsoft Defender for Cloud’ in the menu:

You will notice the recommendation:

Above it you will see (click on configure):

The following dialog will be shown. Here I have it turned on with the storage account configured, but recurring scans are disabled:

Configuring using Terraform
First, we will create the module. 3 files main.tf , output.tf , and variables.tf sit inside a folder called azure_sql_vunerability_scans

main.tf

data “azurerm_storage_account” “storage_account” {
name = var.storage_account_name
resource_group_name = var.storage_resource_group_name
}

data “azurerm_storage_container” “container” {
name = var.container_name
storage_account_name = var.storage_account_name
}

resource “azurerm_mssql_server_security_alert_policy” “policy” {
resource_group_name = var.resource_group_name
server_name = var.sql_server_name
state = “Enabled”
}

resource “azurerm_mssql_server_vulnerability_assessment” “vunerability_assessment” {
server_security_alert_policy_id = azurerm_mssql_server_security_alert_policy.policy.id
storage_container_path = “${data.azurerm_storage_account.storage_account.primary_blob_endpoint}${data.azurerm_storage_container.container.name}/”
storage_account_access_key = data.azurerm_storage_account.storage_account.primary_access_key

recurring_scans {
enabled = true
email_subscription_admins = var.email_admins
emails = var.email_accounts
}
}

variables.tf

variable “storage_resource_group_name” {
type = string
description = “The name of the resource group in which the storage account resource is present”
}

variable “storage_account_name” {
type = string
description = “Storage account name to hold diagnostics log for VM”
}

variable “resource_group_name” {
type = string
description = “The name of the resource group in which the resources are present”
}

variable “container_name” {
type = string
description = “The container name which should be used to store the logs”
}

variable “sql_server_name” {
type = string
description = “The SQL Server name”
}

variable “email_accounts” {
type = list(string)
description = “The list of email accounts to send alerts to”
}

variable “email_admins” {
type = bool
description = “A bool to specify whether the email admins should be enabled or not”
default = true
}

In our main code, we can call the module as follows:

main.tf

module “azure_sql_vunerability_scans” {
source = “../../modules/azure_sql_vunerability_scans”
storage_account_name = var.storage_account_name[0]
storage_resource_group_name = var.storage_resource_group_name
container_name = var.storage_container_name[0]
resource_group_name = var.sqlserverrg[0]
sql_server_name = var.sqlservername[0]
email_accounts = var.vulnerability_scanning_email_addresses[0]
}

variables.tf

variable “storage_account_name” {
type = list(string)
description = “The name for the Storage Account”
default = []
}

variable “storage_resource_group_name” {
type = string
description = “The name of the resource group where your resources should reside”
}

variable “storage_container_name” {
type = list(string)
description = “The name of the storage container for the SQL assessment logs”
default = []
}

variable “sqlserverrg” {
type = list(string)
description = “lists of resource group name that the database will be created in.”
}

variable “sqlservername” {
type = list(string)
description = “List of SQL Server name”
}

variable “vulnerability_scanning_email_addresses” {
type = list(string)
description = “(Optional) A list of email addresses which alerts should be sent to”
}

terraform.tfvars

storage_account_name = [“sqllogssa”]
storage_resource_group_name = “sql-rg”
storage_container_name = [“sql-vulnerability-reports”]
vulnerability_scanning_email_addresses = [“security@xxxx.com”]
sqlservername = [“sqlservername”]
sqlserverrg = [“sqlserver-rg”]

Troubleshooting timeout errors

When I ran the terraform apply, I received the following error after 15m30s:

Error: updataing mssql server vulnerability assessment: sql.ServerVulnerabilityAssessmentsClient#CreateOrUpdate: Failure responding to request: StatusCode=504 — Original Error: autorest/azure: Service returned an error. Status=504 Code=”GatewayTimeout” Message=”The gateway did not receive a response from ‘Microsoft.Sql’ within the specified time period.”

The first thing to check was the permissions. The SQL Server has a managed identity enabled, and I confirmed this was assigned the “storage blob data contributor” role for the SQL Server on the storage account.
The fix was to remove the storage_Account_access_key parameter from the vulnerability assessment block in my main.tfas it is optional and not required while using identities.
After re-running, the changes went through as expected, the ‘periodic recurring scans’ and ‘send scan reports to’ fields were populated correctly.

WordPress Appliance - Powered by TurnKey Linux