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

​

1. Open the Azure CLI and login to your Azure account using the command:
   Copy

   Ask AI

   az login
   

2. Once you are logged in, select the subscription that contains the SQL database you want to remediate using the command:
   Copy

   Ask AI

   az account set --subscription <subscription-id>
   

   Replace <subscription-id> with the ID of the subscription that contains the SQL database.
3. Check the current auditing retention period for the SQL database using the command:
   Copy

   Ask AI

   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.
4. If the retention period is less than the required duration, remediate it by setting the retention period to the required duration using the command:
   Copy

   Ask AI

   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.
5. Verify that the retention period has been updated by running the command in step 3 again.

1. Import the required modules:

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

2. 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
)

3. Instantiate the SQL Management client:

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

4. 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)

5. Check the current auditing retention period for the database:

print(database.auditing_policy.retention_days)

6. 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)

7. Verify that the new auditing retention period has been set:

print(database.auditing_policy.retention_days)