Content
- #1 - What is the SOURCE and DESTINATION that you want to reach?
- #2 - IP and Ports Needed
- #3 - Forcing a failure
- #4 - How to open my firewall
- #4.1 - Client inside Azure
- #4.2 - Client outside Azure
- #5 -Troubleshooting
- #5.1 Transient connection
- #5.2The server was not found or was not accessible
I will do a series of posts regarding Fabric connectivity. As there are a lot of topics to cover such as inbound, outbound, public and private endpoints, managed private endpoints etc., it will be easier to break these into smaller dedicated posts.
I already have a series for Synapse connectivity and you can find those here:
- Synapse Connectivity Series Part #1 - Inbound SQL DW connections on Public Endpoints
- Synapse Connectivity Series Part #2 - Inbound Synapse Private Endpoints
- Synapse Connectivity Series Part #3 - Synapse Managed VNET and Managed Private Endpoints
- Synapse Connectivity Series Part #4 - Advanced network troubleshooting and network trace analysis
First I would like to explore the Fabric DW / SQL Endpoint public endpoint connectivity. But before diving into troubleshooting, it’s essential to understand the key components involved in establishing a connection:
You first need to think about what the source is and what is the destination and and the various components in between:
#1 - What is the SOURCE and DESTINATION that you want to reach?
- Source
- Is your client/VM running OnPrem using PBI Desktop or Inside Azure (Azure VM / PBI Service)?
- Do you have a Corp Firewall?
- What Outbound Ports will be needed?
- Internet Proxy in the middle?
- Destination
- Do you want to use public or private endpoint?
- What endpoint you want to reach?
From Microsoft Fabric Portal you can get the Fully Qualified Domain Name (FQDN), something like [xxxx-xxxx.datawarehouse.fabric.microsoft.com] used for your client to reach your Microsoft Fabric DW or Lakehouse SQL analytics endpoint that can be used as the [server name] in a connection.
#2 - IP and Ports Needed
Fabric DW connection only requires OUTBOUND TCP port 1433 to be open.
ATTENTION!
When configuring your firewall, ensure that outbound port 1433 is open for the (FQDN and additional IPs), as specified in the documentation.
Allow Power BI service tags through firewall
To ensure proper access, you need to allow the Power BI service tags for firewall access. For more information, see Power BI Service Tags. You cannot use the Fully Qualified Domain Name (FQDN) of the TDS Endpoint alone. Allowing the Power BI service tags is necessary for connectivity through the firewall.
Below you can find diagram to better visualize scenario
- NOTE1: This is all transparent from client / driver point of view. You just need to worry to make sure firewall is open or else connection will fail
- NOTE2: Reg 2nd redirect connecton (xxxx-pbidedicated), don't consider it a fixed name as it may change
For people that came from Synapse world, this will work in similar way as the redirect connection
If you do simple test connection and monitor it using Wireshark ( Synapse Connectivity Series Part #4 - Advanced network troubleshooting and network trace analysis ) using filters like samples below, you will be able to visuallize what is happening behind scene
- DNS requests (May not be found if DNS entry is cached)
- (dns.qry.name contains "pbidedicated" or dns.qry.name contains "fabric")
- Client Hello packages
- tls.handshake.extensions_server_name contains "fabric" or tls.handshake.extensions_server_name contains "pbi"
You will be able to find the 2 endpoints / connections and related IPs
You can then use wireshark function to follow up each individual conversation to check status and what could have blocked your connection
- Click on specific conversation and Follow > TCP Stream
Here you can see initial conversation with FQDN [xxxx-xxxx.datawarehouse.fabric.microsoft.com] / IP [52.150.139.133] / port [1433]
Here you can see redirected conversation on TDS Endpoint [XXXX-pbidedicated.windows.net] / IP [52.150.139.166] / port [1433]
#3 - Forcing a failure
To make a failure happen, I will add an entry in hosts file, redirecting a name to wrong ip like 127.0.0.1, but would have similar results as a firewall blocking port
To be more visual, this is what is happening
From client I got error below
- Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection
to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For
more information see SQL Server Books Online.
Note that in this example, the connection using the FQDN obtained from the Fabric portal works properly. What is actually failing is the redirected connection (You can see destination IP = 127.0.0.1 I forced to go to cause an error)
#4 - How to open my firewall
Based on the information shared above you need to open your firewall not only to FQDN, but also to a REDIRECT connection whose name or IP address may vary.
This configuration will also depend if your client is inside Azure or Outside Azure, if your network uses Azure Firewall or Third Party Firewall.
Check below some generial guideline:
#4.1 - Client inside Azure
- As documented [here] open your [NSG] and / or [Azure Firewall] to [PowerBI Service tag].
- A service tag represents a group of IP address prefixes from a given Azure service. Microsoft manages the address prefixes encompassed by the service tag and automatically updates the service tag as addresses change, minimizing the complexity of frequent updates to network security rules.
- You can find list here https://www.microsoft.com/en-us/download/details.aspx?id=56519
- As documented [here] open your [NSG] and / or [Azure Firewall] to [PowerBI Service tag].
#4.2 - Client outside Azure
- #4.2.1 - You can still use Service Tags as above, but then you might need some automation to constantly update this list as new IPs can be added to range.
- You can use Powershell or download JSON - Virtual network service tags
- #4.2.2 - Other option is to use FQDN / URLs as documented Add Fabric URLs to your allowlist
- #4.2.1 - You can still use Service Tags as above, but then you might need some automation to constantly update this list as new IPs can be added to range.
| Required: Datamart SQL | datamart.fabric.microsoft.com | TCP 1433 |
| Required: Datamart SQL | datamart.pbidedicated.microsoft.com | TCP 1433 |
| Required: Datamart SQL | *.pbidedicated.microsoft.com | TCP 1433 |
| Required: Fabric DW SQL | datawarehouse.fabric.microsoft.com | TCP 1433 |
| Required: Fabric DW SQL | datawarehouse.pbidedicated.microsoft.com | TCP 1433 |
| Required: Fabric DW SQL | *.pbidedicated.microsoft.com | TCP 1433 |
| Required: Fabric DW SQL | *.pbidedicated.windows.net | TCP 1433 |
#5 - Troubleshooting
#5.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 / Fabric DW. Here are some recommendations:
#5.2 The server was not found or was not accessible
- 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.)
#5.2.1 - From Notebook / VM / Azure VM / PBI OnPrem Datagateway VM
- Test Name Resolution
- NSLOOKUP XXXXXXX-XXXXXX.datawarehouse.fabric.microsoft.com
- Test Port
- Test-NetConnection -Port 1433 -ComputerName XXXXXXX-XXXXXX.datawarehouse.fabric.microsoft.com
- Test Redirect connection => Need Capture / Analyse network trace
- You won't know what is the TDS endpoint so you might need a network trace to check it
- Synapse Connectivity Series Part #4 - Advanced network troubleshooting and network trace analysis
- Run Powershell TEST SCRIPT
- Use the Fabric version of this script below
- It will test name, port, real connection all at once
- Test Name Resolution
#5.2.2 - From PBI Service (Cloud) using VNET Data Gateway
- You can test Name and Port directly from Power Platform. Follow steps documented at Troubleshoot a virtual network data gateway
