cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marco_fedeli
Frequent Visitor

Weird gateway behaviour - SSAS MD

Hi all,

 

We have a solution that uses SSAS Multi Dimensional Cubes with Power BI service. It works for many of our customers, but with this recent customer, I cannot get it running.

 

I configured everything as usual:

  • Gateway is installed on the sql server where the ssas instance is running
  • Connection to the cubes from PBI service gateway is successful (we use a service user that only has server admin on the ssas instance)
  • All users are "mapped" in the gateway configuration (the M365 users are not the same as the on-prem users)
  • In SSAS, users are assigned to roles

I checked everything multiple times, but all users get an error saying that they cannot access the data source. Accessing the cubes locally works as expected (Excel and PBI Desktop).

 

For testing purposes, I tried different users in the PBI service gateway datasource connection. To my suprprise it started working, when the user was a domain administrator in the local active directory. However, individual users could see all data now and not only the data they are restricted to by SSAS rules.

 

After trying for way to long, I am really hoping that someone can point me into the right direction for solving this issue.

1 ACCEPTED SOLUTION
marco_fedeli
Frequent Visitor

After many more hours of investigation, we finally found a working solution.

 

The service "SQL Server Analysis Services (MSSQLSERVER)" was running under the default user "NT Service\MSSQLServerOLAPService". Changing the service user to a domain service user (e.g. domain\svssas) made all issues go away. I still don't fully understand why, but we are just glad it works.

 

Thank you @GilbertQ for your support.

 

Best,

Marco

View solution in original post

12 REPLIES 12
marco_fedeli
Frequent Visitor

After many more hours of investigation, we finally found a working solution.

 

The service "SQL Server Analysis Services (MSSQLSERVER)" was running under the default user "NT Service\MSSQLServerOLAPService". Changing the service user to a domain service user (e.g. domain\svssas) made all issues go away. I still don't fully understand why, but we are just glad it works.

 

Thank you @GilbertQ for your support.

 

Best,

Marco

Our power bi gateway is setup and declared SQL Analysis Service as data source with administrator on Gateway as well Service Account is set as administrator of SQL Analysis Service.

 

I am trying to created Data Flow with SSAS as data soure using Query generated from Power BI Desktop which is not working and presented with below error .

SSAS cube works absolutely fine with Power BI Desktop.

 

bharatko_0-1647501781697.png

 

Hi @marco_fedeli 

 

The reason it works is because when the service is running under a local system account such as "NT Service\MSSQLServerOLAPService" it cannot query Active Directory.


When you change it it to use a domain account, as you did above it allows it to then query Active Directory and lookup the users!





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

Proud to be a Super User!







Power BI Blog

GilbertQ
Super User
Super User

Hi @marco_fedeli 

 

If you have a look at this documents link you need to convert it from a UPN to Domain\Username

Connection string properties (Analysis Services) | Microsoft Docs

 

You will need to map the usernames as shown below

 

GilbertQ_0-1642059064248.png

 





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

Proud to be a Super User!







Power BI Blog

Hi @GilbertQ 

 

Oh, I thought you were talking about a property on the server. The user mapping is already set up correctly (that's what I ment by "users are mapped" in the original post).

 

Let me explain in some more detail.

Weird behaviour 1: I want to use the service user (e.g. domain\BI) in the data source.

marco_fedeli_1-1642060118335.png

This user is server admin in SSAS and the connection is successful, but all users get the error in PBI Service that the data source cannot be accessed. When I change this user to a domain administrator (e.g. domain\domadmin), users can access the data and view reports, but then SSAS roles do not apply and all users see all data.

Example: Tony is a salesperson. His upn is tony.miller@domain.com and is mapped to his local domain user domain\tmi. There is a role on SSAS that limits him to see only his own sales. When Tony connects to the cube from Excel, this works fine and only his own sales show up. In PBI Service however, he sees all sales.

 

Weir behaviour 2 (and I'm not sure if this is related): in management studio (logged in as domain\bi), i can "change user" to domain\xyz and browse the cube with this users access rights, but when I change to Tony Miller (domain\tmi), I get the error that the username or the passwort is wrong.

Hi @marco_fedeli 


From your description there appears to be an error/issue with the SSAS MD Server when trying to get it working via SSMS and changing the user.


I would suggest chatting to your SSAS Admin to look at SQL Profiler to see what the error is when trying to connect.

 

It could be that the user account has been disabled?





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

Proud to be a Super User!







Power BI Blog

Hi @GilbertQ 

 

I really apprechiate your patience.

 

SQL Profiler shows the same error as management studio (wrong password or user):

marco_fedeli_0-1642142641807.png

 

Out of 12 users, the "change user" works for five and doesn't for seven of them. All users can work properly otherwise, so I don't think that they have been disabled. From an AD perspective, there is nothing special about the five working ones (I don't have access to the AD, but that's what the customer tells me).

Hi @marco_fedeli 

 

I am sure it is because the account has been disabled.


Can you ask your network admin to send you a screenshot of the domain account settings? 

And ask them to just check and possible re-enable the user account?





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

Proud to be a Super User!







Power BI Blog

GilbertQ
Super User
Super User

Hi @marco_fedeli 

 

I would expect it still to work when using a domain admin, as long as the EffectiveUsername is being correctly used?


What version of SSAS MD is being used, if it is an older version that might be the issue?





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

Proud to be a Super User!







Power BI Blog

Hi @GilbertQ 

Thank you for your response. The SSAS Version is 2019 (15.0.32.55).

I also noticed that the "Change User" function in management studio does not work. It states that the username or the password is wrong:

marco_fedeli_0-1642055245446.png

Since the gateway is doing something similar like "change user" (I suppose), could this have something to do with it? And if so, how do I fix it?

Hi @marco_fedeli 

 

Yeah that does seem to be an issue and to fix it I would search for now to get EffectiveUsername on SSAS MD?





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

Proud to be a Super User!







Power BI Blog

Hi @GilbertQ 

Sorry, but I guess I need some more help. How do i get EffectiveUserName on SSAS MD?

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors