使用Node.js连接Microsoft Fabric Warehouse遇连接错误求助

问题背景 #

通过Node.js的mssql包以服务主体认证连接Microsoft Fabric Warehouse时，出现ConnectionError: Connection lost - socket hang up错误，但Python+pyodbc可以正常连接。

报错信息 #

err = new ConnectionError(err)
        ^

ConnectionError: Connection lost - socket hang up  

原Node.js代码 #

const sql = require('mssql');
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 myfunc(){
    const credential = new azureIdentity.ClientSecretCredential(tenantId,clientId,clientSecret);
    const token = await credential.getToken(scopes="https://database.windows.net/.default");
    const sqlConfig = {
        database: database,
        server: dbServer,
        authentication: {
            type: "azure-active-directory-access-token",
            options: {
                token: token.token
                }
        },
        pool: {
            max: 10,
            min: 0,
            idleTimeoutMillis: 30000
        },
        options: {
            encrypt: true, 
            trustServerCertificate: false 
        }
    };

    const conn = await sql.connect(sqlConfig);
}

myfunc();

可正常连接的Python代码 #

from azure.identity import ClientSecretCredential
import os , pyodbc , struct

client_secret = os.environ["CLIENT_SECRET"]
tenant_id = os.environ["TENANT_ID"]
client_id = os.environ["CLIENT_ID"]
database_server_name = "<myfabricwarehouse>.datawarehouse.pbidedicated.windows.net"
database_name = "mydatabase"
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})

except Exception as e:
    print(e)

解决方案 #

1. 升级mssql包到最新版本 #

旧版mssql对Microsoft Fabric的兼容性不足，执行命令升级：

npm install mssql@latest

2. 调整连接配置修复代码 #

关键修改点：

• 显式指定端口1433，并在server地址前添加tcp:前缀
• 增加连接超时时间，避免因超时导致socket断开
• 完善错误捕获逻辑，便于排查问题

修复后的Node.js代码

const sql = require('mssql');
const { ClientSecretCredential } = require('@azure/identity');

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

async function myfunc() {
    try {
        const credential = new ClientSecretCredential(tenantId, clientId, clientSecret);
        const token = await credential.getToken("https://database.windows.net/.default");
        
        const sqlConfig = {
            database: database,
            server: dbServer,
            authentication: {
                type: "azure-active-directory-access-token",
                options: {
                    token: token.token
                }
            },
            pool: {
                max: 10,
                min: 0,
                idleTimeoutMillis: 30000
            },
            options: {
                encrypt: true,
                trustServerCertificate: false,
                connectTimeout: 60000 // 延长连接超时到60秒
            }
        };

        const conn = await sql.connect(sqlConfig);
        console.log("连接成功！");
        // 可在此添加查询逻辑
        await conn.close();
    } catch (err) {
        console.error("连接失败:", err);
    }
}

myfunc();

3. 备选方案：使用mssql内置的服务主体认证 #

如果手动处理token仍有问题，可直接让mssql集成服务主体认证，无需手动获取token：

const sql = require('mssql');

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

async function myfunc() {
    try {
        const sqlConfig = {
            database: database,
            server: dbServer,
            authentication: {
                type: "azure-active-directory-service-principal",
                options: {
                    clientId: clientId,
                    clientSecret: clientSecret,
                    tenantId: tenantId
                }
            },
            pool: {
                max: 10,
                min: 0,
                idleTimeoutMillis: 30000
            },
            options: {
                encrypt: true,
                trustServerCertificate: false,
                connectTimeout: 60000
            }
        };

        const conn = await sql.connect(sqlConfig);
        console.log("连接成功！");
        await conn.close();
    } catch (err) {
        console.error("连接失败:", err);
    }
}

myfunc();

验证点 #

• 确认环境变量TENANT_ID、CLIENT_ID、CLIENT_SECRET已正确配置
• 服务主体已被授予Fabric Warehouse的访问权限（如Reader或Contributor角色）
• 网络环境允许访问1433端口（无防火墙或代理拦截）

内容的提问来源于stack exchange，提问作者SwadX