Node-mssql: Cannot Connect To Named Instance

[dshook]

I've been trying to connect to a local sql server instance for quite some time now with no luck. The error I'm getting is

Connection Error: ConnectionError: Port for sqlserver not found in ServerName;DSHOOK-BCPC;InstanceName;MSSQLSERVER;IsClustered;No;Version;10.50.4000.0;;ServerName;DSHOOK-BCPC;InstanceName;SQLSERVER;IsClustered;No;Version;10.50.4000.0;

Where my config looks like

var config_local = {
  user: 'test',
  password: 'test',
  server: 'localhost\\sqlserver',
  database: 'ReloDotNet2',
  //port: 1433,
  //stream: true,
  options: {
    // localAddress: 'localhost',
    // instanceName: 'sqlserver'
  }
};

I'm using the newest version of node-mssql and tedious:

"tedious": "^1.10.0",
"mssql": "^2.1.x",

And made sure the SQL Server Agent/Browser services are running on my machine. I've tried all the combinations of the config that I can think of with the commented out options and the server property including the instance name.

I've spent a fair amount of time with the debugger trying to figure this out too. It seems like the root error is in tedius line 343:

 create: (function(_this) {
            return function(callback) {
              var c;
              c = new tds.Connection(cfg);
              c.once('connect', function(err) {
                if (err) { // <- right here
                  err = ConnectionError(err);
                }
                if (err) {
                  return callback(err, null);
                }

Where the err is

"EINSTLOOKUP": "Port for sqlserver not found in ServerName;DSHOOK-BCPC;InstanceName;MSSQLSERVER;IsClustered;No;Version;10.50.4000.0;;ServerName;DSHOOK-BCPC;InstanceName;SQLSERVER;IsClustered;No;Version;10.50.4000.0;;"

Any ideas on what's going wrong? I can connect fine with management studio and SqlDBX.

[patriksimek]

This error originates here in Tedious. The problem is SQL Server Browser doesn't respond with server's port. Is TCP enabled on your SQL Server?

[dshook]

Wow yep, that was it. For future reference, the setting is in Sql Server Configuration Manager > SQL Server Network Configuration > Protocols for SQLSERVER > TCP/IP

[stevecooperorg]

For anyone who can't find Sql Server Configuration Manager, it's at

C:\Windows\SysWOW64\SQLServerManager12.msc

For SQL 2014. More direct paths on MSDN

[candidJ]

Facing the same issue.
In my case TCP port is enable but still facing the excat same issue.

Any hints to solve this? Spnt almost 4 days now on this issue... :(

[ashwinijindal10]

I resolved issue by updating db configuration in sequelize as like this
{
"username": "sa",
"password": "sa@123",
"database": "test",
"host": "localhost",
"dialect": "mssql",
"port": 1433,
"dialectOptions": {
"instanceName": "SQLEXPRESS"
},
}

[ashwinijindal10]

I resolved issue by updating db configuration in sequelize as like this
{
"username": "sa",
"password": "sa@123",
"database": "test",
"host": "localhost",
"dialect": "mssql",
"port": 1433,
"dialectOptions": {
"instanceName": "SQLEXPRESS"
},
}

[codeplexer]

Found it on api document http://tediousjs.github.io/tedious/api-connection.html

under config object
...
options.instanceName
...

var config = {
userName: 'test01',
password: 'test01',
server: 'localhost',
options: {
"database":"TestDB",
"instanceName": "SQL2012"
}
};

[Anthropic]

I had to set my instanceName to MSSQLSERVER for 2008 editions and it worked a charm, thanks to commenters @ashwinijindal10 & @codeplexer for leading me to the right solution, saved me a lot :)