Connection Closed error calling nodejs API on Azure

Question

I have a nodejs Rest API calling my on-prem SQL Server. It is hosted on Azure and connected to my SQL server using an Azure Hybrid Connection. All runs as expected on my local machine, but on Azure I'm getting a ConnectionClosed error (ECONNCLOSED) when I attempt to call the Test route below:

Here is my code sample:

var express = require('express');
var app = express();
var sql = require('mssql/msnodesqlv8');

// Connection string parameters.
var sqlConfig = {
    user: 'restapi', //remove if using Windows Auth
    password: 'restapi', //remove if using Windows Auth
    database: 'dbname',
    server: 'server\\instance',
    driver: 'msnodesqlv8',
    options: {
        encrypt: true
    }
}

var server = app.listen(process.env.PORT || 8081, function () {
    var host = server.address().address
    var port = server.address().port

    console.log("app listening at http://%s:%s", host, port)
});

app.get('/', function(req, res) {
    res.json({"message": "Welcome to the API"});
});

app.get('/Test', function (req, res) {
    sql.connect(sqlConfig, function() {
        var request = new sql.Request();
        request.query("select 1 as number")  //query
        .then(result => {
          let rows = result.recordset //first recordset
          res.setHeader('Access-Control-Allow-Origin', '*')
          res.status(200).json(rows);
          sql.close();
        }).catch(err => {
          console.log(err);
          res.status(500).send({
            message: err
          })
          sql.close();
        });
    })
});

Any ideas?

Jack Jia · Accepted Answer · 2019-10-10 02:16:09Z

1

Here is my workable sample:

var express = require('express');
var app = express();
var sql = require('mssql');

var sqlConfig = {
    user: 'jack', 
    password: 'password',
    database: 'jackdemo',
    server: 'jackdemo.database.windows.net',
    options: {
        encrypt: true
    }
}

var server = app.listen(process.env.PORT || 8081, function () {
    var host = server.address().address
    var port = server.address().port

    console.log("app listening at http://%s:%s", host, port)
});

app.get('/', function(req, res) {
    res.json({"message": "Welcome to the API"});
});

app.get('/Test', function (req, res) {
    sql.connect(sqlConfig, function() {
        var request = new sql.Request();
        request.query("select 1 as number") 
        .then(result => {
          let rows = result.recordset //first recordset
          res.setHeader('Access-Control-Allow-Origin', '*')
          res.status(200).json(rows);
          sql.close();
        }).catch(err => {
          console.log(err);
          res.status(500).send({
            message: err
          })
          sql.close();
        });
    })
});

You can also use the approach from the official tutorial: Use Node.js to query an Azure SQL database

By the way, please ensure that you have correctly configure the firewall rule for you Azure SQL. You need to add your client IP to the allowed list as: https://learn.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure#create-and-manage-ip-firewall-rules

answered Oct 10, 2019 at 2:16

Jack Jia

5,5391 gold badge14 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

creamer298 Over a year ago

My SQL server is on-prem. I am connecting to it through an Azure Hybrid Connection.

creamer298 · Accepted Answer · 2019-10-10 19:50:32Z

0

Ok, I fixed this by removing the driver & instance name from my sqlConfig:

var sqlConfig = {
    user: 'restapi', //remove if using Windows Auth
    password: 'restapi', //remove if using Windows Auth
    database: 'dbname',
    server: 'server',
    options: {
        encrypt: true
    }
}

answered Oct 10, 2019 at 19:50

creamer298

255 bronze badges

Collectives™ on Stack Overflow

Connection Closed error calling nodejs API on Azure

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related