Check for Publicly Accessible SQL Servers

Sign in to Azure Management Console.

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

From the Type filter box, select SQL server to list only the SQL database servers provisioned in your Azure account.

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

In the navigation panel, under Security, select Networking to access the private and public network access configuration for the selected database server.

Select the Private access tab, then check the Private endpoint connections list. If there are no private endpoint connections configured, instead a "No results" status is displayed, the selected Microsoft Azure SQL database server does not have a private endpoint connection configured, therefore the resource is publicly accessible.

Select the Public access tab, then check the value set for the Public network access setting.

If Public network access is set to Selected networks, the selected Microsoft Azure SQL database server allows connections via public IP addresses or service endpoints even if a private endpoint connection is configured (as identified at step no. 6), therefore the resource is publicly accessible. The SQL database server is compliant only when a private endpoint connection is configured (step no. 6) and Public network access is set to Disable.

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

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

Run sql server list command with output query filters to list the name and resource group of each Azure SQL database server provisioned in the current Azure subscription:

az sql server list
--query "[].{name:name, resourceGroup:resourceGroup}"

The command output should return the name and resource group for each Azure SQL database server:

[
    {
        "name": "cc-sql-prod-server",
        "resourceGroup": "cloud-shell-storage-westeurope"
    }
]

Run network private-endpoint-connection list command to check whether the selected Azure SQL database server has a private endpoint connection configured (see step no. 1 to identify the right resource):

az network private-endpoint-connection list
--name cc-sql-prod-server
--resource-group cloud-shell-storage-westeurope
--type Microsoft.Sql/servers

The command output should return the private endpoint connection(s) configured for the selected Azure SQL database server:

[
    {
        "id": "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-sql-prod-server/privateEndpointConnections/cc-private-endpoint-connection",
        "name": "cc-private-endpoint-connection",
        "properties": {
            "groupIds": [
                "sqlServer"
            ],
            "privateEndpoint": {
                "id": "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Network/privateEndpoints/cc-private-endpoint",
                "resourceGroup": "cloud-shell-storage-westeurope"
            },
            "privateLinkServiceConnectionState": {
                "actionsRequired": "None",
                "description": "Auto-approved",
                "status": "Approved"
            },
            "provisioningState": "Ready"
        },
        "resourceGroup": "cloud-shell-storage-westeurope",
        "type": "Microsoft.Sql/servers/privateEndpointConnections"
    }
]

If the network private-endpoint-connection list command output returns an empty list ([]), the selected Azure SQL database server does not have a private endpoint connection configured, therefore the resource is publicly accessible.

Run sql server show command with output query filters to check the public network access setting configured for the selected Azure SQL database server (see step no. 1 to identify the right resource):

az sql server show
--name cc-sql-prod-server
--resource-group cloud-shell-storage-westeurope
--query publicNetworkAccess

The command output should return the current publicNetworkAccess value configured for the selected Azure SQL database server:

"Enabled"

If the sql server show command output returns "Enabled" or "SecuredByPerimeter", as shown in the example above, the selected Azure SQL database server allows public network access regardless of whether a private endpoint connection is configured (step no. 4), therefore the database server is not compliant. The SQL database server is compliant only when a private endpoint connection is configured (step no. 4) and the publicNetworkAccess value is set to "Disabled".

Repeat steps no. 3 – 6 to verify the private endpoint connection and public network access setting for other Azure SQL database servers available within the current Azure subscription.

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

Sign in to Azure Management Console.

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

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

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

1. On the Basics tab, 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 tab, 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 the Audit section's Using Azure Console steps to identify the right SQL server) from the Resource dropdown list. Once all the required details are provided, click Next: Virtual Network > to continue.
3. On the Virtual Network tab, select the Azure virtual network and the subnet where the new private endpoint will be deployed. Click Next: DNS > to continue the setup process.
4. On the DNS tab, choose Yes next to Integrate with private DNS zone to enable Private DNS integration. Click Next: Tags > to continue the setup process.
5. On the Tags tab, create the necessary resource tags for your new private endpoint. Once the tags are created, click Next: Review + create > to continue.
6. On the Review + create tab, 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.

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

From the Type filter box, select SQL server to list only the SQL database servers provisioned in your Azure account.

Click on the name of the SQL database server that you want to reconfigure (see the Audit section's Using Azure Console steps to identify the right resource).

In the navigation panel, under Security, select Networking to access the private and public network access configuration for the selected database server.

Select the Private access tab, then make sure that the associated private endpoint, created earlier in the remediation process, has its Connection state set to Approved.

Select the Public access tab. As optional additional hardening, while Selected networks is still selected, under Firewall rules, remove any firewall rules that allow inbound access to the selected SQL database server, and under Exceptions, clear the Allow Azure services and resources to access this server checkbox, then click Save to apply these changes. This step is not required for compliance – any existing firewall rules or virtual network rules are automatically retained but disabled once Public network access is set to Disable at the next step, and switching to Disable first would hide these sections and discard any pending edits made here instead of saving them.

On the Public access tab, select Disable next to the Public network access setting. Click Save to apply the change. This is the change required to disable public network access on the selected SQL database server.

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

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

Run network private-endpoint create command 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

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"
}

Run network private-dns zone create command 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"

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"
}

Run network private-dns link vnet create command 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

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"
}

Run network private-dns record-set a add-record command 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

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"
}

As optional additional hardening, you can also remove any firewall rules configured for the selected Azure SQL database server – these are automatically retained but disabled once the server's public network access setting is disabled at step no. 12, so this is not required for compliance. Run sql server firewall-rule list command 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"

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"
    }
]

Run sql server firewall-rule delete command 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"

Run sql server update command to disable the public network access setting for the selected Azure SQL database server. This is the step that determines compliance – removing firewall rules at step no. 11 is optional hardening and does not, by itself, change the server's publicNetworkAccess property:

az sql server update
--name cc-sql-prod-server
--resource-group cloud-shell-storage-westeurope
--set publicNetworkAccess="Disabled"

The command output should return the updated configuration metadata for the selected Azure SQL database server, with the publicNetworkAccess property set to "Disabled":

{
    "name": "cc-sql-prod-server",
    "publicNetworkAccess": "Disabled",
    "resourceGroup": "cloud-shell-storage-westeurope",

    ...

}

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

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