ClickHouse DB::Exception: Authentication failed

The "DB::Exception: Authentication failed" error in ClickHouse occurs when a user's login attempt is rejected by the server. The AUTHENTICATION_FAILED error code covers a broad range of authentication failures, including incorrect passwords, expired tokens, failed LDAP binds, and other credential validation issues.

Impact

A failed authentication attempt completely blocks the user from establishing a session with ClickHouse. No queries can be executed, and any application or service relying on the affected credentials will be unable to access data. If the failure affects a service account used by multiple applications, the blast radius can be significant.

Common Causes

1. Wrong username or password supplied in the connection request
2. An expired or revoked authentication token (for token-based auth mechanisms)
3. LDAP server unreachable or returning a bind failure for the provided credentials
4. Kerberos ticket expired or keytab misconfigured
5. Password was recently rotated but the client still uses the old credentials
6. SHA256 or double-SHA1 hash mismatch due to encoding issues in the configuration
7. SSL certificate-based authentication failing due to an expired or untrusted certificate
8. HTTP basic auth credentials not properly encoded (e.g., special characters in the password)

Troubleshooting and Resolution Steps

1. Verify credentials manually:

   • Try connecting with clickhouse-client using the suspect credentials:

     clickhouse-client --user my_user --password my_password
     

   • This isolates whether the issue is with the credentials themselves or with how the application passes them.

2. Check the server log for details:

   • ClickHouse logs often include more specific information about why authentication failed:

     tail -100 /var/log/clickhouse-server/clickhouse-server.log | grep -i auth
     

3. Verify the user exists and its authentication method:

   SHOW CREATE USER my_user;
   

   Confirm the authentication type matches what the client is providing (password, LDAP, Kerberos, etc.).

4. For LDAP-based authentication:

   • Confirm the LDAP server is reachable from the ClickHouse host:

     ldapsearch -H ldap://ldap.example.com -D "cn=my_user,dc=example,dc=com" -w password
     

   • Check the LDAP server configuration in config.xml under <ldap_servers>.
   • Verify the <server> reference in the user's <ldap> block matches a defined LDAP server.

5. For token or certificate-based authentication:

   • Verify the token has not expired and is properly formatted.
   • For SSL certificate auth, ensure the certificate is valid, trusted by the ClickHouse CA configuration, and not expired.

6. Reset the password if needed:

   ALTER USER my_user IDENTIFIED BY 'new_secure_password';
   

7. Check for encoding issues:

   • If the password contains special characters, ensure the client properly encodes them, especially in HTTP URLs and connection strings.

Best Practices

• Use secrets management tools to handle credential rotation automatically, reducing the chance of stale passwords.
• Monitor authentication failures in ClickHouse logs or the system.session_log table to detect issues early.
• When using LDAP, configure connection pooling and timeouts to handle LDAP server unavailability gracefully.
• Test authentication changes in a staging environment before applying them to production.
• Maintain fallback local accounts for emergency access in case external authentication providers become unavailable.
• Enable system.session_log to track successful and failed authentication attempts for auditing.

Frequently Asked Questions

Q: How can I tell if the failure is due to wrong credentials versus an LDAP issue?
A: Check the ClickHouse server log. LDAP-related failures typically include messages about LDAP bind errors or connection timeouts, while credential mismatches are logged as straightforward authentication failures.

Q: Can I see failed login attempts in ClickHouse?
A: Yes, if session_log is enabled, you can query system.session_log for entries with type = 'LoginFailure'. The server log also records failed attempts.

Q: Does AUTHENTICATION_FAILED differ from the WRONG_PASSWORD error?
A: Yes. WRONG_PASSWORD is specific to password mismatches, while AUTHENTICATION_FAILED is a broader error that covers any authentication mechanism failure, including LDAP, Kerberos, certificates, and tokens.

Q: What should I do if LDAP authentication suddenly stops working for all users?
A: This likely indicates an LDAP server connectivity issue. Verify the LDAP server is reachable, check for network or firewall changes, and review the LDAP server's own logs. Having a local admin account as a fallback is critical in these situations.