PowerBI still check that the server certificate is valid when an insecure connection is requested, while the server enforces encryption yet has a self-signed fallback certificate.
Clients using an ADO.NET provider are able to connect to the server either when "Encrypt connection" is turned off, or when "Encrypt connection" is turned on and the certificate is trusted.
Even when the certificate is in both the Trusted Root Certification Authorities and the Trusted Publishers certificate stores, if the server certificate's common-name does not match the DNS name PowerBI is connecting to the server with, then PowerBI will still fail to connect, giving the error:
Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: HTTP Provider, error: 0 - )
The error logged by Schannel before the certificate is installed into the certificate stores is:
The certificate received from the remote server was issued by an untrusted certificate authority. Because of this, none of the data contained in the certificate can be validated. The TLS connection request has failed. The attached data contains the server certificate.
The error logged by Schannel after the certificate is installed into the certificate stores is:
The certificate received from the remote server does not contain the expected name. It is therefore not possible to determine whether we are connecting to the correct server. The server name we were expecting is <servername>. The TLS connection request has failed. The attached data contains the server certificate.
though in that case the certificate data does not actually contain the certificate.
The server being connected to is not under our control, and I am submitting a ticket to the provider to have the certificate fixed, but it would be helpful to have PowerBI be able to be directed to trust the server certificate, or to (be directed to) not validate the certificate when an insecure connection is requested but the server enforces encryption.
To others with this issue, if the server is under your control, check that either the certificate is valid, or that at least the certificate common-name matches the server name and you have added it (or its CA) to your Trusted Root Certification Authorities.