I have Power BI and PostgreSQL 11 installed on Windows 10 and my primary PostgreSQL 11 database on Ubuntu 18.10 Linux. I edited the postgresql.conf and pg_hba.conf files correctly to the IP Adresses my Windows 10 computer uses with PostgreSQL 11 and Power BI to connect to my PostgreSQL 11 Ubuntu database. However, I got errors in Power BI while I could manually connect via command line.
After troubleshooting with Microsoft Support, I was told:
This indicates that the Postgresql server does not have its certificates configured and therefore cant connect to PowerBI. To allow the client to verify the identity of the server, place a root certificate on the client and a leaf certificate signed by the root certificate on the server. To allow the server to verify the identity of the client, place a root certificate on the server and a leaf certificate signed by the root certificate on the client. One or more intermediate certificates (usually stored with the leaf certificate) may be used.
However, I am confused by the tutorials at
- https://www.postgresql.org/docs/current/libpq-ssl.html and
The error messages I received were:
"ConnectionId":"bcfc620b-0fda-4db9-bda6-d9ac4aabcc4c","Exception":"Exception:\r\nExceptionType: System.Security.Authentication.AuthenticationException, System, Version=220.127.116.11, Culture=neutral, PublicKeyToken=b77a5c561934e089\r\nMessage: The remote certificate is invalid according to the validation procedure.\r\nStackTrace:\n at System.Net.Security.SslState.StartSendAuthResetSignal(ProtocolToken message, AsyncProtocolRequest asyncRequest, Exception exception)\r\n at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)\r\n at System.Net.Security.SslState.ProcessReceivedBlob(Byte buffer, Int32 count, AsyncProtocolRequest asyncRequest)\r\n at System.Net.Security.SslState.StartReceiveBlob(Byte buffer, AsyncProtocolRequest asyncRequest)\r\n at System.Net.Security.SslState.CheckCompletionBef
Details: "An error happened while reading data from the provider: 'The remote certificate is invalid according to the validation procedure.'"
Details: "Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remost host.)"
I can't tell what steps need to be done on my Ubuntu PC and what steps need to be done on my Windows PC. I don't know if special steps are needed to configure Power BI with the certificates. How do I set up my bidirectional certificates?
Can someone offer a very detailed tutorial, tell me 'how' to read a tutorial, or explain the steps in an answer please?