Fabric data fetching using python

[shubham242]

I have created an azure app from which I need to pull user data present on their fabric workspaces (lakehouses, warehouses etc.).
Fabric APIs like : https://api.fabric.microsoft.com/v1/workspaces/<workspace_id>/lakehouses/<lakehouse_id>

returns onelake paths and connection strings like :

{

"id": "<lakehouse_id>",

"type": "Lakehouse",

"displayName": "TestLakehouse",

"description": "",

"workspaceId": "<workspace_id>",

"properties": {

"oneLakeTablesPath": "https://onelake.dfs.fabric.microsoft.com/<workspace_id>/<lakehouse_id>/Tables",

"oneLakeFilesPath": "https://onelake.dfs.fabric.microsoft.com/<workspace_id>/<lakehouse_id>/Files",

"sqlEndpointProperties": {

"connectionString": ".........dcgootakasfs4i.datawarehouse.fabric.microsoft.com",

"id": "<connection_id>",

"provisioningStatus": "Success"

}

}

}

I have access token from this api:

https://login.microsoftonline.com/common/oauth2/v2.0/token
with scopes : 

"https://api.fabric.microsoft.com/Workspace.ReadWrite.All", "https://api.fabric.microsoft.com/Item.ReadWrite.All", "https://api.fabric.microsoft.com/SQLEndpoint.Execute.All", "https://api.fabric.microsoft.com/Lakehouse.ReadWrite.All"

But not able to connect with server 

I am using  :

connection_string = f"Driver={{ODBC Driver 18 for SQL Server}};Server=<connection_string_from_response>;Authentication=ActiveDirectoryServicePrincipal;UID={client_id}@{tenant_id};PWD={client_secret}"

conn = pyodbc.connect(connection_string)

But getting : InterfaceError('28000', '[28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Could not login because the authentication failed. (18456) (SQLDriverConnect)')


Using other authentication methods like : ActiveDirectoryMsi, ActiveDirectoryPassword etc. give timeout errors

[Anonymous]

Hi @shubham242,

Perhaps you can take a loka te the following link to use client id and secret to getting data based on pyodbc driver:

Load data to MS Fabric Warehouse from notebook - Stack Overflow

Regards,

Xiaoxin Sheng

[shubham242]

connection_string = f"Driver={{ODBC Driver 18 for SQL Server}};Server={server};Authentication=ActiveDirectoryServicePrincipal;UID={client_id};PWD={client_secret};TrustServerCertificate=no"

but while connecting with above I either get login timeout or authentication failed.

[Anonymous]

HI @shubham242,

What type of fabric capacity are you worked? Trial capacity or fabric capacity? If you mean trial version, have you tied to connect with fact fabric capacity?

Regards,

Xiaoxin Sheng

[lbendlin]

Why not use the standard libraries like sempy ?

[shubham242]

I am using this on client app, not on fabric notebook.