ODBC authentication failed error when using the Power BI ClickHouse connector

ODBC authentication failed error when using the Power BI ClickHouse connector

Question

When trying to connect from PowerBI to ClickHouse using the connector, you receive a authentication error.

This error usually looks like the following:

We encountered an error while trying to connect.
Details: "ODBC: ERROR [HY000] HTTP status code: 403
Received error:
Code: 516. DB::Exception: default: Authentication failed: password is incorrect, or there is no user with such name.
If you have installed ClickHouse and forgot password you can reset it in the configuration file.
The password for default user is typically located at /etc/clickhouse-server/users.d/default-password.xml and deleting this file will reset the password.
See also /etc/clickhouse-server/users.ml on the server where
ClickHouse is installed.

Answer

Update the ClickHouse ODBC Driver to version 1.4.1.

If updating is not possible, check whether the password contains a tilde (~), as this can cause issues in versions prior to 1.4.1.

We recommend using a dedicated user for the connection and setting the password manually. If you’re using ClickHouse Cloud and require admin-level access equivalent of the default user, then create a new user and assign the default_role to it.

For more information:
https://clickhouse.com/docs/operations/access-rights#user-account-management https://clickhouse.com/docs/cloud/security/cloud-access-management#database-roles