ClickHouse DB::Exception: MongoDB cannot authenticate

The "DB::Exception: MongoDB cannot authenticate" error is raised when ClickHouse attempts to connect to a MongoDB instance but the authentication handshake fails. The MONGODB_CANNOT_AUTHENTICATE error code indicates that ClickHouse reached the MongoDB server successfully but could not prove its identity, typically due to wrong credentials or an unsupported authentication mechanism.

Impact

This error prevents ClickHouse from reading data through the MongoDB table engine or the mongodb() table function. Any query that references a MongoDB-backed table will fail immediately at connection time. Dictionaries sourced from MongoDB will also fail to load or refresh.

Common Causes

1. Incorrect username or password in the ClickHouse table definition or dictionary configuration.
2. The MongoDB user does not exist in the specified authentication database.
3. Authentication mechanism mismatch — ClickHouse is using SCRAM-SHA-256 but the MongoDB user was created with SCRAM-SHA-1, or vice versa.
4. The MongoDB user does not have the read role on the target database.
5. TLS is required for authentication but ClickHouse is connecting without TLS.
6. The authentication database is wrong — MongoDB authenticates against a specific database (often admin), and ClickHouse must specify the correct one.

Troubleshooting and Resolution Steps

1. Verify the credentials by connecting to MongoDB directly with the same username, password, and authentication database:

   mongosh "mongodb://username:password@mongodb-host:27017/target_db?authSource=admin"
   

2. Check the ClickHouse table or dictionary definition for typos in credentials:

   CREATE TABLE mongo_table
   (
       _id String,
       name String
   )
   ENGINE = MongoDB('mongodb-host:27017', 'mydb', 'mycollection', 'myuser', 'mypassword');
   

3. Confirm the authentication database. By default MongoDB authenticates against the admin database. If your user is in a different database, specify it with the options parameter or connection string:

   CREATE TABLE mongo_table
   (
       _id String,
       name String
   )
   ENGINE = MongoDB('mongodb-host:27017', 'mydb', 'mycollection', 'myuser', 'mypassword', 'authSource=admin');
   

4. Verify the user exists in MongoDB and check their authentication mechanism:

   use admin
   db.getUser("myuser")
   

5. If the MongoDB server requires TLS, make sure the ClickHouse connection includes TLS options:

   ENGINE = MongoDB('mongodb-host:27017', 'mydb', 'mycollection', 'myuser', 'mypassword', 'tls=true&authSource=admin');
   

6. Check the ClickHouse server log for more details about the authentication failure:

   grep -i "MONGODB_CANNOT_AUTHENTICATE\|MongoDB\|mongo" /var/log/clickhouse-server/clickhouse-server.log | tail -20
   

7. If using a MongoDB replica set, ensure the connection string includes the replica set name and all members so ClickHouse connects to the correct instance.

Best Practices

• Always specify the authSource parameter explicitly to avoid confusion about which database holds the user credentials.
• Use a dedicated MongoDB user for ClickHouse with read-only access to the specific databases and collections needed.
• Test MongoDB connectivity and authentication independently before configuring the ClickHouse table engine.
• Store MongoDB credentials in the ClickHouse named_collections feature for better secrets management.
• Enable TLS for all MongoDB connections, especially in production environments.

Frequently Asked Questions

Q: Which MongoDB authentication mechanisms does ClickHouse support?
A: ClickHouse supports SCRAM-SHA-1 and SCRAM-SHA-256, which are the default mechanisms in MongoDB 4.0 and later. Older mechanisms like MONGODB-CR are not supported.

Q: Why does authentication fail even though I can connect with the same credentials using mongosh?
A: The most common reason is a different authSource. The mongosh tool may default to authenticating against the admin database, while ClickHouse may be trying the target database name. Specify authSource=admin explicitly in the ClickHouse configuration.

Q: Can ClickHouse authenticate to MongoDB Atlas?
A: Yes, but you must use a connection string with TLS enabled and the correct authSource. MongoDB Atlas also requires that the ClickHouse server's IP address be added to the Atlas IP access list.

Q: How do I update MongoDB credentials in ClickHouse without dropping the table?
A: You need to drop and recreate the MongoDB-engine table with the new credentials. Alternatively, if you are using a named_collection, you can update the credentials in the named collection configuration and reload the ClickHouse config.