Authentication issue

[bubbaspaarx]

Describe the bug
Unable to authenticate with from PowerBi desktop to our clickhouse cloud server

We receive a message saying that the authentication has failed and either the password is wrong or the user doesn't exist.

Using the same details on a curl request works absolutely fine so I don't believe the that the password or user details are incorrect.

To Reproduce
Steps to reproduce the behavior:

1. Enter details for server, database, user and password
2. Try to connect

Expected behavior
Expect to connect to our database from PowerBi desktop using the OBDC connector

Screenshots
If applicable, add screenshots to help explain your problem.

Configuration

Environment

• PowerBI version: latest
• power-bi-clickhouse version: v0.1.5-beta
• ClickHouse ODBC driver version: 1.2.1.20220905
• Operating system: Windows

[bubbaspaarx]

FYI to anyone who also got here. This is an issue with '~' tilde's being present in the password. It is also a known issue on the clickhouse side of things.

[anob1223]

Could this be related to reports published in PowerBI Service? We are unable to authenticate under "Manage Connections and Gateways" as whenever we are selecting Authentication Type dropdown, it shows blank.

[bubbaspaarx]

Could this be related to reports published in PowerBI Service? We are unable to authenticate under "Manage Connections and Gateways" as whenever we are selecting Authentication Type dropdown, it shows blank.

Not sure this is linked to our issue as we hadn't been able to get that far but I'll bear it in mind.
There is apparently an option in the Powerbi settings related to credentials being saved for all connections. This also prevented us from changing the password on a connection we had already set up so it may be related to that.

It's definitely worth doing a live chat with Clickhouse support if you're having issues as this is how we found out about the ~/tilde issue

[laeg]

@bubbaspaarx I'm trying to locate the known database issue around using a tilda, do you know where it is. However, I noticed you said you can authenticate with the tilda via curl?

[BentsiLeviav]

@bubbaspaarx I created a new user in my CH instance with the following command:
CREATE USER tildatest IDENTIFIED WITH sha256_password BY 'TildaTest123~';

and it worked both in PowerBI and HTTP.
Could you add more about your usecase? what Identification method did you use when creating the user?

[bubbaspaarx]

@bubbaspaarx I'm trying to locate the known database issue around using a tilda, do you know where it is. However, I noticed you said you can authenticate with the tilda via curl?

This is linked to the ODBC Client and the Clickhouse -> PowerBi connector. When inputting the password for authenticating the connection, it won't work with a ~ and possibly other special characters. we did this a few months ago so it may have changed.