Can we connect to Power BI's datamart using Python?

[OmarE]

Hey,

I'm following up on this post: How to connect to Power BI's datamarts using ADF or Python? 

Is there now a way to connect to PowerBI's Datamarts to query/read the data from a Python package (e.g. pyodbc)? If so, how?

[v-pnaroju-msft · Accepted solution]

Hi OmarE,

Thank you for your response.

Please find below the key points when connecting Python to Power BI Datamarts, which may help in resolving the issue:

1. SQL authentication (UID/PWD) is not supported for Power BI Datamarts.
2. Users with Multi-Factor Authentication (MFA) must use Authentication=ActiveDirectoryInteractive, which will trigger a login pop-up for authentication.
3. For users who do not have MFA enabled, Authentication=ActiveDirectoryPassword can be used. However, this method is only applicable if the organisation does not enforce MFA.

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.

[OmarE]

Thank you for the guidance. I connected Python to the datamart using "Authentication=ActiveDirectoryInteractive;" in the conn_str variable provided by @GilbertQ , which triggered a pop-up login where I could enter my email, password, and OTP code to authenticate. However, I couldn't get it to work by specifying only the username (e.g."UID=your_username;") and password ("PWD=your_password") as in the sample code. From my research, it seems that UID/PWD should correspond to SQL database credentials. Can you confirm if that's still the case for Power BI Datamarts, or is there a way to authenticate using my own user credentials (even if my organization uses multi-factor authentication)?

[v-pnaroju-msft]

Hi OmarE,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft]

Thank you, @GilbertQ , for your response.

Hi @OmarE,

We appreciate your inquiry on the Microsoft Fabric Community Forum.

Please refer to the following documents, which may help resolve the issue:

Introduction to datamarts - Power BI | Microsoft Learn
Administration of datamarts (preview) - Power BI | Microsoft Learn
Download ODBC Driver for SQL Server - ODBC Driver for SQL Server | Microsoft Learn

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.

[GilbertQ]

Hi @OmarE 

Here is a sample path and code below that I got from copilot, which should help you to connect to the data mite using the SQL Endpoint connection.

pip install pyodbc

import pyodbc

# Define your connection string
conn_str = (
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=your_server_name;"
    "DATABASE=your_database_name;"
    "UID=your_username;"
    "PWD=your_password"
)

# Establish the connection
conn = pyodbc.connect(conn_str)

# Create a cursor object
cursor = conn.cursor()

# Execute a query
cursor.execute("SELECT * FROM your_table_name")

# Fetch and print the results
for row in cursor.fetchall():
    print(row)

# Close the connection
conn.close()