SQL server auditing disabled

Docs > Datadog Security > Code Security > Infrastructure as Code (IaC) Security > IaC Security Rules > SQL server auditing disabled

Metadata

Id: terraform-azure-sql-server-auditing-disabled

Provider: Azure

Platform: Terraform

Severity: Medium

Category: Observability

Learn More

Provider Reference

Description

SQL Server auditing should be enabled to ensure that all database events and activities are properly logged for security monitoring and compliance purposes. Without the extended_auditing_policy block set as shown below, malicious or unauthorized actions may go undetected, increasing the risk of data breaches and making forensic analysis difficult. For example, secure Terraform configuration includes the following:

extended_auditing_policy {
   storage_endpoint           = azurerm_storage_account.example.primary_blob_endpoint
   storage_account_access_key = azurerm_storage_account.example.primary_access_key
   storage_account_access_key_is_secondary = true
   retention_in_days                       = 90
}

Compliant Code Examples

resource "azurerm_sql_server" "negative1" {
    name                         = "mssqlserver"
    resource_group_name          = azurerm_resource_group.example.name
    location                     = azurerm_resource_group.example.location
    version                      = "12.0"
    administrator_login          = "mradministrator"
    administrator_login_password = "thisIsDog11"

    extended_auditing_policy {
       storage_endpoint           = azurerm_storage_account.example.primary_blob_endpoint
       storage_account_access_key = azurerm_storage_account.example.primary_access_key
       storage_account_access_key_is_secondary = true
       retention_in_days                       = 90
    }
}

resource "azurerm_resource_group" "example" {
  name     = "test-rg"
  location = "eastus"
}

resource "azurerm_storage_account" "example" {
  name                     = "storageaccountexample"
  resource_group_name      = azurerm_resource_group.example.name
  location                 = azurerm_resource_group.example.location
  account_tier             = "Standard"
  account_replication_type = "LRS"
}

resource "azurerm_mssql_server" "negative2" {
  name                         = "mssqlserver2"
  resource_group_name          = azurerm_resource_group.example.name
  location                     = azurerm_resource_group.example.location
  version                      = "12.0"
  administrator_login          = "mradministrator"
  administrator_login_password = "thisIsDog11"
}

resource "azurerm_mssql_server_extended_auditing_policy" "negative2" {
  server_id                   = azurerm_mssql_server.negative2.id
  storage_endpoint            = azurerm_storage_account.example.primary_blob_endpoint
  storage_account_access_key  = azurerm_storage_account.example.primary_access_key
  retention_in_days           = 90
}

Non-Compliant Code Examples

resource "azurerm_sql_server" "positive1" {
    name                         = "mssqlserver"
    resource_group_name          = azurerm_resource_group.example.name
    location                     = azurerm_resource_group.example.location
    version                      = "12.0"
    administrator_login          = "mradministrator"
    administrator_login_password = "thisIsDog11"
}

resource "azurerm_resource_group" "example" {
  name     = "test-rg"
  location = "eastus"
}

resource "azurerm_storage_account" "example" {
  name                     = "storageaccountexample"
  resource_group_name      = azurerm_resource_group.example.name
  location                 = azurerm_resource_group.example.location
  account_tier             = "Standard"
  account_replication_type = "LRS"
}

resource "azurerm_mssql_server" "positive2" {
  name                         = "mssqlserver3"
  resource_group_name          = azurerm_resource_group.example.name
  location                     = azurerm_resource_group.example.location
  version                      = "12.0"
  administrator_login          = "mradministrator"
  administrator_login_password = "thisIsDog11"
}

resource "azurerm_mssql_server_extended_auditing_policy" "positive2" {
  server_id                  = azurerm_mssql_server.positive2.id
  storage_account_access_key = azurerm_storage_account.example.primary_access_key
  retention_in_days          = 90
  enabled                    = false
}

resource "azurerm_resource_group" "example" {
  name     = "test-rg"
  location = "eastus"
}

resource "azurerm_storage_account" "example" {
  name                     = "storageaccountexample"
  resource_group_name      = azurerm_resource_group.example.name
  location                 = azurerm_resource_group.example.location
  account_tier             = "Standard"
  account_replication_type = "LRS"
}

resource "azurerm_mssql_server" "positive3" {
  name                         = "mssqlserver4"
  resource_group_name          = azurerm_resource_group.example.name
  location                     = azurerm_resource_group.example.location
  version                      = "12.0"
  administrator_login          = "mradministrator"
  administrator_login_password = "thisIsDog11"
}

resource "azurerm_mssql_server" "positive3b" {
  name                         = "mssqlserver5"
  resource_group_name          = azurerm_resource_group.example.name
  location                     = azurerm_resource_group.example.location
  version                      = "12.0"
  administrator_login          = "mradministrator"
  administrator_login_password = "thisIsDog11"
}

resource "azurerm_mssql_server_extended_auditing_policy" "positive3" {
  server_id                  = azurerm_mssql_server.positive3.id
  storage_account_access_key = azurerm_storage_account.example.primary_access_key
  retention_in_days          = 90
}