> ## 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 sqlserver threat detection disabled remediation

### Triage and Remediation

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

    <AccordionGroup>
      <Accordion title="Using Console" defaultOpen="true">
        To remediate the threat detection disabled misconfiguration for SQL Servers in Azure using the Azure console, follow these steps:

        1. Log in to the Azure portal ([https://portal.azure.com/](https://portal.azure.com/)).
        2. Navigate to the Azure SQL Server that you want to remediate.
        3. Click on the "Security" option in the left-hand menu.
        4. Click on the "Advanced Threat Protection" option.
        5. In the "Advanced Threat Protection" blade, click on the "Configure" button.
        6. In the "Configure Advanced Threat Protection" blade, toggle the switch for "Threat Detection" to the "On" position.
        7. Configure the settings for threat detection as per your requirements.
        8. Click on the "Save" button to save the changes.

        Once the above steps are completed successfully, the threat detection feature will be enabled for the Azure SQL Server, and you will be alerted about any potential threats or vulnerabilities.

        #
      </Accordion>

      <Accordion title="Using CLI">
        To remediate the misconfiguration of Threat Detection Disabled for SQL Servers in Azure using Azure CLI, follow the steps below:

        1. Open the Azure CLI on your local machine or use the Azure Cloud Shell.
        2. Login to your Azure account using the command: `az login`.
        3. Select the Azure subscription where the SQL Server is located using the command: `az account set --subscription <subscription_id>`.
        4. Get the resource ID of the SQL Server where the Threat Detection is disabled using the command: `az sql server show --name <sql_server_name> --resource-group <resource_group_name> --query id --output tsv`.
        5. Enable Threat Detection for the SQL Server using the command: `az sql server threat-policy update --resource-group <resource_group_name> --server <sql_server_name> --state Enabled`.

        Once executed successfully, Threat Detection will be enabled for the SQL Server in Azure. It is recommended to keep this feature enabled to detect potential security threats in the SQL Server environment.
      </Accordion>

      <Accordion title="Using Python">
        To remediate the misconfiguration of Threat Detection being disabled for SQL Servers in AZURE using Python, you can follow the below steps:

        1. Import the necessary libraries:

        ```
        from azure.common.credentials import ServicePrincipalCredentials
        from azure.mgmt.sql import SqlManagementClient
        ```

        2. Set the credentials for authentication:

        ```
        subscription_id = 'your_subscription_id'
        client_id = 'your_client_id'
        secret = 'your_secret_key'
        tenant = 'your_tenant_id'

        credentials = ServicePrincipalCredentials(client_id=client_id, secret=secret, tenant=tenant)
        ```

        3. Initialize the SQL Management Client:

        ```
        sql_client = SqlManagementClient(credentials, subscription_id)
        ```

        4. Get the list of SQL servers:

        ```
        servers = sql_client.servers.list()
        ```

        5. Loop through the servers and check if Threat Detection is enabled:

        ```
        for server in servers:
            threat_detection_policy = sql_client.server_security_alert_policies.get(server.resource_group_name, server.name)
            if not threat_detection_policy.state:
                # Enable Threat Detection
                threat_detection_policy.state = 'Enabled'
                sql_client.server_security_alert_policies.create_or_update(server.resource_group_name, server.name, threat_detection_policy)
        ```

        6. If Threat Detection is disabled, enable it:

        ```
            if not threat_detection_policy.state:
                # Enable Threat Detection
                threat_detection_policy.state = 'Enabled'
                sql_client.server_security_alert_policies.create_or_update(server.resource_group_name, server.name, threat_detection_policy)
        ```

        7. If Threat Detection is already enabled, print a message:

        ```
            else:
                print(f'Threat Detection is already enabled for SQL server {server.name}')
        ```

        8. Run the script to remediate the misconfiguration.

        This script will check all the SQL servers in the specified subscription and enable Threat Detection if it is disabled. If Threat Detection is already enabled, it will print a message stating that it is already enabled.
      </Accordion>

      <Accordion title="Using Terraform">
        ```hcl theme={null}
        resource "azurerm_mssql_server" "example" {
          name                         = "SQL_SERVER_NAME"              # e.g. "prod-sql-server-01"
          resource_group_name          = "RESOURCE_GROUP_NAME"
          location                     = "AZURE_LOCATION"               # e.g. "eastus"
          version                      = "12.0"
          administrator_login          = "SQL_ADMIN_USERNAME"
          administrator_login_password = "SQL_ADMIN_PASSWORD"

          # ...other settings...
        }

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

          state = "Enabled"

          # Optional tuning:
          # disabled_alerts = []  # keep empty to have all alerts enabled
          # email_account_admins = true
          # email_addresses      = ["SECURITY_TEAM_EMAIL"]  # e.g. "secops@example.com"
          # retention_days       = 30

          # If you use a storage account for threat detection logs:
          # storage_endpoint           = azurerm_storage_account.logs.primary_blob_endpoint
          # storage_account_access_key = azurerm_storage_account.logs.primary_access_key
        }
        ```

        This enables Advanced Threat Protection (threat detection) for the Azure SQL server; it does not force replacement of the server, only adds/updates the separate security alert policy resource.

        Verification: `terraform plan` should show a new `azurerm_mssql_server_security_alert_policy` being created (or updated) with `state: "Enabled"` for each `azurerm_mssql_server`.
      </Accordion>
    </AccordionGroup>
  </Tab>
</Tabs>