Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[NODE v18] Failed to connect to {server} - socket hang up (code: ESOCKET) #1449

Open
MartianH opened this issue Jun 21, 2022 · 39 comments
Open

[NODE v18] Failed to connect to {server} - socket hang up (code: ESOCKET) #1449

MartianH opened this issue Jun 21, 2022 · 39 comments

Comments

@MartianH
Copy link

MartianH commented Jun 21, 2022 •
edited
Loading

Everything works in Node v14 and v16 (haven't used v17 as it isn't LTS or a candidate for it). This issue seems to only pop up with the latest current v18.

Note: sensitive data has been omitted.

Actual behaviour:

Error:: ConnectionError [SequelizeConnectionError]: Failed to connect to {sql_server_address}:1433 - socket hang up
    at ConnectionManager.connect ([omitted]]backend/src/node_modules/sequelize/src/dialects/mssql/connection-manager.js:138:17)
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at ConnectionManager._connect ([omitted]]backend/src/node_modules/sequelize/src/dialects/abstract/connection-manager.js:326:24)
    at [omitted]]backend/src/node_modules/sequelize/src/dialects/abstract/connection-manager.js:250:32
    at ConnectionManager.getConnection ([omitted]]backend/src/node_modules/sequelize/src/dialects/abstract/connection-manager.js:280:7)
    at [omitted]]backend/src/node_modules/sequelize/src/sequelize.js:629:26
    at NameService.getMessage ([omitted]]backend/src/src/app/app.service.ts:105:34)
    at bootstrapFactory ([omitted]]backend/src/src/common/common.utils.ts:42:5) {
  parent: ConnectionError: Failed to connect to {sql_server_address}:1433 - socket hang up
      at Connection.socketError ([omitted]]backend/src/node_modules/tedious/src/connection.ts:2222:28)
      at Connection.socketEnd ([omitted]]backend/src/node_modules/tedious/src/connection.ts:2239:12)
      at Socket.<anonymous> ([omitted]]backend/src/node_modules/tedious/src/connection.ts:2013:37)
      at Socket.emit (node:events:549:35)
      at endReadableNT (node:internal/streams/readable:1359:12)
      at processTicksAndRejections (node:internal/process/task_queues:82:21) {
    code: 'ESOCKET',
    isTransient: undefined
  },
  original: ConnectionError: Failed to connect to {sql_server_address}:1433 - socket hang up
      at Connection.socketError ([omitted]]backend/src/node_modules/tedious/src/connection.ts:2222:28)
      at Connection.socketEnd ([omitted]]backend/src/node_modules/tedious/src/connection.ts:2239:12)
      at Socket.<anonymous> ([omitted]]backend/src/node_modules/tedious/src/connection.ts:2013:37)
      at Socket.emit (node:events:549:35)
      at endReadableNT (node:internal/streams/readable:1359:12)
      at processTicksAndRejections (node:internal/process/task_queues:82:21) {
    code: 'ESOCKET',
    isTransient: undefined
  }
}
Error:: ConnectionError [SequelizeConnectionError]: Failed to connect to {sql_server_address}:1433 - socket hang up
    at ConnectionManager.connect ([omitted]]backend/src/node_modules/sequelize/src/dialects/mssql/connection-manager.js:138:17)
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at ConnectionManager._connect ([omitted]]backend/src/node_modules/sequelize/src/dialects/abstract/connection-manager.js:326:24)
    at [omitted]]backend/src/node_modules/sequelize/src/dialects/abstract/connection-manager.js:250:32
    at ConnectionManager.getConnection ([omitted]]backend/src/node_modules/sequelize/src/dialects/abstract/connection-manager.js:280:7)
    at [omitted]]backend/src/node_modules/sequelize/src/sequelize.js:629:26
    at Name2Service.getMessage ([omitted]]backend/src/src/app/app.service.ts:105:34)
    at bootstrapFactory ([omitted]]backend/src/src/common/common.utils.ts:42:5) {
  parent: ConnectionError: Failed to connect to {sql_server_address}:1433 - socket hang up
      at Connection.socketError ([omitted]]backend/src/node_modules/tedious/src/connection.ts:2222:28)
      at Connection.socketEnd ([omitted]]backend/src/node_modules/tedious/src/connection.ts:2239:12)
      at Socket.<anonymous> ([omitted]]backend/src/node_modules/tedious/src/connection.ts:2013:37)
      at Socket.emit (node:events:549:35)
      at endReadableNT (node:internal/streams/readable:1359:12)
      at processTicksAndRejections (node:internal/process/task_queues:82:21) {
    code: 'ESOCKET',
    isTransient: undefined
  },
  original: ConnectionError: Failed to connect to {sql_server_address}:1433 - socket hang up
      at Connection.socketError ([omitted]]backend/src/node_modules/tedious/src/connection.ts:2222:28)
      at Connection.socketEnd ([omitted]]backend/src/node_modules/tedious/src/connection.ts:2239:12)
      at Socket.<anonymous> ([omitted]]backend/src/node_modules/tedious/src/connection.ts:2013:37)
      at Socket.emit (node:events:549:35)
      at endReadableNT (node:internal/streams/readable:1359:12)
      at processTicksAndRejections (node:internal/process/task_queues:82:21) {
    code: 'ESOCKET',
    isTransient: undefined
  }
}

Configuration:

The connection is handled by sequelize.

{
    username: '[OMITTED]',
    password: '{OMITTED]',
    host: '{OMITTED]',
    logging: true,
}

I have tried passing recommended options but to no avail.

options: {
    keepAlive: true,
    encrypt: true,
    enableArithAbort: true,
}

As previously mentioned: this works fine up to Node v16

Software versions

  • NodeJS: 18.4.0
  • node-mssql: 8.1.2
  • tedious: 14.5.x | 14.6.x
  • sequelize: 6.20.1
  • SQL Server: SQL Server 2016 Service Pack 1 CU11 (13.0.4528.0)
@dhensby dhensby mentioned this issue Jun 21, 2022
Closed
@MichaelSun90
Copy link
Contributor

Hi @MartianH, I am not familiar with sequelize. Based on the information you provided, I just try to create a repo script:

const sequelize = new Sequelize('master', 'username', 'password', {
  host: 'localhost',
  dialect:'mssql'
});
async function asyncCall() {
  try {
    await sequelize.authenticate();
    console.log('Connection has been established successfully.');
  } catch (error) {
    console.error('Unable to connect to the database:', error);
  }
}

This script connected without any problem. I am not sure if this script close to what your program does. If not, can you provide a bit more details on your program. Another thing that you can try is, see if connection works directly with just tedious with out sequelize. In this way, we can accolated the cause of the issue.

@MartianH
Copy link
Author

MartianH commented Jun 22, 2022 •
edited
Loading

Greetings,

Connecting works just fine, it is when i send queries that the problem arise. I should have mentioned that I am using NestJS and I use sequelize for raw queries. Here is the DatabaseModule:

import { Module } from '@nestjs/common';
import { ConfigService } from '@nestjs/config';
import { SequelizeModule } from '@nestjs/sequelize';

import { MssqlObject } from '../config/config.interface';
@Module({
  imports: [
    SequelizeModule.forRootAsync({
      name: Database.NAME,
      inject: [ConfigService],
      useFactory: async (configService: ConfigService) => ({
        dialect: 'mssql',
        name: Database.NAME,
        ...configService.get<MssqlObject>(Database.NAME1),
      }),
    }),
    SequelizeModule.forRootAsync({
      name: Database.NAME2,
      inject: [ConfigService],
      useFactory: async (configService: ConfigService) => ({
        dialect: 'mssql',
        name: Database.NAME2,
        ...configService.get<MssqlObject>(Database.NAME2),
      })
    }),
  ],
  providers: [NameDatabaseService, Name2DatabaseService],
  exports: [NameDatabaseService, Name2DatabaseService],
})
export default class DatabaseModule { };

// config.interface.ts
mssqlName: {
    username: '[omitted]',
    password: '[omitted]',
    host: '[omitted]',
    logging: true,
  },

AppModule imports the DatabaseModule:

@Module({
  imports: [
    CacheModule,
    DatabaseModule,
    LoggerModule(NameService.name),
    ConfigModule.forRoot({
      load: [config],
      isGlobal: true,
    }),
    ScheduleModule.forRoot(),
  ],
  providers: [NameService], //This inherits `AppService` and is @Injectable
})
export class AppModule { }

AppService is then inherited by two services who inject the relevant dependencies using @Injectable.
All raw queries follow the pattern of SELECT {fields} from {table} and work fine under node 16.

// app.service.ts
import { Cron } from '@nestjs/schedule';


export class AppService  {
  constructor(
    protected cacheService: CacheService,
    protected db: DatabaseService,
    protected rrLogger: LoggerService,
  ) {
    // [omitted]
  }
@Cron(UpdateFrequency.HIGH)
  private async handleHighFrequencyCache(): Promise<void> {
    this.appLogger.log('UpdateFrequency::HIGH::CALLED');
    const { smsApplication } = await this.db.handleHighFrequencyQueries();
  }
}

// database.service.ts

export abstract class DatabaseService implements Common {
  constructor(
    protected readonly sequelize: Sequelize
  ) { }

  public async handleHighFrequencyQueries(): Promise<HighReturnObject> {
    const querySMSApplication = this.sequelize.query<SMSApplication>(
      // = 'select [omitted fields from [DB_NAME].[dbo].[TABLE]',
      cacheTables[CacheTableKey.SMS_APPLICATION], // Gets right query using constant key-value
      { type: QueryTypes.SELECT }
    );

    const smsApplication: SMSApplication[] = await querySMSApplication;
    return { smsApplication };
  }
}

// dbInherit.service.ts

import { Injectable } from '@nestjs/common';
import { InjectConnection } from '@nestjs/sequelize';
import { Sequelize } from 'sequelize-typescript';

import { Database } from '../database.constants';
import { DatabaseService } from '../database.service';

@Injectable()
export class NameDatabaseService extends DatabaseService {
  constructor(
    @InjectConnection(Database.NAME)
    public readonly sequelize: Sequelize
  ) { super(sequelize) }
}

All this works flawlessly. I only encountered this problem with v18.

@MichaelSun90
Copy link
Contributor

Hi @MartianH Thanks for the detail. By the way, have you try Node 16 with the latest version of Tedious - 14.6.1? If this combination works, then we can narrow the cause of the issue to Node 18 or support on tedious side for Node 18.

@MartianH
Copy link
Author

have you try Node 16 with the latest version of Tedious - 14.6.1?

Yes. Both 14.6.x and 14.5.x, as mentioned in the ticket. That said sequelize@6.21.1 use an older version of tedios (permalink). So I tried the alpha verrsion that uses 14.4.x but to no avail. Here are the current relevant dependencies:

   "@nestjs/sequelize": "^8.0.0",
  "sequelize": "^6.20.1",
  "sequelize-typescript": "^2.1.3",
   "tedious": "^14.6.1"
    "mssql": "^8.1.2",

@arthurschreiber
Copy link
Collaborator

Can you connect to your SQL Server version just using tedious, and no other packages? The error message you posted points to an error when opening a connection (it doesn't even get to the point where it would send a request).

My guess would be that something changed in Node.JS 17/18 around TLS handling. Probably your SQL Server is running on some old version of Windows where TLS settings are a bit "outdated", and the new Node.JS versions are more picky about what encryption algorithms are supported.

@MartianH
Copy link
Author

I made a custom script to retrieve a table using tedious: ^14.6.0. Code in question (sensitive data omitted):

const { Connection, Request } = require('tedious');

const config = {
  server: '[omitted]', // or "localhost"
  options: {},
  authentication: {
    type: "default",
    options: {
      userName: '[omitted]',
      password: '[omitted]',
      host: '[omitted]',
    }
  }
};

const connection = new Connection(config);

function executeStatement() {
  request = new Request(
    'select {filed`}, {field 2} from [DATABASE].[dbo].[tb_table]',
    function (err, rowCount) {
      if (err) {
        console.log(err);
      } else {
        console.log(rowCount + ' rows');
      }
    });

  request.on('row', function (columns) {
    console.log(columns[0]);
  });

  connection.execSql(request);
}

// Setup event handler when the connection is established. 
connection.on('connect', function (err) {
  if (err) {
    console.log('Error: ', err)
  }
  // If no error, then good to go...
  executeStatement();
});

// Initialize the connection.
connection.connect();

Works as intended in Node v14 and v16. But in Node v18:

Error:  ConnectionError: Failed to connect to [server_omitted]:1433 - socket hang up
    at Connection.socketError ([omitted_root_dir]/node_modules/tedious/lib/connection.js:1425:28)
    at Connection.socketEnd ([omitted_root_dir]/node_modules/tedious/lib/connection.js:1445:12)
    at Socket.<anonymous> ([omitted_root_dir]/node_modules/tedious/lib/connection.js:1196:14)
    at Socket.emit (node:events:549:35)
    at endReadableNT (node:internal/streams/readable:1359:12)
    at process.processTicksAndRejections (node:internal/process/task_queues:82:21) {
  code: 'ESOCKET',
  isTransient: undefined
}
RequestError: Requests can only be made in the LoggedIn state, not the SentTLSSSLNegotiation state
    at Connection.makeRequest ([omitted_root_dir]/node_modules/tedious/lib/connection.js:2252:24)
    at Connection.execSql ([omitted_root_dir]/node_modules/tedious/lib/connection.js:1782:10)
    at executeStatement ([omitted_root_dir]/query.js:33:14)
    at Connection.<anonymous> ([omitted_root_dir]/query.js:42:3)
    at Connection.emit (node:events:537:28)
    at Connection.emit ([omitted_root_dir]/node_modules/tedious/lib/connection.js:1079:18)
    at Connection.socketError ([omitted_root_dir]/node_modules/tedious/lib/connection.js:1425:12)
    at Connection.socketEnd ([omitted_root_dir]/node_modules/tedious/lib/connection.js:1445:12)
    at Socket.<anonymous> ([omitted_root_dir]/node_modules/tedious/lib/connection.js:1196:14)
    at Socket.emit (node:events:549:35) {
  code: 'EINVALIDSTATE',
  number: undefined,
  state: undefined,
  class: undefined,
  serverName: undefined,
  procName: undefined,
  lineNumber: undefined
}

@arthurschreiber
Copy link
Collaborator

This sounds very much like it's related to the OpenSSL 3.0 upgrade that happened in Node.js 17.

Can you try setting the --openssl-legacy-provider Node.js command line flag? In the meantime, I will see if we can improve our error reporting here to be a bit more user friendly.

@MartianH
Copy link
Author

MartianH commented Jun 23, 2022 •
edited
Loading

Reran aforementioned script with node --openssl-legacy-provider query.js as requested. Response is much of the same.

Error:  ConnectionError: Failed to connect to {server_url} - socket hang up
    at Connection.socketError ([root_dir_omitted]/node_modules/tedious/lib/connection.js:1425:28)
    at Connection.socketEnd ([root_dir_omitted]/node_modules/tedious/lib/connection.js:1445:12)
    at Socket.<anonymous> ([root_dir_omitted]/node_modules/tedious/lib/connection.js:1196:14)
    at Socket.emit (node:events:549:35)
    at endReadableNT (node:internal/streams/readable:1359:12)
    at process.processTicksAndRejections (node:internal/process/task_queues:82:21) {
  code: 'ESOCKET',
  isTransient: undefined
}
RequestError: Requests can only be made in the LoggedIn state, not the SentTLSSSLNegotiation state
    at Connection.makeRequest ([root_dir_omitted]/node_modules/tedious/lib/connection.js:2252:24)
    at Connection.execSql ([root_dir_omitted]/node_modules/tedious/lib/connection.js:1782:10)
    at executeStatement ([root_dir_omitted]/query.js:36:14)
    at Connection.<anonymous> ([root_dir_omitted]/query.js:45:3)
    at Connection.emit (node:events:537:28)
    at Connection.emit ([root_dir_omitted]/node_modules/tedious/lib/connection.js:1079:18)
    at Connection.socketError ([root_dir_omitted]/node_modules/tedious/lib/connection.js:1425:12)
    at Connection.socketEnd ([root_dir_omitted]/node_modules/tedious/lib/connection.js:1445:12)
    at Socket.<anonymous> ([root_dir_omitted]/node_modules/tedious/lib/connection.js:1196:14)
    at Socket.emit (node:events:549:35) {
  code: 'EINVALIDSTATE',
  number: undefined,
  state: undefined,
  class: undefined,
  serverName: undefined,
  procName: undefined,
  lineNumber: undefined
}

UPDATE: it seems to work when I set options.encrypt to false. Even without legacy provider. It is definitely a TLS issue.

@arthurschreiber
Copy link
Collaborator

I assume the SQL Server is running on Windows? Which version of Windows is it running on? SQL Server on Windows shares the Windows level TLS settings (like supported TLS versions and supported ciphers).

The list of TLS ciphers in NodeJS is usually quite restrictive (which makes sense in general), but if you're connecting to a server that only supports these outdated TLS settings, you can manually specify other options. See "Modifying the default TLS cipher suite" in the Node.js documentation.

@MartianH
Copy link
Author

MartianH commented Jun 29, 2022 •
edited
Loading

Greetings,

Apologies for the late response. I will have to discuss this with the team to see what TLS cipher is the issue.

@mShan0
Copy link
Contributor

mShan0 commented Jun 29, 2022 •
edited
Loading

Tedious logs which cipher is being used for the TLS negotiation through the debug output. Can you add something like this to your custom script and run it using Node 16 to see the output? There should be a line that says TLS negotiated (<cipher name>, <SSL/TLS version>).

    connection.on('debug', function(text) {
      console.log(text)
    });

@MartianH
Copy link
Author

MartianH commented Jul 4, 2022

Tedious logs which cipher is being used for the TLS negotiation through the debug output. Can you add something like this to your custom script and run it using Node 16 to see the output? There should be a line that says TLS negotiated (<cipher name>, <SSL/TLS version>).

    connection.on('debug', function(text) {
      console.log(text)
    });

Greetings, this is what was returned

State change: Initialized -> Connecting
(node:6836) Warning: `config.options.trustServerCertificate` will default to false in the future. To silence this message, specify a value explicitly in the config options
(Use `node --trace-warnings ...` to show where the warning was created)
connected to {MSSQL_SERVER}
State change: Connecting -> SentPrelogin
State change: SentPrelogin -> SentTLSSSLNegotiation
TLS negotiated (ECDHE-RSA-AES256-SHA384, TLSv1.2) <-- right here
State change: SentTLSSSLNegotiation -> SentLogin7WithStandardLogin
Packet size changed from 4096 to 4096
State change: SentLogin7WithStandardLogin -> LoggedInSendingInitialSql
State change: LoggedInSendingInitialSql -> LoggedIn
State change: LoggedIn -> SentClientRequest
State change: SentClientRequest -> LoggedIn

With Node 18

State change: Initialized -> Connecting
(node:7081) Warning: `config.options.trustServerCertificate` will default to false in the future. To silence this message, specify a value explicitly in the config options
(Use `node --trace-warnings ...` to show where the warning was created)
connected to {MSSQL_SERVER}
State change: Connecting -> SentPrelogin
State change: SentPrelogin -> SentTLSSSLNegotiation <-- fails here
socket ended
Failed to connect to {MSSQL_SERVER} - socket hang up
[ OMITTED STACK TRACE ]

@arthurschreiber
Copy link
Collaborator

Can you also post the output of node -p crypto.constants.defaultCoreCipherList | tr ':' '\n' running against Node 16 and Node 18?

@arthurschreiber
Copy link
Collaborator

ECDHE-RSA-AES256-SHA384 is a fairly recent cipher, and should be enabled in Node 18. 🤔

It's also available in my environment:

@arthurschreiber ➜ /workspaces/tedious $ node -p crypto.constants.defaultCoreCipherList | tr ':' '\n'
TLS_AES_256_GCM_SHA384
TLS_CHACHA20_POLY1305_SHA256
TLS_AES_128_GCM_SHA256
ECDHE-RSA-AES128-GCM-SHA256
ECDHE-ECDSA-AES128-GCM-SHA256
ECDHE-RSA-AES256-GCM-SHA384
ECDHE-ECDSA-AES256-GCM-SHA384
DHE-RSA-AES128-GCM-SHA256
ECDHE-RSA-AES128-SHA256
DHE-RSA-AES128-SHA256
ECDHE-RSA-AES256-SHA384
DHE-RSA-AES256-SHA384
ECDHE-RSA-AES256-SHA256
DHE-RSA-AES256-SHA256
HIGH
!aNULL
!eNULL
!EXPORT
!DES
!RC4
!MD5
!PSK
!SRP
!CAMELLIA
@arthurschreiber ➜ /workspaces/tedious $ node --version
v18.4.0

@arthurschreiber
Copy link
Collaborator

Another thing you can try is running node with the NODE_DEBUG environment variable set to tls. That will give you a lot more debug output regarding what is going on in the tls layer.

@MartianH
Copy link
Author

MartianH commented Jul 7, 2022

Can you also post the output of node -p crypto.constants.defaultCoreCipherList | tr ':' '\n' running against Node 16 and Node 18?

Node 16

TLS_AES_256_GCM_SHA384
TLS_CHACHA20_POLY1305_SHA256
TLS_AES_128_GCM_SHA256
ECDHE-RSA-AES128-GCM-SHA256
ECDHE-ECDSA-AES128-GCM-SHA256
ECDHE-RSA-AES256-GCM-SHA384
ECDHE-ECDSA-AES256-GCM-SHA384
DHE-RSA-AES128-GCM-SHA256
ECDHE-RSA-AES128-SHA256
DHE-RSA-AES128-SHA256
ECDHE-RSA-AES256-SHA384
DHE-RSA-AES256-SHA384
ECDHE-RSA-AES256-SHA256
DHE-RSA-AES256-SHA256
HIGH
!aNULL
!eNULL
!EXPORT
!DES
!RC4
!MD5
!PSK
!SRP
!CAMELLIA

Node 18

TLS_AES_256_GCM_SHA384
TLS_CHACHA20_POLY1305_SHA256
TLS_AES_128_GCM_SHA256
ECDHE-RSA-AES128-GCM-SHA256
ECDHE-ECDSA-AES128-GCM-SHA256
ECDHE-RSA-AES256-GCM-SHA384
ECDHE-ECDSA-AES256-GCM-SHA384
DHE-RSA-AES128-GCM-SHA256
ECDHE-RSA-AES128-SHA256
DHE-RSA-AES128-SHA256
ECDHE-RSA-AES256-SHA384
DHE-RSA-AES256-SHA384
ECDHE-RSA-AES256-SHA256
DHE-RSA-AES256-SHA256
HIGH
!aNULL
!eNULL
!EXPORT
!DES
!RC4
!MD5
!PSK
!SRP
!CAMELLIA

ECDHE-RSA-AES256-SHA384 is available in both instances.

@MartianH
Copy link
Author

MartianH commented Jul 7, 2022

NODE_DEBUG

Node 18

DEBUG:: State change: Initialized -> Connecting
(node:5941) Warning: `config.options.trustServerCertificate` will default to false in the future. To silence this message, specify a value explicitly in the config options
(Use `node --trace-warnings ...` to show where the warning was created)
DEBUG:: connected to {SQL_SERVER}
DEBUG:: State change: Connecting -> SentPrelogin
DEBUG:: State change: SentPrelogin -> SentTLSSSLNegotiation
DEBUG:: socket ended
DEBUG:: Failed to connect to {SQL_SERVER} - socket hang up
Error:  ConnectionError: Failed to connect to {SQL_SERVER} - socket hang up
    at Connection.socketError ([OMITTED]/node_modules/tedious/lib/connection.js:1425:28)
    at Connection.socketEnd ([OMITTED]/node_modules/tedious/lib/connection.js:1445:12)
    at Socket.<anonymous> ([OMITTED]/node_modules/tedious/lib/connection.js:1196:14)
    at Socket.emit (node:events:549:35)
    at endReadableNT (node:internal/streams/readable:1359:12)
    at process.processTicksAndRejections (node:internal/process/task_queues:82:21) {
  code: 'ESOCKET',
  isTransient: undefined
}
DEBUG:: Requests can only be made in the LoggedIn state, not the SentTLSSSLNegotiation state
RequestError: Requests can only be made in the LoggedIn state, not the SentTLSSSLNegotiation state
    at Connection.makeRequest ([OMITTED]/node_modules/tedious/lib/connection.js:2252:24)
    at Connection.execSql ([OMITTED]/node_modules/tedious/lib/connection.js:1782:10)
    at executeStatement ([OMITTED]/query.js:35:14)
    at Connection.<anonymous> ([OMITTED]/query.js:44:3)
    at Connection.emit (node:events:537:28)
    at Connection.emit ([OMITTED]/node_modules/tedious/lib/connection.js:1079:18)
    at Connection.socketError ([OMITTED]/node_modules/tedious/lib/connection.js:1425:12)
    at Connection.socketEnd ([OMITTED]/node_modules/tedious/lib/connection.js:1445:12)
    at Socket.<anonymous> ([OMITTED]/node_modules/tedious/lib/connection.js:1196:14)
    at Socket.emit (node:events:549:35) {
  code: 'EINVALIDSTATE',
  number: undefined,
  state: undefined,
  class: undefined,
  serverName: undefined,
  procName: undefined,
  lineNumber: undefined
}
DEBUG:: State change: SentTLSSSLNegotiation -> Final
DEBUG:: Connection lost - unexpected end of message stream
[OMITTED]/node_modules/tedious/lib/connection.js:2567
          throw err;
          ^

ConnectionError: Connection lost - unexpected end of message stream
    at Connection.socketError ([OMITTED]/node_modules/tedious/lib/connection.js:1429:26)
    at [OMITTED]/node_modules/tedious/lib/connection.js:2535:25
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
Emitted 'error' event on Connection instance at:
    at Connection.emit ([OMITTED]/node_modules/tedious/lib/connection.js:1079:18)
    at Connection.socketError ([OMITTED]/node_modules/tedious/lib/connection.js:1429:12)
    at [OMITTED]/node_modules/tedious/lib/connection.js:2535:25
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ESOCKET',
  isTransient: undefined
}

Node.js v18.4.0

Not that different.

@arthurschreiber
Copy link
Collaborator

Are you sure you are providing NODE_DEBUG correctly? E.g. NODE_DEBUG=tls node ...

You should see output like this:

TLS 11081: client _init handle? true
TLS 11081: client _start handle? true connecting? false requestOCSP? false
TLS 11081: client initRead handle? true buffered? 0
...

@MartianH
Copy link
Author 

DEBUG:: State change: Initialized -> Connecting
(node:3100) Warning: `config.options.trustServerCertificate` will default to false in the future. To silence this message, specify a value explicitly in the config options
(Use `node --trace-warnings ...` to show where the warning was created)
DEBUG:: connected to {mssql_server:port}
DEBUG:: State change: Connecting -> SentPrelogin
TLS 3100: client _init handle? true
TLS 3100: client _start handle? true connecting? false requestOCSP? false
DEBUG:: State change: SentPrelogin -> SentTLSSSLNegotiation
TLS 3100: client initRead handle? true buffered? 0
DEBUG:: socket ended
DEBUG:: Failed to connect to {mssql_server:port} - socket hang up
Error:  ConnectionError: Failed to connect to {mssql_server:port} - socket hang up
    at Connection.socketError ([omitted]]/node_modules/tedious/lib/connection.js:1425:28)
    at Connection.socketEnd ([omitted]]/node_modules/tedious/lib/connection.js:1445:12)
    at Socket.<anonymous> ([omitted]]/node_modules/tedious/lib/connection.js:1196:14)
    at Socket.emit (node:events:549:35)
    at endReadableNT (node:internal/streams/readable:1359:12)
    at process.processTicksAndRejections (node:internal/process/task_queues:82:21) {
  code: 'ESOCKET',
  isTransient: undefined
}
DEBUG:: Requests can only be made in the LoggedIn state, not the SentTLSSSLNegotiation state
RequestError: Requests can only be made in the LoggedIn state, not the SentTLSSSLNegotiation state
    at Connection.makeRequest ([omitted]]/node_modules/tedious/lib/connection.js:2252:24)
    at Connection.execSql ([omitted]]/node_modules/tedious/lib/connection.js:1782:10)
    at executeStatement ([omitted]]/query.js:35:14)
    at Connection.<anonymous> ([omitted]]/query.js:44:3)
    at Connection.emit (node:events:537:28)
    at Connection.emit ([omitted]]/node_modules/tedious/lib/connection.js:1079:18)
    at Connection.socketError ([omitted]]/node_modules/tedious/lib/connection.js:1425:12)
    at Connection.socketEnd ([omitted]]/node_modules/tedious/lib/connection.js:1445:12)
    at Socket.<anonymous> ([omitted]]/node_modules/tedious/lib/connection.js:1196:14)
    at Socket.emit (node:events:549:35) {
  code: 'EINVALIDSTATE',
  number: undefined,
  state: undefined,
  class: undefined,
  serverName: undefined,
  procName: undefined,
  lineNumber: undefined
}
DEBUG:: State change: SentTLSSSLNegotiation -> Final
DEBUG:: Connection lost - unexpected end of message stream
[omitted]]/node_modules/tedious/lib/connection.js:2567
          throw err;
          ^

ConnectionError: Connection lost - unexpected end of message stream
    at Connection.socketError ([omitted]]/node_modules/tedious/lib/connection.js:1429:26)
    at [omitted]]/node_modules/tedious/lib/connection.js:2535:25
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
Emitted 'error' event on Connection instance at:
    at Connection.emit ([omitted]]/node_modules/tedious/lib/connection.js:1079:18)
    at Connection.socketError ([omitted]]/node_modules/tedious/lib/connection.js:1429:12)
    at [omitted]]/node_modules/tedious/lib/connection.js:2535:25
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ESOCKET',
  isTransient: undefined
}

Node.js v18.4.0

The logs after NODE_DEBUG=tls node query.js

@Nedlinin
Copy link
Contributor

Nedlinin commented Aug 2, 2022

Just chiming in to say I have the exact same issue as @MartianH.

Server:
SQL Server 2014 (12.0.5223.6)
Windows Server 2012R2

Works great in Node 16 but upgrading to Node 18 causes the exact same failure as posted above.

connected to db.xxxxxx.local:1433
State change: Connecting -> SentPrelogin
TLS 15488: client _init handle? true
TLS 15488: client _start handle? true connecting? false requestOCSP? false
State change: SentPrelogin -> SentTLSSSLNegotiation
TLS 15488: client initRead handle? true buffered? 0
socket ended
Failed to connect to db.xxxxxxx.local:1433 - socket hang up
State change: SentTLSSSLNegotiation -> Final
Connection lost - unexpected end of message stream

@mShan0
Copy link
Contributor

mShan0 commented Aug 8, 2022

This may be an issue with Node 18 defaulting to TLS 1.3 which is not yet supported by Tedious.

The latest version of Tedious makes the default version TLS 1.2. See if that works for you.
You can also set the default TLS version through your config like this (you don't have to do this with Tedious 15.0.1):

config: {
  ...,
  options: {
    cryptoCredentialsDetails: {
      maxVersion: 'TLSv1.2'
    }
  }
}

@Nedlinin
Copy link
Contributor

Nedlinin commented Aug 8, 2022

@mShan0 : In my case I'm already at 15.0.1; I went ahead and tested adding this option just to be sure and it was the same story.

@ihqtim
Copy link

ihqtim commented Oct 16, 2022

Same issue here Microsoft SQL Server 2016 SP3 on on Windows Server 2012 R2 Standard. Node 18.10, tedious 14.7 (tested and found same issue on 15.10).

Setting options.encrypt = false does resolve the issue however this not an good workaround for obvious reasons.

@MichaelSun90
Copy link
Contributor

Hi @ihqtim , are you try to connect to a Azure data source? We recommended to set encrypt = true when try to connection to a Azure data source. If you have to work with a encrypted connection, you could try to either provide a certificate though options.cryptoCredentialsDetails or you can set options.trustServerCertificate. There is more details about these two options on this page. Hope these can help you with your connection issue.

@ihqtim
Copy link

ihqtim commented Oct 19, 2022 •
edited
Loading

Hey @MichaelSun90, this specific DB server is not in Azure however our internal processes require all communication with DB servers be encrypted. Re option "options.trustServerCertificate" - yes, we already use this option (we needed this even with Node 16).

Tedious works fine when connecting to this DB server using Node 16, however in Node 18 (same codebase) tedious is unable to connect to the DB server. We can connect to other DB servers with tedious + Node 18, just not this one, so there is an environmental factor at play.

I am wondering if this issue may be the root cause, i.e. something to do with IPv6 + encryption:
nodejs/node#40537

Will need to try forcing the connection to use IP v4 to see if it resolves.

=== Follow-up:
Forcing the use of IP v4 did not resolve

@arthurschreiber
Copy link
Collaborator

I'm really at a loss here. Something changed between Node.js 16 and Node.js 18 in the TLS handling, but it's not clear to me what changed and if this can even be fixed from the tediousjs side or if it requires a fix in Node.js itself.

Can you do me a favor and try and see if you can reproduce this in Node.js 17.0.0? If this is not broken in Node.js 17.0.0, can you try all other 17.x versions to see which of them introduced the breaking change?

I know this is a lot to ask for, but I can't reproduce this so there's nothing I can do from my side. 😢

@ihqtim
Copy link

ihqtim commented Oct 20, 2022 •
edited
Loading

Hey @arthurschreiber,

I found a relatively simple way to reproduce using an AWS EC2 instance:

  1. Create an EC2 instance from the standard "Quick Start" AMIs using "Microsoft Windows Server 2012 R2 with SQL Server 2016 Standard" (I used size c3.large)
  2. RDP onto it and install desired node version for testing
  3. Create a temp dir, e.g. C:\Tmp\ and copy the index.js below into the dir (** adjust the password and domain to match the instance's Administrator password)
  4. Launch a command prompt into the C:\Tmp dir and run npm install tedious, then run node .
//index.js
const { Connection, Request } = require('tedious');

const config = {
  server: "localhost",
  options: {
    trustServerCertificate: true,
  },
  authentication: {
    type: "ntlm",
    options: {
      userName: 'Administrator',
      password: '***redacted***',
      domain: 'ip-172-31-22-11.ec2.internal', // change this!
    }
  }
};

const connection = new Connection(config);

function executeStatement() {
  request = new Request(
    'select * FROM sys.databases',
    function (err, rowCount) {
      if (err) {
        console.log(err);
      } else {
        console.log(rowCount + ' rows');
      }
    });

  request.on('row', function (columns) {
    console.log(columns[0]);
  });

  connection.execSql(request);
}

// Setup event handler when the connection is established. 
connection.on('connect', function (err) {
  if (err) {
    console.log('Error: ', err)
  }
  // If no error, then good to go...
  executeStatement();
});

// Initialize the connection.
connection.connect();

This will succeed (return rows) for node 16.18.0 (latest), and an error for node 17 and later, here is the error I got on node 17.0.0

C:\Tmp>node -v
v17.0.0

C:\Tmp>node .
Error:  ConnectionError: Failed to connect to localhost:1433 - socket hang up
    at Connection.socketError (C:\Tmp\node_modules\tedious\lib\connection.js:139
9:28)
    at Connection.socketEnd (C:\Tmp\node_modules\tedious\lib\connection.js:1419:
12)
    at Socket.<anonymous> (C:\Tmp\node_modules\tedious\lib\connection.js:1161:16
)
    at Socket.emit (node:events:402:35)
    at endReadableNT (node:internal/streams/readable:1340:12)
    at processTicksAndRejections (node:internal/process/task_queues:83:21) {
  code: 'ESOCKET',
  isTransient: undefined
}
RequestError: Requests can only be made in the LoggedIn state, not the SentTLSSS
LNegotiation state
    at Connection.makeRequest (C:\Tmp\node_modules\tedious\lib\connection.js:220
8:24)
    at Connection.execSql (C:\Tmp\node_modules\tedious\lib\connection.js:1738:10
)
    at executeStatement (C:\Tmp\index.js:35:14)
    at Connection.<anonymous> (C:\Tmp\index.js:44:3)
    at Connection.emit (node:events:390:28)
    at Connection.emit (C:\Tmp\node_modules\tedious\lib\connection.js:1048:18)
    at Connection.socketError (C:\Tmp\node_modules\tedious\lib\connection.js:139
9:12)
    at Connection.socketEnd (C:\Tmp\node_modules\tedious\lib\connection.js:1419:
12)
    at Socket.<anonymous> (C:\Tmp\node_modules\tedious\lib\connection.js:1161:16
)
    at Socket.emit (node:events:402:35) {
  code: 'EINVALIDSTATE',
  number: undefined,
  state: undefined,
  class: undefined,
  serverName: undefined,
  procName: undefined,
  lineNumber: undefined
}
C:\Tmp\node_modules\tedious\lib\connection.js:2434
          throw err;
          ^

ConnectionError: Connection lost - unexpected end of message stream
    at Connection.socketError (C:\Tmp\node_modules\tedious\lib\connection.js:140
3:26)
    at C:\Tmp\node_modules\tedious\lib\connection.js:2406:25
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
Emitted 'error' event on Connection instance at:
    at Connection.emit (C:\Tmp\node_modules\tedious\lib\connection.js:1048:18)
    at Connection.socketError (C:\Tmp\node_modules\tedious\lib\connection.js:140
3:12)
    at C:\Tmp\node_modules\tedious\lib\connection.js:2406:25
    at processTicksAndRejections (node:internal/process/task_queues:96:5) {
  code: 'ESOCKET',
  isTransient: undefined
}

Node.js v17.0.0

@arthurschreiber
Copy link
Collaborator

Bildschirmfoto 2022-10-21 um 23 01 54

Looks like newer Node.js versions default to using TLS 1.2, but that's not supported by SQL Server 2012 R2 and earlier. Apparently, there are some registry changes that you can apply on the server to support TLS 1.2. But I'm also trying to figure out how an older version of TLS can be specified by the client side.

@arthurschreiber
Copy link
Collaborator 

    cryptoCredentialsDetails: {
      maxVersion: 'TLSv1.1',
      minVersion: 'TLSv1'
    }

That makes the TLS connection go a bit further, but then fail with:

TLS 8519: client onerror Error: C007BEEB257F0000:error:0A0C0103:SSL routines:tls_process_key_exchange:internal error:../deps/openssl/openssl/ssl/statem/statem_clnt.c:2260:

@arthurschreiber
Copy link
Collaborator

arthurschreiber commented Oct 22, 2022 •
edited
Loading

Ok, I think I finally figured out what's going. 🙈

TLS Signature Algorithms extensions

Some background information first. TLS has a client side extension that is part of the Hello message, called Signature Algorithms. That extension allows a client to specify what certificate signature algorithms it supports, and the TLS Server can then respond with a certificate that matches one of the allowed algorithms.

If the Server does not have a signature that matches any of the signature algorithms specified, it can simply close the connection after receiving the Client's Hello message, as clearly the client won't be able to do anything with it (otherwise it wouldn't have specified which algorithms it can support).

OpenSSL 3.0 / Node.js 17+

I'm not sure why this changed and whether this changed in OpenSSL or Node.js, but since the switch to OpenSSL 3.0 with the Node.js 17+ releases, the client TLS Hello message sent by Node.js does not contain any signature algorithm that uses SHA-1. Use of SHA-1 in TLS certificate is hugely problematic, as they basically give zero security over an unencrypted connection. That's why I believe this signature algorithm is not specified - it's better to just fail hard and disallow encrypted but totally insecure connections.

Unfortunately, even explicitly specifying RSA+SHA1 in the signature algorithms option in Node.js does not allow disabling this behaviour.

Default Server certificate for SQL Server

In SQL Server, if you don't install a custom certificate, SQL Server will generate a certificate for you. This is called the "Fallback certificate". In older versions of SQL Server / Windows, this certificate uses the SHA1 algorithm. This, plus the behaviour I described above, leads to what you're seeing here.

This certificate can be swapped with another self-signed certificate, which should fix this behaviour.

TL;DR

Node.js 17+ does not support certificates that use SHA1. The default certificate generated by SQL Server uses SHA1. That's why the connection is closed by SQL Server after receiving the list of supported certificate algorithms from SQL Server. Switch the certificate on SQL Server to one that does not use SHA1, or switch to an unencrypted connection (absolutely not recommended), because using a SHA1 certificate is worse than using no encryption at all, as it adds no security but makes you think your connection is encrypted.

@arthurschreiber
Copy link
Collaborator

Actually, I now found a way to workaround this, but I'd only use it if there's really no way to get a more secure certificate installed:

    cryptoCredentialsDetails: {
      ciphers: 'DEFAULT@SECLEVEL=0',
    }

Any other security level higher than 0 does change the list of sigalgs that are sent by OpenSSL in a way that makes the TLS connection fail.

@ihqtim
Copy link

ihqtim commented Oct 24, 2022

Actually, I now found a way to workaround this, but I'd only use it if there's really no way to get a more secure certificate installed:

    cryptoCredentialsDetails: {
      ciphers: 'DEFAULT@SECLEVEL=0',
    }

Any other security level higher than 0 does change the list of sigalgs that are sent by OpenSSL in a way that makes the TLS connection fail.

Great work, thanks @arthurschreiber, have confirmed that the suggested DEFAULT@SECLEVEL=0 value does work for us & also appreciate the advice to deploy a more secure cert onto the SQL server.

@MartianH
Copy link
Author

MartianH commented Oct 25, 2022 •
edited
Loading

Actually, I now found a way to workaround this, but I'd only use it if there's really no way to get a more secure certificate installed:

    cryptoCredentialsDetails: {
      ciphers: 'DEFAULT@SECLEVEL=0',
    }

Any other security level higher than 0 does change the list of sigalgs that are sent by OpenSSL in a way that makes the TLS connection fail.

Greetings,

This seems to have finally fixed the issue.

Edit: Seems to run fine, I think this should be emphasised somewhere in the documentation or README as many SQL servers use outdated certificates. Especially, since v18 has officially reached active LTS status today [releases].

@ysageev ysageev mentioned this issue Nov 10, 2022
Closed
@dhensby dhensby mentioned this issue Mar 6, 2023
Closed
@olafkrueger
Copy link

@arthurschreiber
Thanks for your effort and this great work!

This might be helpful for others:
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-sql-server-encryption?view=sql-server-ver16#sql-server-generated-self-signed-certificates

It might be also worth to mention to not forget to add the options property to the config object:

options: {
    encrypt: true,
    trustServerCertificate: true,
    cryptoCredentialsDetails: {
      ciphers: 'DEFAULT@SECLEVEL=0',
    }
}

@taozuhong
Copy link

taozuhong commented Aug 18, 2023 •
edited
Loading

SQLServer could provide a C library that could be used by C/C++/PHP/ODBC/Ruby/Nodejs/JDBC, then all problem will be fixed quickly.

@dewrox
Copy link

dewrox commented Nov 9, 2023 •
edited
Loading

SQLServer could provide a library that could use by C/C++/PHP/ODBC/Ruby/Nodejs/JDBC, then all problem will be fixed quickly.

There is, it is the Microsoft.Data.SqlClient package however version 4.0 and up will still be broken. See dotnet/SqlClient#1210 for the breaking changes with Microsoft.Data.SqlClient v4.0 and up. See Certificate Requirements for the security Certificate that you need to install on your SQL Server and ( Instructions )

@josser
Copy link

josser commented Nov 16, 2023

Something has been changed again, so neither ciphers nor maxVersion/minVersion tricks are working :(
Any solution except downgrade to node 16?

@Angelelz
Copy link

Angelelz commented Dec 4, 2023

I'm hitting this issue as well. I'm running a docker instance in an M1 mac. The way we set it up is we create a docker img on the fly just to run the tests and destroy it afterwards. The weird part is, when I point the connection to another docker instance running on the same mac but it's not created on the fly, it connects without issues. I don't think I have any differences between the long running instance and the one I create on the fly but I'll investigate further.

@Angelelz
Copy link

Angelelz commented Dec 5, 2023

I figured out my problem. I had a database name in the config that didn't exists in the server. I just deleted the database from the config and it worked. Is there a way to setup a database name in the docker file?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests