Azure Introduction
Azure Pricing
Azure Threats
Short Auditing Retention Period for SQL Databases
More Info:
Auditing retention period should be greater than defined days. Default 90 days.
Risk Level
Medium
Address
Security
Compliance Standards
HITRUST, SOC2, NISTCSF, PCIDSS
Triage and Remediation
Remediation
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.
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.
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.