NodeJS mysql/xdevapi 'Access denied' using SSL and X Protocol

Question

I am trying to connect to my mysql server using the X Protocol and SSL.
To connect to a database I use the MySQL x devapi for NodeJs(mysql/xdevapi). Both, the MySQL server as well as my nodejs application are running on the same machine. However everytime I try to establish a connection I get the following error:

Error: Access denied for user 'accountservice'@'localhost' (using password: YES)
at AuthenticationHandler.BaseHandler.<computed> (F:\Repositorys\Project\Application\node_modules\@mysql\xdevapi\lib\Protocol\ResponseHandlers\BaseHandler.js:113:19)
at Array.entry (F:\Repositorys\Project\Application\node_modules\@mysql\xdevapi\lib\Protocol\ResponseHandlers\BaseHandler.js:90:29)
at WorkQueue.process (F:\Repositorys\Project\Application\node_modules\@mysql\xdevapi\lib\WorkQueue.js:75:19)
at Client.handleServerMessage (F:\Repositorys\Project\Application\node_modules\@mysql\xdevapi\lib\Protocol\Client.js:201:21)
at Client.handleNetworkFragment (F:\Repositorys\Project\Application\node_modules\@mysql\xdevapi\lib\Protocol\Client.js:245:14)
at Socket.<anonymous> (F:\Repositorys\Project\Application\node_modules\@mysql\xdevapi\lib\Protocol\Client.js:89:36)
at Socket.emit (events.js:315:20)
at addChunk (_stream_readable.js:296:12)
at readableAddChunk (_stream_readable.js:272:9)
at Socket.Readable.push (_stream_readable.js:213:10) {
 info: {
  severity: 0,
  code: 1045,
  sqlState: 'HY000',
  msg: "Access denied for user 'username'@'localhost' (using password: YES)"
 }
}

At first I thought maybe my password is wrong or my certificates are not valid but using
mysql -u username -p --ssl-ca=/etc/certs/ca.pem --ssl-cert=../res/certs/mysql/client-cert.pem --ssl-key=../res/certs/mysql/client-key.pem
to login I get no error.

In addition to that, I also granted my user all privileges on all databases and tables to make sure missing privileges are not the reason for the failed connection.
The only possibility I can think of is that there is an error verifying the server's certificate, even though openssl verify -CAfile ca.pem server-cert.pem returns server-cert.pem: OK.

To connect to the database I use the following code written in TypeScript

const conf = {
    host: 'localhost',//config.getHost(),
    port: 33060,//config.getPort(),
    user: 'username',//config.getUsername(),
    password: 'password',//config.getPassword(),
    schema: 'database',//config.getDatabase(),

    auth: 'SHA256_MEMORY', //removing this results in "Invalid authentication method PLAIN"

    ssl: {
        key: config.getCertKey(),
        cert: config.getCert(),
        ca: config.getCa()
    }
}

this.client = mysql.getClient(conf);

and

connect(next) {
    console.log('Connecting to database ' + this.config.getDatabase() + '(' + this.config.getHost() + ':' + this.config.getPort() + ')...');
    this.client.getSession().then(session => {
        this.session = session;
        console.log('Connection to database ' + this.config.getDatabase() + '(' + this.config.getHost() + ':' + this.config.getPort() + ') established.');
        listen(this, next);
    }).catch((err) => {
        console.log('Failed to establish connection to database ' + this.config.getDatabase() + '(' + this.config.getHost() + ':' + this.config.getPort() + ').');
        console.log(err);
    });
}

Maybe I missed a very important step, but at the moment I have no idea what causes this problem despite having read multiple answers on StackOverflow and other pages.

score 1 · Accepted Answer · answered May 07 '20 at 14:38

Public-key authentication is not a first-class API concern but nothing prevents you from providing the keys, in this case, under a sslOptions property (not the ssl property) in the configuration object. Those options will simply be merged when calling tls.connect(). By contrast, there's API support for certificate authority validation, which in that case only requires you to define the path to the CA file (as described here).

Something like the following should do the trick:

const conf = {
  //...
  sslOptions: {
    // read contents of key.pem and cert.pem
    key: fs.readFileSync('/path/to/key.pem')
    cert: fs.readFileSync('/path/to/cert.pem'),
    // path (only) to ca.pem
    ca: '/path/to/ca.pem'
  }
}

If you are using version 8.0.20 already, this flavour has been deprecated and you should instead use:

const conf = {
  //...
  tls: {
    key: fs.readFileSync('/path/to/key.pem')
    cert: fs.readFileSync('/path/to/cert.pem'),
    ca: '/path/to/ca.pem'
  }
}

The reason you get the Invalid authentication method PLAIN error is because the client is naive and assumes that since ssl is not true, then it means TLS should be disabled, which does not work with PLAIN, the default authentication mechanism.

In any case, this should go away once you start using sslOptions instead.

Disclaimer: I'm the lead developer of the MySQL X DevAPI Connector for Node.js

Thanks for your answer it now finally works. I also found the site you linked above and tried to use "tls", but I probably misspelled "tls", wrote "tsl" or something else and unfortunately never tried it again..... — Luca, May 07 '20 at 15:48

NodeJS mysql/xdevapi 'Access denied' using SSL and X Protocol

1 Answers1