@chapperscobbler
Yes, you can connect a Python + Streamlit app to a Microsoft Fabric SQL database using ODBC and pyodbc, but authentication must be handled via Azure AD with either a user identity or service principal. The key is configuring your connection string and token acquisition correctly.
Steps to Connect Streamlit to Fabric SQL Database
1. Prerequisites
- Fabric workspace with SQL database or Lakehouse (with SQL endpoint enabled)
- Azure AD tenant with access to the Fabric workspace
- Python, Streamlit, and ODBC driver installed locally
- Service Principal or user credentials with appropriate permissions
Authentication Options
Option A: User Identity (Interactive Login)
Use Azure Identity libraries to acquire a token interactively:
from azure.identity import InteractiveBrowserCredential import pyodbc credential = InteractiveBrowserCredential() token = credential.get_token("https://database.windows.net/.default").token conn_str = ( "Driver={ODBC Driver 18 for SQL Server};" "Server=tcp:.database.windows.net;" "Database=;" "Authentication=ActiveDirectoryAccessToken;" ) conn = pyodbc.connect(conn_str, attrs_before={1256: token})
Option B: Service Principal (Recommended for Production)
Use ClientSecretCredential to authenticate with a service principal:
from azure.identity import ClientSecretCredential import pyodbc tenant_id = "" client_id = "" client_secret = "" credential = ClientSecretCredential(tenant_id, client_id, client_secret) token = credential.get_token("https://database.windows.net/.default").token conn_str = ( "Driver={ODBC Driver 18 for SQL Server};" "Server=tcp:.database.windows.net;" "Database=;" "Authentication=ActiveDirectoryAccessToken;" ) conn = pyodbc.connect(conn_str, attrs_before={1256: token})
Streamlit Integration
Once connected, you can use Pandas to query and display data:
import pandas as pd import streamlit as st query = "SELECT TOP 10 * FROM SalesLT.Product" df = pd.read_sql(query, conn) st.dataframe(df)
Troubleshooting Tips
- Ensure the ODBC Driver 18 for SQL Server is installed.
- Your Fabric SQL endpoint must be accessible externally (check firewall settings).
- If using service principal, it must have Viewer or higher access to the Fabric workspace.
- Store secrets securely using .streamlit/secrets.toml or environment variables.
