Skip to main content

Short Auditing Retention Period for SQL Servers

More Info:​

Auditing retention period should be greater than defined days. Default 90 days.

Risk Level​

Low

Address​

Security

Compliance Standards​

  • CIS AZURE
  • Cloudanix Best Practice
  • HITRUST CSF
  • ISO 27001
  • NIST CSF
  • PCI
  • SOC2

Triage and Remediation​

Remediation​

Using Console

To remediate the short auditing retention period for SQL servers in Azure, follow these steps:

  1. Log in to the Azure Portal and go to the SQL Server that you want to remediate.

  2. Click on the "Auditing" option in the left-hand menu.

  3. In the "Auditing" section, click on "Diagnostic settings".

  4. Click on the "Add diagnostic setting" button.

  5. In the "Add diagnostic setting" window, give a name for the new diagnostic setting.

  6. Under "Destination details", select "Log Analytics" or "Event Hub" as the destination.

  7. If you choose "Log Analytics", select the Log Analytics workspace that you want to use.

  8. Under "Categories", select the "SQLSecurityAuditEvents" category.

  9. Under "Retention (days)", set the retention period to the desired number of days.

  10. Click on the "Save" button to save the diagnostic setting.

Once the diagnostic setting is saved, the SQL server will start sending the audit logs to the destination you selected. The audit logs will be retained for the number of days you specified in the retention period.

Using CLI

To remediate the short auditing retention period for SQL servers in AZURE using AZURE CLI, follow these steps:

  1. Open the AZURE CLI and log in to your AZURE account.

  2. Use the following command to check the current retention period for auditing logs in your SQL server:

    az sql server audit-policy show --resource-group <resource-group-name> --server <sql-server-name> --name "Default"

    Replace <resource-group-name> with the name of the resource group in which your SQL server is located, and <sql-server-name> with the name of your SQL server.

  3. If the retention period is less than the required period, use the following command to update the audit policy:

    az sql server audit-policy update --resource-group <resource-group-name> --server <sql-server-name> --name "Default" --state Enabled --retention-days <retention-days>

    Replace <resource-group-name> with the name of the resource group in which your SQL server is located, <sql-server-name> with the name of your SQL server, and <retention-days> with the required retention period in days.

  4. After executing the command, verify the updated retention period using the command in step 2.

By following these steps, you can remediate the short auditing retention period for SQL servers in AZURE using AZURE CLI.

Using Python

To remediate the short auditing retention period for SQL Servers in Azure using Python, you can follow the below steps:

  1. Import the necessary libraries:
import os
from azure.identity import DefaultAzureCredential
from azure.mgmt.sql import SqlManagementClient
  1. Set the credentials and subscription ID:
credential = DefaultAzureCredential()
subscription_id = 'your_subscription_id'
  1. Create an instance of the SqlManagementClient:
sql_client = SqlManagementClient(credential, subscription_id)
  1. Get the list of SQL servers in the subscription:
servers = sql_client.servers.list()
  1. For each server, check the auditing retention period and update it if it is less than the desired value:
for server in servers:
    audit_policy = sql_client.server_audit_policies.get(server.resource_group, server.name, "default")
    if audit_policy.retention_days < 90:
        audit_policy.retention_days = 90
        sql_client.server_audit_policies.create_or_update(server.resource_group, server.name, "default", audit_policy)

In the above code, we are checking the retention period for the default audit policy of each SQL server. If the retention period is less than 90 days, we are updating it to 90 days.

Note: This code assumes that you have the necessary permissions to access and modify the audit policies of the SQL servers in your Azure subscription.

Using Terraform
resource "azurerm_mssql_server" "sql_server" {
  name                         = "SQL_SERVER_NAME"              # replace with your SQL Server name
  resource_group_name          = "RESOURCE_GROUP_NAME"          # replace with your resource group
  location                     = "AZURE_LOCATION"               # e.g. "eastus"
  version                      = "12.0"
  administrator_login          = "SQL_ADMIN_USERNAME"           # replace
  administrator_login_password = "SQL_ADMIN_PASSWORD"           # replace

  # other required arguments as needed …
}

resource "azurerm_storage_account" "audit_sa" {
  name                     = "AUDIT_STORAGE_ACCOUNT_NAME"       # must be globally unique
  resource_group_name      = azurerm_mssql_server.sql_server.resource_group_name
  location                 = azurerm_mssql_server.sql_server.location
  account_tier             = "Standard"
  account_replication_type = "LRS"

  # other required arguments as needed …
}

resource "azurerm_mssql_server_extended_auditing_policy" "sql_server_audit" {
  server_id = azurerm_mssql_server.sql_server.id

  storage_endpoint                        = azurerm_storage_account.audit_sa.primary_blob_endpoint
  storage_account_access_key             = azurerm_storage_account.audit_sa.primary_access_key
  storage_account_access_key_is_secondary = false

  retention_in_days = 90  # must be >= 90 to satisfy the check

  # other optional arguments (e.g. log_monitoring_enabled) as needed …
}

Changing retention_in_days on azurerm_mssql_server_extended_auditing_policy updates in place and does not replace the SQL server.

Verification: terraform plan should show an in-place update to azurerm_mssql_server_extended_auditing_policy.sql_server_audit with retention_in_days changing from its current value to 90.