ODBC: ERROR [HY000] Certificate validation error: Failed to verify certificate chain: CA not trusted

[kleinmantara]

Hello, I have the following error on a Power BI Gateway:

ODBC: ERROR [HY000] Certificate validation error: Failed to verify certificate chain: CA not trusted

We installed the both drivers:

• https://github.com/ClickHouse/clickhouse-odbc
• https://github.com/ClickHouse/power-bi-clickhouse

The PowerBI Gateway ist connected to the Power BI Web. The Status is online.

The Clickhouse driver is added as "User-configured data connectors" and is also displayed as a connection type in Power BI Web. When I then try to add a new "Connection" in Power BI Web, I get the same error message as above:

Details: ClickhousePowerBI_Gateway1: ODBC: ERROR [HY000] Certificate validation error: Failed to verify certificate chain: CA not trusted

I have already added the same in the issue SSL error in the Repo power-bi-clickhouse. But I think this is the better place

[carelesssava]

+1, any updates on this issue?

[kleinmantara]

@carelesssava Unfortunately, no. It might not be the 'actual' error message but rather the result of a previous error.

[carelesssava]

I solved this problem by installing the cloud certificates in 2 places at once on Windows local machine with Power BI:

• Trusted Root Certification Authorities
• Intermediate Certification Authorities

Environment:
I'm not using a Power BI Gateway like you, instead I'm editing the dashboard and using the "Publish" button.

I'm using a cloud platform for ClickHouse deploying (Managed Service), this cloud platform provides certificates for connection to its Managed Services.

[kleinmantara]

Hello @carelesssava , that sounds interesting, thanks for the feedback. Which certificates (and from where) did you take exactly?

[carelesssava]

Hello @carelesssava , that sounds interesting, thanks for the feedback. Which certificates (and from where) did you take exactly?

Hello! I'm using Managed service for ClickHouse in the Yandex Cloud environment. According to the Yandex Cloud documentation, it is required to have special certificates installed to connect to the cluster.

Resource - https://yandex.cloud/en/docs/managed-clickhouse/operations/connect/#get-ssl-cert