Node.js MSSQL tedius ConnectionError: Failed to connect to localhost:1433 - connect ECONNREFUSED

Question

I am trying to connect to MSSQL 2012 using NodeJS with the mssql connection interface.

When attempting to connect I get the following error:

{ [ConnectionError: Failed to connect to localhost:1433 - connect ECONNREFUSED]
  name: 'ConnectionError',
  message: 'Failed to conncet to localhost:1433 - connect ECONNREFUSED',
  code: 'ESOCKET' }

Any ideas on how to fix this?

`localhost` also does not work for me, however using the actual ip address of the machine does. — amin.avan, Jul 24 '18 at 20:17
@amin.avan well it would depend on if you are running the database locally or not. If you are running the db locally localhost should work. If not then I wouldn't expect it to work. — Matt Carrier, Jul 25 '18 at 21:10
@MattCarrier I am getting a similar error when I try to connect to Azure SQL from my node js application. But there is no option of enabling or disabling TCP IP in there. Any solution for this issue? — CtrlAltElite, Nov 06 '19 at 11:24

score 135 · Accepted Answer · answered Aug 29 '14 at 22:50

135

The solution is to enable TCP connections which are disabled by default.

enter image description here

answered Aug 29 '14 at 22:50

Matt Carrier

5,602
6
26
30

3

+1 for the screenshot. I thought I had it enabled because I saw remote connections were enabled in SSMS, but this was not. – Gibryon Bhojraj May 16 '17 at 13:14
4

How do you do this in Docker? – gh0st Jun 26 '18 at 03:41
On windows 10 you can quickly access the configuration window by pressing "window button + R" then type: "SQLServerManager13.msc" – Lothre1 Sep 02 '19 at 07:39
2

I had to restart the SQL Server service after updating this, and it worked! – Allwyn Dsouza Jan 30 '21 at 14:14

MMalke · Answer 2 · 2020-09-23T18:08:28.123

My case wasn't exactly the same as Matt's, but his screenshot was enough to remember me what was missing.

enter image description here

As it is said here, when you are using the SQL Server Instance Name to connect to it, you must have SQL Server Browser running.

options.instanceName

The instance name to connect to. The SQL Server Browser service must be running on the database server, and UDP port 1434 on the database server must be reachable.

(no default)

Mutually exclusive with options.port.

score 22 · Answer 3 · answered Jan 15 '19 at 14:23

22

If somebody still struggles to connect despite doing all that was proposed.
In my case I had to manually set TCP Port property to 1433 in SQL Server Network Configuration -> Protocols for ... -> TCP/IP -> IP Addresses -> IPAll.

[

answered Jan 15 '19 at 14:23

Alen

373
3
8

For anyone who got here - my issue was that TCP/IP wasn't enabled, even though SSMS could connect! Enabling it in the configuration manager at the above location got it working for me. – tristankoffee Apr 01 '20 at 09:26
This really did help! – Abd allah Khateeb Aug 12 '22 at 11:08

Player1 · Answer 4 · 2021-05-19T16:26:17.307

21

If after enabling the TCP connection and your configuration is still not working. Here's my own-configuration.

var config = {
    "user": 'admin',
    "password": 'password',
    "server": 'WINDOWS-PC',
    "database": 'database_name',
    "port": 61427, // make sure to change port
    "dialect": "mssql",
    "dialectOptions": {
        "instanceName": "SQLEXPRESS"
    }
};

edited May 19 '21 at 16:26

answered Dec 13 '16 at 13:26

Player1

2,878
2
26
38

1

This really did help! – Freddy Bonda Feb 06 '19 at 08:04

score 6 · Answer 5 · answered Jun 22 '16 at 01:33

Best practice is to first verify the connection to the SQL server using a query analyzer (SQL Management Studio (Windows) or SQLPro for MSSQL (Mac)) using the same protocol, port and credentials as you wish to use via your application.

In Management Studio, the format is Server,Port (e.g. 192.168.1.10,1433); and you'll probably be using SQL Server Authentication instead of Windows Authentication.

Steps to configure the SQL Server:

Install with Mixed Authentication, if you intend to use SQL Server Authentication.

Setup SQL Server to listen on TCP on a fixed port number:

SQL Configuration Manager SQL Server Network Configuration
- Protocols for {Instance}
  - TCP/IP - Enabled (double-click)
  - IP Address (on all desired interfaces)
    - TCP Dynamic Ports = BLANK! (not zero)
    - TCP Port - 1433 (or desired port)

It worked for me but only when I set `1433` to `TCP Port` of `IPAll` section. Setting the `TCP Dynamic port` to `BLANK` and `TCP Port` to `1433` for Ioopback address i.e 127.0.0.1 alone did not work. I am using MS SQL Server 2014 — Rajaraman Subramanian, Mar 12 '18 at 12:15

score 5 · Answer 6 · answered Sep 10 '20 at 14:19

In my case there was a configuration issue.This was the wrong configuration

let config = {
server: 'localhost',
authentication: {
    type: 'default',
    options: {
        userName: 'sa', // update me
        password: 'xxxxx' // update me
    }
},
options: {
    database: 'SampleDB',
    validateBulkLoadParameters:false,
}}

Then I have added encrypt variable to the options.It solved my issue.Corrected configuration

let config = {
server: 'localhost',
authentication: {
    type: 'default',
    options: {
        userName: 'sa', // update me
        password: 'xxxxxx' // update me
    }
},
options: {
    database: 'SampleDB',
    validateBulkLoadParameters:false,
    encrypt: false,
}

}

score 3 · Answer 7 · answered Apr 25 '18 at 05:34

**Please follow the connection configuration and little test:**

//Declare global variable
var http = require('http');
var events = require('events');
var nodemailer = require('nodemailer');
var sql = require('mssql');<br/>
var Request = require('tedious').Request;  
var TYPES = require('tedious').TYPES; 
//Create an http server
http.createServer(function(req,res)
{
res.writeHead(200, {'Content-Type': 'text/html'});
 var Connection = require('tedious').Connection; 
//Configure the connection 
    var config = {  
        userName: '<user id>',  
        password: '<password>',  
        server: '<system ip>',  
        options: {database: '<database name>'}  
    };  
    var connection = new Connection(config);  
    connection.on('connect', function(err) {  
        console.log("Connected"); 
        executeStatement();     
    }); 

function executeStatement() {  
        request = new Request("select getdate();", function(err) {  
        if (err) {  
            console.log(err);}  
        });
     var result = "";  
        request.on('row', function(columns) {  
            columns.forEach(function(column) {  
              if (column.value === null) {  
                console.log('NULL');  
              } else {  
                result+= column.value + " ";  
              }  
            });  
            console.log(result);  
            result ="";  
        });  

        connection.execSql(request);  
};
  return res.end();
}).listen(8080);

//Post configuration test on browser: http://localhost:8080/

Tusar · Answer 8 · 2020-05-17T19:09:36.863

Apart from setting TCP port no to 1433. If you are getting "Connection lost - Cannot call write after a stream was destroyed" error

This error will also come if you use options.encrypt: true on Node v12+ with old SQL Server versions.

This is caused by Node v12 requiring TLS 1.2.

install the TLS 1.2 security patch for your SQL Server
run node with backwards compatibility flag:

node --tls-min-v1.0

eg: node --tls-min-v1.0 app.js
disable encrypted communication by setting

options.encrypt: false (optional)

Config Object:

const config = {
      user: '...',
      password: '...',
      server: 'localhost', 
      database: '...',
      'options.encrypt': false   
   }

Ref: https://github.com/tediousjs/tedious/issues/903#issuecomment-614597523

score 1 · Answer 9 · answered Aug 05 '16 at 14:25

1

I couldn't connect with 'localhost' although I use 'localhost' in SQL Management Studio and other applications. When I used Computer Name (network address), it worked!

answered Aug 05 '16 at 14:25

DaNeSh

1,022
1
14
24

score 0 · Answer 10 · answered Jul 06 '20 at 09:51

0

My issue was that I needed to start sqlserver using docker first on my mac using this command

sudo docker start sqlserver

answered Jul 06 '20 at 09:51

Muhammad Awais

1,608
1
21
21

score 0 · Answer 11 · answered Jul 08 '21 at 00:07

For me changing this condition from 'No' to 'Yes' worked:

    switch (key) {
      case 'instance':
        return this.config.options.instanceName
      case 'trusted':
        return this.config.options.trustedConnection ? 'Yes' : 'Yes'

It just doesn't load the value from the options object even if I provide it.

I hardcoded this change in file \node_modules\mssql\lib\msnodesqlv8\connection-pool.js

score 0 · Answer 12 · answered Apr 10 '23 at 22:06

I was getting error ConnectionError: Failed to connect to : - D42A0000:error:0A000102:SSL routines:ssl_choose_client_version:unsupported protocol

dbConfig1 = {
    user: 'XXXX',
    password: 'XXXX',
    server: 'XXXX',
    port: XXXX,
    database: 'XXXX'
}

Solution: I added "encrypt": false.

dbConfig1 = {
    user: 'XXXX',
    password: 'XXXX',
    server: 'XXXX',
    port: XXXX,
    database: 'XXXX',
    options: {
        "encrypt": false
    }
}

Node.js MSSQL tedius ConnectionError: Failed to connect to localhost:1433 - connect ECONNREFUSED

12 Answers12

Linked