How do I check that Row Level Security, as applied in Power BI, is working for Analyse in Excel pivot tables? I have tried changing the user that I am signed in to Excel as, but that doesn’t seem to make any difference.
In fact, my Excel defaults to my personal outlook.com user and despite the fact that this user has no permissions to anything on the corporate xxxx.com domain, the Analyse in Excel pivot tables (and Cubevalue formulas) bring back data. I am using my own PC, which I log into with an account that is not my corporate xxxx.com account. It is a mystery to me why Excel lets me anywhere near the corporate data.
RLS roles have been created in the models that I am using in PBI Desktop then corporate AD groups, which my personal account is not connected to, assigned to each of the roles in the Service. These files should be locked down. It’s convenient, but worrying, that I have access.
Apologies if this post did not end up in the right category. There didn’t seem to be one that was a great fit.
Thanks
Ian
Hi DavieJoe,
Thank you for the response but unfortunately that article did not help. What I am trying to test is the RLS security when the connection to the model is made using Excel. In particular via Analyse in Excel, but I have another post asking roughly the same thing about Excel connections made using Get Data > From Power BI. Power BI has features in the Desktop (View As) and in the Service (Test as role) for doing this. I am trying to find something similar for connections within Excel.
Changing the user that I am signed in to Excel as did not seem to make any difference, even when I signed in as a user with no permissions at all.
I wonder if the question I should be asking is – how do I force a connection in Excel, to a model in the Power BI Service, to require a login?
I’m not getting very far with this despite extensive experimenting and Googling. I could be wrong but it looks as though a connection to PBI in Excel only requires credentials once. After that the credentials appear to be baked in to the connection and changing the user I am signed in to Excel as does not make any difference. I’ve tried all three of the options available from the Authentication Settings button on the Connection Properties dialogue, but again, nothing seems to change. I can still refresh or change items in the pivot table without being asked for credentials.
Where the credentials are being stored I don’t know. I can’t see them in either Excel or my Windows 10 Credential Manager.
Apologies, but I don’t understand your reply.
I’m not getting any errors - I’m just not able to do something equivalent to the View As or Test As Role features when I’m using Excel to connect to a PBI Service model. I’d like to be able to force Excel to connect using a different security context so that I can confirm that the RLS is working as expected.
Ultimately I want to see if connections created using the Analyse in Excel feature behave differently to those created using Get Date > From Power BI.
Thanks
Hey @IanR No need to apologise, perhaps I’m being unclear, I’ll also add that I’ve not really used Analyze in Excel much (Certainly not in this scenario)
From what I can gather “row-level security (RLS) is supported for Analyze in Excel. RLS is enforced at the data-model level”.
So I believe your Power BI Service Model/data model should have the RLS you wish to have in place and this will be mirrored in Analyze in Excel. I don’t think you are able to force Excel to connect using a different security context as far as I’m aware. Do you have a user you can test with?
I’m relatively happy that Analyse in Excel honours the RLS in the model, but do you know if ‘Get Date > From Power BI Dataset’ produces a connection that is in any way different to those created by Analyse in Excel? We have a number of Analyse in Excel documents connected to our UAT system. The testers have done some bespoke formatting of these files and the files now need to be switched to point at the live system, without losing any of the formatting. My approach to achieving this is to create another connection in the Excel file, pointing to live, and then to use the Change Data Source feature to point pivot tables at the new connection (and Find and Relace to update the connection names in Cubevalue formulas).
The only way that I can think of to add a new connection is to use Get Date > From Power BI. That works perfectly, and I can happily switch between the data sources. However, I worry that there might be something different about Get Data > From Power BI connections. Analyse in Excel requires installation of an add-in, so I’m assuming that something ‘extra’ is being added, beyond a simple connection. That’s what has got me worried about security.
I don’t have a user to test with yet. That will eventually change, so all is not lost if I can’t get to the bottom of this.
I don’t know the answer to this one either, but suggest you go to this page:
and ask your question in the discussion section at the bottom of the page. Marco Russo monitors this page quite closely and is very generous with his time in responding to technical questions about the Analyze in Excel tool.
Hi @Ian, we’ve noticed that no response has been received from you since November 2.
We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Apologies for the lack of a response from me. I’ve been trying to solve the situation in another way. Thank you to DavieJoe who had a go but unfortunately I haven’t yet found a solution. I will follow up on BrianJ’s suggestion to ask the question on the SQLBI site. I’ll let you know.
To be honest, this looks like a gap in the Power BI ecosystem.
Hello @IanR, just following up if the responses above help you solve your inquiry?
We’ve noticed that no response has been received from you since November 1. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
I have submitted the question to the Marco Russo article as suggested by @BrianJ . I’ll report back how I get on.
Incidentally, that article was about the Analyze in Excel external tool for Power BI Desktop rather than the Analyze in Excel feature from the service. Hopefully Marco won’t mind.
I’ve always found them to be very helpful and responsive so I’m sure if they can’t help directly they will provide some guidance to help you solve this.
Problem(s) solved. I had a reply from Marco Russo:
The connection string you get it’s the same. The more recent versions of Excel (that have Get Data → From Power BI Dataset feature) already have the necessary update to OLEDB drivers that are the reason why you are asked to download a file (it’s not an add-in, just an update to the OLAP OLE DB driver).
Yes, connections honor security. You can change connection properties by going in Existing Connections / Connection Properties dialog box / Definition tab. In the Connection String add the Role=… and/or Username=… to simulate a user belonging to a role as you can do in DAX Studio.
I haven’t found much on how to change the user or role in the connection string. This was helpful:
There was no existing User=”” term in the connection strings created for me by Analyse in Excel. What I ended up doing was placing this at the end of the string:
;USER=”xxxxxxxxx”
Where xxxxxxxxx is the username/email address. Note that the quote marks were required even though the username had no spaces.
This worked. It forced a login when I tried to refresh, after which I could only see data that the user had permission to see. One weird thing. When I went back to look at the connection string again, before writing this, the User= term had disappeared. It’s as if it was used then destroyed. Maybe that’s good security practice? I don’t know.
