Cannot connect to SQL Server with Node.js and Tedious

Question

When I try to use Node.js and Tedioius to connect to a local SQL Server instance I get this error:

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

Here is my connection object:

var config = {
  userName: 'username',
  password: 'password',
  server: 'XXXXX',

  options: {
    database: 'databasename',
    instancename: 'SQLEXPRESS'
  }
};

I have checked and TCP/IP is enabled and broadcasting on port 1443 according to Configuration Manager. The SQL Server Browser service is also running, which I read may be causing this type of issue if not. I have disabled my antivirus and firewall and that hasn't helped either.

Any insight?

Answer 1

So what I am guessing happens is that even though Tedious lets you include instance name in 'options' it either doesn't use it or can't use it as it needs to be used. After doing some research, what should be happening is when you give SQL Server the instance name, it redirects you from port 1433 to the dynamic port it is using for that instance. I didn't know it was using a dynamic port, but if your instance is named the port will always be dynamic. I don't know where I saw it broadcasting on 1433, that was my mistake.

To check the dynamic port, look here:

From this information, I changed my code to this:

var config = {
  userName: 'username',
  password: 'password',
  server: 'XXXXX',

  options: {
    port: 49175,
    database: 'databasename',
    instancename: 'SQLEXPRESS'
  }
};

All is good now, hope this helps someone.