|   Trend Micro Cloud One™
Open menu

Check for Unrestricted SQL Database Access

Cloud Conformity allows you to automate the auditing process of this resolution page. Register for a 14 day evaluation and check your compliance level for free!

Start a Free Trial Product features
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 Cloud 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 Firewalls and virtual networks to access the firewall configuration set for the selected database server.

06 On the Firewalls and virtual networks page, check the Allow access to Azure services setting status. If setting status is set to ON, the selected SQL database server accepts communication from any subnet, therefore the server allows unrestricted access to the hosted SQL databases. If Allow access to Azure services is set to OFF, check the firewall rules for any entry with the START IP set to 0.0.0.0. If one or more rules are configured with START IP of 0.0.0.0, the selected Microsoft Azure SQL server allows unrestricted inbound access to the associated SQL databases.

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

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

Using Azure CLI

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

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

ServerName          ResourceGroupName
----------          -----------------
cc-prod-sql-server  cloud-shell-storage-westeurope
cc-project5-server  cloud-shell-storage-westeurope

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 "cc-prod-sql-server"
	-ResourceGroupName "cloud-shell-storage-westeurope"

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

ResourceGroupName : cloud-shell-storage-westeurope
ServerName        : cc-prod-sql-server
StartIpAddress    : 0.0.0.0
EndIpAddress      : 0.0.0.0
FirewallRuleName  : AllowAllWindowsAzureIps

ResourceGroupName : cloud-shell-storage-westeurope
ServerName        : cc-prod-sql-server
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 Firewalls and virtual networks to access the firewall configuration set for the selected database server.

06 On the Firewalls and virtual networks page, perform the following:

  1. Select OFF next to Allow access to Azure services setting to disable database access to other Azure services.
  2. Select the firewall rule with the START IP 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.
  3. Use RULE NAME, START IP and END IP forms to define new firewall rules 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 only or set START IP to 10.0.1.0 and END IP to 10.0.1.255 to allow access from an IP address range. You can also use Add client IP button to create a new firewall rule that allows connections from your public IP address only.
  4. Click Save to apply the changes.

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 CLI

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

Unlock the Remediation Steps

Gain free unlimited access to our full Knowledge Base


Over 600 rules & best practices for and

Get started for FREE

A verification email will be sent to this address
We keep your information private. Learn more.

Thank you!

Please click the link in the confirmation email sent to

You are auditing:

Check for Unrestricted SQL Database Access

Risk level: Very High