Synapse Connectivity Series Part #1 - Inbound SQL DW connections on Public Endpoints

Published Tuesday, October 25, 2022

I will do a series of posts regarding Synapse connectivity. As there are a lot of topics to cover like inbound, outbound, public and private endpoints, managed VNET, managed private endpoints etc., it will be easier to break these into smaller dedicated posts.

In this first article I would like to explore the SQL DW / Dedicated pool public endpoint connectivity

When troubleshooting connection issues, you need to think about what is the source and what is the destination and lot of thing in between:

What is the source and what is the destination that you want to reach?
Source
- Are you accessing it from your machine or from Spark notebook running on Synapse Spark pool or from ADF Integration runtime machine (Azure Integration Runtime or Self Hosted IR)?
- Is your client/VM running OnPrem or Inside Azure (Azure VM / PBI Service / PBI Data gateway)?
- Do you have a Corp Firewall?
  - What Outbound Ports will be needed?
  - Internet Proxy in the middle?
Destination
- Do you want to use public endpoint or private endpoint?
- What endpoint you want to reach (Synapse Serverless / Synapse Dedicated Pool / former SQL DW)?
  - SERVERNAME.database.windows.net (Azure SQL DB / Former SQL DW)
  - SERVERNAME.sql.azuresynapse.net (Synapse SQL Dedicated Pool)
  - SERVERNAME-ondemand.sql.azuresynapse.net (Synapse SQL Serverless)

Connect to SQL PUBLIC endpoints (Dedicated Pool / Serverless / Former SQL DW)

For this sample we will consider only public endpoints. In a future post, I will speak more about Private endpoints.

Reg ports needs it will change depending on the client (Synapse Studio vs SSMS)

Synapse Studio needs outbound to ports 443/1443 to Synapse gateways
- https://learn.microsoft.com/en-us/azure/synapse-analytics/security/synapse-workspace-ip-firewall#connect-to-azure-synapse-from-your-own-network
SSMS / PowerBI Desktop / Others uses
- 1433 (Proxy Mode)
- 1433 + 11000 to 11999 (Redirect Mode)
- https://learn.microsoft.com/en-us/azure/azure-sql/database/connectivity-architecture?view=azuresql#connection-policy

Find below more details for the 2 processes (Proxy vs Redirect mode)

If you are you coming from Outside Azure (Proxy mode)

You are going to use Proxy mode by default
- That means there is a gateway between you and the cluster that is hosting your database
You are going to reach a region load balancer using one of the Gateways public IPs on port 1433.
- These public gateways IPs are documented at https://docs.microsoft.com/en-us/azure/azure-sql/database/connectivity-architecture#gateway-ip-addresses
  - Samples (CR means Control Ring):
    - Name: cr4.westeurope1-a.control.database.windows.net
      Address: 104.40.168.105
    - Name: cr4.westus2-a.control.database.windows.net
      Address: 40.78.240.8
- You need to make sure you open your corporate firewall to your server region gateways on port 1433
- These gateways are shared infrastructure and this gateway will communicate with your specific customer DW depending on the connection string + user + password used

Are you coming from Inside Azure (Redirect)

You are going to use Redirect Mode by default

Redirect mode is recommended as it's faster when you are connecting directly from the client to the server that is hosting your DW

First you reach the one of the Gateways public IPs on port 1433.
Then you are redirected to one of the multiple possible Tenant Rings on port 11000-11999 range. Tenant rings are clusters where your DW server lives.
- ! This means that your server needs to reach not only the gateway, but also a big range of multiple cluster IPs that can host your server and it may change after a restart or pause/resume
- If you need to open firewall, it would be better to open it with specific service tags like the sample below:

REF: https://docs.microsoft.com/en-us/azure/virtual-network/service-tags-overview

* If for some reason you cannot use Service Tags, like using 3rd party firewall, the workaround would be to use PowerShell to list all possible IP ranges and then create a process to update your firewall from time-to-time

$serviceTags = Get-AzNetworkServiceTag -Location westeurope $SQLserviceTag = $serviceTags.Values | Where-Object Name -Contains "SQL" $SQLserviceTag.Properties.AddressPrefixes ---------------- $serviceTags = Get-AzNetworkServiceTag -Location westeurope $SQLserviceTag = $serviceTags.Values | Where-Object Name -Contains "SQL.WestEurope" $SQLserviceTag.Properties.AddressPrefixes

You can also get list from Json file at Azure IP Ranges and Service Tags – Public Cloud

TROUBLESHOOTING

When dealing with connectivity issues the first step is to understand if NAME RESOLUTION is working and PORT is open

You can use a Powershell script that does all test and even some additional advanced ones. It can run directly from web, from linux or can be downloaded to run offline

SQL Connectivity Checker Script

This script created by a colleague from Microsoft ( ). You just need to follow instructions on this GitHub page below and it will validate gateways, ports, test real connection, etc.

https://github.com/Azure/SQL-Connectivity-Checker

If you cannot run powershell script above because of company policy or just want to check manually there are some commands you can run to test

TEST NAME

First you need to know if your client can resolve the name like samples below:

NSLOOKUP SERVERNAME.database.windows.net
NSLOOKUP SERVERNAME.sql.azuresynapse.net
NSLOOKUP SERVERNAME-ondemand.sql.azuresynapse.net

*NSLOOKUP command works fine on Windows / MAC / Linux

Sample

Server:  dns.google
Address:  8.8.8.8 (What is DNS used? Public / Custom / Azure DNS)
Non-authoritative answer:
Name:    cr4.westeurope1-a.control.database.windows.net (CR4 = Control Ring number 4 from West Eu region)
Address:  104.40.168.105 (Can be found at https://docs.microsoft.com/en-us/azure/azure-sql/database/connectivity-architecture#gateway-ip-addresses)
Aliases:  SERVERNAME.sql.azuresynapse.net
          SERVERNAME.privatelink.sql.azuresynapse.net
          SERVERNAME.database.windows.net
          synapsedataslice1.westeurope.database.windows.net

Take a note of what was the DNS used to resolve. Was it Azure DNS, company DNS, or ISP / local dns? Might need to involve your network team to help troubleshoot any issues here if not resolving correctly.

Check for all configured DNS servers using something like "IPCONFIG /All". Maybe, one of them is resolving correctly other is not. You can force NSLOOKUP to specific DNS server using

NSLOOKUP endpoint dnsserver NSLOOKUP SERVERNAME.sql.azuresynapse.net 8.8.8.8 NSLOOKUP SERVERNAME.sql.azuresynapse.net 8.8.4.4

Check what is the IP resolved? In this case we want to reach public endpoint, so you can verify if this is one of the documented gateways

TEST PORT

You need to have open outbound ports needed to access synapse as documented at https://docs.microsoft.com/en-us/azure/synapse-analytics/security/synapse-workspace-ip-firewall#connect-to-azure-synapse-from-your-own-network

The port will depend on client. Most of them use 1433 (Like SSMS / Power BI) and 11xxx mentioned above (Proxy vs Redirect).

From Synapse Studio as we are using Web APIs requests, so we need ports 443 or 1443

You can test port using as sample Powershell command "Test-NetConnection"

FORMER SQL DW (Dedicated Pool)

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

SYNAPSE WORKSPACE (Dedicated Pool)

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

SYNAPSE SERVERLESS

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

Sample results

ComputerName : SERVERNAME.sql.azuresynapse.net
RemoteAddress : 104.40.168.105
RemotePort : 1433
InterfaceAlias : MSFT
SourceAddress : 100.x.x.x
TcpTestSucceeded : True

Check

TcpTestSucceeded = True?
RemoteAddress = 104.40.168.105 = Same IP you got from NSLookup command above? If not equal you might have fixed entry in HOSTs file (C:\Windows\System32\drivers\etc\hosts)

*You can also use telnet that works fine in other platforms

TROUBLESHOOTING

#1 Transient connection

Transient failures are a normal occurrence and should be expected from time to time. They can occur for many reasons such as balance and deployments in the region your server is in, network issues. A transient failure can takes some seconds or minutes, when this takes more time we can look to see if there was a larger underlying reason.

You should have a retry logic as a best practice when working with Azure SQL DB / Synapse. Here are more information on the connection recommendations:

Troubleshoot transient connection errors in SQL Database and SQL Managed Instance

https://docs.microsoft.com/en-us/azure/azure-sql/database/troubleshoot-common-connectivity-issues

Application development overview - SQL Database & SQL Managed Instance

https://docs.microsoft.com/en-us/azure/azure-sql/database/develop-overview

Troubleshooting connectivity issues and other errors with Azure SQL Database and Azure SQL Managed Instance

https://docs.microsoft.com/en-us/azure/azure-sql/database/troubleshoot-common-errors-issues

#2 A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)
- If problem is SERVER IS NOT FOUND. Its NOT Synapse Firewall blocking your inbound connection or else you would get other error
- Option 1: Check Synapse Dedicated Pool health on Azure Portal
- Option 2: Check name or port as suggested above from client as suggested below
- Option 3: SNAT Port Exhaustion - Not common but it happens. Check https://techcommunity.microsoft.com/t5/azure-database-support-blog/reaching-azure-sql-db-connection-limits-in-many-ways/ba-p/1369218 look for "4 - SNAT Port Exhaustion"

#3 Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=5895; handshake=29;
- In this scenario you reached the gateway, but could not complete the connection in time
- Option 1: Client VM CPU is high
- Option 2: Some appliance in the middle could be with problems slowing down communication
- Option 3: Need network trace to understand what can be happening at network level (Will speak about this in some other post)

#4 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
- This is usually some issue on client side that caused disconnection
- Try to check if CPU is not hitting 100%
- Capture a network trace to check for connection issues (I plan to add later an artile about it)

#5 Cannot connect to SQL Database: 'tcp:asyp-coyote-dataengineering-dev-ondemand.sql.azuresynapse.net,1433', Database: 'Master', User: 'sqladminuser'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. Login failed for user 'sqladminuser'., SqlErrorNumber=18456,Class=14,State=1,

Check if database name is correct
Check if user + password is correct
Try to connect with SQL Admin to check if problem is your user or something else

#6 Login failed for user '<token-identified principal>'

User does not exists on DW you are connecting
Check link below for more info
- https://techcommunity.microsoft.com/t5/azure-database-support-blog/aad-auth-error-login-failed-for-user-lt-token-identified/ba-p/1417535
- Check if AAD user Object ID x SID match with what you see on DW
  - --SID to OBJECTID SELECT DP.name ,DP.principal_id ,DP.type ,DP.type_desc ,DP.SID ,OBJECTID = CONVERT(uniqueidentifier, DP.SID) FROM SYS.database_principals DP WHERE DP.type IN ('S','X','E')

Continue to website...