Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

6 REPLIES 6
Greg_Deckler
Super User
Super User

@Anonymous - 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%20the%20Power%20BI%20service%2C%20navigate%20to,Enable%20Editing%20and%20then%20Enable%20Content%2C...%20More%20

 

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Hello maestro,

I'm having a similar issue with an Excel connection to a dataset in PBI service. I was given the dreaded task of creating an Excel macro which would query data from PBI service. Everything worked well until I realised that users without the required permissions on the dataset could still query it.
I connected from SSMS to the dataset and noticed the query was executed by NT Authority\SYSTEM regardless of whom used the macro.

 

I tried modifying the connection string to prompt user to enter their credentials without success.

I may be wrong but I agree with the user that posted this. 'Analyze in Excel' feature from PBI service is indeed leaving the door open to anyone that can get their hands on a file with a connection...

Any ideas?

 

Thank you and thanks for all the great work! Keep the unplugged sessions going, they're absolute gems!

User credentials are stored neither in the ODC nor in the Excel file. User credentials are stored in the local user profile, so your risk is the same you have if you open Power BI Desktop and connect to a dataset on powerbi.com. If you copy the file on another PC, the user credentials are those of that PC.

Please note that user credentials used to connect to Power BI service (powerbi.com) are not the user credentials of your local users - you are using Azure Active Directory (OAuth) credentials.

 

Anonymous
Not applicable

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!

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors