Skip to main content

Azure Audit SQL Server Unrestricted Database Access Fix

Triage and Remediation​

Remediation​

Using Console

To remediate the misconfiguration "SQL Database Servers Should Not Have Unrestricted Access" in AZURE using AZURE console, follow these steps:

  1. Log in to the Azure portal.
  2. Navigate to the SQL database server that has unrestricted access.
  3. Click on the "Firewalls and virtual networks" option under the "Security" section in the left-hand menu.
  4. Under the "Firewall rules" section, click on the "Add client IP" button to add your IP address to the allowed list.
  5. If you want to allow access to specific IP addresses or ranges, click on the "Add IP range" button and enter the appropriate information.
  6. Click on the "Save" button to apply the changes.

By following these steps, you have successfully remediated the misconfiguration "SQL Database Servers Should Not Have Unrestricted Access" in AZURE using AZURE console.

Using CLI

To remediate the misconfiguration "SQL Database Servers Should Not Have Unrestricted Access" in Azure using Azure CLI, follow these steps:

  1. Open the Azure CLI and login to your Azure account.
  2. Identify the SQL Database Server that has unrestricted access by running the following command: 
    az sql server list
    This will list all the SQL Database Servers in your Azure account.
  3. Once you have identified the SQL Database Server, run the following command to update the firewall rules: 
    az sql server firewall-rule update --resource-group <resource-group-name> --server <server-name> --name AllowAllWindowsAzureIps --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0
    This command will update the firewall rule named "AllowAllWindowsAzureIps" to restrict access to the SQL Database Server to only Azure services and resources.
  4. Verify that the firewall rule has been updated by running the following command: 
    az sql server firewall-rule show --resource-group <resource-group-name> --server <server-name> --name AllowAllWindowsAzureIps
    This command will show the details of the updated firewall rule.

By following these steps, you have successfully remediated the misconfiguration "SQL Database Servers Should Not Have Unrestricted Access" in Azure using Azure CLI.

Using Python

To remediate the misconfiguration "SQL Database Servers Should Not Have Unrestricted Access" in Azure using Python, you can use the following steps:

  1. Import the necessary libraries and authenticate to Azure using the Azure SDK for Python.
from azure.identity import DefaultAzureCredential
from azure.mgmt.sql import SqlManagementClient
from azure.mgmt.resource import ResourceManagementClient

credential = DefaultAzureCredential()
subscription_id = '<your-subscription-id>'

resource_client = ResourceManagementClient(credential, subscription_id)
sql_client = SqlManagementClient(credential, subscription_id)
  1. Retrieve the list of SQL servers in your subscription.
servers = sql_client.servers.list()
  1. For each SQL server, check if it has any firewall rules that allow unrestricted access.
for server in servers:
    firewall_rules = sql_client.firewall_rules.list_by_server(server.resource_group, server.name)
    for rule in firewall_rules:
        if rule.start_ip_address == '0.0.0.0' and rule.end_ip_address == '255.255.255.255':
            # This firewall rule allows unrestricted access, so delete it
            sql_client.firewall_rules.delete(server.resource_group, server.name, rule.name)
  1. Once all the firewall rules have been deleted, you can verify that the SQL servers no longer have unrestricted access.
for server in servers:
    firewall_rules = sql_client.firewall_rules.list_by_server(server.resource_group, server.name)
    for rule in firewall_rules:
        if rule.start_ip_address == '0.0.0.0' and rule.end_ip_address == '255.255.255.255':
            print(f"Server {server.name} still has a firewall rule that allows unrestricted access.")
        else:
            print(f"Server {server.name} has no firewall rules that allow unrestricted access.")

These steps will remediate the misconfiguration "SQL Database Servers Should Not Have Unrestricted Access" in Azure using Python.

Using Terraform
resource "azurerm_mssql_server" "sql_server" {
  name                         = "SQL_SERVER_NAME"          # replace with your server name
  resource_group_name          = "RESOURCE_GROUP_NAME"      # replace with your RG
  location                     = "AZURE_REGION"             # e.g. "eastus"
  version                      = "12.0"
  administrator_login          = "SQL_ADMIN_USERNAME"       # replace
  administrator_login_password = "SQL_ADMIN_PASSWORD"       # replace, or use Key Vault
  minimum_tls_version          = "1.2"

  # Disable public network access so the server is *not* exposed to the internet
  public_network_access_enabled = false
}

# If you must allow public access from specific IPs, enable public_network_access_enabled = true
# above and create *narrow* firewall rules instead of 0.0.0.0–255.255.255.255:

resource "azurerm_mssql_firewall_rule" "office_ip" {
  name             = "office-ip-allow"
  server_id        = azurerm_mssql_server.sql_server.id
  start_ip_address = "OFFICE_START_IP"   # replace, e.g. "203.0.113.10"
  end_ip_address   = "OFFICE_END_IP"     # replace, e.g. "203.0.113.10"
}

If you currently have a Terraform-managed azurerm_mssql_firewall_rule with start_ip_address = "0.0.0.0" and end_ip_address = "255.255.255.255", remove that resource (or change it to a restricted range as above); Terraform will plan to destroy or update only that firewall rule, not the server. Changing public_network_access_enabled does not force replacement of the SQL server.

For verification, terraform plan should show:

  • Either an update to azurerm_mssql_server.sql_server setting public_network_access_enabled from true to false, and/or
  • A destroy or change of any firewall rule that previously allowed 0.0.0.0–255.255.255.255, replaced by one or more rules with specific IP ranges.