Access denied for user 'root'@'external ip' (using password: YES)

Question

i'm working on a google cloud project and i get this error when i run node index.js / try to access mysql database remotely.

this is the complete error message:

Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@'external_ip' (using password: YES)
    at Handshake.Sequence._packetToError (/home/it21695/nodeproject/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14)
    at Handshake.ErrorPacket (/home/it21695/nodeproject/node_modules/mysql/lib/protocol/sequences/Handshake.js:130:18)
    at Protocol._parsePacket (/home/it21695/nodeproject/node_modules/mysql/lib/protocol/Protocol.js:279:23)
    at Parser.write (/home/it21695/nodeproject/node_modules/mysql/lib/protocol/Parser.js:76:12)
    at Protocol.write (/home/it21695/nodeproject/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/home/it21695/nodeproject/node_modules/mysql/lib/Connection.js:103:28)
    at Socket.emit (events.js:182:13)
    at addChunk (_stream_readable.js:277:12)
    at readableAddChunk (_stream_readable.js:262:11)
    at Socket.Readable.push (_stream_readable.js:217:10)
    --------------------
    at Protocol._enqueue (/home/it21695/nodeproject/node_modules/mysql/lib/protocol/Protocol.js:145:48)
    at Protocol.handshake (/home/it21695/nodeproject/node_modules/mysql/lib/protocol/Protocol.js:52:23)
    at Connection.connect (/home/it21695/nodeproject/node_modules/mysql/lib/Connection.js:130:18)
    at Object.<anonymous> (/home/it21695/nodeproject/index.js:10:12)
    at Module._compile (internal/modules/cjs/loader.js:678:30)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:689:10)
    at Module.load (internal/modules/cjs/loader.js:589:32)
    at tryModuleLoad (internal/modules/cjs/loader.js:528:12)
    at Function.Module._load (internal/modules/cjs/loader.js:520:3)
    at Function.Module.runMain (internal/modules/cjs/loader.js:719:10)

this is my index.js code (the test DB and books table both exist):

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'external_ip',
  user     : 'root',
  password : 'password',
  database : 'test',
});

connection.connect();

connection.query('SELECT * from books', function (error, results, fields) {
        if (error) throw error;
        console.log(results);
});

connection.end();

i have added the mysql port (3306) to the firewall exceptions and i've granted privileges for root user. i turned the mysql and node.js external ips to static. i use the passwords that google cloud has assigned.

+------------------+----------------+------------+
| user             | host           | grant_priv |
+------------------+----------------+------------+
| root             | %              | Y          |
| root             | external_ip    | Y          |
| mysql.infoschema | localhost      | N          |
| mysql.session    | localhost      | N          |
| mysql.sys        | localhost      | N          |
| root             | localhost      | Y          |
| stats            | localhost      | N          |
+------------------+----------------+------------+

mysql v8.0.11
node.js v10.1.0
npm v5.6.0

More information is required to help you correctly to address your question due to there are quite a few ways to connect to an Cloud SQL instance. - Are you using Cloud SQL 2nd or 1fst generation? If none of both, what GCP services are you using? - Is your firewall rule configured in VPC Network -> Firewall rule? - What is the result of connecting through the mysql client? — rsantiago, Aug 24 '18 at 16:30

score 0 · Answer 1 · answered Jun 06 '18 at 18:04

0

Before checking the application any further make sure to check the user credentials and access privileges on the database host server.

answered Jun 06 '18 at 18:04

VPS

36
5

score 0 · Answer 2 · answered Jun 06 '18 at 18:09

This problem is related to permissions not with nodejs.

First of all i recommend you to install MySQL Workbech and perform your test from that tool.

Also you need to make some configurations into your MySQL database to allow those remote root connections, take a look at this post:

How to allow remote connection to mysql

Access denied for user 'root'@'external ip' (using password: YES)

2 Answers2