使用Node.js和Python连接Microsoft Fabric数据仓库时遭遇身份验证失败错误

一开始我尝试用Node.js搭配tedious库连接Fabric数据仓库，但没成功——因为tedious目前还不支持Fabric特有的安全层和协议。后来换成ODBC库，却碰到了身份验证错误：

Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Could not login because the authentication failed.

我用的Node.js代码如下：

require('dotenv').config();
const express = require('express');
const odbc = require('odbc');

const app = express();
const port = process.env.PORT || 3000;

// Connection pool configuration
const poolConfig = {
    connectionString: `
    Driver={ODBC Driver 18 for SQL Server};
    Server=${process.env.DB_SERVER};
    Database=${process.env.DB_NAME};
    UID=${process.env.AZURE_APP_CLIENT_ID};
    PWD=${process.env.AZURE_APP_SECRET};
    Authentication=ActiveDirectoryServicePrincipal;
    Encrypt=yes;
    TrustServerCertificate=no;
    Connection Timeout=30;
  `.replace(/\n\s+/g, ' '), // Clean up whitespace
    initialSize: 5,
    maxSize: 20
};

// Create connection pool
let pool;
(async () => {
    try {
        pool = await odbc.pool(poolConfig);
        console.log('Connection pool created successfully');
    } catch (error) {
        console.error('Pool creation failed:', error);
        process.exit(1);
    }
})();

// Basic health check
app.get('/', (req, res) => {
    res.send('Fabric DWH API is running');
});

// Query endpoint
app.get('/api/query', async (req, res) => {
    try {
        const query = req.query.sql || 'SELECT TOP 10 * FROM INFORMATION_SCHEMA.TABLES';
        const connection = await pool.connect();
        const result = await connection.query(query);
        await connection.close();
        res.json(result);
    } catch (error) {
        console.error('Query error:', error);
        res.status(500).json({ error: error.message });
    }
});

app.listen(port, () => {
    console.log(`Server running on http://localhost:${port}`);
});

之后我又试了Python代码，结果遇到了一模一样的错误：

我用的Python代码是这样的：

import pyodbc
import struct
from itertools import chain, repeat
from azure.identity import ClientSecretCredential

def connect_to_fabric():
    tenant_id = "your-tenant-id"
    client_id = "your-client-id"
    client_secret = "your-client-secret"
    database_server = "your-server.datawarehouse.fabric.microsoft.com,1433"
    database_name = "your-database-name"

    credential = ClientSecretCredential(
        tenant_id=tenant_id,
        client_id=client_id,
        client_secret=client_secret
    )
    
    token = credential.get_token("https://database.windows.net/.default").token
    token_bytes = token.encode("UTF-16-LE")
    encoded = bytes(chain.from_iterable(zip(token_bytes, repeat(0))))
    token_struct = struct.pack(f'<I{len(encoded)}s', len(encoded), encoded)

    SQL_COPT_SS_ACCESS_TOKEN = 1256

    connection_string = f"""
        Driver={{ODBC Driver 18 for SQL Server}};
        Server={database_server};
        Database={database_name};
        Encrypt=yes;
        TrustServerCertificate=no;
        Connection Timeout=45;
    """

    try:
        print(" Connecting to Fabric SQL Warehouse...")
        conn = pyodbc.connect(connection_string, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
        cursor = conn.cursor()
        cursor.execute("SELECT 1 AS test")
        row = cursor.fetchone()
        print(f"✅ Connected! Query Result: {row[0]}")

        conn.close()
        print(" Connection Closed.")

    except Exception as e:
        print(f"❌ Connection Failed: {e}")

if __name__ == "__main__":
    connect_to_fabric()

备注：内容来源于stack exchange，提问作者M Junaid