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.
Find below link for second part of this series
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 the source is and what is the destination and lot of things in between:
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)
Find below more details for the 2 processes (Proxy vs Redirect mode)
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
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
This script created by a colleague from Microsoft ( @VitorTomaz ). 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
First you need to know if your client can resolve the name like samples below:
*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-addre...)
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
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#conn...
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"
Sample results
ComputerName : SERVERNAME.sql.azuresynapse.net
RemoteAddress : 104.40.168.105
RemotePort : 1433
InterfaceAlias : MSFT
SourceAddress : 100.x.x.x
TcpTestSucceeded : True
Check
*You can also use telnet that works fine in other platforms
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
--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')
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.