- Heroku does require encryption, so the non-encryption setting in the PostgreSQL connector in Power BI is not an option.
We ended up changing our connection to an ODBC connection, specifying sslmode=require (i.e. encrypted, but trusting server certificates and thus no need to install them on the client side) in the connection string.
I would suggest that the Power BI PostgreSQL connector can come to work with Heroku Postgres databases somehow. I believe, there must be many other potential customers out there with the same need.
This can probably be solved in many different ways. I can think of two suggestions:
- Ability to specify some settings (in particular Trust Server Certificate) for the Npgsql driver.
- Ability to specify the connection string somehow for the Npgsql driver - if that's even possible.
In the past we used Npgsql 2.x (which hadTrust Server Certificate set to true by default) to get around this problem. However, Npgsql 2.x is no longer supported by new Heroku Postgres instances. I don't know why, but maybe due to TLS 1.2 being required on Heroku / AWS side now and not (properly) supported by Npgsql 2.x.