Enterprise Gateway unable to retrieve SSAS tabular data on SQL Server 2016 CTP

[Anonymous]

I have installed the Enterprise Gateway on a server running SQL Server 2016 CTP.  The install was successful and I'm able to create the data sources in the Power BI web portal.  There the datasource test runs fine and a SQL Profile trace shows two queries on the SSAS tabular instance for cube info (showing the ID i'm signed into the Power BI service with).  I then created a report in the designer from the SSAS tabular data and published it to the Power BI Service.  When I attempt to view the published report, or coresponding dataset, I receive the error "Data source access error.  Please contact the gateway administrator."  The SQL Profile trace at this point does not show any traffic related to that attempt.  I also noticed that in event viewer security section I am receiving am Audit Failure error listing the SSAS service account id (NT SERVICE\MSSQLServerOLAPService).  There are several successful logins around this one.  I also did the trace configurator steps defined in this post (https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise-tshoot/).  Those files showed the error The user name or password is incorrect. 

Questions:

• Has anyone got this to work on SQL Server 2016 CTP?
• Is there any specific edition of SQL Server required in this scenario since its a CPT? 
• My SSAS and Gateway are all on the same box.  All services are running using the default IDs from the installs.  Do any of these need to be running as domain accounts?
• Is there any requirement for Kerberos configuration for the SSAS instance?
• I was able to connect to the SSAS instance from the Power BI Desktop fine so I'm pretty sure my Tabular security is OK.  I am a server administrator on that tabular instance.  Note that it is a default instance running on the default port.  I am also able to connect to it from different computers so I suspect the firewall settings are all OK.  Is there anything special within the SSAS Tabular database security that needs to be set?

I did try running the SSAS instance as a domain account and received the same results. 

I was able to get the gateway to work successfully with SQL Server relational on this same box.

Any insights are appreciated.

Update:  I'm beginning to suspect this is a permissions issue and something to do with a mismatch in the email address/AD/UPN mapping between the PowerBI service and my local AD domain.  I believe our exteral domain is different then our internal one.  Does anyone have any problem solving steps that might be able to be used to determine what ID is being presented to the local AD for authentication?  Can anyone explain why the manage gateway function would work successfully in a scenario like this whereas the actual report queries would not?  I suspect its that the manage gateway queries are not using effectiveusername whereas the report queries are.

[Anonymous · Accepted solution]

Folks, we were able to resolve our issue.  It was with the login to local AD account resolution.   Here is some text I received from our Network admin.  Your scenario may be completely different but this might be something to check.

The issue was because our local domain UPN did not match our Office365 Tenent UPN (xxxxx.com).    We cannot easily change our local domain name, and we cannot sync our local domain name out to Office365 because it is not an externally routable domain.  For it to be externally routable, it would have to end in something like “.com”, “.net”, etc.   Others may run into this because it is a common practice is to name ones local domain name as something that is not externally routable, such as “companyXYZ.local”.

A workaround to this problem is to add the external domain as an alternate UPN suffix on the domain.  You can do this in the Active Directory Domains and Trusts properties.  Just type the domain suffix in the UP suffixes field, and click the Add button.   After this is done, for each user that you want to be able to authenticate with the alternate UPN, you need to change their AD account properties and change the logon name UPN suffix from whatever the default was to your new suffix.  These suffix changes in AD can be scripted via PowerShell for large changes or many users.  The downside to this suffix change is if you have any applications or services that are using the old suffix for authentication, the application or services will need to be updated, or if they can’t be updated, they will be unavailable to those associates setup to authenticate with the new UPN. 

[Anonymous]

Just a note, the enterprise gateway now supports UPN mapping so this is an alternative as well.

[Kraaitje]

I'm facing the same problem. Direct Query via the Gateway on my SQL database works. Now I want to connect my Power BI Report to my SSAS Cube (Tabular mode) to make use of a central place for all my formulas and data. I think I have setup everything correctly but still no report. I have mapped the power bi e-mail to my local server accountname.

My SQL profiler show an error but no details.

Note: my server is not part of any domain, it's just a stand-alone server. Maybe this is causing the problem?

C:\Users\Administrator>whoami /upn
ERROR: Unable to get User Principal Name (UPN) as the current logged-on user
is not a domain user.

So I don't have a UPN 

Screenshots:

Gateway to SSAS cube works !

MAP USER NAMES IN THE DATA SOURCE (Power BI Account to Local Server Account)

POWER BI INTERFACE WHEN OPENING THE REPORT:

SQL SERVER PROFILER ERROR:

[asanchez]

Hi .

Was the problem of servers without UPN for the reports relied on with Analysis Service?

Please, if someone has managed to confirm it, that is, publish a report made with analysis service on a server without UPN to the professional power BI service.

Thank you


Greetings.

[Anonymous]

When you specified the userId and password when building the data source in the enterpise gateway was that user an Admin on the SSAS instance itself.  I'd check that.  I believe that needs to be the case for the EffectiveUserName to be accepted in the first place.

[Kraaitje]

IT is. I think it only works with a domain, thats **bleep**ty

[Anonymous]

I would guess you are correct. 

[stevo]

I'm having exactly the same problem. Works fine with DirectQuery to the relational database but not with Anlaysis Services. I get 'Data Source Error' and in my event viewer and sql profiler it is due to a 'bad user name or password' relating to Kerberos.

I've tried everything relating to security, impersonation, changing UPNs etc but no luck. Also downloaded the latest versions of desktop and gateway.

I've had a support ticket on the go for over a month now and when they finally respond after 5 days they don't have any helpful solutions.

Any advice would be greatly appreciated...

Thanks