I'm trying to connect powerbi to a private PostgreSQL Flexible Server integrated into a virtual network.
- I have successfully connected to the VNet over VPN using a point to site conection
- Over that point to site connection I can connect to my PostgreSQL Server directly using it's local IP address but not it's FQDN
- I've spun up a virtual machine inside the VNet that can connect to my PostgreSQL Server directly using the local IP address or the server's FQDN
- I have successfully created a data gateway in power bi to the virtual network that claims a successful connection
- I have successfully created a Data connection to the postgreSQL server using the data gateway that also claims a successful connection
Here's my problem, I cannot use PostgreSQL servers as a data source in the website and power bi desktop cannot connect to the PostgreSQL server because it's private and private DNS zones do not work over VPN point to site connections. From the research I've conducted the general procedure of power bi is that you are suppose to use a computer with direct access to your database to create the report then you publish that report to power bi and in the website are then able to use the data gateway instead of the direct connection. However my computer cannot connect to the database in a way that power bi can recognize.
So how do I create reports in power bi that are based on the data in my private PostgreSQL Server?
UPDATE 07/27/2023: I was able to access the database in power bi desktop using a virtual desktop hosted in the same azure virtual network as my PostgreSQL database. From this virtual desktop I was able to create a report and publish it so that I can view it on the power bi website. However now when I try to switch it over to using the data gateway it says the connection failed. The original error is that no credentials are provided however if I go to the data gateway connection and edit it's settings so that it has credentials it fails with the error "No such host is known". The data gateway connection I originally made still claims success, mapping the reports gateway to this original gateway doesn't appear to do anything.
So how do I use or modify the data gateway so that it connects to the PostgreSQL database?
