Check for Unrestricted SQL Database Access

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.

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

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 

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.

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.

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.