CODEWITHSUNDEEP
sql-servernode.jsnode-mssqltedious
Matt Carrier
Matt Carrier

Reputation: 5992

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

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?

Upvotes: 83

Views: 138722

Answers (13)

Matt Carrier
Matt Carrier

Reputation: 5992

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

enter image description here

Find it by searching windows for "Computer Management":

enter image description here

Upvotes: 149

holydragon
holydragon

Reputation: 6728

In my case, I had to do all of these 3 answers above in order for it to connect successfully.

  1. Enable TCP connection by @Matt Carrier
  2. Run SQL Server Browser by @MMalke
  3. Set TCP Port property by @Alen

and if SSL is not installed, don't forget to set

"options": {
  "encrypt": false, // Disable encryption
}

Upvotes: 1

Diwakar Sinha
Diwakar Sinha

Reputation: 57

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

Upvotes: 0

Aurel Havetta
Aurel Havetta

Reputation: 455

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

Upvotes: 0

Player1
Player1

Reputation: 3175

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

Upvotes: 22

MMalke
MMalke

Reputation: 2116

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.

Upvotes: 34

Susampath
Susampath

Reputation: 716

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

}

Upvotes: 5

Muhammad Awais
Muhammad Awais

Reputation: 1966

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

sudo docker start sqlserver

Upvotes: 0

Tusar
Tusar

Reputation: 769

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

Upvotes: 3

Alen
Alen

Reputation: 423

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.

[1]

Upvotes: 27

user2049446
user2049446

Reputation: 

**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/

Upvotes: 3

DaNeSh
DaNeSh

Reputation: 1062

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!

Upvotes: 1

Adam Cypher
Adam Cypher

Reputation: 397

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)

Upvotes: 6

Related Questions