Creating a connection to a power bi semantic model in a premium workspace using a bearer token

[Corey_M]

Things I have done:

created an app registration in my azure tenant with permissions:

• Microsoft Graph (User.read)

• Power BI Service(Dataset.ReadWriteAll, Workspace.ReaWrite.All)

added the app as an admin to the workspace

obtained access token

I am using python, requests library, and adodbapi 

scope = 'https://analysis.windows.net/powerbi/api/.default'
grant_type = 'client_credentials'
url = f'https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token'

data = {
    'grant_type' : grant_type,
    'client_id' : client_id,
    'client_secret' : client_secret,
    'scope' : scope
}
response = requests.post(url,data=data)
token = response.json().get('access_token')

but when trying to create a connection with a connection string I am struggling:

based on the connection string properties documentation my string should look something like this

connection_string2 = f"Provider=MSOLAP.8; \
    Data Source=powerbi://api.powerbi.com/v1.0/{tenant}/{workspace}; \
    Initial Catalog={dataset}; \
    Password={token};"

when I try this I get an error with "Power BI Request Failed, Action: Resolving the PBI workspace"

to check that the error wasn't caused by incorrect dataset/tenant/workspace I ran with SSPI which works fine.

working_connection_string = f"Provider=MSOLAP.8; \
    Data Source=powerbi://api.powerbi.com/v1.0/{tenant}/{workspace}; \
    Initial Catalog={dataset}; \
    Integrated Security=SSPI;;"   

I believe the error is either in how I am providing the token in the connection string, or with the configuration in obtaining the token. Any help would be greatly appreciated.

[v-yiruan-msft]

Hi @Corey_M ,

Could you please check the following info?

• Ensure that the token you are obtaining has the correct scope and permissions. 
• Verify that the token you are receiving is in the correct format and not expired.
• Confirm that the App Registration is correctly added as an admin to the specific Power BI workspace.

And you can try to use 'Password' and 'User ID' together when using tokens with data source, check if it can work.

connection_string2 = f"Provider=MSOLAP.8; \
    Data Source=powerbi://api.powerbi.com/v1.0/{tenant}/{workspace}; \
    Initial Catalog={dataset}; \
    User ID=app:{client_id}; \
    Password={token};

Best Regards

[Corey_M]

to the best of my knowledge it has the correct permissions and the scope which I detailed before as:

scope = 'https://analysis.windows.net/powerbi/api/.default'
grant_type = 'client_credentials'

created an app registration in my azure tenant with permissions:

• Microsoft Graph (User.read)

• Power BI Service(Dataset.ReadWriteAll, Workspace.ReaWrite.All)

I can also confirm that the app is added to my workspace and also can confirm that it is in a premium capacity workspace.

I tried the connection string you provided and also recieved an error of being unable to authenticate. did you mean to use the client_secret for the password as that gets me back to an error of being unable to resolve the workspace?

[v-yiruan-msft]

Hi @Corey_M ，

I hope this message finds you well. I wanted to check in and see if your issue has been resolved. If not, as you mentioned, you might try using the client secret as the password to see if that resolves the problem. If you have found a solution, could you kindly share it with us? Your insights could greatly benefit others facing similar challenges. Thank you for your contribution.

Best Regards

[Corey_M]

it has not been resolved, I did try using the client secret which returns the original unable to resolve workspace error.