not able to connect to Microsoft Fabric

[tchenazure]

Software versions

• Tedious: 16.2.0
• SQL Server: Microsoft Fabric(12.0.2000.8)
• Node.js:18.17.0

Additional Libraries Used and Versions

node-mssql: 9.1.1

Table schema

Connection configuration
const sqlConfig = { database: 'Root Data Warehouse', server: 'gextrl4zm6culiitwxjyp6ss5y-vh3r3oacuziunk6ffxzgvl2tay.datawarehouse.pbidedicated.windows.net', port: 1433, pool: { max: 10, min: 0, idleTimeoutMillis: 30000 }, options: { encrypt: true, // for azure trustServerCertificate: true// change to true for local dev / self-signed certs }, authentication: { type: 'azure-active-directory-service-principal-secret', options: { clientId: '74329c3d-6630-40f2-bbb6-ecb70659f0b2', clientSecret: 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', tenantId: 'af382f31-6799-4585-a113-b5d387fa52ee' } }, };

Problem description

Got an error message when connecting to Microsoft Fabric
Uncaught ConnectionError Error: Connection lost - read ECONNRESET

Expected behavior

Microsoft Fabric should be connected by service principal, query should work after the connection is established.

Actual behavior

It doesn't connect at all.

Error message/stack trace

Uncaught ConnectionError Error: Connection lost - read ECONNRESET
at socketError (c:\test\fabric\node_modules\tedious\lib\connection.js:1410:26)
at (c:\test\fabric\node_modules\tedious\lib\connection.js:1160:16)
at emit (events:526:35)
at emitErrorNT (internal/streams/destroy:151:8)
at emitErrorCloseNT (internal/streams/destroy:116:3)
at processTicksAndRejections (internal/process/task_queues:82:21)
--- TickObject ---
at init (internal/inspector_async_hook:25:19)
at emitInitNative (internal/async_hooks:202:43)
at emitInitScript (internal/async_hooks:504:3)
at nextTick (internal/process/task_queues:132:5)
at onDestroy (internal/streams/destroy:103:15)
at Socket._destroy (net:812:5)
at _destroy (internal/streams/destroy:109:10)
at destroy (internal/streams/destroy:71:5)
at onStreamRead (internal/stream_base_commons:217:12)
at callbackTrampoline (internal/async_hooks:130:17)
--- TCPWRAP ---
at init (internal/inspector_async_hook:25:19)
at emitInitNative (internal/async_hooks:202:43)
at Socket.connect (net:1176:7)
at connect (net:244:17)
at (c:\test\fabric\node_modules\tedious\lib\connector.js:102:37)
at connectInSequence (c:\test\fabric\node_modules\tedious\lib\connector.js:101:20)
--- await ---
at connectOnPort (c:\test\fabric\node_modules\tedious\lib\connection.js:1157:5)
at initialiseConnection (c:\test\fabric\node_modules\tedious\lib\connection.js:1072:19)
at enter (c:\test\fabric\node_modules\tedious\lib\connection.js:2364:12)
at transitionTo (c:\test\fabric\node_modules\tedious\lib\connection.js:1365:24)
at reconnect (c:\test\fabric\node_modules\tedious\lib\connection.js:2468:14)
at dispatchEvent (c:\test\fabric\node_modules\tedious\lib\connection.js:1391:15)
at socketClose (c:\test\fabric\node_modules\tedious\lib\connection.js:1439:12)
at (c:\test\fabric\node_modules\tedious\lib\connection.js:1163:16)
at emit (events:526:35)
at (net:323:12)
at callbackTrampoline (internal/async_hooks:130:17)
--- TCPWRAP ---
at init (internal/inspector_async_hook:25:19)
at emitInitNative (internal/async_hooks:202:43)
at Socket.connect (net:1176:7)
at connect (net:244:17)
at (c:\test\fabric\node_modules\tedious\lib\connector.js:102:37)
at connectInSequence (c:\test\fabric\node_modules\tedious\lib\connector.js:101:20)
--- await ---
at connectOnPort (c:\test\fabric\node_modules\tedious\lib\connection.js:1157:5)
at initialiseConnection (c:\test\fabric\node_modules\tedious\lib\connection.js:1072:19)
at enter (c:\test\fabric\node_modules\tedious\lib\connection.js:2364:12)
at transitionTo (c:\test\fabric\node_modules\tedious\lib\connection.js:1365:24)
at connect (c:\test\fabric\node_modules\tedious\lib\connection.js:1035:10)
at (c:\test\fabric\ted.js:30:12)
at Module._compile (internal/modules/cjs/loader:1256:14)
at Module._extensions..js (internal/modules/cjs/loader:1310:10)
at Module.load (internal/modules/cjs/loader:1119:32)
at Module._load (internal/modules/cjs/loader:960:12)
at executeUserEntryPoint (internal/modules/run_main:81:12)
at (internal/main/run_main_module:23:47)
Any other details that can be helpful

[MichaelSun90]

Hi @tchenazure, on thing that I noticed is you may have posted your connection credential in the description. If it is for sandbox, then it should be fine.
In terms for you connection error, under your script, have you try to caught the error and output it? it may review more detail relate to the error. Not sure whether node-mssql will make things differently, but for tedious on its own, you can do it like this by add a console.log in the connect call back:

connection.connect((err) => {
  if (err) {
    console.log(err);
  }
});
connection.

also, the error returned maybe a aggregate, and you can output it as mentioned here under section : connection.connect(callback).

can you give this a try see if you can get a bit more detail on the error, so we can work from there.

[tchenazure]

Hi @MichaelSun90 ,

I got this error:
ConnectionError: Connection lost - read ECONNRESET
at Connection.socketError (C:\test\FabricConnectionTest\node_modules\tedious\lib\connection.js:1410:26)
at Socket. (C:\test\FabricConnectionTest\node_modules\tedious\lib\connection.js:1160:16)
at Socket.emit (node:events:526:35)
at emitErrorNT (node:internal/streams/destroy:151:8)
at emitErrorCloseNT (node:internal/streams/destroy:116:3)
at process.processTicksAndRejections (node:internal/process/task_queues:82:21) {
code: 'ESOCKET',
isTransient: undefined
}

[MichaelSun90]

Hi @tchenazure, have you tried the aggregate error option that I mentioned in the pervious message? if that does not return any additional errors underneath that, then you can enable our debug options on tedious side, so it can log more details on the error.

Here is an example for enable debugging from tedious side:

var config = {
  ...
  options: {
    ...
    // Add this to your configuration file. You can pick and choose which ones to enable. 
    debug: {
      packet: true,
      data: true,
      payload: true,
      token: true
    }
  },
  ...
};

Then add this event listener to output the debug messages:

  connection.on('debug', (msg) => {
    console.log(msg);
  });

Then can you share the log here, so we can have a bit more information to work with. Thanks!

[SwadX]

I am facing a similar issue with connection to Microsoft fabric.
Here is my package version details :

  "dependencies": {
    "@azure/identity": "4.0.1",
    "mssql": "10.0.2",
    "tedious": "16.7.0"
  }

My node version is : v18.16.0

My code :

const sqlTds = require('tedious');

const azureIdentity = require('@azure/identity');

const tenantId = process.env.TENANT_ID;
const clientId = process.env.CLIENT_ID;
const clientSecret = process.env.CLIENT_SECRET;
const dbServer = "<myfabricwarehouse>.datawarehouse.pbidedicated.windows.net";
const database = "<mydatabase>"

async function demo(){
    config = {
        server: dbServer,
        authentication: {
            type: "azure-active-directory-service-principal-secret",
            options: {
                clientId: clientId,
                clientSecret: clientSecret,
                tenantId: tenantId,
            }
        },
        options: {
            database: database,
            port: 1433,
            debug: {
                packet: true,
                data: true,
                payload: true,
                token: true
              }
        }
    };
    const conn = new sqlTds.Connection(config);
    conn.on('debug',(msg) => {console.log(msg);});
    //conn.on("secure",(clearText) => {console.log(clearText)});
    conn.on('error',(err) => {console.log(err)});
    conn.on('connect',(err) => {
        if (err) {
            console.log('Connection Failure...');
            throw err;
        }
    });

    conn.connect();

}

The error I am getting :

Connection lost - socket hang up
ConnectionError: Connection lost - socket hang up
at Connection.socketError
... node_modules\tedious\lib\connection.js:1344:26)
at Socket.emit (node:events:525:35)
at endReadableNT (node:internal/streams/readable:1359:12)
at process.processTicksAndRejections (node:internal/process/task_queues:82:21) {
code: 'ESOCKET'
}
State change: SentLogin7Withfedauth -> Final
Connection lost - unexpected end of message stream
ConnectionError: Connection lost - unexpected end of message stream

[SwadX]

some more debug messages :

State change: Initialized -> Connecting
connected to xxxx.datawarehouse.pbidedicated.windows.net:1433
  PreLogin - version:16.7.0.0, encryption:0x01(ON), instopt:0x00, threadId:0x00000000, mars:0x00(OFF)
State change: Connecting -> SentPrelogin
. . .
State change: SentPrelogin -> SentTLSSSLNegotiation

Sent
  type:0x12(PRELOGIN), status:0x01(EOM), length:0x0161, spid:0x0000, packetId:0x01, window:0x00
. . .
Received
  type:0x12(PRELOGIN), status:0x00(), length:0x1000, spid:0x0000, packetId:0x01, window:0x00
. .  .
Received
  type:0x12(PRELOGIN), status:0x01(EOM), length:0x006B, spid:0x0000, packetId:0x02, window:0x00
. . .
Sent
  type:0x12(PRELOGIN), status:0x01(EOM), length:0x00A6, spid:0x0000, packetId:0x01, window:0x00
. . .
Received
  type:0x12(PRELOGIN), status:0x01(EOM), length:0x003B, spid:0x0000, packetId:0x01, window:0x00
. . .
TLS negotiated (ECDHE-RSA-AES256-GCM-SHA384, TLSv1.2)
  Login7 - TDS:0x74000004, PacketSize:0x00001000, ClientProgVer:0x00000000, ClientPID:0x000079F4, ConnectionID:0x00000000
. . .
 Flags1:0xF0, Flags2:0x00, TypeFlags:0x00, Flags3:0x18, ClientTimezone:-330, ClientLCID:0x00000409
           Hostname:'xxxx', Username:'undefined', Password:'undefined', AppName:'Tedious', ServerName:'xxxx.datawarehouse.pbidedicated.windows.net', LibraryName:'Tedious'
           Language:'us_english', Database:'xxxx', SSPI:'undefined', AttachDbFile:'undefined', 
ChangePassword:'undefined'
State change: SentTLSSSLNegotiation -> SentLogin7Withfedauth

Sent
  type:0x10(LOGIN7), status:0x01(EOM), length:0x019A, spid:0x0000, packetId:0x01, window:0x00
. . .
socket ended
Connection lost - socket hang up
ConnectionError: Connection lost - socket hang up
    at Connection.socketError

[MichaelSun90]

Hi @SwadX, Thanks for the detail log. Will look into this a bit more, see if we can figure out why it behave like this.

[SanderBlom]

I also experience the ConnectionError: Connection lost - socket hang up. Is there any progress on this? I face the same issue both using service-principal-secret and token.

Error:

node:events:496
      throw er; // Unhandled 'error' event
      ^

ConnectionError: Connection lost - socket hang up
    at Connection.socketError (/home/sander/development/test/api_poc/node_modules/tedious/lib/connection.js:1342:26)
    at Connection.socketEnd (/home/sander/development/test/api_poc/node_modules/tedious/lib/connection.js:1355:12)
    at Socket.<anonymous> (/home/sander/development/test/api_poc/node_modules/tedious/lib/connection.js:1051:12)
    at Socket.emit (node:events:530:35)
    at endReadableNT (node:internal/streams/readable:1696:12)
    at process.processTicksAndRejections (node:internal/process/task_queues:82:21)
Emitted 'error' event on Connection instance at:
    at Connection.emit (/home/sander/development/test/api_poc/node_modules/tedious/lib/connection.js:957:18)
    at Connection.socketError (/home/sander/development/test/api_poc/node_modules/tedious/lib/connection.js:1342:12)
    at Connection.socketEnd (/home/sander/development/test/api_poc/node_modules/tedious/lib/connection.js:1355:12)
    [... lines matching original stack trace ...]
    at process.processTicksAndRejections (node:internal/process/task_queues:82:21) {
  code: 'ESOCKET'
}

Node.js v20.12.2

[MichaelSun90]

Hi @SanderBlom, we did some investigation and unfortunately that tedious does not currently support connection to Microsoft Fabric DW. Microsoft Fabric DW has a bit more restricted way for handling incoming connection compare to how tedious currently handles it internally. The team will have a discussion and see when we will have the bandwidth to get this working.

[elliot-huffman]

From the authentication side of things, the new PR at #1624 should add authentication support as you can roll any authentication you need from the MSFT side of things.
You can configure your credential chain using the MSFT docs for Fabric to authenticate.

Protocol support is a different thing...