Check for Unrestricted SQL Database Access

Risk Level: Very High (act immediately)

Rule ID: Sql-010

Ensure that the firewall associated with your Microsoft Azure SQL servers is not configured to allow unrestricted inbound access (i.e., 0.0.0.0) in order to protect against unauthorized connections. By default, a Microsoft SQL server firewall contains a Start IP of 0.0.0.0 and an End IP of 0.0.0.0, allowing access to all Microsoft 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 to allow access from any IP address over the Internet. To reduce the potential attack surface for your Azure SQL servers, firewall rules should be configured with granular IPs by referencing the range of IP addresses available from specific, authorized networks.

This rule resolution is part of the Conformity Security & Compliance tool for Azure.

Security

Allowing unrestricted SQL database access can increase opportunities for malicious activities such as hacking and loss of data.

Enabling Allow Azure services and resources to access this server feature for SQL servers in the Azure Portal automatically creates a firewall rule with the source 0.0.0.0, which, while not exposing the server directly to the public Internet, permits access from a wide range of Microsoft-managed services and resources across different Azure tenants. Although this configuration may appear similar to public access, it does not grant access to the public Internet and differs in both scope and security risk profile.

Audit

To determine if the firewall configured for your Microsoft Azure SQL server allows unrestricted SQL access, perform the following operations:

Using Azure Console

01 Sign in to the Microsoft Azure Portal.

02 Navigate to All resources blade available at https://portal.azure.com/#browse/all to access all your Microsoft Azure cloud resources.

03 Choose the Azure subscription that you want to access from the Subscription equalls all filter box and choose Apply.

04 From the Type equals all filter box, select Type for Filter, Equals for Operator, and SQL server for Value, then choose Apply to list the Azure SQL database servers available in the selected subscription.

05 Click on the name (link) of the SQL database server that you want to examine.

06 In the resource navigation panel, under Security, select Networking to access the networking settings available for the selected SQL server.

07 Select the Public access tab and check the Public network access configuration setting to determine the level of access configured for the selected resource. If Public network access is set to Disabled, network access via public endpoints or selected networks is disabled, therefore, the Audit process stops here. If Public network access is set to Selected networks, continue the Audit process with the next step.

08 In the Firewall rules section, check the Start IPv4 address value for each firewall rule defined for your SQL server. If one or more firewall rules are configured with a Start IPv4 address value of 0.0.0.0, then the selected Microsoft Azure SQL server allows unrestricted inbound access to the associated SQL databases.

09 Repeat steps no. 5 – 8 for each SQL database server provisioned within the selected subscription.

10 Repeat steps no. 3 – 8 for each subscription created in your Microsoft Azure cloud account.

Using Azure PowerShell

01 Run Get-AzSqlServer PowerShell command (cmdlet) with custom output filters to list the name of each Microsoft SQL database server (and the associated resource group) available in the current Azure subscription:

Get-AzSqlServer | Select-Object ServerName,ResourceGroupName,PublicNetworkAccess

02 The command output should return the requested SQL database server information:

ServerName             ResourceGroupName           PublicNetworkAccess
----------             -----------------           -------------------
cc-project5-db-server  cc-project5-resource-group  Enabled
cc-prod-sql-db-server  cc-project5-resource-group  Disabled

If PublicNetworkAccess is set to Disabled, network access via public endpoints or selected networks is disabled (compliant), therefore, the Audit process stops here. If PublicNetworkAccess is set to Enabled, continue the Audit process with the next step.

03 Run Get-AzSqlServerAuditing PowerShell command with the name of the Microsoft SQL database server that you want to examine as the identifier parameter and custom output filters to describe the firewall rules configured for the selected SQL server:

Get-AzSqlServerFirewallRule -ServerName "cc-project5-db-server" -ResourceGroupName "cc-project5-resource-group"

04 The command output should return all the firewall rules configured for the selected resource:

ResourceGroupName : cc-project5-resource-group
ServerName        : cc-project5-db-server
StartIpAddress    : 0.0.0.0
EndIpAddress      : 0.0.0.0
FirewallRuleName  : AllowAllWindowsAzureIps

ResourceGroupName : cc-project5-resource-group
ServerName        : cc-project5-db-server
StartIpAddress    : 0.0.0.0
EndIpAddress      : 255.255.255.255
FirewallRuleName  : allow-network-access

If one or more firewall rules returned by the Get-AzSqlServerAuditing command output have the StartIpAddress value set to 0.0.0.0, as shown in the output example above, the selected Microsoft Azure SQL server allows unrestricted inbound access to the associated SQL databases hosted on the server.

05 Repeat steps no. 3 and 4 for each SQL database server provisioned in the selected subscription.

06 Repeat steps no. 1 – 5 for each subscription available within your Microsoft Azure cloud account.

Remediation / Resolution

To update your Microsoft SQL server firewall configuration in order to limit access to authorized networks and connections only, perform the following operations:

Using Azure Console

01 Sign in to the Microsoft Azure Portal.

02 Navigate to All resources blade available at https://portal.azure.com/#browse/all to access all your Microsoft Azure cloud resources.

03 Choose the Azure subscription that you want to access from the Subscription equalls all filter box and choose Apply.

05 Click on the name (link) of the SQL database server that you want to configure.

06 In the resource navigation panel, under Security, select Networking to access the networking settings available for the selected SQL server.

07 In the Firewall rules section, perform the following actions:

Choose the firewall rule with the Start IPv4 address set to 0.0.0.0 and click on the Delete button (bin icon) to remove the non-compliant rule from the server firewall. Repeat this step for all the firewall rules that allow unrestricted inbound access (i.e., with Start IPv4 address set to 0.0.0.0).
Choose Add a firewall rule to add a new, compliant rule to your SQL server firewall. For Rule name provide a unique name for the new rule. For Start IP and End IP, enter the trusted IPv4 address or IPv4 address range that can access your SQL server. For example, set Start IP and End IP to 10.0.10.15 to allow connections from a specific, trusted IP address.
You can also choose Add your client IPv4 address (\<your-ip-address\>) to create a new firewall rule that allows connections from your public IP address only.
Choose Save to apply the firewall changes.

08 Repeat steps no. 5 – 7 for each Azure SQL server that allows unrestricted inbound access, available in the selected Azure subscription.

09 Repeat steps no. 3 – 8 for each subscription created in your Microsoft Azure cloud account.

Using Azure PowerShell

01 Run Remove-AzSqlServerFirewallRule PowerShell command to disable the Allow Azure services and resources to access this server feature by removing the rule created by default for this service (i.e., "AllowAllWindowsAzureIps" rule) from your SQL database server firewall:

Remove-AzSqlServerFirewallRule -FirewallRuleName "AllowAllWindowsAzureIps" -ServerName "cc-project5-db-server" -ResourceGroupName "cc-project5-resource-group"

02 The command output should return the configuration information for the deleted firewall rule:

ResourceGroupName : cc-project5-resource-group
ServerName        : cc-project5-db-server
StartIpAddress    : 0.0.0.0
EndIpAddress      : 0.0.0.0
FirewallRuleName  : AllowAllWindowsAzureIps

03 Run Set-AzSqlServerFirewallRule PowerShell command to update the StartIpAddress and EndIpAddress configuration attributes value for the non-compliant firewall rule that you want to modify in order to restrict the SQL server access to a specific, trusted IP address or IP address range only:

Set-AzSqlServerFirewallRule "cc-project5-db-server" -ResourceGroupName "cc-project5-resource-group" -FirewallRuleName "allow-network-access" -StartIpAddress "192.168.0.15" -EndIpAddress "192.168.0.15"

04 The command output should return the configuration metadata for the modified firewall rule:

ResourceGroupName : cc-project5-resource-group
ServerName        : cc-project5-db-server
StartIpAddress    : 192.168.0.15
EndIpAddress      : 192.168.0.15
FirewallRuleName  : allow-network-access

05 Repeat steps no. 1 – 4 for each Azure SQL server that allows unrestricted inbound access, available within the current Azure subscription.

06 Repeat steps no. 1 – 5 for each subscription created within your Microsoft Azure cloud account.

References

Azure Official Documentation
Azure SQL Database and Azure Synapse Analytics network access controls
Use virtual network service endpoints and rules for servers in Azure SQL Database

Publication date Apr 2, 2020

Audit

Using Azure Console

Using Azure PowerShell

Remediation / Resolution

Using Azure Console

Using Azure PowerShell

References

Related Sql rules