CODEWITHSUNDEEP
node.jssql-serverazure
creamer298
creamer298

Reputation: 25

Connection Closed error calling nodejs API on Azure

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?

Upvotes: 0

Views: 329

Answers (2)

creamer298
creamer298

Reputation: 25

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
    }
}

Upvotes: 0

Jack Jia
Jack Jia

Reputation: 5549

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

Upvotes: 1

Related Questions