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

Check for Publicly Accessible SQL Servers

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

Risk Level: High (not acceptable risk)
Rule ID: Sql-013

Ensure that your Microsoft Azure SQL database servers are accessible through private endpoints instead of public IP addresses or service endpoints, in order to eliminate the exposure from the public Internet.

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

Security

You can connect to your Azure SQL database servers either publicly, via public IP addresses or service endpoints, or privately, using a private endpoint. A private endpoint connection is associated with a private IP address available within an Azure virtual network. This virtual network usually contains the virtual machines (VMs) that can access your targeted private resource – in this case, the SQL database server.

Note: Private endpoint connections are defined at the SQL server level and they provide access to all databases on the server.

Audit

To determine if your Azure SQL database servers are accessible via private endpoints only, perform the following actions:

Note: Auditing Azure SQL database servers for configured private endpoints using Azure CLI or Azure PowerShell is not currently supported.

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 provisioned in your Azure account.

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

05 In the navigation panel, under Security, select Private endpoint connections to view the private endpoint connection(s) configured for the selected database server.

06 On the Private endpoint connections page, check the list with the available private endpoint connections. If there are no private endpoint connections configured, instead a "No results" status is displayed, the selected Microsoft Azure SQL database server is not configured to be accessed via private endpoints, therefore the resource is publicly accessible.

07 Repeat steps no. 4 – 6 for other SQL database servers available within the current Azure subscription.

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

Remediation / Resolution

To configure your Microsoft Azure SQL database servers to be accessible via private endpoints only, perform the following actions:

Using Azure Console

01 Sign in to Azure Management Console.

02 Navigate to Private link center blade at https://portal.azure.com/#blade/Microsoft_Azure_Network/PrivateLinkCenterBlade/overview.

03 In the navigation panel, select Overview, then click Start under Build a private connection to a service option to initiate the setup process.

04 On the Create a private endpoint page, perform the following:

  1. On the Basics panel, provide the main configuration details such as the endpoint name and region, the Azure subscription and the resource group where the private endpoint will be created. Click Next: Resource > to continue the process.
  2. On the Resource panel, choose Connect to an Azure resource in my directory as connection method, then select the name of the SQL database server that you want to configure for private endpoint access (see Audit section part I to identify the right SQL server) from the Resource dropdown list. Once all the required details are provided, click Next: Configuration > to continue.
  3. On the Configuration panel, select the Azure virtual network and the subnet where the new private endpoint will be deployed. Also, choose Yes next to Integrate with private DNS zone to enable Private DNS integration. Click Next: Tags > to continue the setup process.
  4. On the Tags panel, create the necessary resource tags for your new private endpoint. Once the tags are created, click Next: Review + create > to continue.
  5. On the Review + create panel, review and validate the configuration details provided, then click Create to create the new private endpoint, powered by Azure Private Link. The new private endpoint may take few minutes to fully deploy.

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

06 From the Type filter box, select SQL database to list only the SQL databases available in your Azure account.

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

08 In the navigation panel, under Security, select Private endpoint connections to access the private endpoint connection(s) available for the selected SQL server.

09 On the Private endpoint connections page, make sure that the associated private endpoint, created earlier in the remediation process, has its state/status set to Approved.

10 In the navigation panel, under Security, select Firewalls and virtual networks to access the firewall configuration set for the selected database server.

11 On the Firewalls and virtual networks page, perform the following actions to remove database access via public IP addresses or service endpoints:

  1. Select OFF next to Allow Azure services and resources to access this server setting to disable Microsoft Azure services and resources access to the selected database server.
  2. Remove any firewall rules that allow inbound access to the selected SQL database server.
  3. Click Save to apply the changes. The Azure virtual machines (VMs) within the appropriate virtual network can access now privately the selected SQL database server via the newly created private endpoint connection.

12 If required, repeat steps no. 2 – 11 for other SQL database servers provisioned in the selected Azure subscription.

13 Repeat steps no. 2 – 13 for each subscription created in your Microsoft Azure cloud account.

Using Azure CLI

01 Run network private-endpoint create command (Windows/macOS/Linux) to create the private endpoint that will allow the virtual machines (VMs) available within the specified virtual network to access privately the selected Microsoft Azure SQL database server : 

az network private-endpoint create
    --name cc-private-endpoint
    --connection-name cc-private-endpoint-connection
    --resource-group cloud-shell-storage-westeurope
    --vnet-name cc-project5-vnet
    --subnet subnet1
    --private-connection-resource-id "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-sql-prod-server"
    --group-ids sqlServer

02 The command output should return the configuration metadata for the new private endpoint: 

{
  "etag": "W/\"abcd1234-abcd-1234-abcd-abcd1234abcd\"",
  "id": "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Network/privateEndpoints/cc-private-endpoint",
  "location": "westeurope",
  "manualPrivateLinkServiceConnections": [],
  "name": "cc-private-endpoint",
  "privateLinkServiceConnections": [
    {
      "etag": "W/\"abcdabcd-1234-abcd-1234-abcdabcdabcd\"",
      "groupIds": [
        "sqlServer"
      ],
      "id": "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Network/privateEndpoints/cc-private-endpoint/privateLinkServiceConnections/cc-private-endpoint-connection",
      "name": "cc-private-endpoint-connection",

      ...

      "privateLinkServiceConnectionState": {
        "actionRequired": null,
        "actionsRequired": "None",
        "description": "Auto-approved",
        "status": "Approved"
      },
      "privateLinkServiceId": "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-sql-prod-server",
      "requestMessage": null,
      "resourceGroup": "cloud-shell-storage-westeurope",
      "type": "Microsoft.Network/privateEndpoints/privateLinkServiceConnections"
    }
  ],
  "provisioningState": "Succeeded",
  "resourceGroup": "cloud-shell-storage-westeurope",
  "tags": null,
  "type": "Microsoft.Network/privateEndpoints"
}

03 Run network private-dns zone create command (Windows/macOS/Linux) to create a private DNS zone required to access the selected Azure SQL database server via the private endpoint: 

az network private-dns zone create
    --resource-group cloud-shell-storage-westeurope
    --name "privatelink.database.windows.net"

04 The command output should return the private DNS zone metadata: 

{
  "etag": "abcdabcd-1234-abcd-1234-abcdabcdabcd",
  "id": "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Network/privateDnsZones/privatelink.database.windows.net",
  "location": "global",
  "maxNumberOfRecordSets": 25000,
  "maxNumberOfVirtualNetworkLinks": 1000,
  "maxNumberOfVirtualNetworkLinksWithRegistration": 100,
  "name": "privatelink.database.windows.net",
  "numberOfRecordSets": 1,
  "numberOfVirtualNetworkLinks": 0,
  "numberOfVirtualNetworkLinksWithRegistration": 0,
  "provisioningState": "Succeeded",
  "resourceGroup": "cloud-shell-storage-westeurope",
  "tags": null,
  "type": "Microsoft.Network/privateDnsZones"
}

05 Run network private-dns link vnet create command (Windows/macOS/Linux) to create a virtual network link to the private DNS zone created at the previous steps (i.e. "privatelink.database.windows.net"): 

az network private-dns link vnet create
    --resource-group cloud-shell-storage-westeurope
    --zone-name "privatelink.database.windows.net"
    --name cc-project5-dns-link
    --virtual-network cc-project5-vnet
    --registration-enabled false

06 The command output should return the configuration metadata for the new virtual network link: 

{
  "etag": "\"abcdabcd-1234-abcd-1234-abcdabcdabcd\"",
  "id": "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Network/privateDnsZones/privatelink.database.windows.net/virtualNetworkLinks/cc-project5-dns-link",
  "location": "global",
  "name": "cc-project5-dns-link",
  "provisioningState": "Succeeded",
  "registrationEnabled": false,
  "resourceGroup": "cloud-shell-storage-westeurope",
  "tags": null,
  "type": "Microsoft.Network/privateDnsZones/virtualNetworkLinks",
  "virtualNetwork": {
    "id": "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Network/virtualNetworks/cc-project5-vnet",
    "resourceGroup": "cloud-shell-storage-westeurope"
  },
  "virtualNetworkLinkState": "Completed"
}

07 Run network private-dns record-set a add-record command (Windows/macOS/Linux) to create the required A record for the private DNS zone created earlier in the process. Replace the private IP address available as value for the –a configuration parameter with your own private IP: 

az network private-dns record-set a add-record
    --record-set-name cc-sql-prod-server
    --zone-name privatelink.database.windows.net
    --resource-group cloud-shell-storage-westeurope
    -a 10.0.0.5

08 The command output should return the network private-dns record-set a add-record command request metadata: 

{
  "aRecords": [
    {
      "ipv4Address": "10.0.0.5"
    }
  ],
  "etag": "abcdabcd-1234-abcd-1234-abcdabcdabcd",
  "fqdn": "cc-sql-prod-server.privatelink.database.windows.net.",
  "id": "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Network/privateDnsZones/privatelink.database.windows.net/A/cc-sql-prod-server",
  "isAutoRegistered": false,
  "metadata": null,
  "name": "cc-sql-prod-server",
  "resourceGroup": "cloud-shell-storage-westeurope",
  "ttl": 3600,
  "type": "Microsoft.Network/privateDnsZones/A"
}

09 Now that the selected Azure SQL database server is accessible via the private endpoint (using the allocated private IP address), you need to reconfigure the server firewall in order to disable the public access to the selected database server (including Azure services and resources access to the database). Run sql server firewall-rule list command (Windows/macOS/Linux) to list the firewall rules created for the selected Microsoft Azure SQL database server: 

az sql server firewall-rule list
    --ids "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-sql-prod-server"

10 The command output should return the configuration information for each firewall rule set for the selected SQL server: 

[
  {
    "endIpAddress": "0.0.0.0",
    "id": "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-sql-prod-server/firewallRules/AllowAllWindowsAzureIps",
    "kind": "v12.0",
    "location": "West Europe",
    "name": "AllowAllWindowsAzureIps",
    "resourceGroup": "cloud-shell-storage-westeurope",
    "startIpAddress": "0.0.0.0",
    "type": "Microsoft.Sql/servers/firewallRules"
  },
  {
    "endIpAddress": "192.168.0.1",
    "id": "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-sql-prod-server/firewallRules/ClientIp-2019-10-21_13-51-19",
    "kind": "v12.0",
    "location": "West Europe",
    "name": "ClientIp-2019-10-21_13-51-19",
    "resourceGroup": "cloud-shell-storage-westeurope",
    "startIpAddress": "192.168.0.1",
    "type": "Microsoft.Sql/servers/firewallRules"
  }
]

11 Run sql server firewall-rule delete command (Windows/macOS/Linux) to delete any firewall rules that allow public access to the selected Azure SQL database server. For example, the following command request removes a firewall rule identified by the ID "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-sql-prod-server/firewallRules/AllowAllWindowsAzureIps" that allows Azure services and resources to access the selected SQL server (the command does not produce an output): 

az sql server firewall-rule delete
    --ids "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-sql-prod-server/firewallRules/AllowAllWindowsAzureIps"

12 If required, repeat steps no. 1 – 11 for other SQL database servers provisioned in the selected Azure subscription.

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

References

Publication date Oct 26, 2019

Related Sql rules

Unlock the Remediation Steps

Free 30-day Trial

Automatically audit your configurations with Conformity
and gain access to our cloud security platform.

Confirmity Cloud Platform

No thanks, back to article

You are auditing:

Check for Publicly Accessible SQL Servers

Risk Level: High