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

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.

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.

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

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

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

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.

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: https://github.com/nodejs/node/issues/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

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