search

denoland / deno

A modern runtime for JavaScript and TypeScript.
https://deno.com
MIT License
98k stars 5.39k forks source link

mssql ConnectionError #20594

Open sant123 opened 1 year ago

sant123 commented 1 year ago

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

image

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);
})();

image

The SQL Server is running inside a Docker container:

image

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

sant123 commented 1 year ago

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 ->

image

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 ->

image

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 commented 12 months ago

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

mmastrac commented 12 months ago

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 commented 12 months ago

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 commented 12 months ago

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

mmastrac commented 12 months ago

@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 commented 12 months ago

@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 commented 12 months ago

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 commented 12 months ago

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 commented 12 months ago

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());

image

My Docker instance:

image

sant123 commented 12 months ago

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.
jtomaszewski commented 11 months ago

I have the same troubles as in https://github.com/denoland/deno/issues/20594#issuecomment-1730047671 while connecting to an Azure MS SQL server using mssql library .

In node.js, I can connect easily. In deno, when running 

import mssql from "npm:mssql";
await mssql.connect("Server=abcdef-dev-sql-db.123456.database.windows.net,1433;Database=somedb;User Id=someuser;Password=somepassword"); # I've replaced the actual credentials here with fake ones ofc

I get this output:

State change: Initialized -> Connecting
connected to abcdef-dev-sql-db.123456.database.windows.net:1433
State change: Connecting -> SentPrelogin
State change: SentPrelogin -> SentTLSSSLNegotiation
connection to abcdef-dev-sql-db.123456.database.windows.net:1433 closed
State change: SentTLSSSLNegotiation -> Final
Connection lost - read ECONNRESET
Uncaught ConnectionError: Connection lost - read ECONNRESET
    at file:///Users/jacek/src/myevaluations/windmill/node_modules/.deno/mssql@10.0.1/node_modules/mssql/lib/tedious/connection-pool.js:85:17
    at Connection.onError (file:///Users/jacek/src/myevaluations/windmill/node_modules/.deno/tedious@16.6.1/node_modules/tedious/lib/connection.js:842:9)
    at Object.onceWrapper (ext:deno_node/_events.mjs:508:26)
    at Connection.emit (ext:deno_node/_events.mjs:395:35)
    at Connection.emit (file:///Users/jacek/src/myevaluations/windmill/node_modules/.deno/tedious@16.6.1/node_modules/tedious/lib/connection.js:959:18)
    at Connection.socketError (file:///Users/jacek/src/myevaluations/windmill/node_modules/.deno/tedious@16.6.1/node_modules/tedious/lib/connection.js:1345:12)
    at Socket.<anonymous> (file:///Users/jacek/src/myevaluations/windmill/node_modules/.deno/tedious@16.6.1/node_modules/tedious/lib/connection.js:1048:12)
    at Socket.emit (ext:deno_node/_stream.mjs:1854:9)
    at emitErrorNT (ext:deno_node/_stream.mjs:1572:13)
    at emitErrorCloseNT (ext:deno_node/_stream.mjs:1544:7)

deno --version output:

deno 1.38.5 (release, aarch64-apple-darwin)
v8 12.0.267.1
typescript 5.2.2

Testing Deno.connectTls:

input:

let hostname = 'abcdef-dev-sql-db.123456.database.windows.net';
let port = 1433;
let conn = await Deno.connectTls({hostname, port});
console.log(await conn.handshake())

output:

[Object: null prototype] { alpnProtocol: null }

Output for openssl s_client -connect abcdef-dev-sql-db.123456.database.windows.net:1433:

depth=2 C=US, O=DigiCert Inc, OU=www.digicert.com, CN=DigiCert Global Root G2
verify return:1
depth=1 C=US, O=Microsoft Corporation, CN=Microsoft Azure TLS Issuing CA 05
verify return:1
depth=0 C=US, ST=WA, L=Redmond, O=Microsoft Corporation, CN=502330f57f6f.database.windows.net
verify return:1
Connecting to 10.10.1.254
CONNECTED(00000005)
---
Certificate chain
 0 s:C=US, ST=WA, L=Redmond, O=Microsoft Corporation, CN=502330f57f6f.database.windows.net
   i:C=US, O=Microsoft Corporation, CN=Microsoft Azure TLS Issuing CA 05
   a:PKEY: rsaEncryption, 2048 (bit); sigalg: RSA-SHA384
   v:NotBefore: Nov 16 19:33:25 2023 GMT; NotAfter: Jun 27 23:59:59 2024 GMT
 1 s:C=US, O=Microsoft Corporation, CN=Microsoft Azure TLS Issuing CA 05
   i:C=US, O=DigiCert Inc, OU=www.digicert.com, CN=DigiCert Global Root G2
   a:PKEY: rsaEncryption, 4096 (bit); sigalg: RSA-SHA384
   v:NotBefore: Jul 29 12:30:00 2020 GMT; NotAfter: Jun 27 23:59:59 2024 GMT
---
Server certificate
-----BEGIN CERTIFICATE-----
MIIJLjCCBxa....
-----END CERTIFICATE-----
subject=C=US, ST=WA, L=Redmond, O=Microsoft Corporation, CN=502330f57f6f.database.windows.net
issuer=C=US, O=Microsoft Corporation, CN=Microsoft Azure TLS Issuing CA 05
---
No client certificate CA names sent
Peer signing digest: SHA256
Peer signature type: RSA-PSS
Server Temp Key: ECDH, secp384r1, 384 bits
---
SSL handshake has read 4534 bytes and written 865 bytes
Verification: OK
---
New, TLSv1.3, Cipher is TLS_AES_256_GCM_SHA384
Server public key is 2048 bit
This TLS version forbids renegotiation.
Compression: NONE
Expansion: NONE
No ALPN negotiated
Early data was not sent
Verify return code: 0 (ok)
---
read:errno=54
---
Post-Handshake New Session Ticket arrived:
SSL-Session:
    Protocol  : TLSv1.3
    Cipher    : TLS_AES_256_GCM_SHA384
    Session-ID: 3DC1...
    Session-ID-ctx: 
    Resumption PSK: C2B4...
    PSK identity: None
    PSK identity hint: None
    SRP username: None
    TLS session ticket lifetime hint: 36000 (seconds)
    TLS session ticket:
    0000 - d1 ...
    0010 - 2c ...

    Start Time: 1702287665
    Timeout   : 7200 (sec)
    Verify return code: 0 (ok)
    Extended master secret: no
    Max Early Data: 0
---
read R BLOCK

@mmastrac let me know if I can help anyhow differently in this.

Unfortunately this currently blocks me in using mssql & deno for my project, as I need it to connect to Azure MS SQL :(

P.S. My mssql server requires VPN connection. I've got enabled it globally on my OS X. I can connect to mssql server using node.js and DataGrip easily (without the need of preconfiguring any certificates in the connection settings). I tried adding DENO_TLS_CA_STORE=system to the deno but it doesn't help either.

sant123 commented 10 months ago

Using Deno v1.40.1 now logs the following warnings:

warning: Use of deprecated "Deno.TcpConn.rid" API. This API will be removed in Deno 2.

Stack trace:
  at ext:deno_node/internal_binding/tcp_wrap.ts:295:14

hint: Use `Deno.TcpConn` instance methods instead.
hint: It appears this API is used by a remote dependency. Try upgrading to the latest version of that dependency.

warning: Use of deprecated "Deno.TcpConn.rid" API. This API will be removed in Deno 2.

hint: Use `Deno.TcpConn` instance methods instead.

warning: Use of deprecated "Deno.TcpConn.rid" API. This API will be removed in Deno 2.

hint: Use `Deno.TcpConn` instance methods instead.

warning: Use of deprecated "Deno.TcpConn.rid" API. This API will be removed in Deno 2.

hint: Use `Deno.TcpConn` instance methods instead.

error: Uncaught (in promise) ConnectionError: Failed to connect to localhost:1433 in 15000ms
    at file:///home/sant821/.cache/deno/npm/registry.npmjs.org/mssql/10.0.2/lib/tedious/connection-pool.js:85:17
    at Connection.onConnect (file:///home/sant821/.cache/deno/npm/registry.npmjs.org/tedious/16.6.1/lib/connection.js:838:9)
    at Object.onceWrapper (ext:deno_node/_events.mjs:508:26)
    at Connection.emit (ext:deno_node/_events.mjs:383:28)
    at Connection.emit (file:///home/sant821/.cache/deno/npm/registry.npmjs.org/tedious/16.6.1/lib/connection.js:959:18)
    at Connection.connectTimeout (file:///home/sant821/.cache/deno/npm/registry.npmjs.org/tedious/16.6.1/lib/connection.js:1209:10)
    at Timeout._onTimeout (file:///home/sant821/.cache/deno/npm/registry.npmjs.org/tedious/16.6.1/lib/connection.js:1154:12)
    at cb (ext:deno_node/internal/timers.mjs:63:31)
    at Object.action (ext:deno_web/02_timers.js:154:11)
    at handleTimerMacrotask (ext:deno_web/02_timers.js:68:10)
pcc-huangm commented 8 months ago

I have the same troubles as in #20594 (comment) while connecting to an Azure MS SQL server using mssql library .

In node.js, I can connect easily. In deno, when running

import mssql from "npm:mssql";
await mssql.connect("Server=abcdef-dev-sql-db.123456.database.windows.net,1433;Database=somedb;User Id=someuser;Password=somepassword"); # I've replaced the actual credentials here with fake ones ofc

I get this output:

State change: Initialized -> Connecting
connected to abcdef-dev-sql-db.123456.database.windows.net:1433
State change: Connecting -> SentPrelogin
State change: SentPrelogin -> SentTLSSSLNegotiation
connection to abcdef-dev-sql-db.123456.database.windows.net:1433 closed
State change: SentTLSSSLNegotiation -> Final
Connection lost - read ECONNRESET
Uncaught ConnectionError: Connection lost - read ECONNRESET
    at file:///Users/jacek/src/myevaluations/windmill/node_modules/.deno/mssql@10.0.1/node_modules/mssql/lib/tedious/connection-pool.js:85:17
    at Connection.onError (file:///Users/jacek/src/myevaluations/windmill/node_modules/.deno/tedious@16.6.1/node_modules/tedious/lib/connection.js:842:9)
    at Object.onceWrapper (ext:deno_node/_events.mjs:508:26)
    at Connection.emit (ext:deno_node/_events.mjs:395:35)
    at Connection.emit (file:///Users/jacek/src/myevaluations/windmill/node_modules/.deno/tedious@16.6.1/node_modules/tedious/lib/connection.js:959:18)
    at Connection.socketError (file:///Users/jacek/src/myevaluations/windmill/node_modules/.deno/tedious@16.6.1/node_modules/tedious/lib/connection.js:1345:12)
    at Socket.<anonymous> (file:///Users/jacek/src/myevaluations/windmill/node_modules/.deno/tedious@16.6.1/node_modules/tedious/lib/connection.js:1048:12)
    at Socket.emit (ext:deno_node/_stream.mjs:1854:9)
    at emitErrorNT (ext:deno_node/_stream.mjs:1572:13)
    at emitErrorCloseNT (ext:deno_node/_stream.mjs:1544:7)

deno --version output:

deno 1.38.5 (release, aarch64-apple-darwin)
v8 12.0.267.1
typescript 5.2.2

Testing Deno.connectTls:

input:

let hostname = 'abcdef-dev-sql-db.123456.database.windows.net';
let port = 1433;
let conn = await Deno.connectTls({hostname, port});
console.log(await conn.handshake())

output:

[Object: null prototype] { alpnProtocol: null }

Output for openssl s_client -connect abcdef-dev-sql-db.123456.database.windows.net:1433:

depth=2 C=US, O=DigiCert Inc, OU=www.digicert.com, CN=DigiCert Global Root G2
verify return:1
depth=1 C=US, O=Microsoft Corporation, CN=Microsoft Azure TLS Issuing CA 05
verify return:1
depth=0 C=US, ST=WA, L=Redmond, O=Microsoft Corporation, CN=502330f57f6f.database.windows.net
verify return:1
Connecting to 10.10.1.254
CONNECTED(00000005)
---
Certificate chain
 0 s:C=US, ST=WA, L=Redmond, O=Microsoft Corporation, CN=502330f57f6f.database.windows.net
   i:C=US, O=Microsoft Corporation, CN=Microsoft Azure TLS Issuing CA 05
   a:PKEY: rsaEncryption, 2048 (bit); sigalg: RSA-SHA384
   v:NotBefore: Nov 16 19:33:25 2023 GMT; NotAfter: Jun 27 23:59:59 2024 GMT
 1 s:C=US, O=Microsoft Corporation, CN=Microsoft Azure TLS Issuing CA 05
   i:C=US, O=DigiCert Inc, OU=www.digicert.com, CN=DigiCert Global Root G2
   a:PKEY: rsaEncryption, 4096 (bit); sigalg: RSA-SHA384
   v:NotBefore: Jul 29 12:30:00 2020 GMT; NotAfter: Jun 27 23:59:59 2024 GMT
---
Server certificate
-----BEGIN CERTIFICATE-----
MIIJLjCCBxa....
-----END CERTIFICATE-----
subject=C=US, ST=WA, L=Redmond, O=Microsoft Corporation, CN=502330f57f6f.database.windows.net
issuer=C=US, O=Microsoft Corporation, CN=Microsoft Azure TLS Issuing CA 05
---
No client certificate CA names sent
Peer signing digest: SHA256
Peer signature type: RSA-PSS
Server Temp Key: ECDH, secp384r1, 384 bits
---
SSL handshake has read 4534 bytes and written 865 bytes
Verification: OK
---
New, TLSv1.3, Cipher is TLS_AES_256_GCM_SHA384
Server public key is 2048 bit
This TLS version forbids renegotiation.
Compression: NONE
Expansion: NONE
No ALPN negotiated
Early data was not sent
Verify return code: 0 (ok)
---
read:errno=54
---
Post-Handshake New Session Ticket arrived:
SSL-Session:
    Protocol  : TLSv1.3
    Cipher    : TLS_AES_256_GCM_SHA384
    Session-ID: 3DC1...
    Session-ID-ctx: 
    Resumption PSK: C2B4...
    PSK identity: None
    PSK identity hint: None
    SRP username: None
    TLS session ticket lifetime hint: 36000 (seconds)
    TLS session ticket:
    0000 - d1 ...
    0010 - 2c ...

    Start Time: 1702287665
    Timeout   : 7200 (sec)
    Verify return code: 0 (ok)
    Extended master secret: no
    Max Early Data: 0
---
read R BLOCK

@mmastrac let me know if I can help anyhow differently in this.

Unfortunately this currently blocks me in using mssql & deno for my project, as I need it to connect to Azure MS SQL :(

P.S. My mssql server requires VPN connection. I've got enabled it globally on my OS X. I can connect to mssql server using node.js and DataGrip easily (without the need of preconfiguring any certificates in the connection settings). I tried adding DENO_TLS_CA_STORE=system to the deno but it doesn't help either.

Having the exact issue, unable to connect to Azure MSSQL dbs.

KnorpelSenf commented 6 months ago

@nathanwhit are you planning on working on this in the near future?

egous-bunnings commented 4 months ago

Just like many others I too am blocked. The workaround of encrypt: false is not possible within the Azure SQL context as they enforce SSL

Unfortunately this is the bug that will force me to ditch Deno as no other possible workaround exists.

kbrown-remed-com commented 2 months ago

Like many others, I have been waiting for a fix for this. I was hoping that this would work in 2.0, but I'm still getting the exact same errors as I did in 1.4x. I would love to begin to use Deno, but this bug is preventing me from switching.

@satyarohith are you planning on addressing this any time soon?

mderazon commented 3 weeks ago

Trying to convert a Node project to Deno and hitting the same issue. As a last measure, has anyone found a different module that doesn't have this problem ? Or is it something fundumental with the way Deno connects to MSSQL server ?

egous-bunnings commented 3 weeks ago

@mderazon I couldn't find a workable solution/workaround to this issue and was forced to port out.

tjcrowder commented 3 weeks ago

@mderazon Same here I'm afraid. I had to use Node.js instead. I just tried it again with the latest Deno (v2.0.4) and sadly, it still doesn't work. I don't think it's a fundamental Deno issue, "just" a Node.js compatibility issue. I suspect it's possible to write a new Deno-native SQL Server driver, but it's just that no one has (yet).