Virtual Network architecture 4 - SQL Database Private Endpoit

Summary

This article is Part.4 of virtual network architecture series. I explain the details of the private endpoint with Azure SQL Database in api-management-vnet.

• Virtual Network architecture 1 - Do I need virtual network?
• Virtual Network architecture 2 - Deployment pipelines
• Virtual Network architecture 3 - Key Vault Private Endpoint
• Virtual Network architecture 4 - SQL Database Private Endpoit
• Virtual Network architecture 5 - App Service Private Endpoint
• Virtual Network architecture 6 - Service Bus Private Endpoint
• Virtual Network architecture 7 - Self-hosted agent

TOC

• Private Endpoint configuration
• Access to SQL Database
• SQL project update

Private Endpoint configuration

• Private Endpoint: Deploy the private endpoint and connect it to SQL Database and its subnet in PrivateEndpoint.bicep.

SqlServerId:existingSql.id
VirtualNetwork2SubnetIdSql:existingVnet2.properties.subnets[2].id

resource PrivateEndpointSql 'Microsoft.Network/privateEndpoints@2021-03-01' = {
  properties: {
    privateLinkServiceConnections: [
      {
        properties: {
          privateLinkServiceId: SqlServerId
          groupIds: [
            'sqlServer'
          ]
        }
      }
    ]
    subnet: {
      id: VirtualNetwork2SubnetIdSql
      properties: {
        privateEndpointNetworkPolicies: 'Enabled'
      }
    }
  }
}

• Private DNS: Deploy Private DNS with the DNS name privatelink.database.windows.net in PrivateDns2.bicep.

var pdns_name_sql = 'privatelink${environment().suffixes.sqlServerHostname}'

resource PrivateDnsSql 'Microsoft.Network/privateDnsZones@2020-06-01' = {
  name: pdns_name_sql
  location: 'global'
}

• Virtual network link: Link the deployed Private DNS to the virtual network where the SQL Server subnet exists in PrivateDns2.bicep.

VirtualNetwork2Id:existingVnet2.id

resource VnetLinkSql 'Microsoft.Network/privateDnsZones/virtualNetworkLinks@2020-06-01' = {
  name: '${PrivateDnsSql.name}/${PrivateDnsSql.name}-link'
  location: 'global'
  properties: {
    registrationEnabled: false
    virtualNetwork: {
      id: VirtualNetwork2Id
    }
  }
}

• Private DNS A record: Create a DNS A record and set the IP address from the deployed private endpoint in PrivateDns2.bicep.

output PrivateEndpointSqlIpAddress string = PrivateEndpointSql.properties.customDnsConfigs[0].ipAddresses[0]

resource PrivateDnsASql 'Microsoft.Network/privateDnsZones/A@2020-06-01' = {
  name: '${PrivateDnsSql.name}/${SqlServerName}'
  properties: {
    ttl: 3600
    aRecords: [
      {
        ipv4Address: PrivateEndpointSqlIpAddress
      }
    ]
  }
}

Access to SQL Database

App Service and Functions need to access the SQL Database to insert and extract the data records. Both can access through V-net integration and Private Endpoint. In SqlDatabase2.bicep, public network access is disabled so no one can access through the public IP.

resource SqlServer 'Microsoft.Sql/servers@2021-02-01-preview' = {
  properties: {
    publicNetworkAccess: 'Disabled'
  }
}

SQL project update

The biggest problem is when you want to update SQL projects including data schema on the Azure SQL Database. In this sample template, you cannot update the SQL project because a Linux based self-hosted agent does not support build and deployment of SQL Database projects. On the other hand, a Windows based self-hosted agent in the docker container on Azure Container Instance cannot be deployed on the virtual network as I mentioned in the previous article Virtual Network architecture 2 - Deployment pipelines. Possible workaround is to create a Windows based self-hosted agent on a Virtual Machine, to change IP filtering rules when in code deployment, or to wait for the Windows docker container supporting the virtual network deployment.