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

score 5 · Answer 1 · answered May 13 '16 at 09:21

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

score 0 · Answer 2 · answered May 13 '16 at 08:36

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 '16 at 08:36

RaV

1,029
1
9
25

It's the server's hostname, and it works if I use PHP to connect, just not from nodejs – d.a.vorm May 13 '16 at 08:46
Try to define also port then. Default it 1433. – RaV May 13 '16 at 08:50
https://github.com/patriksimek/node-mssql/issues/155 - similar problem, maybe it helps. – RaV May 13 '16 at 08:53
Thank you, that link eventually helped me solve the problem – d.a.vorm May 13 '16 at 09:18

score 0 · Answer 3 · answered Mar 28 '17 at 16:34

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.

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

3 Answers3