While troubleshooting some Power BI Data Gateway errors for one of my solutions, I came across a good post from Adam Saxton that walks you through how to troubleshoot common errors when working with On-Premises Data Gateways. You can check out his post here: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem-tshoot/
In my case, my issue was dealing with authenticating users with an Azure cloud domain trying to access an on-premises Analysis Services Tabular data source. When this happens, you will get a somewhat vague error that says “Error: Data source access error. Please contact the gateway administrator.” As Adam’s post points out, this occurs because the EffectiveUserName being passed to the cube is using a user principal name (UPN) that doesn’t match the local UPN. In other words, because the user is in a different domain, it tries to authenticate it with those credentials against the cube, and you get a login failed error. Because Analysis Services uses Windows Authentication, you can’t add these accounts to the list of authenticated users in the cube, resulting in users not being able to view/access reports that use an Analysis Services cube as their data source.
The initial workaround for this was to create an alternate UPN suffix for the external (Azure) domain for each user. This would involve getting your AD administrator to make these changes and updating applications that may be using the old suffix for authentication, etc., etc….it just didn’t sound like a very good solution. Luckily, the Power BI team realized this and released a more feasible solution in the Power BI update in April of this year (you can read the full blog post here). With this update, you can give your users an alias directly on the Power BI Gateway, removing the need to make any changes to Active Directory, and keeping the changes specific to the Power BI application.
To configure this rule, you need to go to the “Manage Gateways” option from Power BI, and then choose the Gateway and Data Source that is connecting to Analysis Services.
From there, click on “Users” and you should see a “Map user names” option towards the bottom right corner. Clicking on this will open up a window where you can map the external domain to the domain that your Analysis Services cube is using for authentication.
In the new window, create a new rule for replacing the effective user name with a user name that exists in your local domain and that can be authenticated in the Analysis Services cube. Click “Add” to create the new rule. You can test the rule by entering a user name in the Original Name text box and check the value that will be sent to the Analysis Services request.
Tip: Instead of entering a mapping for each of your users, you can simply add the external domain in the “Replace” box, and the on-premises domain in the “With” box. This will apply the mapping to all the external domain users in one easy setting.
If you want to learn more about user names with Analysis Services and how the mapping of user names work, check out Adam’s post and video on this topic.
Hey thanks for the great post
I am getting the same gateway error but the thing is that my server user is not a domain user. In other words there is no UPN for the user. What should i do?
Hi Rashid – in that case you will need to map the server user to a user/account that exists in your domain. For example, if you have JohnSmith@ExternalDomain.com you would map it to an account that exists in your domain, for example JSmith@mydomain.com.
You would need to do that for every user that does not exist in your domain. What I’ve done in these cases, is to map all my users to a service account that has read access to the data on my server.