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.

[BentsiLeviav]

@bubbaspaarx could you please verify if it's still happening?

[bubbaspaarx]

I can't verify right now unfortunately as I wasn't the person in the organisation who ran into the issue. Powerbi desktop is also only on Windows OS and I'm not using that so I can't even check.

I'll see if there's anyone else who can try though.

The other complication is that the password given for the service was automatically generated, so it's not guaranteed the password would contain a ~ either.

[BentsiLeviav]

@bubbaspaarx got it.

Since I managed to connect to CH over PowerBI using the user and password mentioned (with Tilda) - I close this issue. Feel free to reopen it if you encounter the issue again.