Azure Audit SQL Server Auto Failover Groups Remediation

Triage and Remediation

Remediation

Remediation

Using Console

To remediate the misconfiguration "Enable Auto-Failover Groups By Server" for Azure using Azure console, follow the below steps:

Login to Azure portal (https://portal.azure.com/).
Navigate to the SQL Server for which you want to enable auto-failover group.
Click on the "Failover groups" option in the left-hand side menu.
Click on the "Add" button to create a new failover group.
In the "Basic" tab, provide the below details:
- Name: Enter a name for the failover group.
- Subscription: Select the subscription in which the failover group should be created.
- Resource group: Select the resource group in which the failover group should be created.
- Region: Select the primary region for the failover group.
- Primary server: Select the primary SQL server for the failover group.
- Secondary region: Select the secondary region for the failover group.
- Secondary server: Select the secondary SQL server for the failover group.
In the "Advanced" tab, select the checkbox "Enable auto-failover groups by server".
Click on the "Review + create" button.
Review the details and click on the "Create" button to create the failover group.

Once the failover group is created with auto-failover groups enabled by server, the SQL server will be configured to automatically failover to the secondary server in case of any outage or failure in the primary server.

Using CLI

To remediate the misconfiguration "Enable Auto-Failover Groups By Server" in Azure using Azure CLI, follow the below steps:

Step 1: Open Azure CLI and login to your Azure account using the command below:

az login

Step 2: Once you are logged in, set the subscription where the server is located using the command below:

az account set --subscription <subscription_id>

Step 3: Check the current configuration of the server using the command below:

az sql server show --name <server_name> --resource-group <resource_group_name>

Step 4: If the "auto-failover" property is set to "Disabled", you can enable it using the command below:

az sql server update --name <server_name> --resource-group <resource_group_name> --auto-failover-group Enabled

Step 5: Once the command is executed successfully, verify the changes using the command below:

az sql server show --name <server_name> --resource-group <resource_group_name>

Step 6: Verify that the "auto-failover" property is set to "Enabled".

By following the above steps, you can remediate the misconfiguration "Enable Auto-Failover Groups By Server" in Azure using Azure CLI.

Using Python

To remediate the misconfiguration "Enable Auto-Failover Groups By Server" in Azure using Python, follow the below steps:

Import the required libraries:

from azure.common.credentials import ServicePrincipalCredentials
from azure.mgmt.sql import SqlManagementClient
from azure.mgmt.resource import ResourceManagementClient

Authenticate with Azure using Service Principal credentials:

TENANT_ID = 'your_tenant_id'
CLIENT_ID = 'your_client_id'
CLIENT_SECRET = 'your_client_secret'
SUBSCRIPTION_ID = 'your_subscription_id'

credentials = ServicePrincipalCredentials(
    client_id=CLIENT_ID,
    secret=CLIENT_SECRET,
    tenant=TENANT_ID
)

Create a resource management client:

resource_client = ResourceManagementClient(credentials, SUBSCRIPTION_ID)

Get the list of SQL servers:

sql_client = SqlManagementClient(credentials, SUBSCRIPTION_ID)
servers = sql_client.servers.list_by_resource_group(resource_group_name)

For each server, check if Auto-Failover is enabled, and if not, enable it:

for server in servers:
    failover_group_policies = sql_client.failover_groups.list_by_server(resource_group_name, server.name)
    if not failover_group_policies:
        failover_group_policy = sql_client.failover_groups.create_or_update(
            resource_group_name,
            server.name,
            {
                'read_write_endpoint': {
                    'failover_policy': 'Automatic',
                    'failover_with_data_loss_grace_period_minutes': 60
                },
                'partner_servers': []
            }
        )

Save the script and run it to enable Auto-Failover Groups By Server for all SQL servers in the specified resource group.

Note: Replace the placeholders "your_tenant_id", "your_client_id", "your_client_secret", "your_subscription_id" and "resource_group_name" with the appropriate values for your Azure environment.

Using Terraform

# Primary SQL Server (already exists in your env)
resource "azurerm_mssql_server" "PRIMARY_SQL_SERVER" {
  name                         = "PRIMARY-SQL-SERVER-NAME"          # replace
  resource_group_name          = "PRIMARY-RESOURCE-GROUP-NAME"      # replace
  location                     = "PRIMARY-LOCATION"                 # replace
  version                      = "12.0"
  administrator_login          = "SQL_ADMIN_USERNAME"               # replace
  administrator_login_password = "SQL_ADMIN_PASSWORD"               # replace
}

# Secondary SQL Server in a different region
resource "azurerm_mssql_server" "SECONDARY_SQL_SERVER" {
  name                         = "SECONDARY-SQL-SERVER-NAME"        # replace
  resource_group_name          = "SECONDARY-RESOURCE-GROUP-NAME"    # replace
  location                     = "SECONDARY-LOCATION"               # replace
  version                      = "12.0"
  administrator_login          = "SQL_ADMIN_USERNAME"               # same or different as needed
  administrator_login_password = "SQL_ADMIN_PASSWORD"
}

# Auto-failover group for all/some databases on the primary server
resource "azurerm_sql_failover_group" "PRIMARY_SQL_FAILOVER_GROUP" {
  name                = "FAILOVER-GROUP-NAME"                       # replace
  resource_group_name = azurerm_mssql_server.PRIMARY_SQL_SERVER.resource_group_name
  server_name         = azurerm_mssql_server.PRIMARY_SQL_SERVER.name

  partner_servers {
    id = azurerm_mssql_server.SECONDARY_SQL_SERVER.id
  }

  # Replicate specific databases; add all that must be protected
  databases = [
    "/subscriptions/SUBSCRIPTION_ID/resourceGroups/PRIMARY-RESOURCE-GROUP-NAME/providers/Microsoft.Sql/servers/PRIMARY-SQL-SERVER-NAME/databases/PRIMARY-DATABASE-NAME", # replace
  ]

  read_write_endpoint_failover_policy {
    mode          = "Automatic"
    grace_minutes = 60   # replace with your threshold for automatic failover
  }

  read_only_endpoint_failover_policy {
    mode = "Enabled"
  }
}

This change does not force replacement of the existing SQL servers or databases; it adds a new failover group resource that configures replication and automatic failover between them.

Verification: terraform plan should show + create for azurerm_sql_failover_group.PRIMARY_SQL_FAILOVER_GROUP (and for azurerm_mssql_server.SECONDARY_SQL_SERVER if it is new), with read_write_endpoint_failover_policy.mode = "Automatic" and the expected databases and partner_servers values.

Triage and Remediation​

Remediation​

Triage and Remediation

Remediation