Forum Discussion
Using Python + StreamLit to connect to a Fabric SQL database/lakehouse
Hi all,
Has anyone setup a Python Streamlit App, to run locally on their laptop, and connect to a MS Fabric database ? Looking to do this as POC, as we may be looking to utilise Streamlit further in future with Fabric.
I am having issues connecting / authentication / getting token etc.
I have tried a number of ways, within my python code to get a token/credentials from the Fabric database that we have set up.
Initial errors were stating i am missing tenant_id, client_id, token_file_path ?
Can anyone offer up some advice about how to do this, with python/streamlit ?
Hi v-sdhruv , yes thanks for your help, have now connected to Fabric via Streamlit.
7 Replies
Hi chapperscobbler ,
In order to connect to SQL database in fabric using Python/streamlit you can follow this latest article which describes in detail how to connect to a SQL database.
Connect to a SQL database in Fabric with the Microsoft Python Driver for SQL Server - Microsoft Fabric | Microsoft Learn
One of the reasons why you are facing authentication issue could be due to improper configuration of- Azure AD App Registration (for authentication)
- Python environment with required packages
Ensure your app registration has API permissions for Microsoft Fabric and SQL.
For authentication using Python, make sure to install proper packages and then authenticate.
Also, refer - Build a Streamlit application with Fabric SQL Database - Azure SQL Devs’ Corner
If you would like to share the error details and your code it would be great since it will help to point out the issue better.
Hope this helps!
v-sdhruv wrote:Hi chapperscobbler ,
In order to connect to SQL database in fabric using Python/streamlit you can follow this latest article which describes in detail how to connect to a SQL database.
Connect to a SQL database in Fabric with the Microsoft Python Driver for SQL Server - Microsoft Fabric | Microsoft Learn
One of the reasons why you are facing authentication issue could be due to improper configuration of- Azure AD App Registration (for authentication)
- Python environment with required packages
Ensure your app registration has API permissions for Microsoft Fabric and SQL.
For authentication using Python, make sure to install proper packages and then authenticate.
Also, refer - Build a Streamlit application with Fabric SQL Database - Azure SQL Devs’ Corner-Slope Game If you would like to share the error details and your code it would be great since it will help to point out the issue better.
Hope this helps!Okay, let's untangle this authentication knot! Is your Azure AD App Registration feeling lost? Perhaps Python's missing a crucial piece of its puzzle? Hmm, API permissions, a common culprit indeed. Remember that time my smart fridge wouldn't connect to the Wi-Fi? Turns out, I hadn't granted it network access.
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.
Hi chapperscobbler ,
I hope the explaination provided helped you to resolve the issue.
If you still need any help, feel free to reach out to us.Thanks for reaching out on Microsoft Fabric community forum.
Hi v-sdhruv , yes thanks for your help, have now connected to Fabric via Streamlit.
Yes, you can add Python/Streamlit in Fabric. Can you provide more details about your poc to make recommendations?
Hi @chapperscobbler ,
I hope the explaination provided helped you to resolve the issue.
If you still need any help, feel free to reach out to us.Thanks for reaching out on Microsoft Fabric community forum.
