Node-mssql: Multiple Connections to different databases using different logins

[ryankelley]

Recently ran into a problem for us on our db connectivity side. While in staging, we have mutliple tenants, connecting to their own (seperate) databases, using their own login per database. However, once the app makes its first connection, then all subsequent attempts to open a new connection with a different username/password combination are met with the following error:

[Error] MSSQL Connection: { [ConnectionError: Login failed for user 'REDACTEDUSERNAME'.]   name: 'ConnectionError',   message: 'Login failed for user \'REDACTEDUSERNAME\'.',   code: 'ELOGIN' }

If we utilize an admin login, like sa. Then the error goes away, even when switching databases, however we had to put in a query to:

USE [desiredDatabase]

So that the correct data would go out on the request.

Please advise on how you would like us to proceed, not sure if mssql is broken, we're just doing it wrong, or an underlying tedious issue.

[tracker1]

You should have a connection object per database (server:port/user/pass) combination you use... I tend to have a get-connection module that exposes a function that will have a cached set of databases... this way each set can be cached/pooled, but ensures I get the right one.

var _ = rquire('lodash')
    ,sql = require('mssql')
    ,connectionPools = {}
    ;

module.exports = function getConnection(options, callback) {
  if (arguments.length < 2) throw new Error('Missing required argument (connectionName, callback)');
  if (typeof callback !== "function") throw new Error('The callback argument must be a function');

  var key = _.template('u:${user}|p:${password}|s:${server}|d:${database}', credentials);
  if (connectionPools[key]) return callback(null, connectionPools[key]);

  var connection = new sql.Connection(options, function(err){
    if (err) return callbakc(err);

    //in case there was a connection created while waiting, use that...
    if (connectionPools[key]) return callback(null, connectionPools[key]);

    connectionPools[key] = connection;
    return callback(null, connection);
  });
}

Usage:

var connect = require('get-commection')
...
connect(credentials, function(err, connection) {
  if (err) return callback(err);

  var request = new sql.Request(connection);
  request.input('foo', someValue);
  ...
  request.execute(someStatement, function(err, recordsets, returnValue) {
    if (err) return callback(err);

    //do something with the recordsets...
  });
});

I also tend to preface this with a command that will lookup credentials based off of a name from a configuration store/service/db...

One caveat, is the key doesn't store any pool size config, so only the first one will win, if you change the pool-size you'll need to restart...

[ryankelley]

So we were apparently doing it wrong, the piece I think we were missing was:

 //in case there was a connection created while waiting, use that...
    if (connectionPools[key]) return callback(null, connectionPools[key]);

So thank you for the tip!

[edwardstudy]

throw a error: credentials is undefined

this credentials should be modifed by 'options'
var key = _.template('u:${user}|p:${password}|s:${server}|d:${database}', options);

[anton-bot]

As of 2018, is there any way to maintain two connections to different databases at the same time?

It seems that there is some sort of global connection and using different connection pools initialized with different connection strings doesn't help.