Azure Introduction
Azure Pricing
Azure Threats
Enable Auto-Failover Groups By Server
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
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.
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:
- 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.