Azure Introduction
Azure Pricing
Azure Threats
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
ISO27001, CISAZURE, CBP, HITRUST, SOC2, NISTCSF, PCIDSS
Triage and Remediation
Remediation
To remediate the short auditing retention period for SQL servers in Azure, follow these steps:
-
Log in to the Azure Portal and go to the SQL Server that you want to remediate.
-
Click on the “Auditing” option in the left-hand menu.
-
In the “Auditing” section, click on “Diagnostic settings”.
-
Click on the “Add diagnostic setting” button.
-
In the “Add diagnostic setting” window, give a name for the new diagnostic setting.
-
Under “Destination details”, select “Log Analytics” or “Event Hub” as the destination.
-
If you choose “Log Analytics”, select the Log Analytics workspace that you want to use.
-
Under “Categories”, select the “SQLSecurityAuditEvents” category.
-
Under “Retention (days)”, set the retention period to the desired number of days.
-
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.
To remediate the short auditing retention period for SQL servers in AZURE using AZURE CLI, follow these steps:
-
Open the AZURE CLI and log in to your AZURE account.
-
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. -
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. -
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.
To remediate the short auditing retention period for SQL Servers in Azure using Python, you can follow the below steps:
- Import the necessary libraries:
import os
from azure.identity import DefaultAzureCredential
from azure.mgmt.sql import SqlManagementClient
- Set the credentials and subscription ID:
credential = DefaultAzureCredential()
subscription_id = 'your_subscription_id'
- Create an instance of the
SqlManagementClient:
sql_client = SqlManagementClient(credential, subscription_id)
- Get the list of SQL servers in the subscription:
servers = sql_client.servers.list()
- 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.