Monday, March 21, 2022

How to connect Azure Data Factory to SQL Managed Instance over private network

Introduction:

In today Blog article, we are going to go through technical steps on how to connect Azure Data factory to Azure SQL Managed Instance over private network, this will leverage the public preview of the Managed instance private endpoint throughout Azure data factory Managed Virtual network.

 

Use Case:

Customer wants to connect Azure Data Factory IR to Azure Managed instance over the private network, check out the architecture diagram below for more clarification.

Ahmed_S_Mahmoud_0-1647864953259.png

Solution:

At this section, I am listing down the main steps of this set up:

-- Create SQL MI using separate/dedicated VNET

-- Deploy Azure DF with a different managed VNET

-- Create Azure IR with managed VNET (optionally)

-- Create Managed Private endpoint to SQL MI

-- Create Linked service to SQL MI using the private connection

 

Here are some highlights of each step:

  • Create SQL MI using separate/dedicated VNET
Create SQL MI using a new virtual network with a single subnet. Network configuration required for Managed Instance will then be applied to this subnet. This should be different than Client VNET used by Data factory, More information can be found at references.

Quickstart: Create an Azure SQL Managed Instance (portal) - Azure SQL Managed Instance | Microsoft Docs

Connectivity architecture - Azure SQL Managed Instance | Microsoft Docs

 

  • Deploy Azure DF with a different managed VNET
Ahmed_S_Mahmoud_0-1647873520939.png

 

Create Azure DF using Managed virtual network and private endpoints 

Managed virtual network & managed private endpoints - Azure Data Factory | Microsoft Docs

Azure Private Link for Azure Data Factory - Azure Data Factory | Microsoft Docs

 

  • Create Azure IR with managed VNET (optionally)

In case you've disabled the Managed Virtual Network on the default AutoResolveIntegrationRuntime, then you can still choose whether to provision explicitly created Azure integration runtime inside an ADF-managed virtual network.

Use private endpoints to create an Azure Data Factory pipeline - Azure Data Factory | Microsoft Docs

** Make sure Azure IR (or AutoResolveIntegrationRuntime) running in full mode, this to secure a successful run for both data movement and data flow.

 

  • Create Managed Private endpoint to SQL MI
Ahmed_S_Mahmoud_0-1647447211749.png

 

Create a managed private endpoint and choose Azure SQL Database Managed instance. 

** Make sure to approve the endpoint on SQL MI side.

Use private endpoints to create an Azure Data Factory pipeline - Azure Data Factory | Microsoft Docs

 

  • Create Linked service to SQL MI using the private connection
Ahmed_S_Mahmoud_1-1647447421877.png Create new Linked service using the Managed network Integration runtime and the private endpoint.

 

Troubleshooting

Issue#1

Cannot connect to SQL Database: 'XXXXXX.public.XXXXXXX.database.windows.net,3342', Database: 'XXXXXXXXX', User: 'dbadmin'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.), SqlErrorNumber=11001,Class=20,State=0,
No such host is known
Activity ID: XXXX-xxxx-xxxx-xxxx-XXXXX.

 

Possible Solution:

Make sure to configure the linked service to use the private endpoint type.

 

Additional References:

Access Microsoft Azure SQL Managed Instance from Data Factory Managed VNET using Private Endpoint - Azure Data Factory | Microsoft Docs

Azure Private Link for Azure Data Factory - Azure Data Factory | Microsoft Docs

 

Disclaimer

As our products continuously evolve, Please note that products and options presented in this article are subject to change. This article reflects the private endpoint preview feature for Azure Managed instance in March, 2022.

 

Closing remarks

We hope you find this article helpful. If you have any feedback, please do not hesitate to provide it in the comment section below.

 

Ahmed S. Mazrouh

Posted at https://sl.advdat.com/37Ba2nFhttps://sl.advdat.com/37Ba2nF