Overview

You might need to connect to data in your Fabric environment with an on-prem machine to run it through a proprietary model or back it up in an on-premise location. The code below is how you can connect to data in a Fabric Warehouse. You will ideally have a service principal created so that you can automate whatever code you need, but if you don’t have one it should work as is. Don’t forget to fill out the connection string and Lakehouse/Warehouse name.

Prerequisites

• Python

• SQL Server Driver

• `pip install pyodbc azure-identity`

import struct
from itertools import chain, repeat
import urllib
import pyodbc
import pandas as pd
from azure.identity import InteractiveBrowserCredential
# from azure.identity import ClientSecretCredential

credential = InteractiveBrowserCredential() # Use for proof of concept

# Preferred Method so you can automate your code
# tenant_id = "" # fill this out
# client_id = "" # fill this out
# client_secret = "" # fill this out
# credential = ClientSecretCredential(tenant_id, client_id, client_secret)

sql_endpoint = "" # fill this out
database = "" # fill this out

# Either Driver will work as of 2/25/25
connection_string = f"Driver={{ODBC Driver 17 for SQL Server}};Server={sql_endpoint},1433;Database={database};Encrypt=Yes;TrustServerCertificate=No"
# connection_string = f"Driver={{ODBC Driver 18 for SQL Server}};Server={sql_endpoint},1433;Database={database};Encrypt=Yes;TrustServerCertificate=No"

token_object = credential.get_token("https://database.windows.net//.default") # Retrieve an access token valid to connect to SQL databases
params = urllib.parse.quote(connection_string)

# Retrieve an access token
token_as_bytes = bytes(token_object.token, "UTF-8") # Convert the token to a UTF-8 byte string
encoded_bytes = bytes(chain.from_iterable(zip(token_as_bytes, repeat(0)))) # Encode the bytes to a Windows byte string
token_bytes = struct.pack("<i", len(encoded_bytes)) + encoded_bytes # Package the token into a bytes object
attrs_before = {1256: token_bytes}  # Attribute pointing to SQL_COPT_SS_ACCESS_TOKEN to pass access token to the driver

connection = pyodbc.connect(connection_string, attrs_before=attrs_before)

# Query using pandas
df = pd.read_sql("SELECT TOP (10) * FROM [dbo].[locations_with_country_2]", con=connection)

# Alternative way to query
# cursor = connection.cursor()
# cursor.execute("SELECT TOP (10) * FROM [dbo].[locations_with_country_2]")
# rows = cursor.fetchall()
# print(rows)
# cursor.close()