I’ve been noticing a lot of discussion on the community site, in my emails, and in conversation from people having trouble connecting to Analysis Services through the Power BI Gateway – Enterprise. I thought it might help if I break down what actually happens, and what you need to configure to create a successful connection.
Data source account
After you have setup the enterprise gateway, you will want to create a data source for your Analysis Services database.
The user that you supply for this data source is important, as this account will be used to make the initial connection to Analysis Services. In this example, I’m going to use my GUYINACUBE\PBIConnector domain account.
Analysis Service server admin
The account you used for the data source has to be a server admin for the Analysis Services instance. This is because the enterprise gateway will use the EffectiveUserName connection string property, and you have to connect with a server admin in order to use that property. You can verify this by doing the following:
- Connect to your Analysis Services server using SQL Server Management Studio.
- Right click on the server object and select Properties.
- Select the Security tab. You should see the account we used for the data source listed here. If not, you will need to add it.
Your Power BI login and EffectiveUserName
When you interact with a report that uses the live connection, Power BI will send the email address that you used when signing into the enterprise gateway. The enterprise gateway will then connect to Analysis Services with the security context of the account you used as the data source. It also puts your email address into the EffectiveUserName property of the connection string. After it connects to Analysis Services, it will then impersonate the user listed in EffectiveUserName.
The Active Directory UserPrincipalName (UPN)
The email address that is passed into EffectiveUserName has to match a UPN property on a local Active Directory account. For this reason, the Analysis Services machine has to be joined to a domain. The domain of the email address has to match a UPN Suffix within your local Active Directory.
You can add UPN Suffixes to your directory that don't correspond to your actual domain. This is great for demo or testing purposes, but I wouldn’t suggest it for production environments. If the email address does not match a UPN within your local directory, the report will fail.
Hopefully this helps you understand how the enterprise gateway will use your email address from within Power BI to when connecting to Analysis Services.
Adam W. Saxton | Microsoft Business Intelligence
@GuyInACube | YouTube | Facebook.com/guyinacube