Sqlalchemy shows "Code 516 Authentication failed" when trying to connect to clickhouse db

Question

I have connected to a clickhouse db with dbeaver and installed sqlalchemy v1.3.13 and clickhouse-sqlalchemy 0.1.3 for python 3.7.

When I tried to connect with

from sqlalchemy import create_engine 
engine_clickhouse = create_engine('clickhouse://use:pass@host:port/db')
engine_clickhouse.raw_connection()

I got

Exception: Code: 516, e.displayText() = DB::Exception: default: Authentication failed: password is incorrect or there is no user with such name (version 20.3.4.10 (official build))

Does anybody know why? I didn't find a similar issue.

try to check the file /etc/clickhouse-server/users.xml on the server where ClickHouse installed - this file contains the description of all available credentials — vladimir
Hi vladimir thanks! I'm pretty sure my credentials is correctly, I can use it to connect to this db with dbeaver — Steve.Gao
Hi, did you get this solved? Same problem here, I could log with the clickhouse-client command with password flag, but can not use it with python — mingchau
@mingchau No I didn’t. Problem could be sqlalchemy or others, it works with clickhouse_driver try this package instead — Steve.Gao

vladimir vladimir · Accepted Answer · 2020-05-11T05:20:25

This issue is well-known - look at sqlalchemy-clickhouse Issue-45 or sqlalchemy-clickhouse Issue-49.

To fix it need to explicitly downgrade the package infi.clickhouse_orm up to version 1.0.4:

requirements.txt

...
infi.clickhouse_orm==1.0.4

It allows using the _build_params-function with the behavior expected by sqlalchemy-clickhouse (following versions of infi.clickhouse_orm aren't passed the password that leads to 'Authentication failed..'-error).

Or alternatively can be used the fork where fixed this error, for example adaiboy fork.

I would avoid using the official sqlalchemy-clickhouse because:

fixes not committed to master ("Latest commit was on 23 Jan 2019" !!)
new featured not adding
there are some issues with pandas etc

clickhouse-driver is the greatest alternative for sqlalchemy-clickhouse.

Sqlalchemy shows "Code 516 Authentication failed" when trying to connect to clickhouse db

2 Answers