Ensure SQL servers enable data security policy

Error: SQL servers do not enable data security policy

Bridgecrew Policy ID: BC_AZR_GENERAL_69
Checkov Check ID: CKV2_AZURE_13
Severity: MEDIUM

SQL servers do not enable data security policy

Description

Azure Defender for SQL provides a new layer of security, which enables customers to detect and respond to potential threats as they occur by providing security alerts on anomalous activities. Users will receive an alert upon suspicious database activities, potential vulnerabilities, SQL injection attacks, as well as anomalous database access patterns. Advanced threat protection alerts provide details of suspicious activity and recommend action on how to investigate and mitigate the threat.

Fix - Runtime

In Azure CLI

  1. Log in to the Azure Portal.
  2. Go to the reported SQL server
  3. Select 'SQL servers', Click on the SQL server instance you wanted to modify
  4. Click on 'Security Center' under 'Security'
  5. Click on 'Enable Azure Defender for SQL'

Fix - Buildtime

Terraform

  • Resource: azurerm_sql_server, azurerm_mssql_server_security_alert_policy
  • Argument: server_name (of azurerm_mssql_server_security_alert_policy )
resource "azurerm_sql_server" "sql_server_good_1" {
  name                         = "mysqlserver"
  resource_group_name          = "group"
  location                     = "location"
  version                      = "12.0"
  administrator_login          = "4dm1n157r470r"
  administrator_login_password = "4-v3ry-53cr37-p455w0rd"
}

resource "azurerm_sql_server" "sql_server_good_2" {
  name                         = "mysqlserver"
  resource_group_name          = "group"
  location                     = "location"
  version                      = "12.0"
  administrator_login          = "4dm1n157r470r"
  administrator_login_password = "4-v3ry-53cr37-p455w0rd"
}


resource "azurerm_mssql_server_security_alert_policy" "alert_policy_good" {
  resource_group_name        = "group"
  server_name                = azurerm_sql_server.sql_server_good_1.name
  state                      = "Enabled"
  retention_days = 20
}