Published report when datasource reached by hostname, but not IP

[PBIUsername1]

Hello Community ! I'm having a strange problem described below. I don't understand what may be happening and wondering if anyone experienced this kind of behaviour before ? 

I have a report that's been published in 2018, it works well since then and no update was required to that file. It runs using the enterprise data gateway on an azure windows server which connects to our local on-premise SQL server.

This report refreshes everyday without a problem and the datasource is setup with an IP address to the SQL Server on premise.

Now, an updated version of that report was required. We are trying to push the same updated report from 2018 with the added updates, which automatically maps to the existing datasource. The newer report won't work. It shows that the datasource connects properly, but when we actually try to query the dataset we get the following error 

An error occurred during On-Premise Gateway related activity. Additional error details: DM_GWPipeline_Gateway_MashupDataAccessError

Now, for debugging purposes I reused the file from 2018, renamed it to something else, and published it to the service. Again, it automatically maps to the existing datasource but then, i get the same error as above.
At that point, i created a brand new report, very simple, a single table, querying a single column and pushed it to the service... same error.

After trial and error, I created a report but used the hostname instead of the IP in the PBIDesktop file.  Pushed it to the service, configured a new datasource based on hostname, and the report works fine. It seems like the problem is when the report is configured by IP rather than hostname. I picked the newer version of my report, changed it to connect by hostname, and it started workign as well.

I have tested both the IP and hosname version on my PC, and on the azure server hosting the gateway. Both versions works. However, when published to the service, only the hostname based file will work. 

What could it be that makes the IP version fail, where as the hostname works? Seems like this only happens via the powerbi service... I have tried updating both my PowerBI desktop application, as well as the data gateway to the latest version but everything still fails when I reference the datasource by IP... 

Thanks for any input or feedback! 

[GilbertQ]

Thanks for letting us know!

[PBIUsername1 · Accepted solution]

Found my problem !
I wasn't able to find the logs or export them as I'm not the PowerBI gateway administrator and it seems like the admin has to login to the software in order to export logs. Here's what I could do however which helped me figure it out. I tracked it down to our firewall which was setup to do some packet inspection to detect applications and had special instruction for specific softwares detected.

The traffic going through the gateway and SQL server was recognized as one application by the firewall, while the traffic to the newer report was recognized as different one that was not on the allowed list.

I found that out by opening a wireshark session on both the gateway and the SQL server. Reviewing packets between the two, I found that the gateway would initiate connexion with the SQL server via a TDS protocol request and could see the request come in on SQLServer. Then, the SQLServer would send a response.
I never saw the response coming in on the gateway , so that set me on a quest to find the missing response packet... that ended-up being dropped by the firewall !

I hope this can help anyone having the same issue!

Have a great day all. 

[GilbertQ]

Hi @PBIUsername1 

What I would suggest doing is to enable the logs on the On-Premise gateway, then try and do a refresh. Once that is done review the logs for it to indicate where the error is happening?

Monitor and optimize on-premises data gateway performance | Microsoft Learn