​
More Info:

Ensure that Microsoft Azure SQL database servers are using auto-failover groups in order to enable database replication and automatic failover. A Microsoft Azure SQL failover group is designed to automatically manage replication, connectivity, high availability and failover for a set of SQL databases.

​
Risk Level

Medium

​
Address

Security

​
Compliance Standards

CBP

​
Triage and Remediation

​
Remediation

Using Console

To remediate the misconfiguration “Enable Auto-Failover Groups By Server” for Azure using Azure console, follow the below steps:
  1. Login to Azure portal (https://portal.azure.com/).
  2. Navigate to the SQL Server for which you want to enable auto-failover group.
  3. Click on the “Failover groups” option in the left-hand side menu.
  4. Click on the “Add” button to create a new failover group.
  5. 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.
  6. In the “Advanced” tab, select the checkbox “Enable auto-failover groups by server”.
  7. Click on the “Review + create” button.
  8. 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.

​

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.
To remediate the misconfiguration “Enable Auto-Failover Groups By Server” in Azure using Python, follow the below steps:
  1. Import the required libraries:
from azure.common.credentials import ServicePrincipalCredentials
from azure.mgmt.sql import SqlManagementClient
from azure.mgmt.resource import ResourceManagementClient
  1. 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
)
  1. Create a resource management client:
resource_client = ResourceManagementClient(credentials, SUBSCRIPTION_ID)
  1. Get the list of SQL servers:
sql_client = SqlManagementClient(credentials, SUBSCRIPTION_ID)
servers = sql_client.servers.list_by_resource_group(resource_group_name)
  1. 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': []
            }
        )
  1. 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.