Search code examples
node.jssql-servergoogle-cloud-platformtedious

Google Cloud Platform SQL instance won't accept **nodejs application** connections using custom user credentials

I have whitelisted my ip and am able to connect to a google cloud platform sqlserver instance using the sqlcmd utility in my terminal.

sqlcmd -S <ip-address> -U <username> -P<password>

However, when I try this same attempt from my node application on the same machine, I am unable to connect:

my configuration: 

const sql = require('mssql')

const dconfig = {
  server: 'X.X.X.X',
  authentication: {
    type: 'default',
    options: {
      userName: '<username>',
      password: '<password>'
    }
  },
  database: 'logs',
  options: {
    appname: 'ms-inx-notify-prod'
  }
}

const cpool = new sql.ConnectionPool(dconfig).connect().catch((err) => { console.log(err) })

This configuration option works when I connect to a local sqlserver instance that I have installed, but when I changed some parameters to point to the GCP database, it doesn't connect successfully.

This is the error:

{ ConnectionError: Login failed for user 'username'. at ConnectionError (/home/ziggyjosh16/Projects/Node/node_modules/tedious/lib/errors.js:13:12) at Parser.tokenStreamParser.on.token (/home/ziggyjosh16/Projects/Node/node_modules/tedious/lib/connection.js:818:51) at Parser.emit (events.js:193:13) at Parser.parser.on.token (/home/ziggyjosh16/Projects/Node/node_modules/tedious/lib/token/token-stream-parser.js:33:14) at Parser.emit (events.js:193:13) at addChunk (/home/ziggyjosh16/Projects/Node/node_modules/readable-stream/lib/_stream_readable.js:297:12) at readableAddChunk (/home/ziggyjosh16/Projects/Node/node_modules/readable-stream/lib/_stream_readable.js:279:11) at Parser.Readable.push (/home/ziggyjosh16/Projects/Node/node_modules/readable-stream/lib/_stream_readable.js:240:10) at Parser.Transform.push (/home/ziggyjosh16/Projects/Node/node_modules/readable-stream/lib/_stream_transform.js:139:32) at doneParsing (/home/ziggyjosh16/Projects/Node/node_modules/tedious/lib/token/stream-parser.js:114:14) message: "Login failed for user 'username'.", code: 'ELOGIN' }, name: 'ConnectionError' }

(EDIT): I also discovered that if you use the MSSQL default user account for connecting (sqlserver) the above code works successfully. I think this is an issue with the google account platform roles. Is there an option or a setting in GCP which will allow me to connect?

Solution

  • I think I found the issue: you need to do an ALTER ROLE to give the new user a role. I had reproduced your error when I created a new user without doing that step, but now I can create new users and connect with them fine. I gave the new user the built-in db_ddladmin role, so it's able to create a table.

    Here's a Python script that creates a new user for SQL Server authentication and then connects as that user: https://gist.github.com/dmahugh/ae0b829f85c3bbd9ba332712b305bf6b

    I'm not familiar with Tedious, but it looks like you pass SQL commands to it with connection.execSQL(), so you should be able to do something like the example in that gist, and then you can connect as the new user in the same way that you connect as the sqlserver default admin account.