PBI Data Gateway to SQL Server on Azure VM - connections being weird

[mmace1]

We have Power BI Data Gateways running on Azure VMs. All of their connections to multiple SQL Servers running also on Azure VMs, have started 'sort of' working the last week. For an existing semantic model - it says "not reachable", though the refresh continues to work

That's weird.

Check the data source - it is gray and says offline. Try to re-enter credentails - error.

OK, try to create a brand new connection, same error -

 Tried with verified-good Basis and O Auth 2.0 credentials, same.  Same credentials I can use to connect via SSMS. 

Recently, someones existing dataset stopped refreshing - so ok, maybe existing semantic models aren't safe after all.

Troubleshooting - everything seems to be fine.

• Can connect to the sql server from my local machine via RDP

• Can connect to the sql serer from my local machine using SSMS, with both the Basic (i.e. sQL server) credentials and my personal login (i.e. O Auth 2.0) - both work

• Can connect to the SQL server using power bi, from the VM hosting the power bi data gateway

• Can sucessfully run Test-NetConnection in PowerShell from my local machine to the SQL server

• Can sucessfully run Test-NetConnection in PowerShell from the Gateway VM to the SQL Server

So...

• Gateway appears online
  Connections to other non-SQL Servers seem fine
  Tried removing one VM from the gateway cluster, rebooting gateway VM, reinstalling as brand-new gateway - same issue

Any thoughts what could be wrong? Could it be some weird networking thing with the Azure VMs that host SQL Server?

[mmace1 · Accepted solution]

OK I think got this. Had to edit the config file on all gateways, to explicitely allow all the SQL Servers

https://community.fabric.microsoft.com/t5/Service/Received-error-payload-from-gateway-service-with-I...

I'd like to understand this better, since it triggers "wtf?" - i.e. being able to connect to the single most common data source type, should reuiqre manual entries in a text file, so I imagine I'm missing something. 

[v-venuppu]

Hi @mmace1 ,

Thank you for reaching out to Microsoft Fabric Community.

Here are some recommended steps to follow to solve the issue:

1.Ensure that your Power BI Data Gateway is updated to the latest version, as updates often include fixes for connectivity issues.

2.Check that the SSL certificates used by your SQL Server are valid and trusted by the gateway. If necessary, configure the gateway to trust the server certificate.
3.Confirm that there are no recent changes in your Azure network settings that could affect connectivity, such as firewall rules or network security groups.

4.Examine the gateway logs for any error messages or warnings that could provide more insight into the issue.

Refer to the official Microsoft documentation on troubleshooting Power BI gateways for more detailed guidance:

Troubleshoot gateways - Power BI | Microsoft Learn

If the problem still persists, I recommend submitting Support Ticket using the below link:

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

 Thank you.