ClickHouse
ODBC: ERROR [HY000] Certificate validation error: Failed to verify certificate chain: CA not trusted
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 Unfortunately, no. It might not be the 'actual' error message but rather the result of a previous error.
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.
Hello @carelesssava , that sounds interesting, thanks for the feedback. Which certificates (and from where) did you take exactly?
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
+1. We are having the same issue under Windows when using PowerBI Desktop and PowerBi data Gateway. Tested and not working with both self-signed certificates and Let's Encrypt certificates. This is a blocker for us in our possible adoption of Clickhouse in our company PowerBI desktop: 2.143.878.0 64-bit (May 2025) Windows Server 2022 Datacenter, version 21H2
Same issue also with On-Premise Data Gateway (version 3000.266.4 April 2025)
@slabko Sorry to shamelessly tag in this ticket but I tried to investigate further the issue looking at the Poco library. It seems we are using quite an old version: For example we can see some certificate validation fixed (https://github.com/pocoproject/poco/issues/4421) in upstream code:
- https://github.com/pocoproject/poco/pull/4428
- https://github.com/pocoproject/poco/blob/main/NetSSL_OpenSSL/src/SSLManager.cpp#L78
However none of this is in clickhouse-odbc: https://github.com/ClickHouse/clickhouse-odbc/blob/master/contrib/poco/NetSSL_OpenSSL/src/SSLManager.cpp#L69
Could we update to a more recent version of the Poco library and SSL related code?
Another upstream does fix loading the Windows certificate store: https://github.com/pocoproject/poco/pull/2773
Looking at the diff and also upstream main code (https://github.com/pocoproject/poco/blob/main/NetSSL_Win/src/Context.cpp#L171), we seems to be using an older version of the code: https://github.com/ClickHouse/clickhouse-odbc/blob/master/contrib/poco/NetSSL_Win/src/Context.cpp#L170
After further tests, we managed to make it work if we are installing or copying the root certificate in the user Personal store.
- For self-signed certificates: Right Click on the. self-signed root CA certificate → Install certificate → Current user → Manually select Personal as trustore destination
- For a signed certificate (root CA already installed): MMC -> Add Certificate snap-in for User ->
Trusted Root Certification Authorities/Certificates-> right click on the desired certificate (ISRG Root X1 for Let's Encrypt for example) -> Copy -> go to thePersonal/Certificatesand then right click -> Paste
it feel like the Machine Trustore is not used. Maybe related to https://github.com/pocoproject/poco/pull/2773 or a missing option
Thank you, @linux-wizard, for looking into this issue and finding a workaround!
I know this is not ideal, and probably the best solution would be to update Poco or replace it with another well-maintained library. Unfortunately, we’ve run into backward compatibility and stability issues in the past, including other projects, so the tradeoff for now has been to keep things as they are and only cherry-pick critical fixes. Currently, our focus is on improving the infrastructure around the project — things like better test coverage, enabling sanitizers, etc. Without that, updating the networking library would likely cause a flood of other issue reports in this repository.
If you have the time and willingness to fix the problem, I’ll be glad to review and merge your PR, as long as it solves this specific issue and doesn’t attempt to update the entire library.
Thank you again, @linux-wizard, for your effort in finding a solution to this problem — I really appreciate it!
@linux-wizard, the release at https://github.com/ClickHouse/clickhouse-odbc/releases/tag/v1.4.4.20251018 includes the new version of Poco. If I import my custom CA certificate into the Trusted Root Certification Authorities store, it seems to work just fine. Can you please check if it work on your side?
Hi @slabko, still facing the error with the ODBC driver. Used your new v.1.4.4.20251018 version.
When I do a curl to my clickhouse db endpoint via PowerShell, it is working.
What can I do?
Hi @slabko , I checked the code and it seems like that the driver is just checking the Current User Trust Store and not the Local Machine Trust Store. Is that correct?
Hi @FakieHeelflip
I checked the code and it seems like that the driver is just checking the Current User Trust Store and not the Local Machine Trust Store. Is that correct?
I do not think this is the case, can you please point out at the place where you think this is happening in the code?
Can you please share your curl command?
Hi @slabko. Thanks for reaching out.
It must be in the Poco in the SecureSocketImpl.cpp:
// now verify CA's HCERTSTORE trustedCerts = _pContext->certificateStore(); for (DWORD i = 0; i < pChainContext->cChain; i++) { std::vector<PCCERT_CONTEXT> certs; for (DWORD k = 0; k < pChainContext->rgpChain[i]->cElement; k++) { certs.push_back(pChainContext->rgpChain[i]->rgpElement[k]->pCertContext); } // verify that the root of the chain can be found in the trusted store PCCERT_CONTEXT pResult = CertFindCertificateInStore(trustedCerts, certs.back()->dwCertEncodingType, 0, CERT_FIND_ISSUER_OF, certs.back(), 0); if (!pResult) { poco_assert_dbg (GetLastError() == CRYPT_E_NOT_FOUND); VerificationErrorArgs args(cert, i, 0, "Certificate Authority not trusted"); SSLManager::instance().ClientVerificationError(this, args); CertFreeCertificateChain(pChainContext); if (!args.getIgnoreError()) throw CertificateValidationException("Failed to verify certificate chain: CA not trusted"); else return; } CertFreeCertificateContext(pResult);
we throw the error.
and in the Context.cpp we build the trusted store from Collection Store and Current User Store:
` _hMemCertStore = CertOpenStore( CERT_STORE_PROV_MEMORY, // The memory provider type 0, // The encoding type is not needed NULL, // Use the default provider 0, // Accept the default dwFlags NULL); // pvPara is not used
if (!_hMemCertStore)
throw SSLException("Failed to create memory certificate store", GetLastError());
_hCollectionCertStore = CertOpenStore(
CERT_STORE_PROV_COLLECTION, // A collection store
0, // Encoding type; not used with a collection store
NULL, // Use the default provider
0, // No flags
NULL); // Not needed
if (!_hCollectionCertStore)
throw SSLException("Failed to create collection store", GetLastError());
if (!CertAddStoreToCollection(_hCollectionCertStore, _hMemCertStore, CERT_PHYSICAL_STORE_ADD_ENABLE_FLAG, 1))
throw SSLException("Failed to add memory certificate store to collection store", GetLastError());
if (_options & OPT_TRUST_ROOTS_WIN_CERT_STORE)
{
// add root certificates
std::wstring rootStore;
Poco::UnicodeConverter::convert(CERT_STORE_ROOT, rootStore);
_hRootCertStore = CertOpenSystemStoreW(0, rootStore.c_str());
if (!_hRootCertStore)
throw SSLException("Failed to open root certificate store", GetLastError());
if (!CertAddStoreToCollection(_hCollectionCertStore, _hRootCertStore, CERT_PHYSICAL_STORE_ADD_ENABLE_FLAG, 1))
throw SSLException("Failed to add root certificate store to collection store", GetLastError());`
And the curl command is:
curl “https://clickhouse-dev.dev.traefik.url.path:443/?user=theuser&password=thepassword&query=SELECT%201"
This returns 1 as a number via PowerShell and it returns via cmd:
curl: (35) schannel: next InitializeSecurityContext failed: CRYPT_E_REVOCATION_OFFLINE (0x80092013) - The revocation function was unable to check revocation because the revocation server was offline.