lacework-global-538
4.1.2 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
- Go to
SQL servers
- For each SQL server
- Click on
Firewall and virtual networks
- Ensure that
Allow Azure services and resources to access this server
to set toNo
- 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
- Go to
SQL servers
- For each SQL server
- Click on
Firewall and virtual networks
- Set
Allow Azure services and resources to access this server
toNo
- 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 <resource group> --server <sql server name> --name "AllowAllWindowsAzureIps"
Remove a 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 <firewal lrule 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 <firewal lrule 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>"
From Azure PowerShell
Disable Default Firewall Rule Allow access to Azure services
:
Remove-AzSqlServerFirewallRule -FirewallRuleName "AllowAllWindowsAzureIps" -ResourceGroupName <resource group name> -ServerName <server name>
Remove a custom Firewall rule:
Remove-AzSqlServerFirewallRule -FirewallRuleName "<firewall rule name>" -ResourceGroupName <resource group name> -ServerName <server name>
Set the appropriate firewall rules:
Set-AzSqlServerFirewallRule -ResourceGroupName <resource group name> -ServerName <server name> -FirewallRuleName "<firewall rule name>" -StartIpAddress "<IP Address other than 0.0.0.0>" -EndIpAddress "<IP Address other than 0.0.0.0 or 255.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 individual SQL Database using Transact-sql overrides the rules set on SQL server. Azure does not provide any Powershell, API, CLI, 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 SQL client.