Forum Discussion
SQL TDS Endpoint Connector
Hi,
I'm attempting to connect to a TDS Endpoint (Dataverse) read only sql database. I've done my query and put it into power bi.
This refreshes fine locally but gives me below when I'm attempting to refresh on the gateway/service.
Microsoft SQL: 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server
How could it be the correct server locally and not on the service?
Thanks
Hi lherbert501,
Thanks for reaching fabric commuinity. will always happy to answer. It seems issue with the connection configuration for this endpoint. Can you please look at below things.
Why it works locally but not on the service
When you refresh locally, Power BI Desktop uses your machine's credentials and network directly. When refreshing via the Power BI Service/Gateway, it routes through the gateway machine, which has a different network context.
Root Causes & Fixes
- Gateway is using Named Pipes instead of TCP/IP
The error explicitly says Named Pipes Provider, error: 40. The gateway needs to connect via TCP/IP, not Named Pipes.
- On the gateway machine, open SQL Server Configuration Manager
- Enable TCP/IP under SQL Server Network Configuration
- Disable or deprioritize Named Pipes
- The Dataverse TDS Endpoint URL must be used correctly
For Dataverse, your connection string should use the org's TDS endpoint URL format:
Ex- yourenvironment.crm.dynamics.com, 5558
Make sure the gateway machine can resolve and reach this hostname on port 5558.
- Firewall on the gateway machine
The gateway server may be blocking outbound port 5558. Verify that:
- Port 5558 is open outbound on the gateway machine
- No corporate firewall is blocking it
- Gateway credentials / authentication
Dataverse TDS requires Azure AD (OAuth) authentication. In Power BI Service, go to:
Dataset Settings → Data source credentials → Edit credentials → Set authentication to OAuth2
- On-premises Data Gateway version
Ensure your gateway is up to date, older versions had known issues with Dataverse TDS. Download the latest from Microsoft.
Quick checklist
Check
Action
Port 5558 open on gateway?
Test with telnet yourenvironment.crm.dynamics.com 5558
OAuth2 set in Power BI Service?
Dataset → Settings → Credentials
Gateway updated?
Download latest version
TCP/IP enabled on gateway machine?
SQL Server Config Manager / Please try to connect the SQL read
only endpoint from the Gateway machine (SSMS Connection Test)The most common fix for this specific error is setting OAuth2 credentials in the Power BI Service for the Dataverse data source.
Hope these can help to fix your issue. Please consider this post as eccepted solution if helps.
2 Replies
Hello
I’m not completely sure, but this usually happens when the gateway can’t reach the same endpoint your local machine can, even if it works fine on your side, often it’s a network or DNS issue rather than the query itself. I’d check if the gateway machine can resolve and access the TDS endpoint, maybe try connecting from that server directly, also verify firewall rules, ports, and that the connection is using the right auth method in the service, sometimes switching from named pipes to TCP/IP or using the full server URL helps. If it’s Dataverse, also double check that TDS is enabled and allowed for external connections, and that the gateway is updated.Best regards,
DanieleHi lherbert501,
Thanks for reaching fabric commuinity. will always happy to answer. It seems issue with the connection configuration for this endpoint. Can you please look at below things.
Why it works locally but not on the service
When you refresh locally, Power BI Desktop uses your machine's credentials and network directly. When refreshing via the Power BI Service/Gateway, it routes through the gateway machine, which has a different network context.
Root Causes & Fixes
- Gateway is using Named Pipes instead of TCP/IP
The error explicitly says Named Pipes Provider, error: 40. The gateway needs to connect via TCP/IP, not Named Pipes.
- On the gateway machine, open SQL Server Configuration Manager
- Enable TCP/IP under SQL Server Network Configuration
- Disable or deprioritize Named Pipes
- The Dataverse TDS Endpoint URL must be used correctly
For Dataverse, your connection string should use the org's TDS endpoint URL format:
Ex- yourenvironment.crm.dynamics.com, 5558
Make sure the gateway machine can resolve and reach this hostname on port 5558.
- Firewall on the gateway machine
The gateway server may be blocking outbound port 5558. Verify that:
- Port 5558 is open outbound on the gateway machine
- No corporate firewall is blocking it
- Gateway credentials / authentication
Dataverse TDS requires Azure AD (OAuth) authentication. In Power BI Service, go to:
Dataset Settings → Data source credentials → Edit credentials → Set authentication to OAuth2
- On-premises Data Gateway version
Ensure your gateway is up to date, older versions had known issues with Dataverse TDS. Download the latest from Microsoft.
Quick checklist
Check
Action
Port 5558 open on gateway?
Test with telnet yourenvironment.crm.dynamics.com 5558
OAuth2 set in Power BI Service?
Dataset → Settings → Credentials
Gateway updated?
Download latest version
TCP/IP enabled on gateway machine?
SQL Server Config Manager / Please try to connect the SQL read
only endpoint from the Gateway machine (SSMS Connection Test)The most common fix for this specific error is setting OAuth2 credentials in the Power BI Service for the Dataverse data source.
Hope these can help to fix your issue. Please consider this post as eccepted solution if helps.
