Cannot connect to MongoDB on localhost using JDBC

c51a1608bd6dd1713da7ee458e75803 · July 9, 2022, 2:14pm

I’m trying to use a simple Java program to connect to a MongoDB hosted on my Mac accessible by Compass using the authentication user and password I set up.

However, in the JDBC code, I get the error 18 (authentication failure). I have no idea why. Same connection string as in Compass.

My two dependencies in pom.xml are:

  <dependencies>
        <dependency>
            <groupId>org.mongodb</groupId>
            <artifactId>mongodb-driver-sync</artifactId>
            <version>4.6.0</version>
        </dependency>

        <dependency>
            <groupId>org.mongodb</groupId>
            <artifactId>mongodb-jdbc</artifactId>
            <version>2.0.0</version>
        </dependency>
    </dependencies>

The main method is utterly simple but fails

  try
            {
                Class.forName("com.mongodb.jdbc.MongoDriver");
                
                Connection connection = DriverManager.getConnection(
                        "mongodb://corpus:passpass@localhost:27017/?authMechanism=DEFAULT&authSource=corpus_test");

.
.
.

I’ve tried many variations on the connection string. None have worked.

Here is the error I cannot get past: “Command failed with error 18 (AuthenticationFailed): ‘Authentication failed.’ on server localhost:27017. The full response is {“ok”: 0.0, “errmsg”: “Authentication failed.”, “code”: 18, “codeName”: “AuthenticationFailed”}”

Would appreciate any suggestions on how to tackle this issue.

Thanks

steevej · July 10, 2022, 12:22pm

Most likely your authSource is wrong.

Try to simply remove it and the default admin will be used.

It it stiĺl does not work share the user data.

c51a1608bd6dd1713da7ee458e75803 · July 10, 2022, 1:43pm

I tried to remove that auth string, but it doesn’t make any difference.

Is JDBC a good way to work with MongoDB? I’m really having trouble using the standard Mongo API and even getting a basic JDBC example to work. Also, can’t find any books that contain JDBC for Mongo code. Stuff online is either out of date or just snippets with no complete examples. Difficult to get started…

Here’s my revised connection code:

   Class.forName("com.mongodb.jdbc.MongoDriver");

                String url = "jdbc:mongodb://localhost:27017";
                Properties properties = new Properties();
                properties.put("database", "corpus_test");
                properties.put("user", "corpus");
                properties.put("password", "the_password");

                Connection connection = DriverManager.getConnection(url, properties);

Caused by: com.mongodb.MongoCommandException: Command failed with error 18 (AuthenticationFailed): 'Authentication failed.' on server localhost:27017. The full response is {"ok": 0.0, "errmsg": "Authentication failed.", "code": 18, "codeName": "AuthenticationFailed"}
	at com.mongodb.internal.connection.ProtocolHelper.getCommandFailureException(ProtocolHelper.java:175)
	at com.mongodb.internal.connection.InternalStreamConnection.receiveCommandMessageResponse(InternalStreamConnection.java:302)
	at com.mongodb.internal.connection.InternalStreamConnection.sendAndReceive(InternalStreamConnection.java:258)
	at com.mongodb.internal.connection.CommandHelper.sendAndReceive(CommandHelper.java:83)
	at com.mongodb.internal.connection.CommandHelper.executeCommand(CommandHelper.java:33)
	at com.mongodb.internal.connection.SaslAuthenticator.sendSaslStart(SaslAuthenticator.java:158)
	at com.mongodb.internal.connection.SaslAuthenticator.access$100(SaslAuthenticator.java:40)
	at com.mongodb.internal.connection.SaslAuthenticator$1.run(SaslAuthenticator.java:54)
	... 30 more

steevej · July 10, 2022, 2:00pm

I do not use JDBC as I avoid abstraction layers.

But Authentication Error means wrong user name, wrong password or wrong authentication database.

c51a1608bd6dd1713da7ee458e75803 · July 10, 2022, 2:01pm

The problem could be that there is no user created in the corpus_test database.

In the admin database, I see this:

Does this help?

Thanks,

c51a1608bd6dd1713da7ee458e75803 · July 10, 2022, 2:06pm

See my posting (sorry, was posting when you wrote your reply) I added. I can connect, using Compass using these credentials.

When I click “Connect”, it connects with no problems.

(The actual password is “passpass” for testing.)

Does this help?

Thanks,

steevej · July 10, 2022, 2:32pm

The only thing I can say is that your JDBC abstraction layer is your obstruction layer because it does not generates the appropriate URI using the properties you set.

I would first create my user in the admin database rather than corpus_test. Hopefully JDBC can use the default authentication database correctly. I suspect that the property database is not to specify the authSource. May be there is another one for that purpose.

Hopefully, the driver uses user and password correctly, but you could put them yourself in the URL and not setting the properties.

c51a1608bd6dd1713da7ee458e75803 · July 10, 2022, 2:46pm

If you check the screenshot I posted above, you see that I did, in fact, create the user in the corpus_test database. Other than that, I have no idea what to try. I’ve used every variation on the JDBC string I can think of.

If I try something simple like:

    Connection connection = DriverManager.getConnection(
                        "jdbc:mongodb://localhost:27017/corpus_test");

I get:

java.sql.SQLException: There are issues with your connection settings : Mandatory property ‘database’ is missing.

But, adding the “database” as a property to the other JDBC connection attempt didn’t work either.

Thanks,

c51a1608bd6dd1713da7ee458e75803 · July 10, 2022, 2:47pm

→ Oops, sorry I meant the user was created in the admin database.

See screenshot above.

steevej · July 10, 2022, 2:57pm

Apparently not, the evidences say otherwise, as the _id is corpus_test.corpus and you can conncect in Compass with authSource corpus_test.

When publishing code or documents please do so in text rather than screenshot. It helps replying as we can cut-n-paste rather than typing.

c51a1608bd6dd1713da7ee458e75803 · July 10, 2022, 3:17pm

OK, you’ve lost me. Here’s the text version of the screenshot you requested. I tried to create the user in the corpus_test database, but it shows up here:

use admin
'switched to db admin'
db.system.users.find()
{ _id: 'corpus_test.corpus',
  userId: UUID("3732b335-8734-4c15-b2f8-db2b2f0abb41"),
  user: 'corpus',
  **db: 'corpus_test',**
  credentials:
   { 'SCRAM-SHA-1':
      { iterationCount: 10000,
        salt: '9yIjqaRMSG3umZbPXkvCEw==',
        storedKey: 'meLH8bvOfPXA1LjcjCEX4Lt/Ecw=',
        serverKey: 'k9dBvlJYm24BzAGX8s4Ugk30fwM=' },
     'SCRAM-SHA-256':
      { iterationCount: 15000,
        salt: 'vWw3BFDN88idZqGnnZYNDLFq4Y03Y1GUe4hU+A==',
        storedKey: 'b1HixboG/j34sPbvl5+mfFHsQofCHM1vH/XfQZ1XXrM=',
        serverKey: 'J1dRZvofxEzX81Y1SlfI8eQQcjKrniDvSwiQ2uUfrjw=' } },
  roles: [ { role: 'readWrite', db: 'corpus_test' } ] }

If I go to the corpus_test database and try to create the user there, I get an incomprehensible message:

‘switched to db corpus_test’
db.createUser(
{
user: “corpus”,
pwd: “passpass”,
roles: [ { role: “readWrite”, db: “corpus_test” }]

}

Error: clone(t={}){const r=t.loc||{};return e({loc:new Position("line"in r?r.line:this.loc.line,"column"in r?r.column:……)} could not be cloned.
at Object.serialize (v8.js:256:7)
at u (/Applications/MongoDB Compass.app/Contents/Resources/app.asar.unpacked/node_modules/@mongosh/node-runtime-worker-thread/dist/worker-runtime.js:1917:593159)
at postMessage (/Applications/MongoDB Compass.app/Contents/Resources/app.asar.unpacked/node_modules/@mongosh/node-runtime-worker-thread/dist/worker-runtime.js:1917:593767)
at i (/Applications/MongoDB Compass.app/Contents/Resources/app.asar.unpacked/node_modules/@mongosh/node-runtime-worker-thread/dist/worker-runtime.js:1917:598575)

kevinadi · July 11, 2022, 7:19am

Hi @c51a1608bd6dd1713da7ee458e75803

First of all, I’m not an expert in Java nor JDBC but I’ll try to answer your question. Is this the JDBC driver you’re using? GitHub - mongodb/mongo-jdbc-driver: JDBC Driver for MongoDB Atlas SQL interface

If yes, I think that driver is meant for connecting to a specific setup in Atlas and not to locally deployed instances, as mentioned in the Readme file there:

The MongoDB Atlas SQL JDBC Driver provides SQL connectivity to MongoDB Atlas for client applications developed in Java.
See the Atlas SQL Documentation for more information.

This is the diagram taken from the linked page https://www.mongodb.com/docs/atlas/data-federation/query/query-with-sql/

So in the diagram, there’s Atlas → Atlas SQL Interface → JDBC driver

In my limited knowledge, JDBC is a method for Java to connect to SQL/tabular databases. MongoDB is definitely not an SQL database. If you’re looking to connect to MongoDB from Java, I think you’re looking for the MongoBD Java driver, along with the free MongoDB University course M220J MongoDB for Java Developers

Best regards
Kevin

steevej · July 11, 2022, 1:49pm

You did indeed created it in the corpus_test. This we know for sure as you could connect with Compass using corpus_test as authenticatin source. It is possible thzt JDBC driver cannot use an authenticaton source other than admin. So you should not

because you already created there and it does not work with JDBC. You have to create it in the admin database.

c51a1608bd6dd1713da7ee458e75803 · July 11, 2022, 2:11pm

This is very confusing. I did create the login in the admin database, but that login (user) refers to the corpus_test database. Trying to create the login in the corpus_test database throws that error I posted above:

Error: clone(t={}){const r=t.loc||{};return e({loc:new Position("line"in r?r.line:this.loc.line,"column"in r?r.column:……)} could not be cloned.
at Object.serialize (v8.js:256:7)
at u (/Applications/MongoDB Compass.app/Contents/Resources/app.asar.unpacked/node_modules/@mongosh/node-runtime-worker-thread/dist/worker-runtime.js:1917:593159)
at postMessage (/Applications/MongoDB Compass.app/Contents/Resources/app.asar.unpacked/node_modules/@mongosh/node-runtime-worker-thread/dist/worker-runtime.js:1917:593767)
at i (/Applications/MongoDB Compass.app/Contents/Resources/app.asar.unpacked/node_modules/@mongosh/node-runtime-worker-thread/dist/worker-runtime.js:1917:598575)

steevej · July 11, 2022, 2:30pm

What is confusing is that I wrote many times that the user has NOT been created in the admin database:

I also wrote:

but you try to create it again in corpus_test.

c51a1608bd6dd1713da7ee458e75803 · July 11, 2022, 2:46pm

Doing a “db.system.users.find()” in the corpus_test database shows no users. Nothing displays.

If I try to create a user there, I get the errors I posted above.

If I go to the admin database, db.system.users.find() shows the corpus_test user.

If that is not correct, what database and what create user command should I use?

Thanks,

Yilmaz_Durmaz · July 11, 2022, 3:48pm

check if using this helps; "mongodb://corpus:passpass@localhost:27000/corpus_test?authMechanism=DEFAULT&authSource=corpus_test"
- this should connect you to corpus_test database. yours currently connects to some other default database like test
check if using this helps; "mongodb://corpus:passpass@localhost:27000/corpus_test?authMechanism=DEFAULT&authSource=admin"
- same as above, corpus auth source is admin
try giving username/password outside the URL as described in the driver’s github page.

if these two does not work,

check your config file for security.authorization: "enabled"/"disabled"
try adding an admin to your admin database
try removing corpus from both admin and corpus_test … db.dropUser("corpus")
- and add the user again and try to connecting.

c51a1608bd6dd1713da7ee458e75803 · July 11, 2022, 4:01pm

I’m able to log in with no problems in Compass using authentication. Thanks.

Yilmaz_Durmaz · July 11, 2022, 5:00pm

my bad … forgot the mongsh part while copy-pasting. remove it and use the rest of the string as your URL. I will edit my above post. the point is to have /corpus_test after the host name.

the rest of those I suggested are because of the error you get for trying to add user to corpus_test db.

Yilmaz_Durmaz · July 11, 2022, 8:13pm

TL;DR : you need to hard check your URL or give us which driver and version you use, where you downloaded it from, how you use it. because I tried this and have absolutely no problem connecting if credentials has no typo.

I have this working file I extracted and adapted from one of test files in the source of wise-coders/mongodb-jdbc-driver: MongoDB JDBC Driver | DbSchema MongoDB Designer (github.com)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class SimpleTest {
    private static Connection con;
    private static final String urlWithAuth = "jdbc:mongodb://corpus:passpass@localhost:27017/corpus_test?authSource=corpus_test";
    public static void main(String args[]) throws ClassNotFoundException, SQLException {
        Class.forName("com.wisecoders.dbschema.mongodb.JdbcDriver");
        Connection connection = DriverManager.getConnection( urlWithAuth, null, null);
        Statement stmt=connection.createStatement();
        //stmt.execute("use corpus_test;"); // use this or embed after hostname "localhost:27017/corpus_test"
        System.out.println( "\n" );
        stmt.executeQuery("corpus_test.listCollectionNames()");
        stmt.close();
    }
}

save this code into SimpleTest.java
download driver from http://www.dbschema.com/jdbc-drivers/MongoDbJdbcDriver.zip and open it into MongoDbJdbcDriver folder with the file
compile with javac SimpleTest.java
run with java -cp MongoDbJdbcDriver/mongojdbc4.1.jar;. SimpleTest

after testing many wrong credentials, as soon as I have it correct, there was only 1 error (of type 13) for trying to use admin without permission. and it was just as easy as adding corpus_test after host:port portion, or use stmt.execute("use corpus_test;"); before any more queries.

I have also tried DbSchema and got auth error a few times, but it fixed itself somehow after a few connection attempts. maybe the driver it downloaded was not yet fully downloaded. I don’t know. or the connection pooling failing due to my low connection limit of 10. but at the end, there was not a single leftover to get the error you experienced.

so please this time be more clear and explaining. Examine your URL carefuly and if it still won’t solve then give use more. Which driver and which version is it. Where you get it and how you use it. how you import driver and how you compile your source.