Azure Private Link for Azure SQL Database and Azure Synapse Analytics

APPLIES TO: Azure SQL Database Azure Synapse Analytics (dedicated SQL pool (formerly SQL DW) only)

Private Link allows you to connect to various PaaS services in Azure via a private endpoint. For a list of PaaS services that support Private Link functionality, go to the Private Link Documentation page. A private endpoint is a private IP address within a specific VNet and subnet.

Important

This article applies to both Azure SQL Database and dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics. These settings apply to all SQL Database and dedicated SQL pool (formerly SQL DW) databases associated with the server. For simplicity, the term 'database' refers to both databases in Azure SQL Database and Azure Synapse Analytics. Likewise, any references to 'server' is referring to the logical SQL server that hosts Azure SQL Database and dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics. This article does not apply to Azure SQL Managed Instance or dedicated SQL pools in Azure Synapse Analytics workspaces.

Creation Process

Private Endpoints can be created using the Azure portal, PowerShell, or the Azure CLI:

Approval process

Once the network admin creates the Private Endpoint (PE), the SQL admin can manage the Private Endpoint Connection (PEC) to SQL Database.

  1. Navigate to the server resource in the Azure portal as per steps shown in the screenshot below

    • (1) Select the Private endpoint connections in the left pane
    • (2) Shows a list of all Private Endpoint Connections (PECs)
    • (3) Corresponding Private Endpoint (PE) created Screenshot of all PECs
  2. Select an individual PEC from the list by selecting it. Screenshot selected PEC

  3. The SQL admin can choose to approve or reject a PEC and optionally add a short text response. Screenshot of PEC approval

  4. After approval or rejection, the list will reflect the appropriate state along with the response text. Screenshot of all PECs after approval

  5. Finally clicking on the private endpoint name Screenshot of PEC details

    leads to the Network Interface details Screenshot of NIC details

    which finally leads to the IP address for the private endpoint Screenshot of Private IP

Test connectivity to SQL Database from an Azure VM in same virtual network

For this scenario, assume you've created an Azure Virtual Machine (VM) running a recent version of Windows in the same virtual network as the private endpoint.

  1. Start a Remote Desktop (RDP) session and connect to the virtual machine.

  2. You can then do some basic connectivity checks to ensure that the VM is connecting to SQL Database via the private endpoint using the following tools:

    1. Telnet
    2. Psping
    3. Nmap
    4. SQL Server Management Studio (SSMS)

Check Connectivity using Telnet

Telnet Client is a Windows feature that can be used to test connectivity. Depending on the version of the Windows OS, you may need to enable this feature explicitly.

Open a Command Prompt window after you have installed Telnet. Run the Telnet command and specify the IP address and private endpoint of the database in SQL Database.

>telnet 10.9.0.4 1433

When Telnet connects successfully, you'll see a blank screen at the command window like the below image:

Diagram of telnet

Check Connectivity using Psping

Psping can be used as follows to check that the private endpoint is listening for connections on port 1433.

Run psping as follows by providing the FQDN for logical SQL server and port 1433:

>psping.exe mysqldbsrvr.database.windows.net:1433
...
TCP connect to 10.9.0.4:1433:
5 iterations (warmup 1) ping test:
Connecting to 10.9.0.4:1433 (warmup): from 10.6.0.4:49953: 2.83ms
Connecting to 10.9.0.4:1433: from 10.6.0.4:49954: 1.26ms
Connecting to 10.9.0.4:1433: from 10.6.0.4:49955: 1.98ms
Connecting to 10.9.0.4:1433: from 10.6.0.4:49956: 1.43ms
Connecting to 10.9.0.4:1433: from 10.6.0.4:49958: 2.28ms

The output show that Psping could ping the private IP address associated with the private endpoint.

Check connectivity using Nmap

Nmap (Network Mapper) is a free and open-source tool used for network discovery and security auditing. For more information and the download link, visit https://nmap.org. You can use this tool to ensure that the private endpoint is listening for connections on port 1433.

Run Nmap as follows by providing the address range of the subnet that hosts the private endpoint.

>nmap -n -sP 10.9.0.0/24
...
Nmap scan report for 10.9.0.4
Host is up (0.00s latency).
Nmap done: 256 IP addresses (1 host up) scanned in 207.00 seconds

The result shows that one IP address is up; which corresponds to the IP address for the private endpoint.

Check connectivity using SQL Server Management Studio (SSMS)

Note

Use the Fully Qualified Domain Name (FQDN) of the server in connection strings for your clients (<server>.database.windows.net). Any login attempts made directly to the IP address or using the private link FQDN (<server>.privatelink.database.windows.net) shall fail. This behavior is by design, since private endpoint routes traffic to the SQL Gateway in the region and the correct FQDN needs to be specified for logins to succeed.

Follow the steps here to use SSMS to connect to the SQL Database. After you connect to the SQL Database using SSMS, the following query shall reflect client_net_address that matches the private IP address of the Azure VM you are connecting from:

select client_net_address from sys.dm_exec_connections 
where session_id=@@SPID

Limitations

Connections to private endpoint only support Proxy as the connection policy

On-premises connectivity over private peering

When customers connect to the public endpoint from on-premises machines, their IP address needs to be added to the IP-based firewall using a Server-level firewall rule. While this model works well for allowing access to individual machines for dev or test workloads, it's difficult to manage in a production environment.

With Private Link, customers can enable cross-premises access to the private endpoint using ExpressRoute, private peering, or VPN tunneling. Customers can then disable all access via the public endpoint and not use the IP-based firewall to allow any IP addresses.

Clients can connect to the Private endpoint from the same virtual network, peered virtual network in same region, or via virtual network to virtual network connection across regions. Additionally, clients can connect from on-premises using ExpressRoute, private peering, or VPN tunneling. Below is a simplified diagram showing the common use cases.

Diagram of connectivity options

In addition, services that are not running directly in the virtual network but are integrated with it (for example, App Service web apps or Functions) can also achieve private connectivity to the database. For more information on this specific use case, see the Web app with private connectivity to Azure SQL database architecture scenario.

Connecting from an Azure VM in Peered Virtual Network

Configure virtual network peering to establish connectivity to the SQL Database from an Azure VM in a peered virtual network.

Connecting from an Azure VM in virtual network to virtual network environment

Configure virtual network to virtual network VPN gateway connection to establish connectivity to a database in SQL Database from an Azure VM in a different region or subscription.

Connecting from an on-premises environment over VPN

To establish connectivity from an on-premises environment to the database in SQL Database, choose and implement one of the options:

Connecting from Azure Synapse Analytics to Azure Storage using Polybase and the COPY statement

PolyBase and the COPY statement is commonly used to load data into Azure Synapse Analytics from Azure Storage accounts. If the Azure Storage account that you're loading data from limits access only to a set of virtual network subnets via Private Endpoints, Service Endpoints, or IP-based firewalls, the connectivity from PolyBase and the COPY statement to the account will break. For enabling both import and export scenarios with Azure Synapse Analytics connecting to Azure Storage that's secured to a virtual network, follow the steps provided here.

Data exfiltration prevention

Data exfiltration in Azure SQL Database is when a user, such as a database admin is able extract data from one system and move it 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.

  1. Disable all Azure service traffic to SQL Database via the public endpoint by setting Allow Azure Services to OFF. Ensure no IP addresses are allowed in the server and database level firewall rules. For more information, see Azure SQL Database and Azure Synapse Analytics network access controls.
  2. Only allow traffic to the database in SQL Database using the Private IP address of the VM. For more information, see the articles on Service Endpoint and virtual network firewall rules.
  3. On the Azure VM, narrow down the scope of outgoing connection by using Network Security Groups (NSGs) and Service Tags as follows
    • Specify an NSG rule to allow traffic for Service Tag = SQL.WestUs - only allowing connection to SQL Database in West US
    • Specify an NSG rule (with a higher priority) to deny traffic for Service Tag = SQL - denying connections to SQL Database in all regions

At the end of this setup, the Azure VM can connect only to a database in SQL Database in the West US region. However, the connectivity isn't restricted to a single database in SQL Database. The VM can still connect to any database in the West US region, including the databases that aren't part of the subscription. While we've reduced the scope of data exfiltration in the above scenario to a specific region, we haven't eliminated it altogether.

With Private Link, customers can now set up network access controls like NSGs to restrict access to the private endpoint. Individual Azure PaaS resources are then mapped to specific private endpoints. A malicious insider can only access the mapped PaaS resource (for example a database in SQL Database) and no other resource.

Next steps