Power BI live over SSAS via OPDG

The connection suddenly stopped working

Prelude

Last week, Lorenzo and I were contacted by a customer regarding an issue where a Power BI semantic model, connected live to SQL Server Analysis Services (SSAS) Tabular, suddenly stopped working.

This is a scenario we’ve encountered multiple times before, so I wanted to take a moment to share the solution, explain why this happens most of the time, and provide guidance on how to prevent this issue from happening in the future.

Live connection storage mode means your semantic model acts only as a bridge between your reports and an existing semantic model stored in Analysis Services that keeps all the data and model definition (schema and measures).

The scenario

The Tabular instance in this case is hosted on-premises, and the customer uses an On-Premises Data Gateway (OPDG) to connect the Power BI service to SQL Server Analysis Services using a service account without MFA.

Everything had been working smoothly for months, but the connection suddenly stopped functioning with no apparent changes made in both SSAS and On-Premises Data Gateway. Because of this, all users attempting to access reports linked to the live-connected semantic model receive an error similar to the following:

This report couldn’t access the data source. Please contact the report author to correct the issue.

Image showing a report presenting the error “This report couldn’t access the data source”

This kind of scenario is quite common, fortunately, the resolution is simple.

The resolution

According to the documentation, the user defined in the on-premises data gateway data source for connecting to the SSAS Tabular database must be a server administrator on the Tabular instance.

Image showing how to add a “server administrator” to SSAS Tabular instance

In other words, it’s not sufficient for the user to simply have access to the Tabular database or be an administrator at the database level — they must have the highest level of permissions on the SSAS instance itself (i.e. be a server administrator).

Therefore, the solution was quite simple: we asked the customer to add the user defined in the data source to the server administrator role on the Tabular instance and everything started working again.

Why is this so common?

The reason I’m writing this blog post about what seems like a simple case is because we’ve seen this issue arise in many environments. Most of the time, customers are unable to explain why the integration suddenly stops working.

In this case, just like in many others, no one had touched the Tabular model’s permissions, and no one had explicitly removed the account from the server administrator role. So we had to dig deeper to find a proper explanation.

One commonly overlooked detail is that users granted administrator privileges on the virtual machine running Analysis Services automatically inherit the server administrator role on the Tabular instance. As shown in the screenshot below, built-in administrators are included by default in the SSAS server administrator role.

Image showing BUILTIN\administrators groups under Server Administrators for Tabular instance

Let’s pretend an IT team, unaware of this inheritance, removes what they believe to be unnecessary administrative rights from a service account on the machine. Doing this they remove both admin rights on the virtual machine and its server administrator permissions on the Tabular instance, breaking the connection between Power BI and Analysis Services.

In the end, nobody touched the Analysis Services instance, the Tabular database, the On-Premises Data Gateway or the semantic model but the connection is broken.

To better control this setting, that is mandatory for our scenario, we could:

• Explicitly add the service account used in the On-Premises Data Gateway connection to the server administrator role in SSAS
• Create a dedicated Active Directory group (i.e. AD\TabularAdmins) and add it to server administrators role in SSAS

This second approach is often preferable, especially when migrating to a new server, because it makes very clear which accounts are required to administer and run workloads relying on the Tabular instance.

Conclusions

When connecting a live semantic model to an on-premises SQL Server Analysis Services (SSAS) Tabular instance, the user used for the connection must be a server administrator on the SSAS Tabular instance.