Azure Audit Sqldatabase Short Auditing Retention Remediation

Triage and Remediation

Remediation

Remediation

Using Console

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

Log in to the AZURE portal and navigate to the SQL server that needs to be remediated.
Click on the "Auditing" tab from the left-hand menu.
Under the "Auditing" tab, click on the "Diagnostic settings" option.
Click on the "Edit setting" option to modify the diagnostic settings.
Under the "Retention (days)" option, increase the retention period to a value that meets your organization's compliance requirements.
Click on the "Save" button to save the changes.
Once the changes are saved, you will receive a notification that the diagnostic settings have been updated successfully.

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

Using CLI

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

Open the Azure CLI and login to your Azure account using the command:
```
az login
```
Once you are logged in, select the subscription that contains the SQL database you want to remediate using the command:
```
az account set --subscription <subscription-id>
```
Replace <subscription-id> with the ID of the subscription that contains the SQL database.
Check the current auditing retention period for the SQL database using the command:
```
az sql db audit-policy show --name <database-name> --resource-group <resource-group-name> --server <server-name>
```
Replace <database-name>, <resource-group-name>, and <server-name> with the name of the SQL database, the resource group it belongs to, and the name of the server that hosts the database.
If the retention period is less than the required duration, remediate it by setting the retention period to the required duration using the command:
```
az sql db audit-policy update --name <database-name> --resource-group <resource-group-name> --server <server-name> --state Enabled --retention-days <retention-period>
```
Replace <database-name>, <resource-group-name>, and <server-name> with the name of the SQL database, the resource group it belongs to, and the name of the server that hosts the database. Replace <retention-period> with the required retention period in days.
Verify that the retention period has been updated by running the command in step 3 again.

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

Using Python

To remediate the short auditing retention period for SQL databases in Azure, you can use the following steps in Python:

Import the required modules:

import os
from azure.common.credentials import ServicePrincipalCredentials
from azure.mgmt.sql import SqlManagementClient

Set up the credentials to authenticate with Azure:

subscription_id = '<subscription_id>'
client_id = '<client_id>'
client_secret = '<client_secret>'
tenant_id = '<tenant_id>'

credentials = ServicePrincipalCredentials(
    client_id=client_id,
    secret=client_secret,
    tenant=tenant_id
)

Instantiate the SQL Management client:

sql_client = SqlManagementClient(
    credentials=credentials,
    subscription_id=subscription_id
)

Get the SQL server and database you want to remediate:

server_name = '<server_name>'
database_name = '<database_name>'

server = sql_client.servers.get_by_resource_group('<resource_group_name>', server_name)
database = sql_client.databases.get('<resource_group_name>', server_name, database_name)

Check the current auditing retention period for the database:

print(database.auditing_policy.retention_days)

Set the new auditing retention period for the database (e.g. 90 days):

database.auditing_policy.retention_days = 90
sql_client.databases.create_or_update('<resource_group_name>', server_name, database_name, database)

Verify that the new auditing retention period has been set:

print(database.auditing_policy.retention_days)

These steps will remediate the short auditing retention period for the specified SQL database in Azure.

Using Terraform

resource "azurerm_mssql_database" "example" {
  name           = "SQL_DB_NAME"            # replace with your DB name
  server_id      = azurerm_mssql_server.example.id
  sku_name       = "GP_Gen5_2"              # replace with your SKU
  collation      = "SQL_COLLATION"          # e.g. "SQL_Latin1_General_CP1_CI_AS"
  max_size_gb    = 32                       # adjust as needed
  zone_redundant = false
}

resource "azurerm_mssql_database_extended_auditing_policy" "example" {
  database_id = azurerm_mssql_database.example.id

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

  retention_in_days = 90 # must be >= 90 per the check
}

Changing only retention_in_days on azurerm_mssql_database_extended_auditing_policy does not force replacement of the database or storage account; it is an in‑place update.

After you update Terraform, terraform plan should show an in-place update (~) to the azurerm_mssql_database_extended_auditing_policy resource with retention_in_days changing from its current value to 90 (or higher).

Triage and Remediation​

Remediation​

Triage and Remediation

Remediation