Connecting to mysql using ssh tunnel does not work

Question

I am trying to connect to a remote server via ssh tunnel and then connect to a mysql.

I first tried on mysql workbench and it works fine. The "fake" information looks like this: With the same information I tried to connect to mysql with nodeJS but I have no idea how to do it. Please help. This is what I have tried so far.

var mysql = require('mysql');
var tunnel = require('tunnel-ssh');

var config = {
  username:'hoffnung8493',
  Password:'password123',
  host:'hoffnung.com',// not sure if its right
  port:22,// not sure if its right
  dstHost:'hoffnung.com',// not sure if its right
  dstPort:3306, // not sure if its right
  localHost:'127.0.0.1',// not sure if its right
  localPort: 27000 // not sure if its right
};

tunnel(config, function(error, server){
  var db = mysql.createConnection({
    host: '127.0.0.1',// not sure if its right
    user: 'stock_id',
    password: 'stock_password',
    database: 'database_name',
    charset  : 'utf8'
  });
db.connect();
db.query(`show tables`, function(error, result){
  console.log(error);
  console.log(1, result);
  })
})

module.exports = db; //probably wrong

Then from main.js I want to send query like this:

db = require('./lib/db.js');

db.query('show tables', function(error, result){
    console.log(result)
});

On my console I get the following error message:

0|main   | Error: All configured authentication methods failed
0|main   |     at tryNextAuth (/home/cabox/workspace/node_modules/tunnel-ssh/node_modules/ssh2/lib/client.js:376:17)
0|main   |     at SSH2Stream.onUSERAUTH_FAILURE (/home/cabox/workspace/node_modules/tunnel-ssh/node_modules/ssh2/lib/client.js:587:5)
0|main   |     at emitTwo (events.js:125:13)
0|main   |     at SSH2Stream.emit (events.js:213:7)
0|main   |     at parsePacket (/home/cabox/workspace/node_modules/ssh2-streams/lib/ssh.js:3929:10)
0|main   |     at SSH2Stream._transform (/home/cabox/workspace/node_modules/ssh2-streams/lib/ssh.js:669:13)
0|main   |     at SSH2Stream.Transform._read (_stream_transform.js:186:10)
0|main   |     at SSH2Stream._read (/home/cabox/workspace/node_modules/ssh2-streams/lib/ssh.js:251:15)
0|main   |     at SSH2Stream.Transform._write (_stream_transform.js:174:12)
0|main   |     at doWrite (_stream_writable.js:385:12)

After I fixed from "Password" ->"password", I added db.query after db.connect to see if it works and I got the following error

0|main   | { Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'stock'@'firstpg-server197-22' (using password: YES)
0|main   |     at Handshake.Sequence._packetToError (/home/cabox/workspace/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
0|main   |     at Handshake.ErrorPacket (/home/cabox/workspace/node_modules/mysql/lib/protocol/sequences/Handshake.js:124:18)
0|main   |     at Protocol._parsePacket (/home/cabox/workspace/node_modules/mysql/lib/protocol/Protocol.js:278:23)
0|main   |     at Parser.write (/home/cabox/workspace/node_modules/mysql/lib/protocol/Parser.js:76:12)
0|main   |     at Protocol.write (/home/cabox/workspace/node_modules/mysql/lib/protocol/Protocol.js:38:16)
0|main   |     at Socket.<anonymous> (/home/cabox/workspace/node_modules/mysql/lib/Connection.js:91:28)
0|main   |     at Socket.<anonymous> (/home/cabox/workspace/node_modules/mysql/lib/Connection.js:502:10)
0|main   |     at emitOne (events.js:115:13)
0|main   |     at Socket.emit (events.js:210:7)
0|main   |     at addChunk (_stream_readable.js:266:12)
0|main   |     --------------------
0|main   |     at Protocol._enqueue (/home/cabox/workspace/node_modules/mysql/lib/protocol/Protocol.js:144:48)
0|main   |     at Protocol.handshake (/home/cabox/workspace/node_modules/mysql/lib/protocol/Protocol.js:51:23)
0|main   |     at Connection.connect (/home/cabox/workspace/node_modules/mysql/lib/Connection.js:118:18)
0|main   |     at /home/cabox/workspace/inven_man/lib/db2.js:24:4
0|main   |     at Server.<anonymous> (/home/cabox/workspace/node_modules/tunnel-ssh/index.js:89:13)
0|main   |     at Object.onceWrapper (events.js:314:30)
0|main   |     at emitNone (events.js:105:13)
0|main   |     at Server.emit (events.js:207:7)
0|main   |     at emitListeningNT (net.js:1349:10)
0|main   |     at _combinedTickCallback (internal/process/next_tick.js:135:11)
0|main   |   code: 'ER_ACCESS_DENIED_ERROR',
0|main   |   errno: 1045,
0|main   |   sqlMessage: 'Access denied for user \'stock\'@\'firstpg-server197-22\' (using password: YES)',
0|main   |   sqlState: '28000',
0|main   |   fatal: true }
0|main   | 1 undefined

You you really have an uppercase P in Password? The options are case sensitive , so it should be password. — t.niese
– t.niese, Commented Oct 4, 2018 at 6:35
I'd agree with @t.niese. "Password" in config should instead be password with a lowercase "p". And another thing, username and password of what are you using? — Harshith Rai
– Harshith Rai, Commented Oct 4, 2018 at 6:37
ok i dont get the authentication error anymore. But I am getting undefined from my query results. Also am i exporting db correctly? — Sihoon Kim
– Sihoon Kim, Commented Oct 4, 2018 at 7:18
Do not change your original question into a new one by removing the original problem. Otherwise the already given answers become wrong and useless. If you have a new question then create a new one. — t.niese
– t.niese, Commented Oct 4, 2018 at 7:37
If result is empty then most likely error is set. So add a check if an error occurred and inspect that error to see what the problem is. Something like if( error ) { console.error(error) } else { console.log(result) } but as I said that's a new question. — t.niese
– t.niese, Commented Oct 4, 2018 at 7:39

t.niese · Accepted Answer · 2018-10-04 06:36:28Z

1

I cant test it right now, but it should be sufficient to set port in the options to 27000.

tunnel(config, function(error, server){
  var db = mysql.createConnection({
    host: '127.0.0.1',
    port: 27000, // the local port you defined for your tunnel
    user: 'stock_id',
    password: 'stock_password',
    database: 'database_name',
    charset  : 'utf8'
  });
  db.connect();
})

Beside that the options are case sensitive, so it should be password and not Password.

var config = {
  username:'hoffnung8493',
  password:'password123',
  host:'hoffnung.com',// not sure if its right
  port:22,// not sure if its right
  dstHost:'hoffnung.com',// not sure if its right
  dstPort:3306, // not sure if its right
  localHost:'127.0.0.1',// not sure if its right
  localPort: 27000 // not sure if its right
};

edited Oct 4, 2018 at 6:36

answered Oct 4, 2018 at 5:57

t.niese

41k9 gold badges78 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sihoon Kim Over a year ago

i added the port number but I still get error("ALL configured authentication methods failed")

StackMaster · Accepted Answer · 2018-10-04 06:03:14Z

0

First of all, I'd like to suggest you to change / hide the domain name in the future, just for caution cases.
Secondly, can you post the output you get from the console? If you get any output.
Thirdly, as this is an ssh connection matter, you need to make sure you are set as allowed to connect to the remote server, on port 22 in this case, whether in it's settings / firewall settings.

Regarding the settings, double check the variable values to be sure not to confuse between the MySQL and SSH connections variables, rely on the fact that the MySQL Workbench connection is working.

answered Oct 4, 2018 at 6:03

StackMaster

3992 gold badges6 silver badges22 bronze badges

2 Comments

Sihoon Kim Over a year ago

Thanks for the advice. I have posted the error shown on console.

Sihoon Kim Over a year ago

The variables on mysql workbench is the correct ones. Because I had successful connection there. But Im not sure if I did the right match on node.js Thats where I need help

Collectives™ on Stack Overflow

Connecting to mysql using ssh tunnel does not work

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related