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

CIS AZURE
Cloudanix Best Practice
HITRUST CSF
NIST CSF
SOC2

Triage and Remediation

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:

Log in to the Azure portal (https://portal.azure.com/).
In the left-hand menu, click on "SQL servers".
Select the SQL server you want to remediate.
In the left-hand menu of the SQL server page, click on "Advanced Data Security".
On the Advanced Data Security page, click on the "Settings" tab.
Scroll down to the "Data retention" section.
Increase the retention period to the desired value (e.g. 90 days).
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.

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:

Open Azure CLI and log in to your Azure account.
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
```
Wait for the command to complete. This may take a few minutes.
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.

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:

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

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
)

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
)

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.

Using Terraform

resource "azurerm_mssql_server" "example" {
  name                         = "SQL_SERVER_NAME"          # e.g. my-sql-server
  resource_group_name          = "RESOURCE_GROUP_NAME"
  location                     = "AZURE_REGION"             # e.g. eastus
  version                      = "12.0"
  administrator_login          = "SQL_ADMIN_USERNAME"
  administrator_login_password = "SQL_ADMIN_PASSWORD"
}

resource "azurerm_mssql_server_azuread_administrator" "example" {
  server_id = azurerm_mssql_server.example.id

  login_username = "AAD_ADMIN_DISPLAY_NAME"                # e.g. sql-aad-admin
  object_id      = "AAD_PRINCIPAL_OBJECT_ID"               # from Azure AD
  tenant_id      = "AAD_TENANT_ID"                         # directory tenant ID
}

This adds an Azure AD administrator to the specified SQL server without replacing the server. After updating your configuration, terraform plan should show a new azurerm_mssql_server_azuread_administrator resource being created (and no replacement of the existing azurerm_mssql_server).

More Info:​

Risk Level​

Address​

Compliance Standards​

Triage and Remediation​

Remediation​