Skip to main content

lacework-global-538

Ensure no Azure SQL Databases allow ingress from 0.0.0.0/0 (any IP) (Automated)

Profile Applicability

• Level 1

Description

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

Rationale

Azure 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 datacenters.

By default, for a SQL server, a Firewall exists with StartIp of 0.0.0.0 and EndIP of 0.0.0.0 allowing access to all the Azure services.

Additionally, a custom rule can be set up with StartIp of 0.0.0.0 and EndIP 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 datacenters.

Impact

Disabling Allow Azure services and resources to access this server will break all connections to SQL server and Hosted Databases unless custom IP specific rules are added in Firewall Policy.

Audit

From Azure Portal

  1. Go to SQL servers
  2. For each SQL server
  3. Click on Firewall and virtual networks
  4. Ensure that Allow Azure services and resources to access this server to set to No
  5. Ensure that no firewall rule exists with
    • Start IP of 0.0.0.0
    • or other combinations which allows access to wider public IP ranges

From Azure CLI

List all SQL servers

az sql server list

For each SQL server run the following command

az sql server firewall-rule list --resource-group <resource group name> --server <sql server name>

Ensure the output does not contain any firewall allow rules with a source of 0.0.0.0, or any rules named AllowAllWindowsAzureIps

From Azure PowerShell

Get the list of all SQL Servers

Get-AzSqlServer

For each Server

Get-AzSqlServerFirewallRule -ResourceGroupName <resource group name> -ServerName <server name>

Ensure that StartIpAddress is not set to 0.0.0.0, /0 or other combinations which allows access to wider public IP ranges including Windows Azure IP ranges. Also ensure that FirewallRuleName doesn't contain AllowAllWindowsAzureIps which is the rule created when the Allow Azure services and resources to access this server setting is enabled for that SQL Server.

Remediation

From Azure Portal

  1. Go to SQL servers.
  2. For each SQL server.
  3. Click Networking.
  4. Set Allow Azure services and resources to access this server to No.
  5. Set firewall rules to limit access to only authorized connections.

From Azure CLI

Disable default firewall rule Allow access to Azure services:

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

Remove a custom firewall rule:

az sql server firewall-rule delete --resource-group <resourceGroup> --server <sqlServerName> --name <firewallRuleName>

Create a firewall rule:

az sql server firewall-rule create --resource-group <resourceGroup> --server <sqlServerName> --name <firewallRuleName> --start-ip-address "<IPAddressOtherThan0.0.0.0>" --end-ip-address "<IPAddressOtherThan0.0.0.0Or255.255.255.255>"

Update a firewall rule:

az sql server firewall-rule update --resource-group <resourceGroup> --server <sqlServerName> --name <firewallRuleName> --start-ip-address "<IPAddressOtherThan0.0.0.0>" --end-ip-address "<IPAddressOtherThan0.0.0.0Or255.255.255.255>"

From Azure PowerShell

Disable Default Firewall Rule Allow access to Azure services:

Remove-AzSqlServerFirewallRule -FirewallRuleName "AllowAllWindowsAzureIps" -ResourceGroupName <resourceGroupName> -ServerName <serverName>

Remove a custom Firewall rule:

Remove-AzSqlServerFirewallRule -FirewallRuleName "<firewallRuleName>" -ResourceGroupName <resourceGroupName> -ServerName <serverName>

Set the appropriate firewall rules:

Set-AzSqlServerFirewallRule -ResourceGroupName <resourceGroupName> -ServerName <serverName> -FirewallRuleName "<firewallRuleName>" -StartIpAddress "<IPAddressOtherThan0.0.0.0>" -EndIpAddress "<IPAddressOtherThan0.0.0.0Or255.255.255.255>"

References

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

Additional Information

Firewall rules configured on an individual SQL Database using Transact-SQL overrides the rules set on SQL server. Azure does not provide any PowerShell, API, CLI, or Portal option to inspect database level firewall rules, and so far Transact-SQL is the only way to inspect the same. For comprehensive control over egress traffic on SQL Databases, use SQL client to inspect Firewall rules.