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.

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

mssql ConnectionError #20594

Open
sant123 opened this issue Sep 21, 2023 · 21 comments
Open

mssql ConnectionError #20594

sant123 opened this issue Sep 21, 2023 · 21 comments
Assignees
Labels
bug Something isn't working correctly node API Related to various "node:*" modules APIs node compat
Milestone

Comments

@sant123
Copy link

sant123 commented Sep 21, 2023

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

import sql from "npm:[email protected]";

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

@littledivy littledivy added bug Something isn't working correctly node compat labels Sep 21, 2023
@sant123
Copy link
Author

sant123 commented Sep 21, 2023

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/[email protected]/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
Copy link

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

@mmastrac
Copy link
Contributor

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

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
Copy link
Contributor

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

@mmastrac
Copy link
Contributor

@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
Copy link

@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
Copy link
Contributor

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

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
Copy link
Author

sant123 commented Nov 27, 2023

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
Copy link
Author

sant123 commented Nov 27, 2023

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

jtomaszewski commented Dec 11, 2023

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/[email protected]/node_modules/mssql/lib/tedious/connection-pool.js:85:17
    at Connection.onError (file:///Users/jacek/src/myevaluations/windmill/node_modules/.deno/[email protected]/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/[email protected]/node_modules/tedious/lib/connection.js:959:18)
    at Connection.socketError (file:///Users/jacek/src/myevaluations/windmill/node_modules/.deno/[email protected]/node_modules/tedious/lib/connection.js:1345:12)
    at Socket.<anonymous> (file:///Users/jacek/src/myevaluations/windmill/node_modules/.deno/[email protected]/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
Copy link
Author

sant123 commented Jan 25, 2024

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

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/[email protected]/node_modules/mssql/lib/tedious/connection-pool.js:85:17
    at Connection.onError (file:///Users/jacek/src/myevaluations/windmill/node_modules/.deno/[email protected]/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/[email protected]/node_modules/tedious/lib/connection.js:959:18)
    at Connection.socketError (file:///Users/jacek/src/myevaluations/windmill/node_modules/.deno/[email protected]/node_modules/tedious/lib/connection.js:1345:12)
    at Socket.<anonymous> (file:///Users/jacek/src/myevaluations/windmill/node_modules/.deno/[email protected]/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.

@bartlomieju bartlomieju added the node API Related to various "node:*" modules APIs label Mar 4, 2024
@KnorpelSenf
Copy link
Contributor

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

@egous-bunnings
Copy link

egous-bunnings commented Jul 9, 2024

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

kbrown-remed-com commented Sep 20, 2024

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?

@bartlomieju bartlomieju added this to the 2.1.0 milestone Sep 21, 2024
@mderazon
Copy link

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

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

@tjcrowder
Copy link

tjcrowder commented Oct 30, 2024

@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).

@bartlomieju bartlomieju modified the milestones: 2.1.0, 2.2.0 Nov 12, 2024
@v1d3rm3
Copy link

v1d3rm3 commented Dec 12, 2024

I believe that adoption of Deno depends of creators officially support ways to connect to database in platform. They have to learn with elixir community which has all components they need to create anything, sadly functional languages are not good.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working correctly node API Related to various "node:*" modules APIs node compat
Projects
None yet
Development

No branches or pull requests