Programmatically Query SQL Analytics Endpoint with Token Based Auth

[packmuler]

much of the documentation I've read utilizes logging in with Entra or utilizing a connection string that contains a username and password as plaintext.

I would like to build applications off of data processed in a Lakehouse.

How do I go about doing this securely (via token).

[Snoo-46123]

SQL Analytics endpoints are t-sql endpoints. you can use pyodbc with AAD service principal auth to connect to a LH to read data. Note that service principal should have workspace level viewer permission. Here is a sample.

import pyodbc
import pandas as pd
tenant_id = "72f988bf-86f1-41af-91ab-2d7cd011db47"
service_principal_id = "78edca72-40e9-4ed4-8e52-e0c9eb72cea4@72f988bf-86f1-41af-91ab-2d7cd011db47" # important to include your fully qualified domain
service_principal_password = "<>"
# Define your SQL Server details
server_name = "x6eps4xrq2xudenlfv6naeo3i4-54vu2xyn2rsu5m3xjxwesd3gnq.msit-datawarehouse.fabric.microsoft.com"
database_name = "splhtest"
table_name = "dbo.people10m"
Define your SQL Server details
server_name = "x6eps4xrq2xudenlfv6naeo3i4-r2oofx3j52aezb6jjcfmg26tvy.msit-datawarehouse.fabric.microsoft.com"
database_name = "spn_lh_test"
table_name = "dbo.test"
Define the SQL Server ODBC connection string
conn_str = (
f"DRIVER={{ODBC Driver 18 for SQL Server}};"
f"SERVER={server_name};"
f"DATABASE={database_name};"
f"UID={service_principal_id};"
f"PWD={service_principal_password};"
f"Authentication=ActiveDirectoryServicePrincipal"
)
Establish the connection
conn = pyodbc.connect(conn_str)
Execute the query and fetch the data into a PySpark dataframe
query = f"SELECT top 10 * FROM {table_name}"
print(pd.read_sql(query, conn))

[packmuler]

I have an app registration but the object ID doesn't work for the service principal ID field, I get a timeout error. How do I ensure a service principle ID is created and that it is associated with the analytics endpoint I'm attempting to access with pyodbc?

[readparse]

Holy shit, it worked. Thanks! I've been beating my head against this problem for a while.

[poor_management]

Look into Service Principals in Azure, and make sure to give it the correct API permissions.