0

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

Error messages

The error messages I received were:

"ConnectionId":"bcfc620b-0fda-4db9-bda6-d9ac4aabcc4c","Exception":"Exception:\r\nExceptionType: System.Security.Authentication.AuthenticationException, System, Version=4.0.0.0, 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

and

Details: "An error happened while reading data from the provider: 'The remote certificate is invalid according to the validation procedure.'"

and

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.)"

Questions

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?

  • Is the PostgreSQL 11 on your Windows machine doing something, or is just there to get the client libraries? – jjanes Apr 23 at 16:38
  • What is the text of the error message? – jjanes Apr 23 at 16:39
  • The postgresql 11 on Windows is just there to get data from my Ubuntu database (client libraries?) / log in remotely into my psql users on the Ubuntu Database. The error message text is edited into my post. – Humble-Data-22 Apr 23 at 18:29
0

I believe that PowerBI uses npgsql to connect, which defaults to the behavior that libpq/psql gives you under 'PGSSLMODE=verify-full'. Can you connect to your server from Windows psql if you use 'PGSSLMODE=verify-full'?

The tutorial you linked assumes that you use the same machine for the certificate authority and for the database server, which will probably work for you. But it also assumes the client machine is debian/ubuntu, not Windows. So you will have do everything before "Configure the client" on your ubuntu machine. But you probably already did that, based on the error message you are getting.

And you will have to do everything after "Configure the client" on your Windows machine, except you have to do it using Windows mechanisms, not linux mechanisms. How to do that is a question of how your infrastructure is set up--you need some way to copy the file ca-cert.pem from the cert authority to the Windows machine, but that mechanism could be scp (if you have that installed on Windows, perhaps via cygwin) or a CIFS mount, or just copy the text of the .pem files out of putty and paste it into notepad, or use a thumbdrive.

Note that the tutorial is for using certs in both directions--for the client to verify the identity of the server, and for the server to verify the client too. But it is rare for people to actually use client certs--they usually use server certs only. If you are not using client certs, then you only need to install one cert on the client, which is the "ca-cert.pem" file (which should get named "root.crt" when installed on the client). The other two are only needed for client certs. And on Windows, the path it needs to be installed in is %APPDATA%\postgresql\root.crt, rather than ~/.postgresql/root.crt.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.