Wednesday, April 12, 2017

Node.js Authentication Error Connecting to Google Cloud SQL Proxy

I was receiving this error trying to run a local instance of a Node.js API against a Google Cloud Platform (GCP) database.

Error: ER_ACCESS_DENIED_ERROR: Access denied for user ''@'cloudsqlproxy~174.7.116.43' (using password: NO) at Handshake.Sequence._packetToError (/Users/cawood/GitHub/project/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14) at Handshake.ErrorPacket (/Users/cawood/GitHub/project/node_modules/mysql/lib/protocol/sequences/Handshake.js:103:18) at emitOne (events.js:96:13) at Socket.emit (events.js:191:7) at readableAddChunk (_stream_readable.js:178:18) at Socket.Readable.push (_stream_readable.js:136:10) 

The solution was quite straightforward, but when I first googled the error, I couldn't find anything about the access denied error showing no user--the username should be in the error (i.e. 'username'@'cloudsqlproxy).

I thought the error was the GCP Cloud SQL Proxy, but I was initiating it correctly and with a valid credential file for a service account:

$ ./cloud_sql_proxy -instances=name-111111:us-central1:instancename=tcp:0000 \ -credential_file=serviceAccountCreds.json &

The problem was actually with the environment variables for my local instance of the Node.js API. I hadn't exported them. Of course, the error was correct, I wasn't trying to connect with any user at all.

$ export MYSQL_USER="username" 
$ export MYSQL_PASSWORD="password" 
$ export MYSQL_DATABASE="test" 
$ npm start

To check if you have set these correctly, you can output them to the console when you start the server: console.log(config);

cawood$ npm start 
> projectapi@0.0.1 start /Users/cawood/GitHub/project
> node server.js 
 { user: 'username', password: 'password', database: 'test' } 
App listening on port 8000 Press Ctrl+C to quit.