How to access SQL Managed Instance from Data Factory Managed VNET using Private Endpoint-ADF

Issue: How to access SQL Managed Instance from Data Factory Managed VNET using Private Endpoint-ADF

In this article we are going to learn how to access SQL Managed Instance from Data Factory Managed VNET using private endpoint in Azure Data Factory, we will also learn How to create VNET in Azure, How to create Subnets in Azure, How to create Managed Instance in Azure, How to create VM in Azure, Once all above resources are ready, then you will learn how I configured the Private EndPoints so I can access Managed Instance from Azure Data Factory, let's start our demonstration.

here is the link that I followed.

https://docs.microsoft.com/en-us/azure/data-factory/tutorial-managed-virtual-network-sql-managed-instance

How to create VNET:

Open your Azure Portal, then find and click on the virtual networks, then click on + Create to create a new virtual network, then select your Azure subscription, then provide resource group, name your VNET, select your region then click on Review + Create and then click on Create.

Once our VNET is ready, now we have to create five subnets 1st subnet for backend servers, then the other one for the standard internal load balancer, the third one for Private Link Service, the fourth one for our Virtual Machine, and the fifth one for our Managed instance, to create a subnet go to the Subnets under the settings tab inside the VNET, click on the + Subnet button, then name it and click on Save.

How to create a Managed instance:

On the Azure portal find and go to the SQL Managed Instance, then click on the + Create button to create a new managed instance, then select your subscription, select your resource group, name your managed instance, then select your region, then provide a unique login name and create a strong password, then click on Next to networking.

Then select the subnet which we have created earlier for our managed instance then click next to the additional settings then next to the review + create it will show the summary of your managed instance and then click on Create.

How to create a Virtual Machine:

Find and go to the Virtual machines then click on + Create, in the basics tab select your subscription, select your resource group, then name your VM, select your region, and then select your Operation system.

Create user name and password, then select Inbound port I have selected RDP (3389) then click on Review + Create and then click on create.

How to create a load balancer:

On the Azure portal, find and go to the Load Balancer, then click on + Create to create a new load balancer.

Select your subscription, select your resource group, name the load balancer, select the region, select type as internal, select SKU as Standard, select the virtual network, then select the subnet, IP address assignment will be dynamic, availability zone will be Zone-redundant, then click on Review + create and then create.

How to create Create a backend pool:

Open the recently create load balancer and go to the backend pools under the settings tab then click on the + add button to create a backend pool.

Name the backend pool and click on Add.

Next, we have to create a health probe.

How to create a Health Probe:

Click on the health probes under the settings tab, then click on + Add to create a new health probe.

Name the health probe, then select TCP as protocol, Port will be 22, interval will be 15 select 2 for unhealthy threshold and then click on add.

How to create a load balancer rule:

A load balancer rule is used to define how traffic is distributed to the VMs. You define the frontend IP configuration for the incoming traffic and the backend IP pool to receive the traffic. The source and destination ports are defined in the rule.

Click on the load balancing rules under the settings tab, then click on + Add to create a load balancer rule.

Use these values to configure the load-balancing rule and then click on Add

Create a private link service:

Search and go to the private link center, then click on private link services then click on the + Add button to create a private link service.

Select your subscription, then select your resource group, then name the link service. then select your region and then click on next to the outbound settings.

Select the load balancer, then select the load balancer frontend IP address then select the subnet and then click next and then leave rest as default and click on create.

Create backend servers:

Find and go to the virtual machines then click on + Create to create a new virtual machine, then in the basics tab select your subscription, then select resource group, name the VM, then select your region and select the operating system which will be ''Ubuntu Server 18.04LTS – Gen1.'' then select authentication type as Passowrd, then provide a username and create a strong password and then leave the rest as it is and then go to the Disks tab.

Select the Virtual network then select subnet, select none for public IP.

Select '' Place this virtual machine behind an existing load balancing solution'' then select the load balancer, then select the backend pool, and then click on Next and leave all the tabs as a default and at the end click on Create.

Creating Forwarding Rule to Endpoint

1. Login to your Virtual machine and copy script ip_fwd.sh to your backend server VMs.

2. Run the script on with the following options:
   sudo ./ip_fwd.sh -i eth0 -f 1433 -a <FQDN/IP> -b 1433
   <FQDN/IP> is the host of your SQL Managed Instance.

3. Run below command and check the iptables in your backend server VMs. You can see one record in your iptables with your target IP.
   sudo iptables -t nat -v -L PREROUTING -n --line-number

Next, Create a Private Endpoint to Private Link Service

How to create a Private Endpoint to Private Link Service:

Open Azure Data factory studio, then go to the Manage tab, then click on managed private endpoint under the security tab, and then click on + New to create.

Then find and select private link service and then click on continue.

Name the Private link service then select your Azure subscription, then select private link service, then click on the + New button and provide the host of your SQL managed instance, to find the SQL managed instance host, go back to your managed instance and copy the hostname and paste it, then click on the + New button again and here you have to provide the NAT IP of private link service, to find that IP, go to the resource group and then go to the private link service, copy the IP from there and paste it then click on Create.

Once our private link service is created, go to the linked service and click on + New to create a new linked service.

Select the Azure SQL Database Managed Instance and then click on continue. 

Name the linked service, then select enter manually, and provide the Managed SQL instance hostname, then provide the database name, select the authentication type, provide the user name and password, test the connection and then click on create.

 

Next, go to the author and click on the pipeline then click on + sign to create a new pipeline, then find and drag the copy data activity, then go to the source tab and click on + New to create a new source dataset.

Select the Azure SQL Database Managed Instance and then click on continue. 

Name the pipeline, then select the linked service, select the table name. select none for import schema and then click on ok.

Next, go to the sink tab, and click on  New to create a new sink dataset.

Select the Azure SQL Database Managed Instance and then click on continue. 

Name the dataset, then select the linked service, then select the destination table name, then select none for import schema, then click ok and then go back to your pipeline and click on Debug.

 

Video Demo: How to access SQL Managed Instance from Data Factory Managed VNET using Private Endpoint-ADF