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.
It's important to review Azure SQL Managed Instance connector supported capabilities for either using Azure Integration runtime or Self-hosted Integration runtime:
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. |
Connectivity architecture - Azure SQL Managed Instance | Microsoft Docs |
- Deploy Azure DF with a different managed VNET
|
|
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
|
|
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
|
|
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:
Azure Private Link for Azure Data Factory - Azure Data Factory | Microsoft Learn
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 Feb, 2023.
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
Microsoft
