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

# Azure audit sqldatabase short threat detection retention remediation

### Triage and Remediation

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

    <AccordionGroup>
      <Accordion title="Using Console" defaultOpen="true">
        Step-by-step instructions to remediate the misconfiguration "Short Threat Detection Retention Period for SQL Databases" for Azure using the Azure console are:

        1. Login to Azure portal ([https://portal.azure.com/](https://portal.azure.com/)).
        2. Navigate to the SQL server that has the short threat detection retention period.
        3. Click on the "Security" tab on the left-hand side of the page.
        4. Under "Advanced Threat Protection", click on "Advanced Threat Protection settings".
        5. In the "Advanced Threat Protection settings" page, scroll down to the "Data retention" section.
        6. Increase the retention period to the desired duration.
        7. Click on the "Save" button to save the changes.

        By following these steps, you will remediate the misconfiguration "Short Threat Detection Retention Period for SQL Databases" for Azure using the Azure console.

        #
      </Accordion>

      <Accordion title="Using CLI">
        To remediate the short threat detection retention period for SQL databases in Azure using Azure CLI, follow these steps:

        1. Open Azure CLI and login to your Azure account.

        2. Run the following command to set the retention period for the SQL database threat detection to 90 days:

        ```
        az sql db threat-policy update --resource-group <resource-group-name> --server <server-name> --database <database-name> --retention-days 90
        ```

        Replace `<resource-group-name>`, `<server-name>`, and `<database-name>` with the actual names of your resource group, server, and database.

        3. Verify that the retention period has been set to 90 days 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, including the retention period.

        4. Repeat these steps for all SQL databases in your Azure environment to ensure that the threat detection retention period is set to 90 days for all databases.

        By following these steps, you will remediate the short threat detection retention period for SQL databases in Azure using Azure CLI.
      </Accordion>

      <Accordion title="Using Python">
        The short threat detection retention period for SQL databases in Azure can be remediated using the Azure Python SDK. Here are the step-by-step instructions to remediate this issue:

        1. Install the Azure Python SDK using the following command:

           ```
           pip install azure-mgmt-monitor
           ```

        2. Authenticate to your Azure account using the Azure CLI or by setting the environment variables `AZURE_CLIENT_ID`, `AZURE_CLIENT_SECRET`, and `AZURE_TENANT_ID`.

        3. Import the necessary modules:

           ```
           from azure.common.credentials import ServicePrincipalCredentials
           from azure.mgmt.monitor import MonitorManagementClient
           from azure.mgmt.monitor.models import RetentionPolicy
           ```

        4. Define the credentials and the client:

           ```
           credentials = ServicePrincipalCredentials(
               client_id='<your-client-id>',
               secret='<your-client-secret>',
               tenant='<your-tenant-id>'
           )

           client = MonitorManagementClient(
               credentials,
               '<your-subscription-id>'
           )
           ```

        5. Get the current retention policy for the SQL databases:

           ```
           current_policy = client.metric_definitions.get(
               '<your-resource-group>',
               '<your-sql-server-name>',
               '<your-database-name>',
               'ThreatDetectionPolicy'
           ).retention_policy
           ```

        6. Update the retention policy to the desired value:

           ```
           new_policy = RetentionPolicy(enabled=True, days=30)
           client.metric_definitions.create_or_update(
               '<your-resource-group>',
               '<your-sql-server-name>',
               '<your-database-name>',
               'ThreatDetectionPolicy',
               {'retention_policy': new_policy}
           )
           ```

           In this example, we set the retention period to 30 days. You can adjust this value to meet your specific requirements.

        7. Verify that the retention policy has been updated by checking the current policy again:

           ```
           updated_policy = client.metric_definitions.get(
               '<your-resource-group>',
               '<your-sql-server-name>',
               '<your-database-name>',
               'ThreatDetectionPolicy'
           ).retention_policy

           print('Updated retention policy:', updated_policy)
           ```

           This should output the updated retention policy.

        That's it! With these steps, you should be able to remediate the short threat detection retention period for SQL databases in Azure using Python.
      </Accordion>

      <Accordion title="Using Terraform">
        ```hcl theme={null}
        resource "azurerm_mssql_database" "SQL_DATABASE" {
          name           = "SQL_DATABASE_NAME"              # replace with your DB name
          server_id      = azurerm_mssql_server.SQL_SERVER.id
          sku_name       = "P1"                             # replace with your SKU
          collation      = "SQL_Latin1_General_CP1_CI_AS"   # or your desired collation
          max_size_gb    = 10                               # adjust as needed
          zone_redundant = false
        }

        resource "azurerm_mssql_database_security_alert_policy" "SQL_DB_THREAT_DETECTION" {
          resource_group_name = azurerm_resource_group.RG.name
          database_name       = azurerm_mssql_database.SQL_DATABASE.name
          server_name         = azurerm_mssql_server.SQL_SERVER.name

          state                       = "Enabled"
          retention_days              = 90   # must be >= 90 to satisfy the check
          email_account_admins        = "Enabled"
          disabled_alerts             = []   # or specify alerts to disable
          email_addresses             = ["SECURITY_TEAM_EMAIL@example.com"] # replace as needed
          storage_endpoint            = azurerm_storage_account.AUDIT_STORAGE.primary_blob_endpoint
          storage_account_access_key  = azurerm_storage_account.AUDIT_STORAGE.primary_access_key
          storage_account_subscription_id = data.azurerm_client_config.current.subscription_id
        }
        ```

        Changing `retention_days` does not force replacement of the database or policy; it will update in place.

        Verification: `terraform plan` should show an in-place update on `azurerm_mssql_database_security_alert_policy.SQL_DB_THREAT_DETECTION` changing `retention_days` to `90` (or a higher value you choose).
      </Accordion>
    </AccordionGroup>
  </Tab>
</Tabs>