How to Connect Azure Data Factory to an Azure SQL Database Using a Private Endpoint

Photo by Hafidh Satyanto on Unsplash

A step-by-step tutorial

Azure Data Factory (ADF) is great for extracting data from multiple sources, the most obvious of which may be Azure SQL. However, Azure SQL has a security option to deny public network access, which, if enabled, will prevent ADF from connecting without extra steps.

In this article, we’ll look at the steps required to set up a private endpoint and use it to connect to an Azure SQL database from Azure Data Factory.


‘Deny public network access’ setting in Azure SQL

Before we get started, let’s review which setting I’m referring to in Azure SQL. It’s a toggle named deny public network access found under Security > Firewalls and virtual networks in the Azure portal.

Source: author

When this setting is enabled, Azure Data Factory won’t connect without a private endpoint. You can see there’s even a link to create a private endpoint below the toggle control, but don’t use this now — we’ll create the request from Azure Data Factory in a minute.


ADF integration runtime

To use private endpoints in Azure Data Factory, you must use an integration runtime with virtual network configuration enabled. The setting cannot be changed, so you’ll need to create a new runtime if you don’t have one with it enabled already.

Source: author
Source: author

Now that you have an integration runtime with virtual network configuration enabled, you’re ready to create a new linked service.


ADF linked service

While still in Azure Data Factory, click to create a new linked service.

Source: author

When you select an integration runtime with virtual network configuration enabled, a managed private endpoint setting will appear in the account selection method section. The setting is read-only and will populate as you enter subscription and server details. If a managed private endpoint is already available — you’re good to go!

If a managed private endpoint isn’t available, click the create new link button to start the process.

Source: author

When you save the new managed private endpoint in Azure Data Factory, it will be provisioned in Azure but remain in a Pending status until approved.


Azure private endpoint

Now we need to hop back to Azure to approve the new private endpoint. Find your Azure SQL database in the Azure portal, and browse to Security > Private endpoint connections.

You should see the connection created by Azure Data Factory with the status Pending. Select its checkbox and click the Approve button.

Source: author

The status will change to Approved in the Azure portal. It takes a minute or two for the status to make its way to Azure Data Factory, but it will show as Approved there after a moment, too.

Source: author

Once it shows as approved, you’re ready to go. You can enter the rest of your connection info and connect!


Recap

Most of the settings I’ve shown can be accessed in several different ways and performed in different orders. For example, you could create the private endpoint from the Azure portal instead of through Azure Data Factory. You can obviously experiment and find the process that works for you.

The important pieces are the following:

  1. Azure Data Factory has an integration runtime with virtual network configuration enabled.
  2. Azure SQL has an approved private endpoint connection.
  3. Azure Data Factory has a linked service using the integration runtime and private endpoint connection.

That’s it — now go have fun with your new connection!


This article was originally published in Towards Data Science on January 20, 2021.


Interested in learning more about Azure Data Factory? Give these books a try. Note that I use affiliate links when linking to products on Amazon.