Connecting to offsite SQL server - translating server name/port issue

[mattlancs]

I've got a report which collects data from a SQL server running in the cloud, named "AB-SQL-01". The virtual machine it's sitting on is tightly locked down, so communication has to be on a specific port (let's say 14330), and it can only come from pre-defined IP addresses. There's no option to connect to that server through a VPN. That machine does have the Power BI gateway on it and working though, at least.

When I create a Power BI report on my desktop, I can connect by using the SQL server name as "123.456.789:14330". However, when I upload the report to the Power BI website, that won't be able to refresh because the SQL server won't accept traffic from Microsoft.

If I try to add the server to the gateway that doesn't help, because to the gateway's point of view the server name is "AB-SQL-01", not "123.456.789:14330", because it's already on the network and doesn't need to connect from outside.

So, I need to write the report on my laptop to collect data from AB-SQL-01, so that when I upload it to the Power BI website it can use the gateway.

I’ve tried editing my hosts file (C:\Windows\System32\drivers\etc) to add the translation from the server name to the IP address (“123.456.789 AB-SQL-01”) and that almost works, but it doesn’t account for the port name and ports can’t be added in the hosts file. It does allow me to have a report with a data source as “AB-SQL-01:14330”, but that still doesn't work when uploaded.

The next step seems to be to define that all traffic from my laptop going to 123.456.789 should go to port 14330. Stack Exchange says that’s possible, but I’m falling down when it comes to knowing what my listenport and listenaddress should be.

Does anyone else have any experience with this?

[mattlancs · Accepted solution]

Just to follow up: I got this working in the end, with some help from IT support. They had to create a rule on the virtual machine which would direct SQL traffic coming from someone in the management group - i.e. me - to go to the right port number. That meant I no longer have to define the port number, when connecting from the laptop.

That combined with the edited hosts file meant that I could connect using just the SQL server name, because the hosts file points that traffic at the right IP address.

I found this only works if I use basic authentication and a service account set up on the database, rather than my usual Microsoft 365 credentials.

[lbendlin]

, because it's already on the network and doesn't need to connect from outside.

It doesn't need to, but it also won't hurt if it does.

The connection string MUST MATCH EXACTLY between Power BI Desktop and Gateway.  

[mattlancs]

Thanks very much for the reply! Unfortunately trying to connect from 'outside' brings issues of reconfiguring the firewall on the server, which is outside of my remit. I'd thought it might work, since the data gateway can communicate, but apparently not.

One option might be for me to replicate the database locally, so I can use the same connection string as from the Power BI site, but it feels like there must be an easier way...

Cheers,

Matt