Node-mssql: Connection Error: Connection is Closed

[hasan3ysf]

Hi..

I'm able to access my MSSQL from MSQL Studio.
defined my index.js file as below, but it keeps give me this error:

{ [ConnectionError: Connection is closed.]
name: 'ConnectionError',
message: 'Connection is closed.',
code: 'ECONNCLOSED'}

what wrong I'm doing, if below is my code, thanks

 var sql = require('mssql'); 
 var config = { user: 'sa', password: 'bk123', server: 'HASN-BK\\SQLEXPRESS', database: 'BK',
stream: true }

  sql.connect(config, function(err) {
 var request = new sql.Request();
 request.stream = true; 
request.query('SELECT * from emp');

request.on('recordset', function(columns) {
    console.log(columns);
    // Emitted once for each recordset in a query
});

request.on('row', function(row) {
    console.log(row);
    // Emitted for each row in a recordset
});

request.on('error', function(err) {
    console.log(err);
    // May be emitted multiple times
});

request.on('done', function(returnValue) {
    // Always emitted as the last one
});

});

[mutato]

What version of SQLServer are you using? I had the same problem after upgrading to version 2.1 from 1.3. I have only 2008R2 here to use and test so I'm only speculating that version 2.1 has problems with older SQLServer versions.
Going back to 1.3 solved the issue for me.

[MJLang]

Seeing the same issue since upgrading to 2.1 - Using SQL Azure.

[tracker1]

From the example on the homepage, set encrypt option...

    options: {
        encrypt: true // Use this if you're on Windows Azure
    }

NOTE: you really should be checking your err before trying to use your connection.

[MJLang]

{
    user: "<username>",
    password: "<password>",
    server: "<server>",
    port: "1433",
    database: "<dbname>",
    options: {
      encrypt: true
    }
}

Already doing that.
The issue just really started popping up since upgrading to 2.1. That is the only thing that changed in the codebase.

[tracker1]

Is anything in the err that's being passed to the connect callback?

[patriksimek]

Could you please post some debug data for us?

https://github.com/patriksimek/node-mssql/wiki/Debugging-TDS

[christopheranderson]

Just to add my two cents. I'm getting an error on Azure, though it works locally.

{ name: 'ConnectionError',
  message: 'Failed to connect to ------.database.windows.net:1433 - connect EADDRNOTAVAIL',
  code: 'ESOCKET' }

I am using encrypt:true. It's kinda perplexing. Let me know if you have any ideas.

[GalKlein]

Having the same issue, everything is working locally and when published to Azure I get the same error...
Any solution?

[benoror]

I have the same issue when I run 2 or more queries one right after the other

Microsoft SQL Server 2012 - 11.0.5058.0 (X64) May 14 2014 18:34:29 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

[GalKlein]

I found a solution :)
Ignore the samples on the page and only use 1 connection:

var config = {
user: 'xxxxx',
password: 'xxxxxxxxx,
server: 'servfername.database.windows.net', // You can use 'localhost\instance' to connect to named instance
database: 'db-name,
options: {
encrypt: true // Use this if you're on Windows Azure
}
}

var connection = new sql.Connection(config);
connection.connect();

var request = new sql.Request(connection);
request.input('myEmail', sql.VarChar, req.params.user);
request.input('myPass', sql.VarChar, req.params.pass);
var sqlquery = "SELECT * From USERTable WHERE Email=@myEmail AND Password=@myPass";
request.query(sqlquery, function (err, recordset) {
if (err)
res.json(err);
else
res.json(recordset);
});

[benoror]

I actually make a sql.connect... for every SQL query.

Is it preferred to have one global connection running in the bg? What about the conn's TTL? Any documentation regarding DB connection best practices?

[benoror]

Related to https://github.com/patriksimek/node-mssql/issues/135

[benoror]

It seems working OK now, but I took a different approach:

I used Promises for my queries

I guess it is better at handling async requests and closing the connections properly

[guillaumedardenne]

Worked fine for me, better use a global connection rather connecting to the databse each time you have to make a request to avoid the 'Connection is closed Error'.

[lehneres]

I currently experience the same issue, having an node application running which connects to MS Azure, at some point the connection will be closed. I think I tried all of the possible solutions in this thread, however, I think that the connection losses ocure now less often, but still they happen.

To start, I wonder what is the connect() method doing? It seems it is executed on the pool, but what is it doing there? Establishing all available a connection for all instances in the pool?

[guillaumedardenne]

Hi,

In the « config » variable, was the option « encrypt » as True ?

options: {
encrypt: true // Use this if you're on Windows Azure
}

Le 16 août 2015 à 21:40, lehneres [email protected] a écrit :

I currently experience the same issue, while having an node application running which connects to MS Azure, at some point the connection will be closed. I think I tried all of the possible solutions in this thread, however, I think that the connection losses ocure now less often, but still they happen.

To start, I wonder what is the connect() method doing? It seems it is executed on the pool, but what is it doing there? Establishing all available a connection for all instances in the pool?

—
Reply to this email directly or view it on GitHub https://github.com/patriksimek/node-mssql/issues/138#issuecomment-131612533.

[lehneres]

Yes, without it I'm not even able to connect in the beginning

[patriksimek]

@christopheranderson could you please post an example of both connection string and config object?

[christopheranderson]

Sorry @patriksimek - I deleted my post on GitHub (in case you see this in email). Copy+Paste error I did twice when adding app settings to the Web App, so nothing to do with mssql. Should have finished testing before posting.

If you'd like some user experience feedback, this error message is the one that pops up if the connection closes on purpose or due to error. I kept assuming it was a bug because my request spit out this error and the connection wasn't emitting an on('error',... message because the connection string didn't enable streaming globally (but didn't realize that until much later). I finally captured the error parameter that the connection callback provides and realized my mistake then. Two things might have helped me.

1. This code snippet in the readme should theoretically include a comment about how it only works when the stream: true config option is turned on.

// This only works if the config has { ..., stream: true} sql.on('error', function(err) { // ... error handler });

1. The connection closed error message might be improved if it noted that the connection was closed intentionally by user code or due to error (and maybe even where the error was).

[mackeym]

I seem to be having the same issue, everything works fine when connecting locally but if I try to reach my azure db it throws the 'Connection is Closed' error.

var config = {
    user: '************',
    password: '******',
    server: 'tcp:*****.database.windows.net,1433', 
    database: '*****',

    options: {
        encrypt: true // Use this if you're on Windows Azure 
    }
}

Definitely not a login permission issue as I tried it with my primary dev account and had the same issue. I think I read that in a recent version we can use our standard connection string now? Maybe I'll give that a try and see if there is any difference.

EDIT : Nevermind, my issue was just related to using the full connection string they provide me when MSSQL wants just the needed *.database.windows.net

[AdamGerthel]

I recently started getting connection closed issues as well. I've never closed any connection in my application code. Should I? My requests typically look like this:

var connection = new sql.Connection(cred, function(err) {
  var request = new sql.Request(connection);
  request.query(query).then(function(recordset) {
    // Do success stuff here
  }).catch(function(err) {
    // Handle errors here
  });
});

Should I rather do like this?

var connection = new sql.Connection(cred, function(err) {
  var request = new sql.Request(connection);
  request.query(query).then(function(recordset) {
    // Do success stuff here
    connection.close();
  }).catch(function(err) {
    // Handle errors here
    connection.close();
  });
});

[kemalmuderrisoglu]

I'm also getting same error. Any suggestions about below code?

var sql = require('mssql');
var config = require('config');
var log = require('../../core/log/')(module);

var client = {
  user: config.mssql.user,
  password: config.mssql.password,
  server: config.mssql.server, // You can use 'localhost\\instance' to connect to named instance
  database: config.mssql.database,
  options: {
    encrypt: true // Use this if you're on Windows Azure
  }
};


function operationalWallboard(date, cb) {
  sql.connect(client).then(function () {
    new sql.Request().input('DATE', sql.DATE, date)
      .execute('Operational_Wallboard').then(function (recordsets) {
      return cb(null, recordsets[0]);
    }).catch(function (err) {
      log.error('MSSQL error Operational_Wallboard execute error\n' + err.toString());
      return cb(err);
    });

  }).catch(function (err) {
    log.error('MSSQL error Operational_Wallboard connection error\n' + err.toString());
    return cb(err);
  });
}

[Shockolate]

I am also getting indeterminate Connection is Closed errors in my AWS Lambdas.
Most of the time it works perfectly. Other times, I get this strange error.
Entry points are at the bottom: addAliasesById, addMetadataById, & updateMetadataById
I used a promise approach as suggested by: https://github.com/patriksimek/node-mssql/issues/138#issuecomment-103549335
Perhaps @benoror, @devotis could provide some insight?

function insertAliasTable(entityId, tenant, aliases, connection) {
  const aliasTable = constructAliasSqlTable();
  aliases.forEach((alias) => {
    aliasTable.rows.add(tenant, entityId, alias.AliasKey, alias.Value);
  });
  return new sql.Request(connection).bulk(aliasTable);
}

function insertMetadataTable(entityId, tenant, metadataKey, metadataValue, connection) {
  const request = constructMetadataRequest(entityId, tenant, metadataKey, metadataValue,
    connection);
  return request.query(
    'INSERT INTO Metadata VALUES(@tenant, @entityId, @metadataKey, @metadataValue)');
}

function updateMetadataTable(entityId, tenant, metadataKey, metadataValue, connection) {
  const request = constructMetadataRequest(entityId, tenant, metadataKey, metadataValue,
    connection);
  return request.query('UPDATE Metadata SET Value=@metadataValue WHERE Tenant=@tenant AND EntityId=@entityId' +
    ' AND MetaKey=@metadataKey');
}

function verifyExistence(rows, type) {
  if (!rows.length) {
    throw new Error(`${type} Not Found`);
  }
  return rows[0].EntityId;
}

function connectToDatabase(execute) {
  return new Promise((resolve, reject) => {
    loadConfig()
    .then(sql.connect)
    .then(execute)
    .then(resolve)
    .catch(error => reject(sqlErrorParser(error)));
  });
}

function verifyId(id, tenant, connection) {
  return selectEntityIdFromEntity(id, tenant, connection)
  .then(rows => (verifyExistence(rows, 'Entity')));
}

function connectAndVerifyId(entityId, tenant, execute) {
  return connectToDatabase(
    connection => (
      verifyId(entityId, tenant, connection)
      .then(execute)
    )
  );
}

function addAliasesById(entityId, tenant, aliases) {
  return connectAndVerifyId(entityId, tenant,
    connection => (
      insertAliasTable(entityId, tenant, aliases, connection)
      .then(() => (entityId))
    )
  );
}

function addMetadataById(entityId, tenant, metadataKey, metadataValue) {
  return connectAndVerifyId(entityId, tenant,
    connection => (
      insertMetadataTable(entityId, tenant, metadataKey, metadataValue, connection)
    )
  );
}

function updateMetadataById(entityId, tenant, metadataKey, metadataValue) {
  return connectAndVerifyId(entityId, tenant,
    connection => (
      updateMetadataTable(entityId, tenant, metadataKey, metadataValue, connection)
    )
  );
}

[Shockolate]

I was able to solve the connection error problem in my lambdas by abstracting the connections away to module:

'use strict';

const sql = require('mssql');
const configLoader = require('./configLoader');
let connection = null;

function getDatabaseConnection() {
  return new Promise((resolve, reject) => {
    if (connection) {
      resolve(connection);
    } else {
      configLoader.getDatabaseConfig()
      .then((config) => {
        connection = new sql.Connection(config);
        connection.connect()
        .then(() => {
          resolve(connection);
        },
        (error) => {
          log.error(`Connecting error: ${error}`);
          reject(error);
        });
      })
      .catch(reject);
    }
  });
}

And refactoring the persistence functions:

function connectToDatabase(execute) {
  return new Promise((resolve, reject) => {
    connectionManager.getDatabaseConnection()
    .then(execute)
    .then(resolve)
    .catch(error => reject(sqlErrorParser(error)));
  });
}

[Shockolate]

I'm finding rampant "Connection is Closed" errors when attempting to paralellize multiple requests using the native Promise.all.

For example if I try to run 4 queries in parallel to SELECT from the same table, the first Request in the Promise.all reports the error.

If I try to run 4 INSERTs requests in parallel into the same table (yes I'm familiar with bulk), the first Request in the Promise.all array reports the error, but the last Request succeeds with insertion (as verified by direct SQL calls to the server).

I believe this bug is related to the Tedious Bug.

[chriscrowley]

@dw1284's solution fixed the "Connection is closed" error for me.

Changed from:

sql.connect(dbConfig).then(function () {
      new sql.Request()
        .execute(storedProcName).then(function (recordset) {

Changed to:

sql.connect(dbConfig).then(function (connection) {
      new sql.Request(connection)
        .execute(storedProcName).then(function (recordset) {

[takotuesday]

Unfortunately I have tried every iteration of the above solutions to no avail. This isnt happening locally, only when I try to hit a service that is deployed to Heroku. @patriksimek any suggestions?

A sample of code:

var config = {
  user: user,
  password: password,
  server: server,
  database: database,
  pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 100000,
  },
};

function upsertAsset(assets) {
  return new Promise((resolve, reject) => {
    var connection = new sql.Connection(config, () => {
      connection.connect();
      var transaction;
      collectMetaData(assets, connection)
        .then(function() {
          return assets.map(asset => {
            if (checkExistingMap[asset.Name].Exists) {
              return updateAsset(asset);
            } else {
              return createAsset(asset);
            }
          });
        })
        .then(sqlArray => {
          transaction = wrapTransaction(sqlArray);
          new sql.Request(connection)
            .query(transaction)
            .then(recordset => {
              resolve(recordset);
            })
            .catch(err => {
              reject(err);
            });
        })
        .catch(err => {
          reject(err);
        });
    });
  });
}

[Shockolate]

@takotuesday Am I correct in my understanding that you have a service deployed to Heroku that cannot access your database (you get a connection is closed error)? You also can access the database locally using the same code and credentials?

My next step would be to verify you can ping the database from your service. There might be an internet configuration (e.g. firewall) gone wrong for your deployed service.

[sumalatha1920]

I'm using webdriverIo and Javascript. WebdriverIo cannot handle promises. I am trying to connect to the database and I get name: 'ConnectionError',
message: 'Connection is closed.',
code: 'ECONNCLOSED' } error. Please help.
var config = {
user: '',
password: '',
server: '*', // You can use 'localhost\instance' to connect to named instance
database: 'db1',
port: '3512'
}

var connection = new sql.Connection(config);

connection.connect(function(err){
console.log(err);
});

var request = new sql.Request(connection);
var sqlQuery = "select * from table1";
request.query(sqlQuery, function(err, recordset) {
if(err){
console.log(err);
}
else{
console.log(recordset);
}connection.close();
});

[Shockolate]

@sumalatha1920
You need to put your Request within the callback of the connection.connect call. The "Connection is Closed" because you haven't waited for it to open in you connection.connect line.
Please refer to the documentation for guidance: ConnectionPools, Callback Model

[occulter]

I always got the error "sql.Connection is not a constructor", why?

[Shockolate]

@occulter
sql.Connection is valid with 3.x.
Double check your installed version - sql.Connection was renamed to sql.ConnectionPool in 4.x

[cicorias]

Note that mssql implements a pool manager, so acquiring a connection is really coming from the pool. So new connection and close on every request. Pool also handles bad connection ejecting

[oicnev]

I encountered the same problem. But I solved it by creating a delay before running the query. It seems like mssql needs at least 30 microseconds to check the mssql connection at the library declaration level before running the query. Otherwise, I always got the "Connection is closed" error. Below is my delay function:

const mssqlconn=require('./api/models/mssql-connection');

var runQuery=function () {
var sql=SELECT TOP 5* FROM CanonicalDocument where ConversionFlag=1;
var request=mssqlconn.request();
request.query(sql).then((result)=>{
var js=result.recordset;
for(var key in js){
console.log(js[key].Id);
}
});
}

function delay(time) {
return new Promise(resolve=>{
setTimeout(resolve,time);
});
}

delay(30).then(()=>{
runQuery();
});

[willmorgan]

This is an old issue and we've encountered similar recently using Azure SQL Database, and have fixed them. Upgrading to the latest version of Tedious / MSSQL should resolve this, but if it doesn't, feel free to reopen.

[Acinate]

Previously I would use sql.connect(config,(err) => { }) every time I did a select query. This produced errors after the first SQL query. I ended up creating a global connection (see below) and only call sql.query(queryString, (err,results) => { } ) to execute my SQL Statements.

const sql = require('mssql');

var dbConfig = {
    user: 'Jason',
    password: auth.DBPassword(),
    server: 'yourserver.database.windows.net',
    database: 'runetracker',
    options: {
        encrypt: true
    }
}

// Create a global DB Connection
ConnectToDB();
function ConnectToDB() {
    sql.connect(dbConfig, (err) => {
        if (err) return console.log('Could not create DB Connection!');
        console.log('Successfully Connected to Database!');
    });
}

[s-a]

@willmorgan can you please re open the issue ?
I have the same error using mssql 6.2.3 vs. Microsoft SQL Server 2016 (SP2-GDR) (KB4532097) - 13.0.5102.14 (X64)

[dhensby]

@s-a I think this is old enough that it probably warrants a new issue if you think this still exists.

Are you using SQL Express like the OP? If so, then it's not something wrong with the library.

[s-a]

@dhensby thank you for the fast response, Yes I think they use express. So today I already think the server drops connection on some random point. So I agree this seems not to be a problem with this module. Can you point me to a thread/issue/ticket where I can study this topic further? Seems you are sure it has something to do with Express Edition. It might impact decision für database solution for my current software project.

Maybe for other coders helpfull. This is the way I just worked arround.

// eslint-disable-next-line max-params
Provider.prototype.waitForDatabaseConnection = async function (db, msg, timeout, attempts, currentAttempt) {
    if (!db.connection._connected || db.connection._connecting) {
        await delay(timeout)
        await this.connectDatabase(db, msg, attempts, currentAttempt + 1)
    }
}

Provider.prototype.connectDatabase = async function (db, msg, attempts, currentAttempt) {
    const ca = (currentAttempt || 0)
    const delay = 100
    if (((currentAttempt || 0) < attempts) && !db.connection._connected) { // FIXME: workaround for ms sql connection lost. Seems like server drops too many connections
        this.log.warn(`Microsoft SQL Server connection problem.`)
        this.log.warn(`Delay ${delay} milliseconds to give Microsoft SQL Server system a chance to cooldown...`)
        this.log.warn(`Try to reconnect ${msg} (${ca + 1} of ${attempts} attempts)`)
        await db.connect()
        await this.waitForDatabaseConnection(db, msg, delay, attempts, ca)
    }
}

I call this connectDatabase as and extra before each query or command. Feels pretty bad but it seems to work.

[dhensby]

There shouldn't really be any need for this logic you have. When a database connection is dropped that's just one connection in the whole pool. It seems like you're acting on one dropped connection and deciding to spin up an entirely new pool rather than just trying to get another connection from the existing pool.

In terms of other issues about SQL Express, I would just suggest using GitHub search for issues in this repo (that's all I would do) and there have been some over the past few years that all had common issues around SQLExpress.