Issues connecting to On-Prem Tabular Model

[igordossantos]

All, 

We have the "Power BI Analysis Services" up and running to our tabular models in Dev and Prod environment. 

For some reason, I have three users that are not able to see the databases on-prem, using the 'Get-Data' option. 

These users in question have PowerBI Pro accounts assigned to them, they have access to the server at the SSAS level as well as they are setup as local admin on those boxes where SSAS is running. 

On the other hand, I am able to see and connect to data on-prem. Another account with Domain Admin rights is also able to connect. 

What could be the issue? 

Thanks in advance for the help. 

[waltheed]

Hi,

SSAS Tabular only accepts Windows accounts, I mean accounts that exist in your local Active Directory (AD) domain.

Between logging to Power BI and accessing the data source, smart things happen.

Your local AD has to be synchronized with the Azure AD. So if you are trying this on your non-domain joined laptop, like I was, you can stop here...

The Power BI user is converted into an effective username which should be authorized as user on the SSAS Tabular Database. You can read that here.

Read from Usernames in Power BI and further

https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise-indepth/

Here is how to add User Principal Names, if you are allowed to

https://technet.microsoft.com/en-us/library/cc756018(v=ws.10).aspx

HTH,

[Bjoern]

"they are setup as local admin on those boxes where SSAS is running."

When you say that: Are the 2 users in questions domain-users with local admin rights?

Usually it should work the way you described it.

[igordossantos]

That is correct. They are domain users with local admin rights as well as they are part of the Server Administrators Role in SSAS. With the above, I'm still getting the error messages described on my post. 

[Seth_C_Bauer]

@igordossantos Did everything check out in the links? Are these a subset of users, and there are other users that see things as they should?

[igordossantos]

@Seth_C_Bauer, Yes as well. The I read the links entirely, and nothing relevant to this issue came up. I'm not sure what to do next. 

[Seth_C_Bauer]

@igordossantos I'm currently learning the Azure AD side of things, so I can't speak deeply on that topic atm, but have you verified that the users are set up the same as you and the other working user?

How did you implement AAD? To me the "impersonate" leads me in that direction.

[igordossantos]

@Seth_C_Bauer, 

That's a question I need to ask my Server/Infrastructure Team. Would you be able to help me formulate a question to them?

[mroomi]

igordossantos I had to add my users in the Tabular Model.

Select Roles --> Create One and add those users under Members Tab.

[Seth_C_Bauer]

@igordossantos I talked to my Azure expert here, and he confused me greatly 🙂 But it leads to other q's.

1) Are all the users connecting from inside or outside the proxy (Is everyone on the internal network) when connecting?

Before I start spewing more info, let me know about the above first.

[igordossantos]

@Seth_C_Bauer,

Everyone is inside the network, physically at the same location.

They autheticate against AD using their local machine, then login to PowerBI.com. 

All 5 users included on the process are authenticating the same way as the above. 

2 users are able to get data from on-prem sources. 

3 users are not able to get data from on-prem sources. 

[igordossantos]

All, 

After 1 month and 2 days, we finally got to the bottom of the issue. 

I opened a ticket with Microsoft to troubleshoot the issue. The engineer I was working with worked on comparing outputs of whoami /all across different users (the ones experiencing the issues vs. the ones not experiencing the issue) to see what kind of membership at the domain level one user had that the other didn't. Unfortunatelly, we did not get anywhere with this approach. 

The Engineer then brought someone from Directory Services into the issue. The DS Engineer requested us to add the “Authenticated Users” group to the “Pre-Windows 2000 Compatible Access” group and like magic, users were able to access data on-premises from PowerBI.com. 

After the above was done, I introduced two new users to the equation by:

• Assigning user a PowerBI Pro account
• Added user to the Data Management Gateway group on the machine where the Data Management Gateway service is running
• Added user to Read Role on SSAS
• Shared a test dashboard connecting to SSAS on on-premises with the two users

The users are now in business. We can now roll this out to production.

Is anyone from Microsoft watching this forum? Is the above documented anywhere? If not, this should be documented somewhere. 

@Seth_C_Bauer @Bjoern

[Bjoern]

Great - thank you for the detailed investigation and sharing it here in the community!

[Seth_C_Bauer]

@igordossantos Thanks for the loop in to the resolution.

[igordossantos]

@Seth_C_Bauer, that is 100% accurate. 

Myself and a user with Domain Admin rights are able to see data on-premises without issues. 

3 users with PowerBI Pro accounts, part of SSAS Admin Role and local admin on where SSAS is running cannot connect. 

Given this specific informational message below, something tells me that they need special permissions that allows them to impersonate accounts. Does that ring a bell to you?

Informational:

Do impersonation for user domain\bi-asconnector-dev. Activity ID: 05c5f73d-1d5b-45e3-969f-3d2ac4302861
Activity ID: 05c5f73d-1d5b-45e3-969f-3d2ac4302861

I have opened a PowerBI Pro ticket to look into this issue. I'll post the results once I hear back from them. Let me know if you can help out. 

[Seth_C_Bauer]

@igordossantos I would first suggest you take a look at the AS in depth documentation under "Where things can go wrong" to check some of the potential issues off your list.

https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-analysis-services-connector-indepth/

in that post you'll also find a link to the troubleshooting page which has additional areas you should verify are set up correctly.

https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-troubleshoot-analysis-service-connec...

Let me know if you've gone thru all this already and still have an issue, otherwise there is a wealth of information to verify in these to posts that will most likely lead to a resolution.

[igordossantos]

Eno, 

I'm going through the links you sent right now. 

Below are the informational and error messages that both of my users are seeing when they try to connect to the data on-prem:

Informational:

Creating new MSOLAP connection to AS server. Activity ID: 05c5f73d-1d5b-45e3-969f-3d2ac4302861
Activity ID: 05c5f73d-1d5b-45e3-969f-3d2ac4302861

Informational:

Do impersonation for user domain\bi-asconnector-dev. Activity ID: 05c5f73d-1d5b-45e3-969f-3d2ac4302861
Activity ID: 05c5f73d-1d5b-45e3-969f-3d2ac4302861

Error:

Open MSOLAP connection failed. The connection string is Provider=MSOLAP.5;Data Source=server|dev;transport compression=None;protocol format=XML;effectiveusername=user@domain.com;sspropinitappname="PowerBI - Model Creation";timeout=180. Activity ID: 05c5f73d-1d5b-45e3-969f-3d2ac4302861
Activity ID: 05c5f73d-1d5b-45e3-969f-3d2ac4302861

Do they need any specific rights at the AD level in order to impersonate a the login? Because remember that I and another domain admin user can get to the on-prem data.