SQL Databases should only allow ingress traffic from specific IP addresses

azure.security

Warning: This rule will be deprecated 18 December 2023 as part of the update to Azure CIS version 2.0.0

Description

Ensure that no SQL Databases allow ingress from 0.0.0.0/0 (any IP).

Default value

By default, Allow access to Azure Services is set to NO.

Rationale

SQL Server includes a firewall to block access to unauthorized connections. More granular IP addresses can be defined by referencing the range of addresses available from specific data centers. By default, for an SQL server, a firewall exists with start IP of 0.0.0.0 and end IP of 0.0.0.0, allowing access to all the Azure services. Additionally a custom rule can be set up with start IP of 0.0.0.0 and end IP of 255.255.255.255, allowing access from any IP over the Internet.

In order to reduce the potential attack surface for a SQL server, firewall rules should be defined with more granular IP addresses by referencing the range of addresses available from specific data centers.

Impact

Disabling allow access to Azure Services will break all connections to SQL server and hosted databases unless custom IP-specific rules are not added in Firewall Policy.

Firewall rules configured on individual SQL Database using Transact-SQL override the rules set on SQL Server. Azure does not provide any Powershell, API, CLI, or Portal option to check database level firewall rules, and so far, Transact-SQL is the only way to check for the same. For comprehensive control over egress traffic on SQL Databases, Firewall rules should be checked using an SQL client.

Remediation

From the console

  1. Go to SQL servers
  2. For each SQL server, click on Networking
  3. Uncheck the checkbox for Allow Azure services and resources to access this server
  4. Set firewall rules to limit access to only authorized connections

Using Azure CLI

Disable default firewall rule “Allow access to Azure services”:

az sql server firewall-rule delete \
    --resource-group <resource group> \
    --server <sql server name> --name "AllowAllWindowsAzureIps"

Remove custom firewall rule:

az sql server firewall-rule delete \ 
    --resource-group <resource group> \
    --server <sql server name> --name <firewall rule name>

Create a firewall rule:

az sql server firewall-rule create \
    --resource-group <resource group> \
    --server <sql server name> \
    --name <firewall rule name> \
    --start-ip-address "<IP Address other than 0.0.0.0>" \
    --end-ip-address "<IP Address other than 0.0.0.0 or 255.255.255.255>"

Update a firewall rule:

az sql server firewall-rule update \
    --resource-group <resource group> \
    --server <sql server name> \
    --name <firewall rule name> \
    --start-ip-address "<IP Address other than 0.0.0.0>" \
    --end-ip-address "<IP Address other than 0.0.0.0 or 255.255.255.255>"

Using PowerShell

Disable default firewall rule “Allow access to Azure services”:

Remove-AzureSqlServerFirewallRule \
    -FirewallRuleName "AllowAllWindowsAzureIps" \
    -ResourceGroupName <resource group name> \
    -ServerName <server name>

Remove custom firewall rule:

Remove-AzureSqlServerFirewallRule \
    -FirewallRuleName "<firewallRuleName>" \
    -ResourceGroupName <resource group name> \
    -ServerName <server name>

Set the appropriate firewall rules:

Set-AzureSqlServerFirewallRule \
    -ResourceGroupName <resource group name> \
    -ServerName <server name> \
    -FirewallRuleName "<firewallRuleName>" \
    -StartIpAddress "<IP Address other than 0.0.0.0>" \
    -EndIpAddress "<IP Address other than 0.0.0.0 or 255.255.255.255>"

References

  1. https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-windows-firewall-for-database-engine-access?view=sql-server-2017
  2. https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/get-azurermsqlserverfirewallrule?view=azurermps-5.2.0
  3. https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/set-azurermsqlserverfirewallrule?view=azurermps-5.2.0
  4. https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/remove-azurermsqlserverfirewallrule?view=azurermps-5.2.0
  5. https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure
  6. https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-database-firewall-rule-azure-sql-database?view=azuresqldb-current
  7. https://docs.microsoft.com/en-us/security/benchmark/azure/security-controls-v3-network-security#ns-2-secure-cloud-services-with-network-controls