NodeJS connecting to sql server using node-mssql does not work

Question

I use a named instance of SQL Server Express 2012 If I try to connect to it using SSMS it works, using these parameters:

Server name: mit-007\SQLEXPRESS2012
Authentication: SQL Server Authentication
    Login: sa
    Password: mit

Using node-mssql:

var sql = require('mssql');
var config = {
    user: 'sa',
    password: 'mit',
    server: 'mit-007',
    driver: 'tedious',
    database: 'Delvi',
    options: {
        instanceName: 'SQLEXPRESS2012'
    }
};

sql.connect(config).then(function(){ // and so on

It logs this error

{ [ConnectionError: Failed to connect to mit-007:undefined in 15000ms]
  name: 'ConnectionError',
  message: 'Failed to connect to mit-007:undefined in 15000ms',
  code: 'ETIMEOUT' }

d.a.vorm · Accepted Answer · 2016-05-13 09:21:24Z

6

After browsing around I solved the problem, here's what I did

Open SQL Server Configuration Manager
Click SQL Server Network Configuration => Protocols for SQLEXPRESS2012
Double click TCP/IP
Change Enabled to Yes
Click IP Addresses
IPAll => Clear TCP Dynamic Ports, set TCP Port 1433
Open services.msc
Start SQL Server Browser Service
Restart SQL Server

I'm not sure that every single one of the steps above are necessary, but they worked for me

answered May 13, 2016 at 9:21

d.a.vorm

5101 gold badge5 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

RaV · Accepted Answer · 2016-05-13 08:36:42Z

0

I think mit-007 is not your network adress.

https://msdn.microsoft.com/pl-pl/library/ms189921(v=sql.110).aspx

answered May 13, 2016 at 8:36

RaV

1,0481 gold badge9 silver badges25 bronze badges

4 Comments

d.a.vorm Over a year ago

It's the server's hostname, and it works if I use PHP to connect, just not from nodejs

RaV Over a year ago

Try to define also port then. Default it 1433.

RaV Over a year ago

github.com/patriksimek/node-mssql/issues/155 - similar problem, maybe it helps.

d.a.vorm Over a year ago

Thank you, that link eventually helped me solve the problem

l3x · Accepted Answer · 2017-03-28 16:34:22Z

There is a flaw in the tedious driver logic that indicates that the port is "undefined" when running on a mac platform.

The program (testtedious.js) runs fine when using the following configuration settings on a MS Windows platform:

var connectionConfig = {
    userName: 'xxx',
    password: 'yyy',
    database: 'zzz',
    server: '127.0.0.1',
    port: 1443,
    debug: true,
    driver: 'tedious',
    options: {
        encrypt: false,
        instanceName: 'SQLEXPRESS',
        database: 'www',
        useColumnNames: false,
        debug: {
            packet: true,
            data: true,
            payload: true,
            token: true,
            log: true
        }
    }
};

However, when running on a mac I get the following error:

$ node testtedious.js
Tedious-Connection-Pool: filling pool with 2
Tedious-Connection-Pool: creating connection: 1
Tedious-Connection-Pool: creating connection: 2
Tedious-Connection-Pool: connection connected: 1
Tedious-Connection-Pool: connection closing because of error
{ ConnectionError: Failed to connect to 127.0.0.1:undefined in 15000ms
    at ConnectionError (/project-dir/node_modules/tedious/lib/errors.js:12:12)
    at Connection.connectTimeout (/project-dir/node_modules/tedious/lib/connection.js:467:28)
    at ontimeout (timers.js:365:14)
    at tryOnTimeout (timers.js:237:5)
    at Timer.listOnTimeout (timers.js:207:5)
  message: 'Failed to connect to 127.0.0.1:undefined in 15000ms',
  code: 'ETIMEOUT' }
Tedious-Connection-Pool: connection connected: 2
Tedious-Connection-Pool: connection closing because of error
{ ConnectionError: Failed to connect to 127.0.0.1:undefined in 15000ms
    at ConnectionError (/project-dir/node_modules/tedious/lib/errors.js:12:12)
    at Connection.connectTimeout (/project-dir/node_modules/tedious/lib/connection.js:467:28)
    at ontimeout (timers.js:365:14)
    at tryOnTimeout (timers.js:237:5)
    at Timer.listOnTimeout (timers.js:207:5)
  message: 'Failed to connect to 127.0.0.1:undefined in 15000ms',
  code: 'ETIMEOUT' }
Tedious-Connection-Pool: creating connection: 3
Tedious-Connection-Pool: creating connection: 4

Here's the "fix":

var connectionConfig = {
    userName: 'xxx',
    password: 'yyy',
    database: 'zzz',
    server: '127.0.0.1',
    port: 1443,
    debug: true,
    driver: 'tedious',
    options: {
        port: 1443,
        encrypt: false,
        database: 'www',
        useColumnNames: false,
        debug: {
            packet: true,
            data: true,
            payload: true,
            token: true,
            log: true
        }
    }
};

Note the movement of the port setting into options and the removal of the instanceName: 'SQLEXPRESS' option setting.

Collectives™ on Stack Overflow

NodeJS connecting to sql server using node-mssql does not work

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related