ODBC driver doesn't work with HTTPS proxy

[alexey-milovidov]

The quote from internal customer:

We are using https proxy between ODBC-driver and CH (for load balancing, caching and special SQL fix for Tableau, etc...). Everything was fine in 1.0.0.20190611 but it was broken in 1.1.2.20191216: SSL Exception: error:14007086:SSL routines:CONNECT_CR_CERT:certificate verify failed

The driver is from: https://github.com/ClickHouse/clickhouse-odbc/releases/tag/v1.1.2.20191216

odbc.ini:

[cubes_clickhouse_prd_new]
Driver = /opt/clickhouse/odbc_1.1.2.20191216/driver/libclickhouseodbc.so
url = https://...:443
database = cubes
port = 443
proto = https

I am checking it with the following command:

echo "show tables;" | isql cubes_clickhouse_prd_new cubes -v

I have tried to add calocation = .../MyCompanyInternalRootCA.crt, but in that case, the driver is sending broken query.

[alexey-milovidov]

If it requires internal CA to work, then the issue is the following:

• previous version of the driver did not validate the certificate;
• new version started to validate it but it doesn't work if you specify custom root CA.

[traceon]

.../MyCompanyInternalRootCA.crt

This is an absolute path, right?

the driver is sending broken query

What exactly does "broken" mean here?

[traceon]

Meanwhile, I verified that values for PrivateKeyFile, CertificateFile, CALocation DSN config entries (names are case insensitive) passed correctly to the only place they are used: Poco::Net::Context() in https://github.com/ClickHouse/clickhouse-odbc/blob/2032dad9646670e28daf05529a77aff96ae5d740/driver/connection.cpp#L24-L48 Does the reporter have a history of successful use of that MyCompanyInternalRootCA.crt anywhere else? E.g., with older versions of the ClickHouse ODBC driver?

[alexey-milovidov]

According to the info from customer, it is their own misconfiguration issue.

[nvm1]

@alexey-milovidov , hi there!

Could you please specify what exactly was misconfigured?

We actually see the same issue: we have a clickhouse cluster behind a nginx proxy and DSNs that work on the 20190611 version of the driver. If we update to 20191108+ versions, we experience certificate issues (host certificate verification failed), even if I set CALocation path to MyCompanyInternalRootCa.crt.

If I set sslmode = allow, then connection works just fine, but my application (based on python + pyodbc) claims that it can't decode unicode...

btw, our environment is Windows based.

[alexey-milovidov]

Sorry, I don't remember. Will try to find the info.

[alexey-milovidov]

@traceon

A quote from a stranger:

Привет! Мы не обновляем у себя драйвер именно по этой причине. Может попробуем разобраться вместе и пофиксить проблему?

Последняя рабочая версия - https://github.com/ClickHouse/clickhouse-odbc/releases/tag/v1.1.1.20191108 (проверял на маке)

[traceon]

@alexey-milovidov if the SSLMode is set to strict, which is the default, I guess, a CALocation should be specified in macOS at least:

CALocation    = /usr/local/etc/[email protected]/cert.pem

(Certs from brew version of openssl.)