We know that the problem is remote applications being unable to connect to SQL Server. I want to simplify this and get the gateway out of the picture.
A lot of times we can become fixated on the app we are looking at and ignore other possibilities. You may know that locally works and remote does and start looking at why the gateway specifically isn’t working. Maybe you start to think something on the Power BI side is broken, or maybe we can’t talk to the Azure Service Bus. This is why I like to remove the gateway from the picture. Or, if you had a custom application, let’s remove that from the picture. Simplify as best you can. This helps to exclude a lot of potential complexity.
How do we do that? You can use the same test that you used locally! Use something like a UDL file or another tool to see if it can connect. Because it is SQL Server, I’d really like to try Management Studio. Management Studio uses .NET as does the gateway. To start, it doesn’t necessarily have to be the same machine as the gateway. If you have a different machine that has Management Studio installed, use that! If that works, then we may want to do the test specifically from the gateway machine.
This is a good troubleshooting guide, dealing with some of the more likely causes before moving on to the esoteric issues.