​
More Info:

Set an Azure Active Directory admin for every SQL server.

​
Risk Level

Medium

​
Address

Security

​
Compliance Standards

CISAZURE, CBP, HITRUST, SOC2, NISTCSF

​
Triage and Remediation

​
Remediation

Using Console

Sure, here are the step-by-step instructions to remediate the misconfiguration of a short threat detection retention period for SQL Servers in Azure:
  1. Log in to the Azure portal (https://portal.azure.com/).
  2. In the left-hand menu, click on “SQL servers”.
  3. Select the SQL server you want to remediate.
  4. In the left-hand menu of the SQL server page, click on “Advanced Data Security”.
  5. On the Advanced Data Security page, click on the “Settings” tab.
  6. Scroll down to the “Data retention” section.
  7. Increase the retention period to the desired value (e.g. 90 days).
  8. Click on the “Save” button at the top of the page to save the changes.
Once you have completed these steps, your SQL server will have a longer threat detection retention period. This will allow you to detect and investigate security threats over a longer period of time, improving your overall security posture.

​

Short Threat Detection Retention Period for SQL Servers means that the logs for threat detection are not being retained for a sufficient period of time. This can make it difficult to detect and investigate security incidents. To remediate this issue for Azure SQL Servers using Azure CLI, follow these steps:
  1. Open Azure CLI and log in to your Azure account.
  2. Run the following command to set the retention period for threat detection logs: 
    az sql db threat-policy update --resource-group <resource-group-name> --server <server-name> --database <database-name> --retention-day <number-of-days>
    Replace <resource-group-name>, <server-name>, <database-name> and <number-of-days> with the appropriate values for your environment. For example, if you want to set the retention period to 30 days for a database named “mydatabase” on a server named “myserver” in a resource group named “myresourcegroup”, you would run the following command: 
    az sql db threat-policy update --resource-group myresourcegroup --server myserver --database mydatabase --retention-day 30
  3. Wait for the command to complete. This may take a few minutes.
  4. Verify that the retention period has been set correctly by running the following command: 
    az sql db threat-policy show --resource-group <resource-group-name> --server <server-name> --database <database-name>
    This command will display the current threat detection policy for the specified database. Verify that the “retentionDays” property is set to the value you specified in step 2.
By following these steps, you can remediate the short threat detection retention period for Azure SQL Servers using Azure CLI.
The remediation process for this misconfiguration involves increasing the threat detection retention period for SQL Servers in AZURE using Python. Here are the step-by-step instructions to remediate this issue:
  1. First, you need to install the azure-mgmt-monitor and azure-common Python packages. You can do this using the following command: 
    pip install azure-mgmt-monitor azure-common
  2. Next, you need to authenticate to your AZURE account using the ServicePrincipalCredentials class from the azure.common.credentials package. You will need to provide your AZURE subscription ID, client ID, client secret, and tenant ID. Here’s an example: 
    from azure.common.credentials import ServicePrincipalCredentials

subscription_id = 'your-subscription-id'
client_id = 'your-client-id'
client_secret = 'your-client-secret'
tenant_id = 'your-tenant-id'

credentials = ServicePrincipalCredentials(
    client_id=client_id,
    secret=client_secret,
    tenant=tenant_id
)
  3. Once you have authenticated, you can use the MonitorManagementClient class from the azure.mgmt.monitor package to update the threat detection retention period for your SQL Servers. You will need to provide the name of your AZURE resource group, the name of your SQL Server, and the new retention period in days. Here’s an example: 
    from azure.mgmt.monitor import MonitorManagementClient
from azure.mgmt.monitor.models import DiagnosticSettingsResource

resource_group_name = 'your-resource-group-name'
server_name = 'your-sql-server-name'
retention_days = 90

client = MonitorManagementClient(credentials, subscription_id)

settings = DiagnosticSettingsResource(
    storage_account_id=None,
    workspace_id=None,
    metrics=None,
    logs=[{
        'category': 'SQLSecurityAuditEvents',
        'enabled': True,
        'retentionPolicy': {
            'days': retention_days,
            'enabled': True
        }
    }]
)

client.diagnostic_settings.create_or_update(
    resource_group_name=resource_group_name,
    resource_uri=f'/subscriptions/{subscription_id}/resourceGroups/{resource_group_name}/providers/Microsoft.Sql/servers/{server_name}',
    parameters=settings
)
  4. Finally, you can run the Python script to update the threat detection retention period for your SQL Servers in AZURE.
By following these steps, you can remediate the misconfiguration of short threat detection retention period for SQL Servers in AZURE using Python.