Synapse Connectivity Series Part #2 - Inbound Synapse Private Endpoints

[FonsecaSergio]

This is part 2 of a series related to Synapse Connectivity - the link below references the previous blog article:

• Synapse Connectivity Series Part #1 - Inbound SQL DW connections on Public Endpoints
• Synapse Connectivity Series Part #3 - Synapse Managed VNET and Managed Private Endpoints

This blog article will feature Synapse Private Endpoint. The foundation of this article was based on a previous post - Azure SQL DB Private Link / Private Endpoint - Connectivity Troubleshooting) which I will go more in depth with Synapse specific features.

This post does not cover: Managed Private Endpoints that are private endpoints, but from Synapse resources (ADF / Spark) to reach external resources. This is covered in the next blog article of this series Synapse Connectivity Series Part #3 - Synapse Managed VNET and Managed Private Endpoints.

In this article we are going to explore:

1 - What is the Private Endpoint for Synapse?

2 - Creation of Private Endpoint

• 2.1 - Creation of SQL, SQLOnDemand, Dev endpoints
• 2.2 - Creation of Web endpoint

3 - Name resolution

4 - Scenario: Azure VM > Private Endpoint (Azure DNS)

5 - Scenario: OnPrem VM > VPN (P2S) > Private Endpoint (Hosts File)

6 - Scenario: Azure VM > Private Endpoint (Custom DNS)

7 - Scenario: OnPrem VM > VPN (P2S) > Private Endpoint (Custom DNS)

8 - Scenario: Power BI > Data Gateway > Private Endpoint

9 - Scenario: Private endpoint with Redirect (*New scenario - Updated on 2023-11)

10 - Troubleshooting

• 10.1 - Private endpoint exists, but still failing to connect or still connecting to public endpoint
• 10.2 - Synapse Studio failing to load resources

1 - What is the Private Endpoint for Synapse?

Azure Private Endpoint is a network feature in Microsoft Azure that enables you to access Azure PaaS (Platform as a Service) services, like Azure Storage, Azure SQL Database and Synapse, over a private network connection. Private Endpoint provides private connectivity from your on-premises or virtual network to Azure PaaS services over a Microsoft-managed network. 

The traffic between your virtual network and the service travels the Microsoft backbone network. Exposing your service to the public internet is no longer necessary.

In the image below we can see how the connection using private endpoint works. And as mentioned on the other last post Synapse clients by default will try go by default to the public gateway IPs (Azure Synapse Analytics connectivity architecture). With the usage of private endpoints you can close the public path and users can only connect from private endpoint.

If you have multiple Azure VNETs you will need to use VNET peering or VNET VPN between two Azure VNETs, or P2S,S2S or Express Route to connect your onprem to Azure Network as we can see on below image

But when working with Synapse, its not just one endpoint (SQL) but multiples of them (SQL, Serverless, DEV, Web, Datalake). To have a full picture, I'm adding the multiple endpoints and I'm adding also DNS name resolution that will be essential in private connection and for you to have a full picture in mind. We will also explain it later in this post.

One small note from official documentation Azure Synapse Analytics IP firewall rules

"The Public network access feature is only available to Azure Synapse workspaces associated with Azure Synapse Analytics Managed Virtual Network", so you won't see the option to close it in the portal (image below)

So if you have an environment without managed vnet and you want to close public endpoint, just do not open to any public IP.

Let us start now reviewing how to create the private endpoints.

2 - Creation of a Private Endpoint

To create the private endpoints just follow the step-by-step procedure documented at 

• Connect to workspace resources from a restricted network
• Connect to your Azure Synapse workspace using private links

2.1 - Creation of SQL, SQLOnDemand, Dev endpoints

The creation steps may change if you are using a Synapse or Former SQL DW connected to Synapse. Make sure to check

What's the difference between Azure Synapse (formerly SQL DW) and Azure Synapse Analytics Workspace....

This table shows the differences related with private endpoints:

Synapse Workspace	Former SQL DW + Connected Synapse Workspace
For Synapse Workspace you have private endpoints: SERVERNAME.sql.azuresynapse.net (Created on Synapse workspace) SERVERNAME-OnDemand.sql.azuresynapse.net SERVERNAME.dev.azuresynapse.net	For Former SQL DW you have: SERVERNAME.database.windows.net (Created on SQL Logical Server) SERVERNAME-OnDemand.sql.azuresynapse.net SERVERNAME.dev.azuresynapse.net
In a Synapse Workspace you can create all 3 endpoints (SQL, Dev and OnDemand:(	From Workspace you can create the Dev and OnDemand endpoints: The SQL Endpoint is in the Resource Type Microsoft.Sql/Servers for same resource name

2.2 - Creation of Web endpoint

The Web private endpoint is not related with resource type Microsoft.Synapse/workspace or Microsoft.Sql/servers, but on Microsoft.Synapse/privateLinkHubs:

Connect to Azure Synapse Studio using Azure Private Link Hubs

IMPORTANT NOTE !!!

The Synapse Private Link Hub is not attached to any workspace. That means that you will NOT need to have Synapse Private Link Hub for each environment (DEV Synapse Private Link Hub / QUA Synapse Private Link Hub / PROD Synapse Private Link Hub), etc... UNLESS your networks / DNS Servers are different. Like one network for dev network and different network for production.

You actually need 1 Synapse Private Link Hub per DNS, because at end of your day you will have VMs that will use same DNS to access and resolve private endpoint for web.azuresynapse.net. - sample provided

nslookup web.azuresynapse.net

Server: XXXXXX.internal.cloudapp.net
Address: 10.0.0.5

Non-authoritative answer:
Name: web.privatelink.azuresynapse.net
Address: 10.0.1.10
Aliases: web.azuresynapse.net

When you access Synapse Studio (using Private or Public web endpoint), to connect and load information from a workspace, your machine will access the Synapse endpoints (Dev, SQL, Serverless) that can also be Private or Public. The web interface will not access Synapse endpoints on your behalf.

I've also provided additional troubleshooting steps at end of this post that explains in detail how to troubleshoot this scenario, refer to section 10.2 - Synapse Studio failing to load resources.

You may also want to close all public network access to make sure all connections flow from the private endpoint. 

To learn more, check out Azure Synapse Analytics connectivity settings.

3 - Name resolution

You must use the FQDN to connect to Synapse as documented at Azure Private Link for Azure SQL Database and Azure Synapse Analytics

Use the Fully Qualified Domain Name (FQDN) of the server in connection strings for your clients (<server>.database.windows.net / <server>.sql.azuresynapse.net). Any login attempts made directly to the IP address or using the private link FQDN (<server>.privatelink.database.windows.net / <server>.privatelink.sql.azuresynapse.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.

The image below provides a more detail depiction.

If you try to connect using private endpoint IP or custom DNS name, and even privatelink alias you are going to get the following error:

===================================
 - Cannot connect to 12.1.1.6.
 - Cannot connect to mysynapse.mydomain.com.
 - Cannot connect to SERVERNAME.privatelink.sql.azuresynapse.net.
===================================
A connection was successfully established with the server, but then an error occurred during the login process. 
(provider: SSL Provider, error: 0 - The target principal name is incorrect.) (.Net SqlClient Data Provider)
===================================

You will need to use some kind of name resolution solution to get you connected, to learn more check out the following links:

• Azure Private Endpoint DNS configuration
• What is a private endpoint? - DNS configuration

It's important to correctly configure your DNS settings to resolve the private endpoint IP address to the fully qualified domain name (FQDN) of the connection string.

You can use the following options to configure your DNS settings for private endpoints:

• Use the host file (only recommended for testing). You can use the host file on a virtual machine to override the DNS.
• Use a private DNS zone. You can use private DNS zones to override the DNS resolution for a private endpoint. A private DNS zone can be linked to your virtual network to resolve specific domains.
• Use your DNS forwarder (optional). You can use your DNS forwarder to override the DNS resolution for a private link resource. Create a DNS forwarding rule to use a private DNS zone on your DNS server hosted in a virtual network.

Important:  It's not recommended to override a zone that is actively in use to resolve public endpoints. Connections to resources won't be able to resolve correctly without DNS forwarding to the public DNS. To avoid issues, create a different domain name or follow the suggested name for each service below.

Your applications don't need to change the connection URL. When attempting to resolve using a public DNS, the DNS server will now resolve to your private endpoints. The process does not impact your applications.

The following sample scenarios will guide you on how to get it resolved:

4 - Scenario: Azure VM > Private Endpoint (Azure DNS)

From an Azure VM deployed to same VNET and using Azure DNS (168.63.129.16), if we test command below on command prompt BEFORE you create the Private Endpoint.

nslookup SERVERNAME.sql.azuresynapse.net

You will get result like below that shows that this server is using public gateway IP: 104.40.168.105. So NOT using any private IP

Server: UnKnown
Address: 168.63.129.16

Non-authoritative answer:
Name: cr4.westeurope1-a.control.database.windows.net
Address: 104.40.168.105 (This is Synapse public gateway IP range - Azure SQL Database and Azure Synapse Analytics connectivity architecture)
Aliases: 
SERVERNAME.sql.azuresynapse.net
SERVERNAME.database.windows.net
synapsedataslice1.westeurope.database.windows.net

After you create the private endpoint, you will get the following result. SAME FQDN used but resolving to private IP

nslookup SERVERNAME.sql.azuresynapse.net

Server: UnKnown
Address: 168.63.129.16

Non-authoritative answer:
Name: SERVERNAME.privatelink.sql.azuresynapse.net
Address: 10.0.1.6
Aliases: SERVERNAME.sql.azuresynapse.net

Be sure to also to open outbound communication from Azure VM VNET to Private Endpoint on Local Firewall, Corporate Firewall, or Azure NSGs to required ports. For more information, check out Azure Synapse Analytics IP firewall rules.

Important !!! 

Don't forget to add virtual network link from Private Zone to the VNET where name resolution is happening. In this scenario the VNET where client lives.

5 - Scenario: OnPrem VM > VPN (P2S) > Private Endpoint (Hosts File)

If you need to connect from your Onprem VM to Azure VNET you can use the template below to create the VPN Point-to-Site (P2S)

• Quickstart: Configure a point-to-site connection to Azure SQL Managed Instance from on-premises
• Configure a point-to-site VPN connection using Azure certificate authentication: Azure portal

After creating the VPN, downloading, and installing the VPN client and connecting to it

Check name resolution

nslookup SERVERNAME.sql.azuresynapse.net

Pay attention to what DNS is being used in your scenario. In this test, we can see that it is using My test DNS server 

Server: XXXXXX.internal.cloudapp.net
Address: 10.0.0.5 (My DNS Server)

Non-authoritative answer:
Name: cr4.westeurope1-a.control.database.windows.net
Address: 104.40.168.105 (Resolving to public)
Aliases: SERVERNAME.sql.azuresynapse.net
         SERVERNAME.privatelink.sql.azuresynapse.net
         SERVERNAME.database.windows.net
         synapsedataslice1.westeurope.database.windows.net

My DNS is not able to resolve to private, as a workaround or as a test, we can use Windows HOSTS file (C:\Windows\System32\drivers\etc\hosts).

*Don't forget to run notepad as an admin to be able to change this file

*Do not forget to add all private IPs needed like SQL, Ondemand and Dev

And we are able to connect successfully. You will be able to check and get your private IP using TSQL below running on master DB

SELECT client_net_address 
FROM sys.dm_exec_connections 
where session_id = @@SPID

6 - Scenario: Azure VM > Private Endpoint (Custom DNS)

There are multiple scenarios on how you can use custom DNS. Make sure to check all options at Azure Private Endpoint DNS configuration

On Azure Virtual Network settings I've replaced default Azure DNS with custom DNS (On my tests 10.0.0.5).

If your client lives in Azure, the DNS on VM can be a forwarder to Azure DNS

 - Go to DNS > Domain > Properties > Forwarders

 - Add Azure DNS (168.63.129.16)

*In this case I'm forwarding everything to Azure DNS. In the next part I will explain the conditional forwarder

Important !!! 

Do not forget to add virtual network link from Private Zone to the VNET where name resolution is happening. In this scenario the VNET where DNS VM lives, not with final client VM.

7 - Scenario: OnPrem VM > VPN (P2S) > Private Endpoint (Custom DNS)

There are multiple scenarios how you can use custom DNS. Make sure to check all options at https://docs.microsoft.com/en-us/azure/private-link/private-endpoint-dns

If you have an onprem DNS you may need a Conditional forwarder

Go to DNS > Domain > Conditional Forwarders > New

You may need multiple conditional forwarders one for each zone

• Conditional forwarder for DNS Domain database.windows.net. DO NOT USE privatelink.database.windows.net
• Conditional forwarder for DNS Domain sql.azuresynapse.net. DO NOT USE privatelink.sql.azuresynapse.net

To learn more, check out On-premises workloads using a DNS forwarder

And you can check if name resolution is successful

nslookup SERVERNAME.sql.azuresynapse.net

Server: XXXXXX.internal.cloudapp.net
Address: 10.0.0.5 (My DNS Server)

Name: SERVERNAME.privatelink.sql.azuresynapse.net
Address: 10.0.1.6
Aliases: SERVERNAME.sql.azuresynapse.net

As mentioned in the previous scenario:

Important !!! 

Do not forget to add virtual network link from Private Zone to the VNET where name resolution is happening. In this scenario the VNET where DNS VM lives, not with final client VM.

If you do not want to use forwarder you can also create a forward lookup zone and add manually the host to match the FQDN. This might be a problem as you will need to add all names / IP manually

8 - Scenario: Power BI > VNET integration > Private Endpoint

To make Power BI or Power Platform to connect to a Synapse private endpoint you will need to use a Data Gateway, these PaaS services are not part of your Azure Network. To learn more, check out https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem

To learn more about on-premises data gateway, check out On-premises data gateway architecture.

There is also aa VNET Data gateway, but until time I'm writing this post (2022-12-30) it's still in Preview

For more details, check out What is a virtual network (VNet) data gateway (Preview)?

9 - Scenario: Private endpoint with Redirect (*New scenario - Updated on 2023-11)

Before this recent update when you were using private endpoint by default you were only possible to use PROXY mode, but now there is the option to use REDIRECT mode with Private Endpoints. There are still some constraints, but check below them how to use it

• For more info reg PROXY vs REDIRECT check
  • Azure Synapse Analytics connectivity architecture - Connection Policy
  • Synapse Connectivity Series Part #1 - Inbound SQL DW connections on Public Endpoints

• The documentation of this feature is located at Use Redirect connection policy with private endpoints

  • We recommend that customers use the private link with the redirect connection policy for reduced latency and improved throughput. For connections to use this mode, clients need to:

    • Allow outbound communication from the VNET hosting the private endpoint to port range 1433 to 65535.
    • Use the latest version of drivers that have redirect support built in. Redirect support is included in ODBC, OLEDB, NET SqlClient Data Provider, Core .NET SqlClient Data Provider, and JDBC (version 9.4 or above) drivers. Connections originating from all other drivers are proxied.

• CURRENTLY: To enable this feature, you need to make sure that you are using a Former SQL DW where you have a SQL Logical Server that may or not have workspace experience. This will not work on new Synapse workspace.
  • Go to your SQL Logical Server > Networking > change connection policy to redirect
  • This will make ALL connection to this server use redirect. Make sure te test it and open required ports before saving it

Below you can find sample of a network trace considering this configuration. We can see that using Private IP filter it starts connection going to gateway on port 1433, then it will close this connection and start new one using new port, from range mentioned above (port range 1434 to 65535) to speack directly with host server. In my test port 33314

10 - Troubleshooting

10.1 - Private endpoint exists, but still failing to connect or still connecting to public endpoint

To make sure you are using a private connection, there are 3 requirements:

• Path / Route
  • You need make sure your client is able to reach your private endpoint IP
    • Using VPN, ExpressRoute or just being an Azure VM that is already part of the Azure VNET, but even in this case need to make sure Client VNET communication can flow to VNET where Private endpoint was creates
  • You need to make sure Private endpoint is healthy and approved
    • Manage Azure Private Endpoints
• Name
  • You need to get FQDN like SERVERNAME.SQL.azuresynapse.net or SERVERNAME.DEV.azuresynapse.net or STORAGEACCOUNT.dfs.core.windows.net and have it resolve to a private IP created not to the public one
    • But I have a private IP can I use it directly. NO!
    • Can I create a customer name Synapse.MyDomain.com. NO!
  • You can check all private endpoints manually below

NSLOOKUP SERVERNAME.database.windows.net
NSLOOKUP SERVERNAME.sql.azuresynapse.net
NSLOOKUP SERVERNAME-ondemand.sql.azuresynapse.net
NSLOOKUP SERVERNAME.dev.azuresynapse.net
NSLOOKUP web.azuresynapse.net
NSLOOKUP STORAGEACCOUNT.dfs.core.windows.net

We have also seen customer issues where it was actually on an Internet proxy, so when connecting to Synapse Studio, name resolution is not happening locally but on proxy server.

• Port
  • Is the port needed open? Samples below
    • For SQL endpoint - SERVERNAME.SQL.azuresynapse.net
      • 1433 for SSMS or PowerBI
      • 1443 or 443 for Synpse Studio
    • For DEV endpoint - SERVERNAME.DEV.azuresynapse.net
      • 443
    • More details can be found at Azure Synapse Analytics IP firewall rules 
    • You can check ports manually using Powershell commands below

Test-NetConnection -Port 1433 -ComputerName SERVERNAME.database.windows.net
Test-NetConnection -Port 1433 -ComputerName SERVERNAME.sql.azuresynapse.net
Test-NetConnection -Port 1443 -ComputerName SERVERNAME.sql.azuresynapse.net
Test-NetConnection -Port 443 -ComputerName SERVERNAME.sql.azuresynapse.net

Test-NetConnection -Port 1433 -ComputerName SERVERNAME-ondemand.sql.azuresynapse.net
Test-NetConnection -Port 1443 -ComputerName SERVERNAME-ondemand.sql.azuresynapse.net
Test-NetConnection -Port 443 -ComputerName SERVERNAME-ondemand.sql.azuresynapse.net

Test-NetConnection -Port 443 -ComputerName web.azuresynapse.net
Test-NetConnection -Port 443 -ComputerName SERVERNAME.dev.azuresynapse.net

You can also use the following script to test all endpoints needed, to check if they are resolving to private or to public and if ports are open

• https://github.com/microsoft/Azure-Synapse-Connectivity-Checker

We can see what endpoints are resolving to private and which are public. In this case, the database is resolving to public as expected, because it's not a Former SQL DW.

We can also see ports are open

This other script also good to use as it will actually make the API calls and check for failures. To learn more, check out Troubleshoot Synapse Studio connectivity with PowerShell

10.2 - Synapse Studio failing to load resources

When you are trying to reach Synapse Studio you may face the following error: (Example: Your machine tried to reach one API but failed to load)

When you use Synapse Studio, it is NOT the web interface accessing Synapse resources. It is your machine making the API calls directly to Synapse. You will need to see if your machine is resolving them to private or public endpoints

If you debug Synapse Studio you will notice that your machine calling Synapse APIs is failing, so your machine will need to resolve FQDN to connect to Synapse APIs (For more details, check out Capture a browser trace for troubleshooting)

You will also notice that some APIs may succeed while others may fail

Sample 1 - OnDemand API call failed

In this test, notice the port could not be reached. (In this case, I forced an error forcing connection to incorrect IP 127.0.0.1)

Test-NetConnection -Port 443 -ComputerName SERVERNAME-ondemand.sql.azuresynapse.net
WARNING: TCP connect to (127.0.0.1 : 443) failed

ComputerName : SERVERNAME-ondemand.sql.azuresynapse.net
RemoteAddress : 127.0.0.1
RemotePort : 443
InterfaceAlias : Loopback Pseudo-Interface 1
SourceAddress : 127.0.0.1
PingSucceeded : True
PingReplyDetails (RTT) : 0 ms
TcpTestSucceeded : False

You may also see it on console view

You may ask, is the dedicated pool information showing correctly? The other API (management.azure.com) has succeeded in the example below.

*Pay attention to the below REMOTE ADDRESS. It's your machine resolving the name to the public IP and port. 

Below we can see API Request result being applied while other failed

Hope this post provides you the guidance on how to deal and troubleshoot Synapse Private Endpoints. If you need more details or have questions, please provide a comment below.

References and links

• Synapse Connectivity Series Part #1 - Inbound SQL DW connections on Public Endpoints
• Azure Synapse Analytics connectivity architecture
• Azure SQL DB Private Link / Private Endpoint - Connectivity Troubleshooting
• Connect to workspace resources from a restricted network
• Connect to your Azure Synapse workspace using private links
• Connect to Azure Synapse Studio using Azure Private Link Hubs
• Azure Private Endpoint DNS configuration
• What's the difference between Azure Synapse (formerly SQL DW) and Azure Synapse Analytics Workspace
• Capture a browser trace for troubleshooting
• Manage Azure Private Endpoints
• Troubleshoot Synapse Studio connectivity with PowerShell