Azure Private Link for Azure SQL Database and Azure Synapse Analytics

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 the best practice when data loading where the experience is simple, flexible, and fast. 

Data exfiltration prevention

Data exfiltration in Azure SQL Database is when an authorized user, such as a database admin is able to extract data from one system and move it to another location or system outside the organization. For example, the user moves the data to a storage account owned by a third party.

Consider a scenario with a user running SQL Server Management Studio (SSMS) inside an Azure virtual machine connecting to a database in SQL Database. This database is in the West US data center. The example below shows how to limit access with public endpoints on SQL Database using network access controls.

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: YesThis 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.