Synapse Connectivity Series Part #2 – Inbound Synapse Private Endpoints

Posted on by — No Comments ↓

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

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

 

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. I will have this covered in the next blog article of this series.

 

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 - Troubleshooting

  • 9.1 - Private endpoint exists, but still failing to connect or still connecting to public endpoint
  • 9.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

 

FonsecaSergio_0-1672246890173.png

 

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.


FonsecaSergio_0-1672657690767.png

 

Let us start by reviewing how to create these private endpoints.

 

2 - Creation of a Private Endpoint

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

 

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

FonsecaSergio_4-1672316606317.png

FonsecaSergio_5-1672316622863.png

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:(

FonsecaSergio_1-1672316250361.png

 

From Workspace you can create the Dev and OnDemand endpoints:

FonsecaSergio_2-1672316402366.png

 

The SQL Endpoint is in the Resource Type Microsoft.Sql/Servers for same resource name

 

FonsecaSergio_3-1672316482470.png

 

 

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:

 

FonsecaSergio_1-1672402750202.png

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.

FonsecaSergio_0-1672417589698.png

 

I've also provided additional troubleshooting steps at end of this post that explains in detail how to troubleshoot this scenario, refer to section 9.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. 

FonsecaSergio_6-1672316736473.png

 

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.

FonsecaSergio_1-1672410577843.png

 

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:

 

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.

 

FonsecaSergio_1-1672743195197.png

 

 

FonsecaSergio_5-1672411777897.png

 

FonsecaSergio_6-1672411811262.png

 

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)

 

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

FonsecaSergio_7-1672246889796.png

 

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

 

FonsecaSergio_0-1672741769040.png

*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

 

FonsecaSergio_2-1672411073641.png

 

 

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).

 

FonsecaSergio_1-1672658880756.png

 

 

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

FonsecaSergio_10-1672246890079.png

 

 

 - 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

 

FonsecaSergio_11-1672246890058.png

 

 

 

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.

 

FonsecaSergio_4-1672411691088.png

 

FonsecaSergio_5-1672411777897.png

 

FonsecaSergio_6-1672411811262.png

 

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

FonsecaSergio_13-1672246890206.png

 

 

 

Go to DNS > Domain > Conditional Forwarders > New

FonsecaSergio_14-1672246890005.png

 

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

 

FonsecaSergio_15-1672246890115.png

 

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.

 

FonsecaSergio_4-1672411691088.png

 

FonsecaSergio_5-1672411777897.png

 

FonsecaSergio_6-1672411811262.png

 

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

FonsecaSergio_16-1672246890465.png

 

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

FonsecaSergio_8-1672413371699.png

 

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

FonsecaSergio_11-1672413558505.png

 

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

 

FonsecaSergio_13-1672413722944.png

 

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

 

9 - Troubleshooting

9.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
  • 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.

 

FonsecaSergio_0-1672671313232.png

 

  • 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

 

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.

FonsecaSergio_3-1672419233864.png

 

We can also see ports are open

FonsecaSergio_4-1672419312938.png

 

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

 

9.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)

FonsecaSergio_0-1672669706103.pngFonsecaSergio_1-1672669719612.png

 

 

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

FonsecaSergio_2-1672670111871.png

 

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

FonsecaSergio_2-1672671770496.png

 

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. 

FonsecaSergio_3-1672670343075.png

 

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

FonsecaSergio_1-1672671472786.png

 

 

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.