Postgresql Data Loads, Fails on Refresh

[pb_]

I am attempting to connect to a postgresql database and create a dataflow. I am able to connect to the database and load the tables initially. However, it fails on the first attempt to refresh.

Error: Data Source Error : DataSource.Error: An error happened while reading data from the provider: 'The remote certificate is invalid according to the validation procedure.' DataSourceKind = <ccon>PostgreSQL</ccon> DataSourcePath = <ccon>x.x.x.x:5435;xxxxx</ccon>. RootActivityId = 67c26df2-1939-4ca9-90b6-ff9864375d18.Param1 = DataSource.Error: An error happened while reading data from the provider: 'The remote certificate is invalid according to the validation procedure.' DataSourceKind = <ccon>PostgreSQL</ccon> DataSourcePath = <ccon><ip>x.x.x.x</ip>:5435;xxxx</ccon> Request ID: aa994cad-1770-e926-fdb9-e2cd77b5b4b1.

Not sure why it is able to query the data initially and then immediately fails. The desktop app is able to refresh endlessly. I have tried starting on desktop, publishing, then editing the data source with the credentials on the site and not include encryption. That also returns the same error.

I believe ODBC does not support direct query so I would obviously prefer to avoid that. It appears to work just fine in Tableau.

[tharunkumarRTK]

@pb_

 Is your postgres connection is an on premises gateway connection? If yes, have you installed necessary certificates in your gateway machine?

You can try opening the connection from 'Manage connections and gateways' page and change the encryption method to 'Not encrypted'. if that does not work then make sure you installed all the certificates in your gateway machine and then try again. 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[pb_]

I am trying to avoid using a gateway as I do not want to have to set dozens of refresh times. If it fails because my PC turns off, I'd rather not have to turn it back on. I do have that set to "unencrypted", as I mentioned in the original post.

This does not really address the behavior of it initially working and failing to refresh (or working flawlessly on desktop). It is able to initially connect just fine and pull whatever data I want. I can connect using the web service app or the desktop. In both cases it initally loads the data and then does not refresh.

Is there a way to run a gateway, using a certificate, and not have to set 96 refresh times to have it update every 15 minutes? Is there a way to have it automatically resume refreshing when the gateway reconnects without having to manually tell it to? If so, I would use it via gateway, or at least establish the connection in that manner.

I am trying to maintain a direct query connection / on demand with automatic refresh.

Edit: My inutition is telling me that the initial connection runs as told without encryption and in the subsequent ones the software fails to remember this.

[tharunkumarRTK]

@pb_ 

Firstly, I guess you installed gateway in your personal machine, you should consider installing it in a virtual machine and your machine should be up and running 24/7. Otherwise your gateway connection wont work when your machine is turned off. Also make sure you installed the gateway in standard/enterprise mode not in personal mode. 

https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-install

After installing the gateway, you need to install necessary certificates and drivers to establish the connection. Then you will be able to configure a gateway connection. 

Regarding the refresh schedule, if you do not want to manually schedule it multiple times then you should consider creating a power automate flow and schedule it which can triggers the data refresh.

Or you can also write a script in your preferred language and execute power bi apis and schedule the script, and it can trigger the data refresh.

https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/refresh-dataset-in-group

Regarding the issue that you are facing, as you not using any gateway and your connection is sharable clould connection, I think you should raise a Microsoft support ticket and they can help you to resolve the problem.