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

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
  
  • Configure IP firewall rules - Azure Synapse Analytics
    

  [*]SSMS / PowerBI Desktop / Others uses

  • 1433 (Proxy Mode)
    
  • 1433 + 11000 to 11999 (Redirect Mode)
    
  • Azure SQL Database connectivity architecture - Azure SQL Database and Azure Synapse Analytics
    

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 Azure SQL Database connectivity architecture - Azure SQL Database and Azure Synapse Analytics
    
    • 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:
    

* 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

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 Azure SQL Database connectivity architecture - Azure SQL Database and Azure Synapse Analytics)

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 Configure IP firewall rules - Azure Synapse Analytics

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
  

• #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 Reaching Azure SQL DB connection limits in many ways 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
    
    • AAD Auth Error - Login failed for user '<token-identified principal>'
      
    • 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 reading...