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,

[v-xiaoyan-msft]

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

[v-xiaoyan-msft]

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