CODEWITHSUNDEEP
sql-servernode.js
lutaoact
lutaoact

Reputation: 4429

Can't connect to SQL Server from Node

I use these versions:

node v4.7.3
mssql v3.2.1

I use mssql module to connect server, the doc for mssql is here.

SQL server is run on Windows, I run the following command in Windows system to confirm the status of SQL server:

> netstat -ano | findstr 1433
 TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING       1452
 TCP    127.0.0.1:1433         127.0.0.1:50093        ESTABLISHED     1452
 TCP    127.0.0.1:50093        127.0.0.1:1433         ESTABLISHED     3636

Then I use nc to test the port for server:

$ nc -z msserver 1433
Connection to msserver port 1433 [tcp/ms-sql-s] succeeded!

which indicates that I can connect the port.

However, I can't connect the server by mssql module with the following config:

{
  user: 'sa',
  password: 'pwd',
  server: 'msserver',
  port: '1433',
  database: 'dbname',
  pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 30000,
  },
  requestTimeout: 0,
  connectionTimeout: 15000,
  debug: true,
}

I use debug: true to get the debug info as follows:

connected to msserver:1433

Sent
  type:0x12(PRELOGIN), status:0x01(EOM), length:0x002F, spid:0x0000, packetId:0x01, window:0x00
  PreLogin - version:0.0.0.1 1, encryption:0x02(NOT_SUP), instopt:0x00, threadId:0x00000000, mars:0x00(OFF)
State change: Connecting -> SentPrelogin
socket ended
State change: SentPrelogin -> Final
connection to msserver:1433 closed
State is already Final

My code is here:

mssql.connect(config.mssql).then(function() {
  console.log('connected');
  new mssql.Request().query('select top 1 * from qmx.dbo.QMXStockPool order by ID desc', (err, records) => {
    console.log(err, records);
  });
}).catch(function(err) {
  console.log(err);
});

no output, no error, finished soon.

Upvotes: 2

Views: 2519

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131364

From the comments, it appears that this error was logged in SQL Server's Error log file:

Could not connect because the maximum number of ‘4’ user connections has already been reached.

This means that someone explicitly set a maximum concurrent connection limit. The default is unlimited connections.

The solution is to change the value back to the default 0 = unmlimited. The GUI way is to open SSMS, go to the Connections tab in the server's properties and set Maximum number of concurrent connections back to 0. enter image description here

The other option, described in Error while connecting to SQL Server – “Could not connect because the maximum number of ‘1’ user connections has already been reached.” and numerous other articles, is to connect from the command line using sqlcmd and change the configuration setting : 

sp_configure 'show advanced options', 1;  
go 

reconfigure 
go 

sp_configure 'user connections', 0  
go 

reconfigure 
go

Upvotes: 2

Related Questions