Deep technical Question - Analyze in Excel and auth-claims storage

[ppvinsights]

Hi community,

using the odc-Files which you can export form Power BI Service, you can create connections in excel to Power BI Service. Using some macros, you are able to query the connection.

The authorization is done via OAuth - excel opens a browser dialog.

My problem starts when I want to switch tenants. I export the odc-information form the other tenant and update the connection string in excel (in the properties dialog of the connection). Sometimes this is not working an I get an "forbidden" (the second tentant has the ability to "Analyze in Excel" - so it is an authorization problem).

When any browser connects to Power BI Service an identity token and a session token is generated (as far as I know). Using this information I do not have to sign in every time I come back to Power BI Service. When I delete all cookies, I do have to sign in again.

Where is this authorization information stored, when I sign in fro excel?? And how can I delete this information so that I need to sign in again, when refreshing my data?

I already tried:

- every browser (I signed out form every browser - but I was still able to refresh my data in Excel)

- the login in excel (I thought so, because this is just user for the license for excel and for onedrive etc.. but anyway...)

- using "Profiles"-Button in the Analyse to Excel AddIn - not working (I think that the addIn just updates connections it made itself)

Any suggestions?

Thanks

Holger

[v-lid-msft]

Hi @ppvinsights ,

Based on my research , the first time you open a new .ODC file in Excel you may be asked to sign in to Power BI with your Power BI account. This authenticates the connection from Excel to Power BI.

You can also select Profile from the Power BI ribbon tab in Excel, which identifies which account you're currently logged in with, and provides a link that lets you sign out (and subsequently, sign in with a different account). You can download Power BI publisher for Excel if you don't already have it installed, which is how the Power BI ribbon in Excel is installed.

Based on our test, it will clear and acquire account again if we open a odc file which the previous account does not have premission.

Best regards,

[ppvinsights]

Hi,

thanks a lot for the answer!

But this is not exactly the solutiion I was looking for...

If I import an odc file a data connection is generated in Excel (you can see it in the connections list). Somethimes I have to authenticate with my Power BI User. After I create any kind of report with data of the data connection.

If I now choose "Profiles" from the AddIn Ribbon and select "Sign out" I should not be able anymore to refresh the data connection without signing in again. But: this is possible. Even after closing and reopenind Excel.

And: If you open the same ODC File in another Excel, you do not have to sign in again. The new Excel is using the already signed in user (which was signed out using the AddIn function=.

So: There has to be any kind of storage where excel is storing any kind of an authentification token. But where?

Thanks

Holger