Sonoma Partners Microsoft CRM and Salesforce Blog

Connecting the Power BI On-Premise Gateway to a SQL Server Analysis Services Tabular Database

Today's blog post was written by Neil Erickson, Principal Developer at Sonoma Partners.

We’ve been surfacing a lot of our information for internal users via Power BI, and recently we wanted to author a round of reports that accessed an SSAS tabular database directly. We ran into some early snags, which were easily resolved by following some of the solution posted by the Power BI Team here.

Our takeaways from these early issues were to ensure a few things:

  1. Confirm that the account entered in the gateway configuration has been given Full Access within tabular database.

    Nerickson 1
  2. Confirm that the UPN that Power BI is using, the one on your Office 365 User object, matches the UPN of the target User in Active Directory.

Even with these changes in place we continued to see our report failing to load anything. We knew that the gateway was using EffectiveUserName to impersonate the end user, so we tried to determine if this was the culprit. In SSMS you can pass in the EffectiveUserName by going to the Connection Options and selecting the “Additional Connection Parameters” tab. By doing this, we captured in SQL Profiler the same error that we were seeing when the Power BI report would fail.

Nerickson 2

The following system error occurred: The user name or password is incorrect.

Nerickson 3

Nerickson 4

At this point we were confident that permissions were set properly within Analysis Services, so we began looking elsewhere. The step of verifying the UPN led us to Active Directory, and we discovered that making the account that runs Analysis Services an Domain Administrator solved the issue. This was not desirable as a long-term solution for obvious reasons so we continued to try various settings. Eventually whittled down the necessary permissions to Read on the AD User that is being specified by EffectiveUserName. To achieve this, we took the following steps.

1. Create a new Security Group and add the account running the SSAS Service to this group

We did this because we like to separate accounts that run different applications, and we like to retire old account when migrating to new versions of applications. It seems like this permission will be common, so our preference was to add the permission one time and manage the group membership as needed.

2. Grant the new security group read permissions to user objects in Active Directory

This permission can be granted to individual objects, or to an OU at a level that is sufficient to cover any users that would be passed into EffectiveUserName.

Nerickson 5

If this is being granted to an OU, click into “Advanced” and verify that is will apply to descendant objects as well. If it reads “This object only” edit the row and set “Applies To:” to include descendant objects as well.

Before:

Nerickson 6

After:

Nerickson 7

3. Restart the SSAS Service

Initially the report in Power BI still was showing the error but after waiting a few minutes and trying again it work. Are assumption here is Power BI has some caching so key point is when changing domain level security make sure you provide ample time between tests for settings to proliferate through the various layers.

Now our report is accessible on powerbi.com and is connecting to our On Premise Analysis Services Tabular Model deployed in our Data Center via the Enterprise Gateway.

Nerickson 8

Please reach out if you have any questions by filling out our contact us form or commenting below.

New Call-to-action

Topics: Analytics Microsoft Dynamics 365