> ## Documentation Index
> Fetch the complete documentation index at: https://cloudanix.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Short Threat Detection Retention Period for SQL Servers

### 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

<Tabs>
  <Tab title="Remediation">
    ### Remediation

    <AccordionGroup>
      <Accordion title="Using Console" defaultOpen="true">
        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/](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.

        #
      </Accordion>

      <Accordion title="Using CLI">
        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.
      </Accordion>

      <Accordion title="Using Python">
        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:

           ```python theme={null}
           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:

           ```python theme={null}
           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.
      </Accordion>
    </AccordionGroup>
  </Tab>
</Tabs>