[Python] Getting Authentication failed error while connecting with MS Fabric data Warehouse...

First I ahve used Node.js and tedious but it didn't work becase 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. Now I have used ODBC library but got Authentication Error

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

I am using this code in 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 17 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}`);
});


using python

import pyodbc
import struct
from azure.identity import ClientSecretCredential

def connect_to_database():
# Step 1: Define Azure Credentials
tenant_id = "*****"
client_id = "*****"
client_secret = "*****"

# Step 2: Define Database Server and Name
database_server_name = "*****.datawarehouse.fabric.microsoft.com"
database_name = "WH_Atlas"

# Step 3: Authenticate using Azure AD
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")
token_struct = struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes)

SQL_COPT_SS_ACCESS_TOKEN = 1256 # ODBC Access Token option

# Step 4: Define ODBC Connection String
connection_string = f"""
Driver={{ODBC Driver 17 for SQL Server}};
Server={database_server_name};
Database={database_name};
Encrypt=yes;
TrustServerCertificate=no;
Connection Timeout=30;
"""

try:
# Step 5: Establish Connection
print(" Connecting to Azure SQL Database...")
conn = pyodbc.connect(connection_string, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
cursor = conn.cursor()

# Step 6: Test Query
cursor.execute("SELECT 1 AS number")
row = cursor.fetchone()
print(f"✅ Connection Successful! Query Result: {row[0]}")

# Step 7: Close Connection
conn.close()
print(" Connection Closed.")

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

if __name__ == "__main__":
connect_to_database()


In python I also got the same error



Additional resources: Issue On Microsoft fabric community

Continue reading...