The SQL instance on Google Cloud Platform is currently unable to establish connections with a **nodejs application** when using custom user credentials

After adding my IP to the whitelist, I successfully connected to a Google Cloud Platform SQL Server instance using the sqlcmd utility in my terminal.

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

However, when attempting the same connection from my node application on the same machine, it fails:

Here's 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 setup works fine for connecting to a local SQL Server instance I have installed. However, changing some parameters to connect to the GCP database results in unsuccessful connection attempts.

Here is the error message:

{ 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 found that using the MSSQL default user account for connection (sqlserver) makes the above code work successfully. It seems like an issue with Google Cloud Platform roles. Is there a specific option or setting in GCP that will enable successful connections?

node.jssql-servergoogle-cloud-platformtedious

Answer №1

After investigating, I've identified the root cause of the issue: you must execute an ALTER ROLE command to assign a role to the new user. I encountered the same error when I neglected this step while creating a new user. However, after granting the new user the built-in db_ddladmin role, everything worked smoothly, allowing the user to create tables.

If you're interested, here's a Python script that demonstrates how to create a new user for SQL Server authentication and then connect using that user account: https://gist.github.com/dmahugh/ae0b829f85c3bbd9ba332712b305bf6b

I'm not well-versed in Tedious, but it seems like you can send SQL commands using connection.execSQL(). Therefore, by following the example provided in the gist, you should be able to establish a connection as the new user just like you would with the sqlserver default admin account.

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Version 2.8.8 of prettier.js just dropped! Now, the burning question: what is the minimum version of Node

When examining the package.json file for Prettier version 2.8.8 at this link, it shows "engines": { "node": ">=14"} However, upon checking the package.json file of the installed prettier 2.8.8 version, I find: "engines": { "node": ">=10.13.0" }, https:/ ...

node.jsnpmprettier

No data retrieved when running nested queries in MongoDB through Node.js and React

In my Node JS + Express code, I have implemented an api call handler like this: const express = require("express"); const router = express.Router(); const PostModel = require("../models/postModel"); const ProfileModel = require(".. ...

node.jsreactjsmongodbexpress

What is the best way to iterate over a MySQL query multiple times in node.js and retrieve the data from each iteration of the query?

Within my database, there exists a table named Inventory with five columns: primary key, sku, item name, description, and price. Additionally, I possess an array of SKUs labeled skus. My objective involves executing a for-loop to query the Inventory table ...

mysqlnode.jsasynchronousq

Guide to implementing callback functions within a loop

I have a code snippet here where I am receiving n number of values from a function and I need to store them in an array before returning a callback. if(code=='ok'){ let arrl = idArr.map(obj =>{ array[0].userId = obj; return array ...

node.jscallback

Troubleshooting Node.js TypeScript breakpoints in Visual Studio Code

I've attempted multiple solutions, but none seem to be working for me. Although the code is running, I'm having trouble setting breakpoints and debugging it. Can you offer any assistance? Below is the configuration script I've tried in VSCo ...

javascriptnode.jstypescriptvisual-studio-codevscode-debugger

Having trouble with the installation of Aerospike, encountering an Aerospike error during the 'node-gyp rebuild' step

Encountering an issue while trying to install the Aerospike NodeJS module. The error message received is as follows: Aerospike failed at step 'node-gyp rebuild' with exit status 1. /usr/bin/ld: cannot find -lssl collect2: error: ld returned 1 ex ...

node.jslinuxnpmnpm-install

What strategies can I implement to prevent the JavaScript CallStack from becoming overloaded?

The code snippet below outlines the functionality achieved through JavaScript (specifically for a node.js COMET application): A request is sent to the server and held until there is a response. Upon receiving a response, the data is processed, and anothe ...

javascriptjquerynode.jscomet

Utilizing a class structure to organize express.Router?

I've been playing around with using Express router and classes in Typescript to organize my routes. This is the approach I've taken so far. In the index.ts file, I'm trying to reference the Notes class from the notes.ts file, which has an en ...

node.jstypescriptexpress

Having trouble getting create-react-app to run with "npm start" despite using the correct npm and node versions

I'm currently in the process of learning React by following a helpful tutorial. Recently, I encountered an error regarding a missing start script. After adding it to the package.json file using my text editor, I now find myself at a loss as to how to ...

node.jsreactjsnpmcreate-react-app

Unveiling the Server Configuration in Vue.js and Node.js

(disclaimer: I may not be the best at coding, so excuse any mistakes in my explanation.) I recently launched a Vue.js project using the standard webpack template for creating a single-page application. (I haven't made any changes to the document stru ...

node.jsvue.jsvue-router

What causes the variable to be invisible in the imported file?

Within the main.js file, there is a specific code snippet: var test_mysql = require('./test_mysql.js') ... //additional code before(function(){ test_mysql.preparingDB(test_mysql.SQL_query.clear_data); // or test_mysql.preparingDB(SQL ...

javascriptnode.jsmocha.jsnode-mysql

Tips for transferring a JavaScript object to a Node.js server

I must admit, I am positive that the solution to my issue is incredibly simple yet it has been evading me for more than a week now and causing me endless frustration. My current project involves creating a web app for quoting purposes within my workplace, ...

javascriptnode.jsjson

Can express-handlebars tags be utilized within an HTML script tag when on the client side?

For a while now, I've been facing a challenge. I'm in the process of building an application with express-handlebars and so far everything is going smoothly. The data that needs to be displayed on the webpages looks good thanks to the Helper func ...

javascriptnode.jsexpressnpmexpress-handlebars

Installation of a cloned Parse server

I downloaded the Parse server repository from parse-server-example and set up MongoDB, as well as installed Node.js packages using npm install. However, when I try to start the app with npm start, I encounter this error in the terminal!! How can I resolve ...

javascriptnode.jsmongodbparse-platform

The number of active MongoDB connections is steadily rising

My issue revolves around hitting my connection limit constantly, despite consistent http traffic. After using MMS to profile my mongod process, I noticed that the number of connections is continuously increasing: In my Node.js (Express) application, I uti ...

node.jsmongodbexpressmongoskinnode-mongodb-native

Unexpected behavior: custom event firing multiple times despite being emitted only once

I am utilizing the ws module for incorporating web sockets functionality. An event named newmessage seems to be triggering multiple times in correlation with the number of active sockets connected to the web-socket-server. The scenario puzzled me initiall ...

javascriptnode.jssocketsevents

Matching queries precisely in MongoDB

I developed an Express.js API with MongoDB integration that filters products based on a filter property. The issue I am facing is ensuring that the API output exactly matches the filter property criteria. Currently, if Product A has [{name: 'a', ...

javascriptnode.jsmongodbexpress

My goal is to transfer information from the client-side to the server-side upon the user disconnecting from a socket.io connection using Node.js

Is there a way to transmit data from the client side to the server side when a user disconnects from a socket.io connection in Node.js? I attempted creating a new event and calling it within the 'disconnect' event, but it seems impossible since t ...

javascriptnode.jsexpresswebsocketsocket.io

The HighChart export server is unable to generate static images when using the Docker file for exporting

My current setup includes OS-Windows 11 Enterprice, NodeJS 14.0.0, NPM 6.14.4, and Highchart export server 2.1.0 I am attempting to generate/export static images through Docker. Here is my Docker File: # Pulling image from node FROM node:carbon ENV ACC ...

node.jsdockerexpresshighchartsserver

What sets crypto apart from bcrypt?

These necessary modules are implemented in this way const crypto = require('crypto'); const bcrypt = require('bcrypt'); I am puzzled by their usage. I am seeking clarification on the distinctions between them and the situations in whi ...

node.jscryptographybcrypt