mssql ConnectionError

[sant123]

Hi, I'm trying to run this code with mssql:

import sql from "npm:mssql@10.0.1";

const sqlConfig = {
  server: "localhost",
  user: "sa",
  password: "",
  database: "my_db",
  options: {
    trustServerCertificate: true,
  },
};

const pool = await sql.connect(sqlConfig);
const result = await pool.request().execute("spGetPurchases");

console.dir(result);

But I get Uncaught (in promise) ConnectionError: Failed to connect to localhost:1433 in 15000ms

I decided to do the same with Node v18.16.0 and worked perfectly:

const sql = require("mssql");

const sqlConfig = {
  server: "localhost",
  user: "sa",
  password: "",
  database: "my_db",
  options: {
    trustServerCertificate: true,
  },
};

(async () => {
  const pool = await sql.connect(sqlConfig);
  const result2 = await pool.request().execute("spGetPurchases");

  console.dir(result2);
})();

The SQL Server is running inside a Docker container:

I'm using Deno latest version as of today v1.37.0

[sant123]

Alright I found some logs that may be helpful. I created a second project using Node so debugging is easier:

For Node v18.16.0, open node_modules/tedious/lib/connection.js#1040

In line 1041 add a console.log ->

Back in Deno project, set vendor: true in deno.json and open node_modules/.deno/tedious@16.4.1/node_modules/tedious/lib/connection.js#1040

In line 1041 add a console.log ->

Node logs:

State change: Initialized -> Connecting
connected to localhost:1433
State change: Connecting -> SentPrelogin
State change: SentPrelogin -> SentTLSSSLNegotiation
TLS negotiated (ECDHE-RSA-AES128-GCM-SHA256, TLSv1.2)
State change: SentTLSSSLNegotiation -> SentLogin7WithStandardLogin
Packet size changed from 4096 to 4096
State change: SentLogin7WithStandardLogin -> LoggedInSendingInitialSql
State change: LoggedInSendingInitialSql -> LoggedIn
State change: LoggedIn -> Final
connection to localhost:1433 closed
State is already Final
State change: Initialized -> Connecting
connected to localhost:1433
State change: Connecting -> SentPrelogin
State change: SentPrelogin -> SentTLSSSLNegotiation
TLS negotiated (ECDHE-RSA-AES128-GCM-SHA256, TLSv1.2)
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
State change: LoggedIn -> SentClientRequest
State change: SentClientRequest -> LoggedIn

Deno logs:

State change: Initialized -> Connecting
connected to localhost:1433
State change: Connecting -> SentPrelogin
State change: SentPrelogin -> SentTLSSSLNegotiation
Failed to connect to localhost:1433 in 15000ms
State change: SentTLSSSLNegotiation -> Final
connection to localhost:1433 closed
State is already Final

It seems there is a issue when attempting to negotiate the TLS certificate.

[massalinux]

hi @sant123 , i got the same issue, while is not a fix i managed to proceed with encrypt: false

[mmastrac]

Could you run:

openssl s_client -connect localhost:1433

... and paste the output here? It may be that the underlying cipher supported by the DB engine is not supported by our TLS code.

[massalinux]

8085DC0201000000:error:8000003D:system library:BIO_connect:Connection refused:crypto/bio/bio_sock2.c:114:calling connect() 8085DC0201000000:error:10000067:BIO routines:BIO_connect:connect error:crypto/bio/bio_sock2.c:116: connect:errno=61

[mmastrac]

It looks like the TLS cipher should be supported -- I will make sure that we correctly enable it for our tls library.

[mmastrac]

@massalinux Could you run this from Deno 1.38.2, but using the address of your database?

let hostname = 'HOSTNAME';
let port = PORT_NUMBER;
let conn = await Deno.connectTls({hostname, port});
console.log(await conn.handshake();)

[massalinux]

@mmastrac here it is

error: Uncaught (in promise) ConnectionRefused: Connection refused (os error 61) let conn = await Deno.connectTls({hostname, port}); ^ at async Object.connectTls (ext:deno_net/02_tls.js:36:51) at async file:///Users/martino/d/varie/variedeno/main.ts:3:12

[mmastrac]

Hmm. It looks like the server is not running or is not accessible. Is the docker container running? Can you access it any other way?

You maybe need to use 0.0.0.0 as a hostname

[massalinux]

Hi Matt, actually i can connect to that address at that port to SQLServer via mssql (with the encrypt: false option), do you want me to run additional tests?

[sant123]

openssl s_client -connect localhost:1433

CONNECTED(00000003)
009E1113B37F0000:error:0A000126:SSL routines:ssl3_read_n:unexpected eof while reading:ssl/record/rec_layer_s3.c:303:
---
no peer certificate available
---
No client certificate CA names sent
---
SSL handshake has read 0 bytes and written 302 bytes
Verification: OK
---
New, (NONE), Cipher is (NONE)
This TLS version forbids renegotiation.
Compression: NONE
Expansion: NONE
No ALPN negotiated
Early data was not sent
Verify return code: 0 (ok)
---

Running this code with Deno v1.38.3

const hostname = "localhost";
const port = 1433;
const conn = await Deno.connectTls({ hostname, port });
console.log(await conn.handshake());

My Docker instance:

[sant123]

These are the logs from the container:

2023-11-27 17:08:28.72 Logon       Error: 17821, Severity: 20, State: 1.
2023-11-27 17:08:28.72 Logon       A valid TLS certificate is not configured to accept strict (TDS 8.0 and above) connections. The connection has been closed.