Published on January 14th, 2016 | by Fredrik Knalstad0
Quick Post: Cannot connect to Analysis Services Server due to an effective user name mismatch.
Earlier this week I was working on a PowerBI project for a customer. They wanted to connect their PowerBI account to a ON-Prem SSAS solution.
The installation of the SSAS Connector is quite easy. -> https://powerbi.microsoft.com/en-us/documentation/powerbi-analysis-services-connector/
During the installation we got this error message:
Cannot connect to ‘DB06’ Analysis Services Server due to an effective user name mismatch.
So what we had done during the installation was the following:
1. Logged in with a PowerBI user account Firstname.Lastname@Powation.com
2. Added the Analysis Services Server: ( Servername\Instance) & Windows User Name: (powation\username) Account that has access to the SSAS Instance ( Read)
Then we got the error message. So after a little digging on the support page for PowerBI, the following guide lines was outlined.
The e-mail address users sign into Power BI with is known as an effective username. In-fact, when connecting to Analysis Services, it’s a value in an EffectiveUserName connection string property. Each time a user interacts with a report connected to Analysis Services, the effective username is passed to the Analysis Services Connector and then onto your on-premises Analysis Services server. It must then be converted by an Active Directory server in the same domain to a Windows domain\username account specified for the roles that Analysis Services use to secure your data.
After checking PowerBI and AD on Premise, we could see the following.
Powerbi Account: Firstname.Lastname@Powation.com
AD OnPremise: FirLas@Powation.com ( first 3 in Firstname and Lastname)
So the effective username in PowerBI mismatched with the username in the Local AD. Even if we added UPN suffixes to it.
So after changing the Powerbi account to match the AD On-Premise (username) the connector was successfully installed.
TIP: A good practice is to create a PowerBI Service account and connect the account to the Connectors, then share rights on SSAS Instance.
More deep dive on the PowerBI SSAS Connector -> https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-analysis-services-connector-indepth/