Connecting Power BI to on Premises SSAS Tabular without Active Directory Sync (Effective username mismatch Problem)


 

One of the great new features of Power BI is its ability to connect to on-premises SSAS Tabular data models directly with the use of “Power BI AS connector”.

This connector allows you to directly query on-premises Tabular models without caching or refreshing your data, it is simply a pass-through connector between on premises and Azure.

This is quite beneficial when data is updated on regular basis (Near-Real-Time) or the data is sensitive and cannot leave on-premises servers.  Power BI AS connector does not in anyway cache the data, only pass the query parameters at the time of user login and user permissions and security roles apply to the data on the fly with every refresh.

You can find details on how to setup PBI AS Connector here.

However if your Power BI service account domain is not synched locally you will receive the error message: “Power BI user name ‘xxx@xxx.xx’ cannot connect to ‘servername’ analysis Services Server due to an effective username mismatch.”

image

This error suggests that your Power BI username is not matching your on premises username.  A proper solution for this issue would be to perform Directory Sync of your on-prem active directory with your Azure Active Directory, however if all you need to do is a demo or development or if for some practical reasons you cannot setup ADS a nice trick would be to add “Alternative UPN Suffix” to your Azure active directory that would match your on-prem UPN.

For example, if I create my service account under my Microsoft ID (myEmail@microsoft.com )  and I login to my SQL Analysis Services server using (myEmail@contoso.com) then the effective username error above will appear. 

To help resolve this issue if you are doing development or demo work, you can create the same usernames between your Azure and on-prem AD and then add alternative UPN Suffix to your on-prem AD that matches the one from your PowerBI service account.

So the username will have to match, and the domain can be faked.

image

As mentioned earlier for production or proper solution Active Directory on-prem and Azure must be federated and that would be the proper way ahead, however for testing, development or demo we may need to do this workaround.

This work around is possible due to the fact that Power BI Analysis service connector does not use or store user passwords when connecting to SSAS tabular models.

Happy Power BI everyone!

Skip to main content