How to connect Data Factory Managed VNet to on-prem SQL Server

Azure Data Factory is a fully managed, easy-to-use, serverless data integration, and transformation solution to ingest and transform all your data. Choose from over 90 connectors to ingest data and build code-free or code-centric ETL/ELT processes.

Security is one of key requirements for many companies, so ADF comes with a solution for this which is called Managed Virtual Network. This feature allows to transfer data in a secure way using Microsoft backbone network.

Managed Virtual Network is also a requirement if would like to connect on-premises databases and ingest and transform our data, but we don’t want or we can’t to use Self-Hosted Integration Runtime with a software required for data extraction.

Configuration

Microsoft documentation https://docs.microsoft.com/en-us/azure/data-factory/tutorial-managed-virtual-network-on-premise-sql-server is not clear how to exactly configure such a connection. There are missing various required steps so below I create complete guideline.

  1. Create Private DNS Zone in this example I will be using domain contoso.com Private DNS Zone

  2. Add Record A to a created DNS Zone with on-prem IP address Record A

  3. Link DNS Zone to Virtual Network with auto-registration enabled Private DNS Zone VNet Link

  4. Create Load Balancer and DNS Forwarder VM according to a documentation

  5. In Data Factory create new Integration Runtime with option Managed Virtual Network enabled. Please be aware and carefully choose a correct region from supported regions list https://docs.microsoft.com/en-us/azure/data-factory/managed-virtual-network-private-endpoint#limitations-and-known-issues Integration Runtime configuration

  6. Create Managed Private Endpoint to Private Link Service. Use only FQDN name from point 2, ex. database.contoso.com FQDN Managed Private Endpoint

  7. Create your Linked Service connection to SQL Server using newly created Integration Runtime and FQDN name

Summary

After this configuration and DNS propagation time whole setup should work as expected. This blog post doesn’t provide guidelines how to setup VPN Gateway\Express Route connection to on-prem environment.

I hope this post is useful.