How to set up Private Link with high throughput data ingestion

[kevin_ngo]

Azure Synapse Analytics supports Private Link enabling you to securely connect to SQL pools via a private endpoint. This quick how-to guide provides a high-level overview and walks you through how to set up Private Link when you’re using the COPY statement for high-throughput data ingestion. Using the COPY statement is a best practice when data loading where the experience is simple, flexible, and fast.

The following diagram illustrates a simple set-up and the interactions happening across various components when Private Link is enabled for a SQL pool with a single VM within a VNet accessing the SQL endpoint (front-end control node):

The following settings are required on your SQL Server when securing your SQL pool:

1. Deny public network access: Yes
2. Allow Azure services and resources to access this server: No
3. Create a Private endpoint

These steps can all be easily done in the Azure portal. After configuring your SQL Server, access to the SQL pool is secured which can only be done via the private endpoint in your VNet.

The following settings are required on your storage account that you are loading from:

1. Allow access from Selected Networks: On
2. Create a Private endpoint
3. Create a system-assigned MI in your AAD tenant for your SQL Server via PowerShell
4. Give the required Storage Azure role (Storage Blob Data Reader or higher) to your system-assigned MI
5. Allow trusted Microsoft services to access storage: Yes
   1. This configuration allows the SQL pool backend compute nodes to bypass the storage network configurations using the system-assigned MI for your specific SQL Server resource. This allows the COPY statement to directly access the storage account for high through data ingestion over the Azure backbone.

For more details on setting up your storage account for COPY access, you can visit the following documentation. You can visit the following links to learn how Azure Synapse provides secure network access for your analytics platform:

• Learn more about Private Link with Azure Synapse Analytics
• Learn about managed private endpoints currently in preview for Azure Synapse

[Johannes_Vink]

"This configuration allows the SQL pool backend compute nodes to bypass the storage network configurations using the system-assigned MI. This allows the COPY statement to directly access the storage account for high through data ingestion over the Azure backbone."

Is this specific to Synapse SQL Pool or does did for example also apply to ADF?

[kevin_ngo]

If you're using the COPY statement or PolyBase in an ADF pipeline, it will follow the same flow when authenticating using MSI.

[James Cheng]

Do you have the PowerShell commands for the following statement?

"3. Create a system-assigned MI in your AAD tenant for your SQL Server via PowerShell"

I have looked the Az.Synapse 0.1.2
https://www.powershellgallery.com/packages/Az.Synapse/0.1.2

But, I am not sure which command to do what you said here.

[kevin_ngo]

Hi James, for more details on setting up your storage account for COPY access, you can visit the following documentation. That link has all the steps in detail.

[Greg Galloway]

@kevin_ngo looking forward to an update when this (or something very similar) is supported on Synapse workspaces.

[edkreuk72]

@kevin_ngo, why are you setting Allow Azure services and resources to access this server: No. 

I thought we had to set this to yes so all Azure Services communicate through the Microsoft Backbone.

Thanks for letting me know.