NodeJS to SQL Server Connection not working: socket hang up issue

Question

Here is my complete code for sql connection, all code I have got from stackoverflow issues. Everywhere, I found the same code is being suggested, hence I also tried with the same. I have some other application which uses same connection with NextJs and it works fine, however, If I try only with NodeJS code, it gives some socket hang up error (code:'ESOCKET' name:'ConnectionError'). Please make a note that TCP is already configured on remote server and its working fine with other applications.

Any help is appreciated, thank you.

const express = require('express');
const fs = require('fs');
const path = require('path');
const cheerio = require("cheerio");
const sql = require('mssql');

require('dotenv').config(); //to use the env variables

// config for your database
var config = {
    user: process.env.DATABASE_USER,
    password: process.env.DATABASE_PASSWORD,
    server: process.env.DATABASE_HOST,
    database: process.env.SOMEDB,
    port: 14345, // process.env.DATABASE_PORT,
    options: {
        encrypt: true, // for azure
        trustServerCertificate: false // change to true for local dev / self-signed certs
    }
};

        // make sure that any items are correctly URL encoded in the connection string
        let appPool = new sql.ConnectionPool(config);
        
        //I got error on below connect
        sql.connect(config).then(function(pool) {
            //It never reaches here, it directly goes to the catch block
            app.locals.db = pool;
            const server = app.listen(3000, function () {
              const host = server.address().address
              const port = server.address().port
              console.log('Example app listening at http://%s:%s', host, port)
            })
          }).catch(function(err) {
            console.error('Error creating connection pool', err)
          });

Does your version of NodeJS support TLS1.2? Are you connecting to Azure? — Charlieface, Jan 10 '22 at 04:27
_it gives some socket hang up error_ ... which is? Error messages are fairly important to diagnose issues and correct them. Please [Edit](https://stackoverflow.com/posts/70647699/edit) your question to include the full and complete error message - as text, not screen shot(s). — AlwaysLearning, Jan 10 '22 at 10:12

score 2 · Answer 1 · answered Jan 20 '23 at 13:23

For me in driver mssql@9.1.1 making encrypt=false worked

const config = {
    user: process.env.DATABASE_USER,
    password: process.env.DATABASE_PASSWORD,
    server: process.env.DATABASE_HOST,
    database: process.env.SOMEDB,
    port: 14345, // process.env.DATABASE_PORT,
    options: {
        encrypt: false
    }
};

score 0 · Answer 2 · answered Jan 12 '22 at 10:17

0

I have the same issue. Try to use mssql version 6.0.1, it works on my code, but for sure we need to figure out the problem, since we can't think to mantain forever an old version of a package.

answered Jan 12 '22 at 10:17

aletenti

1

score 0 · Accepted Answer · answered Jan 21 '22 at 19:03

I kept trying to find the solution with different different configuration changes. Finally, I have made a proper config, which worked and now its connecting properly as well as returning the data from the table.

require('dotenv').config(); //to access the process.env params

const sql = require("mssql"); //mssql object


var dbConfig = {
    user: "ajay",
    password: "abcd123",
    server: "your_remote_sql_server_path",
    port: 1433,
    database: "your_database_name",
    options: {
        database: 'your_database_name',
        trustServerCertificate: true
    }
};

try {
        //connection config will be used here to connect to the local/remote db
        sql.connect(dbConfig)
            .then(async function () {
                // Function to retrieve the data from table
                const result = await sql.query`select top 1 * from table_name`
                console.dir(result)
                
            }).catch(function (error) {
                console.dir(error);
            });
    } catch (error) {
        console.dir(error);
    }

I am not sure what was the exact issue, but as per the previous config and this one, it seems like adding database name to the options has solved the issue.

Please make sure to save all the sensitive data to the .env file. (which you can access as PROCESS.env.parametername)

NodeJS to SQL Server Connection not working: socket hang up issue

3 Answers3