Configure SQL Server to use private DNS zones

[anwarnk]

Could you add a built-in policy to use private DNS zones to override the DNS resolution for a SQL Server private endpoint similar to built-in in policy "Configure App Service apps to use private DNS zones"?

[DavidLHannah]

Seconded, we also have a need here.

We are using ALZ and EPAC. The Policy Sets included there are only the built-in DNS config policies. As such, we do not have proper deployment of Azure SQL DNS like we see with other resource types.

[achechen]

Same here, ALZ initiative lacks a built in policy for this therefore we have to use a custom policy + custom initiative

[alexeyzolotukhin]

+1 for adding this to the "Configure Azure PaaS services to use private DNS zones" initiative.

[KennethBess]

+1 as we have the same requirement using PE's with SQL! Please prioritize this

[anwarnk]

FYI we are still waiting for this to happen. As we use the terraform-azurerm-caf-enterprise-scale we created our own library with a policy definition ourselves and then added it it to the Deploy-Private-DNS-Zones definition set. Here is the policy definition, was a pain , however we needed a workaround for now until this has been implemented.

{
  "name": "Deploy-SQLServerPrivateDnsZone",
  "type": "Microsoft.Authorization/policyDefinitions",
  "properties": {
    "displayName": "Configure Azure SQL Server to use private DNS zones",
    "policyType": "Custom",
    "mode": "Indexed",
    "description": "Use private DNS zones to override the DNS resolution for a private endpoint. A private DNS zone can be linked to your virtual network to resolve to Azure SQL Server. Learn more at: https://aka.ms/privatednszone.",
    "metadata": {
      "category": "SQL Server"
    },
    "parameters": {
      "privateDnsZoneId": {
        "type": "String",
        "metadata": {
          "displayName": "Private DNS Zone Id",
          "description": "The resource id of the private DNS zone",
          "strongType": "Microsoft.Network/privateDnsZones"
        }
      },
      "effect": {
        "type": "String",
        "metadata": {
          "displayName": "Effect",
          "description": "Enable or disable the execution of the policy"
        },
        "allowedValues": [
          "DeployIfNotExists",
          "Disabled"
        ],
        "defaultValue": "DeployIfNotExists"
      }
    },
    "policyRule": {
      "if": {
        "allOf": [
          {
            "field": "type",
            "equals": "Microsoft.Network/privateEndpoints"
          },
          {
            "count": {
              "field": "Microsoft.Network/privateEndpoints/privateLinkServiceConnections[*].groupIds[*]",
              "where": {
                "field": "Microsoft.Network/privateEndpoints/privateLinkServiceConnections[*].groupIds[*]",
                "equals": "sqlServer"
              }
            },
            "greaterOrEquals": 1
          }
        ]
      },
      "then": {
        "effect": "[parameters('effect')]",
        "details": {
          "type": "Microsoft.Network/privateEndpoints/privateDnsZoneGroups",
          "roleDefinitionIds": [
            "/providers/Microsoft.Authorization/roleDefinitions/4d97b98b-1d4f-4787-a291-c67834d212e7"
          ],
          "deployment": {
            "properties": {
              "mode": "incremental",
              "template": {
                "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
                "contentVersion": "1.0.0.0",
                "parameters": {
                  "privateDnsZoneId": {
                    "type": "string"
                  },
                  "privateEndpointName": {
                    "type": "string"
                  },
                  "location": {
                    "type": "string"
                  }
                },
                "resources": [
                  {
                    "name": "[concat(parameters('privateEndpointName'), '/deployedByPolicy')]",
                    "type": "Microsoft.Network/privateEndpoints/privateDnsZoneGroups",
                    "apiVersion": "2020-03-01",
                    "location": "[parameters('location')]",
                    "properties": {
                      "privateDnsZoneConfigs": [
                        {
                          "name": "sqlserver-privateDnsZone",
                          "properties": {
                            "privateDnsZoneId": "[parameters('privateDnsZoneId')]"
                          }
                        }
                      ]
                    }
                  }
                ]
              },
              "parameters": {
                "privateDnsZoneId": {
                  "value": "[parameters('privateDnsZoneId')]"
                },
                "privateEndpointName": {
                  "value": "[field('name')]"
                },
                "location": {
                  "value": "[field('location')]"
                }
              }
            }
          }
        }
      }
    }
  }
}