> ## 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 Auditing Retention Period for SQL Servers

### More Info:

Auditing retention period should be greater than defined days. Default 90 days.

### Risk Level

Low

### Address

Security

### Compliance Standards

* CIS AZURE
* Cloudanix Best Practice
* HITRUST CSF
* ISO 27001
* NIST CSF
* PCI
* SOC2

### Triage and Remediation

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

    <AccordionGroup>
      <Accordion title="Using Console" defaultOpen="true">
        To remediate the short auditing retention period for SQL servers in Azure, follow these steps:

        1. Log in to the Azure Portal and go to the SQL Server that you want to remediate.

        2. Click on the "Auditing" option in the left-hand menu.

        3. In the "Auditing" section, click on "Diagnostic settings".

        4. Click on the "Add diagnostic setting" button.

        5. In the "Add diagnostic setting" window, give a name for the new diagnostic setting.

        6. Under "Destination details", select "Log Analytics" or "Event Hub" as the destination.

        7. If you choose "Log Analytics", select the Log Analytics workspace that you want to use.

        8. Under "Categories", select the "SQLSecurityAuditEvents" category.

        9. Under "Retention (days)", set the retention period to the desired number of days.

        10. Click on the "Save" button to save the diagnostic setting.

        Once the diagnostic setting is saved, the SQL server will start sending the audit logs to the destination you selected. The audit logs will be retained for the number of days you specified in the retention period.

        #
      </Accordion>

      <Accordion title="Using CLI">
        To remediate the short auditing retention period for SQL servers in AZURE using AZURE CLI, follow these steps:

        1. Open the AZURE CLI and log in to your AZURE account.

        2. Use the following command to check the current retention period for auditing logs in your SQL server:

           ```
           az sql server audit-policy show --resource-group <resource-group-name> --server <sql-server-name> --name "Default"
           ```

           Replace `<resource-group-name>` with the name of the resource group in which your SQL server is located, and `<sql-server-name>` with the name of your SQL server.

        3. If the retention period is less than the required period, use the following command to update the audit policy:

           ```
           az sql server audit-policy update --resource-group <resource-group-name> --server <sql-server-name> --name "Default" --state Enabled --retention-days <retention-days>
           ```

           Replace `<resource-group-name>` with the name of the resource group in which your SQL server is located, `<sql-server-name>` with the name of your SQL server, and `<retention-days>` with the required retention period in days.

        4. After executing the command, verify the updated retention period using the command in step 2.

        By following these steps, you can remediate the short auditing retention period for SQL servers in AZURE using AZURE CLI.
      </Accordion>

      <Accordion title="Using Python">
        To remediate the short auditing retention period for SQL Servers in Azure using Python, you can follow the below steps:

        1. Import the necessary libraries:

        ```python theme={null}
        import os
        from azure.identity import DefaultAzureCredential
        from azure.mgmt.sql import SqlManagementClient
        ```

        2. Set the credentials and subscription ID:

        ```python theme={null}
        credential = DefaultAzureCredential()
        subscription_id = 'your_subscription_id'
        ```

        3. Create an instance of the `SqlManagementClient`:

        ```python theme={null}
        sql_client = SqlManagementClient(credential, subscription_id)
        ```

        4. Get the list of SQL servers in the subscription:

        ```python theme={null}
        servers = sql_client.servers.list()
        ```

        5. For each server, check the auditing retention period and update it if it is less than the desired value:

        ```python theme={null}
        for server in servers:
            audit_policy = sql_client.server_audit_policies.get(server.resource_group, server.name, "default")
            if audit_policy.retention_days < 90:
                audit_policy.retention_days = 90
                sql_client.server_audit_policies.create_or_update(server.resource_group, server.name, "default", audit_policy)
        ```

        In the above code, we are checking the retention period for the default audit policy of each SQL server. If the retention period is less than 90 days, we are updating it to 90 days.

        Note: This code assumes that you have the necessary permissions to access and modify the audit policies of the SQL servers in your Azure subscription.
      </Accordion>

      <Accordion title="Using Terraform">
        ```hcl theme={null}
        resource "azurerm_mssql_server" "sql_server" {
          name                         = "SQL_SERVER_NAME"              # replace with your SQL Server name
          resource_group_name          = "RESOURCE_GROUP_NAME"          # replace with your resource group
          location                     = "AZURE_LOCATION"               # e.g. "eastus"
          version                      = "12.0"
          administrator_login          = "SQL_ADMIN_USERNAME"           # replace
          administrator_login_password = "SQL_ADMIN_PASSWORD"           # replace

          # other required arguments as needed …
        }

        resource "azurerm_storage_account" "audit_sa" {
          name                     = "AUDIT_STORAGE_ACCOUNT_NAME"       # must be globally unique
          resource_group_name      = azurerm_mssql_server.sql_server.resource_group_name
          location                 = azurerm_mssql_server.sql_server.location
          account_tier             = "Standard"
          account_replication_type = "LRS"

          # other required arguments as needed …
        }

        resource "azurerm_mssql_server_extended_auditing_policy" "sql_server_audit" {
          server_id = azurerm_mssql_server.sql_server.id

          storage_endpoint                        = azurerm_storage_account.audit_sa.primary_blob_endpoint
          storage_account_access_key             = azurerm_storage_account.audit_sa.primary_access_key
          storage_account_access_key_is_secondary = false

          retention_in_days = 90  # must be >= 90 to satisfy the check

          # other optional arguments (e.g. log_monitoring_enabled) as needed …
        }
        ```

        Changing `retention_in_days` on `azurerm_mssql_server_extended_auditing_policy` updates in place and does not replace the SQL server.

        Verification: `terraform plan` should show an in-place `update` to `azurerm_mssql_server_extended_auditing_policy.sql_server_audit` with `retention_in_days` changing from its current value to `90`.
      </Accordion>
    </AccordionGroup>
  </Tab>
</Tabs>