cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

How does "Analyze in Excel" know who I am? How to clear credentials?

I am logged onto my home computer. I have no saved connection to my work PC / Azure AD login on this computer that I can find anywhere (System Settings > Accounts, or Excel > Data Sources).

 

I have a model in the Power BI service. On my work PC I did Analyze in Excel. I export the connection as an ODC file. The I copied it to my home computer.

 

I can now go into my home computer and open this ODC file. It does not ask me for connection credentials. It lets me query anything in the model. When I pull the UPN (through a measure I have in the model), it shows "NT Authority\SYSTEM". That's scary. So I assume my RLS won't even work. What is going on here? Excel will connect to my model despite not knowing who I am (i.e. my UPN).

Capture.PNG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Re: How does "Analyze in Excel" know who I am? / Scary Security Loophole?

Well I figured it out myself through process of elimination. I systematically deleted every file in my user folder in Windows, then rebooted. Great way to spend an entire afternoon.

 

...And the top secret undocumented location of the saved credentials for Excel to connect to the Power BI service (until they change it again) is:

 

%userprofile%\AppData\Roaming\Microsoft\Protect

 

Delete that entire folder, reboot, and the next time you open Excel you'll be asked to log in to Power BI again. It also clears a saved login if you're using OneDrive to sync with Windows, but whatever. Now I can test as different users in Excel. Hooray.  I'm disappointed that this was so hard to figure out.

 

Now I just have to figure out why USERPRINCIPALNAME() returns "NT AUTHORITY\SYSTEM" instead of, you know, my user principal name.

View solution in original post

4 REPLIES 4
Highlighted
Super User IX
Super User IX

Re: How does "Analyze in Excel" know who I am? / Scary Security Loophole?

@Sorphicle - I am fairly certain that your credentials are stored somewhere after reading through this documentation: https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-analyze-in-excel#:~:text=%20In%2...

 

Also, I thought I just read where Analyze in Excel was going away from ODC files...  @marcorusso was that you that I heard about that from?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

Re: How does "Analyze in Excel" know who I am? / Scary Security Loophole?

The Analyze in Excel feature you use to connect to powerbi.com uses the OAuth2 credentials to connect to an external service. You can see that user through the USERPRINCIPALNAME function in DAX: https://dax.guide/userprincipalname/

 

When you use Analyze in Excel for Power BI Desktop (https://www.sqlbi.com/tools/analyze-in-excel-for-power-bi-desktop/) you connect locally and in this case, you use integrated authentication instead of OAuth2. The version 1.0.x of this tool used the ODC file to create a local connection, whereas version 1.1.x creates an XLSX file writing the connection string straight into the XLSX file, unless there is some issue to do that, in which case it reverts to the ODC file. In both cases, the connection string is the same and uses integrated security with a local user that is an administrator of the PBIX file, so RLS does not apply when you use Power BI Desktop in any case.

Highlighted
Helper I
Helper I

Re: How does "Analyze in Excel" know who I am? / Scary Security Loophole?

I just tried this again on a computer I've never used.  I took that XLSX file with the embedded connection to the PowerBI.com model.and opened it.  The first time I opened it, it did ask me for a login to the Power BI service, which is somewhat comforting. But I have 2 major concerns remaining. 

 

1) Where are these credentials stored and how can I clear them? 

I've tried deleting every cookie on my system, deleting every certificate on my computer, and deleting everything in Control Panel > Credentials. But every time I open this Excel file, it is already connected to Power BI. Why? I need to test this model with diferent users to verify RLS, and I cannot. This used to be in Excel under DATA > GET DATA > DATA SOURCE SETTINGS, I think. But now there is nothing there.

 

2) Why does Excel show my User Principal Name as NT AUTHORITY \ SYSTEM if I am authenticated as myself? 

 

I need to be able to rest easy that I am not exposing all my corporate data to anyone and everyone.  Thanks very much!

Highlighted
Helper I
Helper I

Re: How does "Analyze in Excel" know who I am? / Scary Security Loophole?

Well I figured it out myself through process of elimination. I systematically deleted every file in my user folder in Windows, then rebooted. Great way to spend an entire afternoon.

 

...And the top secret undocumented location of the saved credentials for Excel to connect to the Power BI service (until they change it again) is:

 

%userprofile%\AppData\Roaming\Microsoft\Protect

 

Delete that entire folder, reboot, and the next time you open Excel you'll be asked to log in to Power BI again. It also clears a saved login if you're using OneDrive to sync with Windows, but whatever. Now I can test as different users in Excel. Hooray.  I'm disappointed that this was so hard to figure out.

 

Now I just have to figure out why USERPRINCIPALNAME() returns "NT AUTHORITY\SYSTEM" instead of, you know, my user principal name.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors