ERROR - Connect to SQL Endpoint using Node.js and tedious npm package.

[Omar_Osman]

Unsuccessful connect to SQL Endpoint using Node.js and tedious or mssql npm package

Software versions

• tedious: "^17.0.0"
• Node.js:18.17.1
  

var Connection = require("tedious").Connection;

var config = {
  server:
    //"****.datawarehouse.pbidedicated.windows.net",
    "*****.datawarehouse.fabric.microsoft.com",
  database: "Gold",
  options: {
    encrypt: true,
    trustServerCertificate: false,
  },
  authentication: {
    //type: "azure-active-directory-password",
    type: "azure-active-directory-service-principal-secret",
    options: {
      //userName: "@.onmicrosoft.com",
      //password: "****",
      clientId: "****",
      tenantId: "****",
      clientSecret: "****",
    },
  },
};

var connection = new Connection(config);

connection.connect((err) => {
  if (err) {
    console.log("Connection Failed");
    throw err;
  }

  console.log("Custom connection Succeeded");
  connection.close();
});

 Problem description
Got an error message when connecting to Microsoft Fabric SQL Endpoint

ConnectionError: Connection lost - socket hang up
     {at Connection.socketError
... node_modules\tedious\lib\connection.js:1344:26)
    code: 'ESOCKET'
  }

Note:that the connection works when using the powershell with the same parameters.

Install-Module -Name SqlServer
$connectionString = "Server=$server;Database=$database;User Id=$username;Password=$password;Authentication=Active Directory Password;Encrypt=True;TrustServerCertificate=False;"
# Create a SQL connection
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = $connectionString

Best Regards,

[Anonymous]

Hi @Omar_Osman ,

Power BI now has a problem connecting to SQL endpoint.

We have reported this issue and submitted it to the product team.
They have been aware of the issue and the engineers will do their best to resolve it. I will update here if there is any progress, so please be patient. 

Best regards.
Community Support Team_ Caitlyn

[Anonymous]

Hi @Omar_Osman ,

The issue about connecting to SQL Endpoint has been fixed, if your problem still exists,it may need to collect log files for further troubleshooting. Since community support engineers don't have that access, I would suggest opening a Support Ticket. If you are a Power BI Pro or Fabric licensee, you can create a support ticket for free and a dedicated Microsoft engineer will come to solve the problem for you. 
It would be great if you continue to share in this issue to help others with similar problems after you know the root cause or solution.

The link of Power BI Support: Support | Microsoft Power BI

For how to create a support ticket, please refer to How to create a support ticket in Power BI - Microsoft Power BI Community

Best Regards,
Community Support Team _ Caitlyn

[erz1234]

Hi, I have the same issue here. If I connect with python, connection establishes:

# db_connect.py
import os
import pyodbc
import struct
from azure.identity import ClientSecretCredential

def main():
    client_secret = os.environ["AZURE_APP_SECRET"]
    tenant_id = os.environ["AZURE_TENANT"]
    client_id = os.environ["AZURE_APP_CLIENT_ID"]
    database_server_name = os.environ["DB_SERVER"]
    database_name = os.environ["DB_NAME"]
    connection_string = f"Driver={{ODBC Driver 17 for SQL Server}};Server={database_server_name};Database={database_name};Port=1433;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30"
    
    try:
        credential = ClientSecretCredential(
            tenant_id=tenant_id,
            client_id=client_id,
            client_secret=client_secret
        )
        
        token_bytes = credential.get_token("https://database.windows.net/.default").token.encode("UTF-16-LE")
        token_struct = struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes)
        SQL_COPT_SS_ACCESS_TOKEN = 1256  # This connection option is defined by Microsoft in msodbcsql.h
        
        conn = pyodbc.connect(connection_string, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
        cursor = conn.cursor()
        cursor.execute("SELECT 1 AS number")
        row = cursor.fetchone()
        print(f"Query result: {row[0]}")
        
    except Exception as e:
        print(f"Error: {e}")

if __name__ == "__main__":
    main()

If I try to connect with NodeJS, socket hangs up. I tried to use mssql library, used token and service principal for authentication, tried a lot of different code variants. Same error always. Latest code change was, I tried to replicate your code, and the issue still exists:

import * as dotenv from 'dotenv';
var Connection = require("tedious").Connection;

dotenv.config();

const tenantId = process.env.AZURE_TENANT || '';
const clientId = process.env.AZURE_APP_CLIENT_ID || '';
const clientSecret = process.env.AZURE_APP_SECRET || '';
const dbServer = process.env.DB_SERVER || ''; // <server_name>.datawarehouse.fabric.microsoft.com

if (!tenantId || !clientId || !clientSecret || !dbServer || !database) {
    throw new Error("Missing required environment variables");
}

async function connectToDatabase() {
        const config = {
            server: dbServer,
            database: database,
            options: {
                encrypt: true,
                trustServerCertificate: true,
                connectTimeout: 30000,
                requestTimeout: 30000,
                enableArithAbort: true
            },
            authentication: {
                type: 'azure-active-directory-service-principal-secret',
                options: {
                    tenantId: tenantId,
                    clientId: clientId,
                    clientSecret: clientSecret
                }
            }
        };
        var connection = new Connection(config);

        connection.connect((err : any) => {
            if (err) {
              console.log("Connection Failed");
              throw err;
            }
          
            console.log("Custom connection Succeeded");
            connection.close();
          });
}

connectToDatabase();

It seems that the problem is with tedious library (or mssql library, which uses tedious under the hood if I remember correctly). It seems that only solution for me now is to call my python script from within my nodejs service, but would rather avoid this for now. I don' know what can I do at the moment, since I can imagine that the problem is within the imported libraries...

[MAXI2799]

Having same 

ConnectionError: Connection lost - socket hang up

issue as @erz1234 trying to connect using nodejs to a Microsoft SQL Server 2022 16.0.4075.1 using mssql - tedious, if you found a soluiton please let me know, thanks!

[erz1234]

Hey @MAXI2799, your issue might be a bit different then ours. I say that because I have no problem connecting to SQL Server with mssql. Maybe I am wrong though, as I have not try to connect to 2022 version. So if the issue you have is the same as with connecting to Fabric, I found library odbc, that connects successfully to fabric dwh. As it turns out (or so i have read), tedious library can't connect to fabric dwh because fabric has a bit different security layers and protocols, that tedious so far do not have. 

ODBC library code that works for me:

import * as dotenv from 'dotenv';
import odbc from 'odbc';

// Load environment variables from .env file
dotenv.config();

const tenantId = process.env.AZURE_TENANT || '';
const clientId = process.env.AZURE_APP_CLIENT_ID || '';
const clientSecret = process.env.AZURE_APP_SECRET || '';
const dbServer = process.env.DB_SERVER || ''; // <server_name>.datawarehouse.fabric.microsoft.com
const database = process.env.DB_NAME || '';

if (!tenantId || !clientId || !clientSecret || !dbServer || !database) {
    throw new Error("Missing required environment variables");
}

async function connectToDatabase() {
    const connectionString = `
        Driver={ODBC Driver 17 for SQL Server};
        Server=${dbServer};
        Database=${database};
        UID=${clientId};
        PWD=${clientSecret};
        Authentication=ActiveDirectoryServicePrincipal;
        Encrypt=yes;
        TrustServerCertificate=no;
        Connection Timeout=30;
    `;

    try {
        const connection = await odbc.connect(connectionString);
        console.log('Connection established!');

        // Your query execution code here
        const result = await connection.query('SELECT 1');
        console.log(result);

        // Close the connection
        await connection.close();
        console.log('Connection closed');
    } catch (error) {
        console.error('ODBC Error:', error);
    }
}

For tedious library, this is the link where I found most usefull information - https://github.com/tediousjs/tedious/issues/1563 

[M_Junaid]

Hi @erz1234 I am getting this error using your code Pool creation failed: [Error: [odbc] Error connecting to the database] { odbcErrors: [ { state: '28000', code: 18456, message: '[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Could not login because the authentication failed.' } ] }