Power BI Desktop & Self-signed certificates

[gampy]

*** The Problem:

I'm looking for a solution to get data in Power BI Desktop from some Application Server that uses the SSL protocol by default but provides only a self-signed certificate with the distribution kit. The vulnerability of such a certificate is clear but assuming that the Application Server is often supposed to work inside an Intranet or via a VPN (see below), a signed by a CA certificate is not always needed.

*** The Infrastructure:

The Application Server, mentioned above, provides a REST API that supports the OData standard (v4).

The Application Server is mainly used inside the Intranet, uses internal IP addresses, remote clients connect to the server via a corporate VPN.

A corporate VPN must ensure the security of transmitted data.

*** Details:

1. Power BI Web.Contents() requests data from the Application Server by GET and POST http requests.
2. For test purposes I can to switch the Application Server to non-SSL mode. In this case, there are no problems with getting data.
3. In SSL mode with the self-signed certificate used an error occurs: “DataSource. Error: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel”.
4. I know that in the Power BI Desktop release of November 2020, the option “Enable certificate revocation check” appeared  (File > Options and settings > Options > Security > "Enable certificate revocation check"). The description of this checkbox shows that unchecking it should solve the problem not only with revoked certificates but also with self-signed certificates. But in my case, unchecking the checkbox did not change anything, the error remains.
5. The Microsoft documentation mentions the key “DisableCertificateRevocationCheck” in the system registry that works in a similar way. I didn't find such a key in my registry. (Current installed release of PBI: 2.88.1682.0 64-bit, December 2020).
6. The validity of http requests is checked through Postman. If the “Enable SSL certificate verification option” is enabled, an error is returned: "SSL Error: Self signed certificate". if the option is disabled – the requests are executed successfully. It seems the Power BI Desktop option "Enable certificate revocation check" should work in a similar way.
7. I have not found any other way to disable SSL certificate verification in Power BI although I have seen such an option in many http clients.  This option is indispensable for test operations.

I would be very grateful for any assistance and recommendations in solving this problem.

[ERIC_A]

Hi 

Maybe is something similar as your case. I had a challenge to fetch a table from a dashboard that is in the intranet. I found a way to do it with python where I set the path to it in my request.post method and it worked fine. I haven't tried to publish this app but I think It might fail unless I find a way to have pbi see a certificate reachable for secure connection.

rgds!

[gampy · Accepted solution]

I've tried many options including installing local web servers with a redirect from http to https and disabling SSL certificate validation.

So far the only working option is the following:

find the Power BI Desktop config Microsoft.Mashup.Container.NetFX45.exe.config and add a configuration there:

<configuration>
  <system.net>
    <settings>
      <servicePointManager
        checkCertificateName="false"
        checkCertificateRevocationList="false"         
      />
    </settings>
  </system.net>
</configuration>

Thanks to the user @tr123 

[PhilipTreacy]

Hi @gampy 

You could also try using a proxy like Proxifier which will create an SSL tunnel between it and your App Server.

Proxifier - The Most Advanced Proxy Client

You make your PBI requests using SSL (HTTPS) via the proxy and it then forwards that to the server as unencrypted HTTP.

regards

Phil

[gampy]

Hi @PhilipTreacy 

Thank you for your reply. I did ponder over some intermediate proxy-server and concluded it might help here.

I downloaded and installed the trial version of Proxifier. As far as I understood from the documentation, this is not a proxy server but an aggregator for proxy servers. That is, Proxifier is installed on a client machine, catches all local http(s) requests, and redirects them directly to the target server or to an intermediate proxy server.

In my case, I need a Proxy Server to be installed somewhere on the intranet on one of the remote servers (or even on the same server where the Applications Server is installed). Power BI sends an https request to the Proxy Server and it redirects unencrypted HTTP (but since this already happens inside a closed Intranet we consider all the Intranet resources to be trustworthy).

Is it possible to configure Proxifier this way or I ought to look for another software?

Update

--------

On second thought, after reading more about certificates, I realized that a proxy server inside the Internet will not help any way. Power BI, which is a client in this scheme, will reject attempts to establish a SSL connection without a CA certificate in any case.

Yet, how does “Enable certificate revocation check” option work?

[PhilipTreacy]

Hi @gampy 

Can you install an SSL cert on the App Server?  If so you can generate a free one from Let's Encrypt

Let's Encrypt - Free SSL/TLS Certificates

and PBI shouldn't have any issues with that cert as the issuing Certificate Authority is known and trusted.

Regards

Phil

[gampy]

Hi @PhilipTreacy ,

Unfortunately, I am not really familiar with the certificates’ basis but as far as I understand Certificate Authorities (including Let's Encrypt) fundamentally cannot issue and validate certificates for local IPs and internal domain names as the latter are not unique.