Analyze in Excel - Connection open regardless of user permissions

[NAOS]

Hi all,

I'm having issues with an Excel connection to a dataset in PowerBI service. While testing I realised users without any permissions to the dataset (or the workspace where that dataset is) could still refresh the connection and obtain the data it provides. I haven't tested this but I assume they could also modify the connection query and retrieve any other data hosted in the dataset.

I connected to the dataset via SSMS and reviewed the connections to the dataset. Any interaction with the dataset coming from Excel show NT Authority\System as the user running the query.

This is the connection string I'm using, which resembles what's created by Analyze in Excel:

Provider=MSOLAP.8;
Integrated Security=ClaimsToken;
Persist Security Info=True;
Initial Catalog=sobe_wowvirtualserver-xxxx;
Data Source=pbiazure://api.powerbi.com;
MDX Compatibility=1;
Safety Options=2;
MDX Missing Member Mode=Error;
Identity Provider=https://login.microsoftonline.com/common,
https://analysis.windows.net/powerbi/api, xxxx;
Update Isolation Level=2

On the excel connection Authentication settings, 'Use the autehnticated user's account' is selected. The workspace is backed by premium capacity.

Anyone that may be able to help on this?

Thanks,

NAOS

[v-kkf-msft]

Hi @NAOS ,

Does your problem have been solved? 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[NAOS]

Hi Winniz,

I'm very sorry for the late reply, somehow the notifications ended up in my spam folder.
I'll test what you suggested in your first response and let you know how it goes, thank you!

NAOS

[v-kkf-msft]

Hi @NAOS ,

When I use the credentials that do not have permissions to the dataset to sign into Power BI, I receive an error titled Forbidden:

Based on my test, once we authenticate the Power BI when we open the .ODC, the data connection will use this authenticated user to connect to Power BI.

There is a manual work around to change accounts for testing.

• Navigate to the ODC file you downloaded.
• Right click the ODC file and edit it in Notepad.
• Find the section that starts with <odc:ConnectionString>
• Add the following text immediately after this string

• User ID = name@youremailaddress.com;

• Save the file, and then double click to open it again.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NAOS]

Hi Winniz!

Unfortunately I have had no luck with this. My 'test' colleagues can still access the dataset even if I remove their permissions.

A bit more background to what I'm doing. I'm querying the data using DAX and triggering a data refresh via a macro when the user presses a button. We have also tried refreshing the data manually to make sure the macro isn't somehow providing the permissions. Regardless of how they refresh the data, they can still do it and get the data from the dataset (while not having any kind of permissions in the dataset).

If I look at the connections to the dataset, I can see that their queries are logged under the Session_User_Name: NT Authority / System, and not their personal credentials as happens when querying the dataset from Power BI (like those rows blanked out in the image below):

I don't know if I'm doing something wrong or if this is a genuine security problem with the datasets.

Look forward to hearing from you. If you'd like to contact me directly and/or have a call feel free to reach out.

Thanks,

NAOS

[NAOS]

Hi @v-kkf-msft ,

I should have tagged you on the above. Any further suggestions?

Regards,

NAOS

[v-kkf-msft]

Hi @NAOS ,

Do you add these users to any role in the workspace?

Try to export connection file and use accounts that do not have permissions to the dataset to sign in. See if it works.

There is a manual work around to change accounts.

• Navigate to the ODC file you downloaded.
• Right click the ODC file and edit it in Notepad.
• Find the section that starts with <odc:ConnectionString>
• Add the following text immediately after this string
  User ID = name@youremailaddress.com;
• Save the file, and then double click to open it again.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.