Forum Discussion
Advocate IIConnecting to offsite SQL server - translating server name/port issue
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.
Super User, 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.
Advocate IIThanks 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