Logo of Trend Micro
Use the Conformity Knowledge Base AI to help improve your Cloud Posture

Check for Unrestricted SQL Database Access

Trend Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 1000 automated best practice checks.

Risk Level: Very High (act immediately)
Rule ID: Sql-010

Ensure that firewalls associated with your Microsoft Azure SQL servers are not configured to allow unrestricted inbound access (i.e. 0.0.0.0/0) in order to protect against unauthorized connections. By default, a Microsoft SQL server firewall contains a StartIP of 0.0.0.0 and an EndIP of 0.0.0.0, allowing access to all Microsoft Azure services. Additionally, a custom rule can be set up with StartIP of 0.0.0.0 and EndIP of 255.255.255.255 to allow access from any IP address over the Internet. To reduce the potential attack surface for your 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.

Audit

To determine if your Microsoft SQL server firewalls allow unrestricted SQL access, perform the following actions:

Using Azure Console

01 Sign in to Azure Management Console.

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

03 From the Type filter box, select SQL server to list only the SQL database servers currently available in your account.

04 Click on the name of the SQL server that you want to examine.

05 In the navigation panel, under Security, select Networking to access the firewall configuration set for the selected database server.

06 Under the Public access tab, if Public network access is set to Disabled, then no direct public access to the SQL Server is possible and the server is compliant. If Public network access is set to Selected networks, you will need to check the Firewall rules to determine if there is unrestricted access.

07 Under Firewall rules, check each of the firewall rules for their Start IPv4 address. If one or more rules are configured with Start IPv4 address of 0.0.0.0, the selected Microsoft Azure SQL server allows unrestricted inbound access to the associated SQL databases.

08 Repeat steps no. 4 – 7 for each SQL database server provisioned within the selected subscription.

09 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) using custom query filters to list the names of all SQL database servers (and their associated resource groups) 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
----------  -----------------  —------------------
server1     rg-1               Enabled
server2     rg-1               Disabled

If PublicNetworkAccess is set to disabled, no direct public access is possible and your SQL Server is compliant.

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

Get-AzSqlServerFirewallRule
  -ServerName "server1"
  -ResourceGroupName "rg-1"

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

ResourceGroupName : rg-1
ServerName        : server1
StartIpAddress    : 0.0.0.0
EndIpAddress      : 0.0.0.0
FirewallRuleName  : AllowAllWindowsAzureIps

ResourceGroupName : rg-1
ServerName        : server1
StartIpAddress    : 0.0.0.0
EndIpAddress      : 255.255.255.255
FirewallRuleName  : cc-database-access-rule1

If one or more firewall rules returned by the Get-AzSqlServerAuditing command output have the StartIpAddress configuration attribute value set to 0.0.0.0 (i.e. any IP address), as shown in the example above, the selected Microsoft Azure SQL server allows unrestricted ingress/inbound access for all the SQL databases hosted on the server.

05 Repeat step no. 3 and 4 for each Azure 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 Azure SQL server firewall configuration in order to limit access to authorized connections only, perform the following actions:

Using Azure Console

01 Sign in to Azure Management Console.

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

03 From the Type filter box, select SQL server to list only the SQL database servers currently available in your account.

04 Click on the name of the SQL server that you want to reconfigure (see Audit section part I to identify the right database server).

05 In the navigation panel, under Security, select Networking to access the firewall configuration set for the selected database server.

06 On the Networking page, on the Public access tab, perform the following:

  1. Under Firewall rules, select the firewall rule with the Start IPv4 address set to 0.0.0.0., click on the context menu (...), then select Delete to remove the noncompliant rule from the selected server firewall. Repeat this step for all the firewall rules that allow unrestricted inbound access.
  2. Click on Add a firewall rule. Add a Rule name, Start IP and End IP based on your access requirements. For example, set Start IP and End IP to 10.0.1.5 to allow connections from a specific IP address. You can also use Add your client IPv4 address button to create a new firewall rule that allows connections from your public IP address only.
  3. Click Save to apply the changes.
  4. If you do not require any direct access from a known IP address, set Public network access to Disabled and click save.

07 Repeat steps no. 3 – 6 for all the Azure SQL servers that allow unrestricted inbound access to their SQL databases, available in the selected Azure subscription.

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

Using Azure PowerShell

01 Run Remove-AzSqlServerFirewallRule PowerShell command to disable "Allow access to Azure services" feature by removing the required rule (i.e. "AllowAllWindowsAzureIps") from the selected SQL database server firewall: 

Remove-AzSqlServerFirewallRule
-FirewallRuleName "AllowAllWindowsAzureIps"
-ServerName "cc-prod-sql-server"
-ResourceGroupName "cloud-shell-storage-westeurope"

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

ResourceGroupName : cloud-shell-storage-westeurope
ServerName        : cc-prod-sql-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 attribute values for the SQL server firewall rule that you want to modify (see Audit section part II to identify the right firewall rule) in order to restrict the database access to a specific IP address/IP address range only: 

Set-AzSqlServerFirewallRule -ServerName "cc-prod-sql-server"
-ResourceGroupName "cloud-shell-storage-westeurope"
-FirewallRuleName "cc-allow-public-access-rule"
-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 : cloud-shell-storage-westeurope
ServerName        : cc-prod-sql-server
StartIpAddress    : 192.168.0.15
EndIpAddress      : 192.168.0.15
FirewallRuleName  : cc-allow-public-access-rule

05 Repeat step no. 1 – 4 for all the Azure SQL servers that allow unrestricted inbound access to their SQL databases, available within the current Azure subscription.

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

References

Publication date Apr 2, 2020

Related Sql rules