Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kobe24
Frequent Visitor

Enterprise Gateway unable to logon to SSAS server

I set up the Enterprise Gateway to connect to an SSAS server (SQL Server 2012 SP2). The connection tested fine in the Gateway management page, and when I tested the connection, the enterprise gateway successfully logged on to SSAS server. Here is the what the windows security logs captured:

 

An account was successfully logged on.

Subject:
Security ID: NT SERVICE\MSSQLServerOLAPService
Account Name: MSSQLServerOLAPService
Account Domain: NT SERVICE
Logon ID: 0x86082b0

Logon Type: 3

New Logon:
Security ID: MYDOMAIN\myUserName
Account Name: myUserName
Account Domain: MYDOMAIN
Logon ID: 0x861799c
Logon GUID: {30e418ff-065b-e9ca-ad0f-a3fe31cd1430}

 

After uploading a PowerBI report using the data source in this gateway (the report uses live connection to SSAS server), the report is unable to refresh and reported data source access error. The SSAS trace captured an error: The following system error occurred: Logon failure: unknown user name or bad password. 

 

Windows security log captured:

An account failed to log on.

Subject:
Security ID: NT SERVICE\MSSQLServerOLAPService
Account Name: MSSQLServerOLAPService
Account Domain: NT SERVICE
Logon ID: 0x7b290ce

Logon Type: 3

Account For Which Logon Failed:
Security ID: NULL SID
Account Name:
Account Domain:

 

It seems like when the report try to access data through the Enterprise Gateway, it is unable to pass stored credentials when trying to log in. But it obviously passed the stored credentials when I tested the data source in the enterprise gateway management page

 

Any idea why this is happening? Thanks.

1 ACCEPTED SOLUTION
GuyInACube
Employee
Employee

I just tried this with a SQL 2014 SP1 AS MD instance and it worked without problems for multiple users. Seems like it may be environmental. The test connection for the data source will use the credentials you supplied in the data source. Usually this is formated as DOMAIN\user.

 

When you run the report, we will pass whatever the email address is, that you signed into Power BI with, in the EffectiveUserName property. This could lead to issues if that email address doesn't map to a UPN on your local AD account.

 

That could potentially explain the difference in behavior.  Can you verify that the email address the user logged in with matches the UPN for the Active Directory account on-premises?

Adam W. Saxton | Microsoft Employee | Business Intelligence
@GuyInACube | youtube.com/guyinacube

View solution in original post

16 REPLIES 16
WaltDjr
Regular Visitor

So I seem to be having this problem even though my UPN matches and everything "seems" to be right.  I am new to SSAS but everything looks correct at this point.  I have checked the UPN, the user is in the admin role on the cube, the user has datareader access to the underlying database, I even tried giving my test user sysadmin access.  I've tried all 4 security types and they all do the same thing.  With everything I've tried I'm still receiving the NT SERVICE\MSSQLServerOLAPService failed login error.  Any additional troubleshooting steps?

 

Thanks,

WaltDjr

After speaking with Microsoft and trying a few things, I tried something on a whim and it worked.  

 

I had to put our SSAS server into the Pre-Windows 2000 Compatible Access group in AD's "BUILTIN" groups.

 

Thanks,

WaltDjr

GuyInACube
Employee
Employee

I just tried this with a SQL 2014 SP1 AS MD instance and it worked without problems for multiple users. Seems like it may be environmental. The test connection for the data source will use the credentials you supplied in the data source. Usually this is formated as DOMAIN\user.

 

When you run the report, we will pass whatever the email address is, that you signed into Power BI with, in the EffectiveUserName property. This could lead to issues if that email address doesn't map to a UPN on your local AD account.

 

That could potentially explain the difference in behavior.  Can you verify that the email address the user logged in with matches the UPN for the Active Directory account on-premises?

Adam W. Saxton | Microsoft Employee | Business Intelligence
@GuyInACube | youtube.com/guyinacube

I have verified that the UPN is the same and this still doesn't work for several accounts.  It works for a couple, but every other account we have tried runs into this issue.  I ran a trace for support and it provides as little information as the browser error does.  It provides plently is useful information for the user that is able to see the data etc, but when I try with a user who has this issue it gives me the exact same error message that the browser does while not showing me what is passed to the server etc.  This issue is extremely urgent because the client I'm working with will not be purchasing PowerBI licenses if it is not fixed very soon.  Any help would be greatly apprecated. 

Turns out that the issue was something like this... 

 

If the underlying error message is similar to the following, it could mean that the service account for Analysis Services may be missing the token-groups-global-and-universal (TGGAU) directory attribute.

 

Here is more information on that

http://setspn.blogspot.com/2012/05/service-accounts-active-directory_4905.html

 

What we had to do was add the Authenticated Users to the Windows Authorized Access group.

 

Does anyone know if this causes any sort of security issue, or know why adding just the service account wasn't sufficient. 

 

 

That solution is listed in the Gateway troubleshooting doc.  https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem-tshoot/#data-sources

 

I'm not sure as to the ramifications of that though.

Adam W. Saxton | Microsoft Employee | Business Intelligence
@GuyInACube | youtube.com/guyinacube

I had the exact same issue and the solution provided by GuyInACube resolved my problem!

The UPN of some users in AD were invalid 🙂

Thank you for the suggestion. I don't think we have set up the PowerBI account and AD account mappings. One thing I don't understand is when I open up a report using a live connection to a relational database (SQL Server 2012 SP2), it can correctly query the data through the enterprise gateway. Maybe the gateway has different impersonation settings for relational databases and AS databases?

We only pass the user's email address for Analysis Services live connections. For DirectQuery data sources, like SQL Server, we use the credential that you defined in the data source for all users.

Adam W. Saxton | Microsoft Employee | Business Intelligence
@GuyInACube | youtube.com/guyinacube

Thank you for the clarification. Another quetion for us is, when the gateway passes the email address and try to lookup the AD account using the UPN field, does this account has to be the local account on the machine where the PowerBI Gateway Service is installed? Or it can be any account in the entire directory?

Anonymous
Not applicable

Hi @Kobe24

 

According to what I've read on this, the email adress corresponds to the AD UPN. So I think that if your SSAS user is just a local account, it won't work.

I think that the UPN is only AD property.

 

The best way to manage user roles is to synchronize your AD with the Azure Active Directory created with Power BI.

 

Sebastien

Correct. It has to be a user account within Active Directory. That is what will have the UPN property on it. Local accounts on a box don't have a UPN property and would not be looked at.

Adam W. Saxton | Microsoft Employee | Business Intelligence
@GuyInACube | youtube.com/guyinacube

Thank you for the explanation. Due to some policies within my corporation, the UPN fields of our accounts cannot be our emails. Is there any workaround in my PowerBI account settings for that? 

 

Thanks

Unfortunately there is not a good workaround to that. We pass the email address, and effectiveusername evaluates that to a local account. It has to match.

Adam W. Saxton | Microsoft Employee | Business Intelligence
@GuyInACube | youtube.com/guyinacube
Phil_Seamark
Employee
Employee

Start a trace on your SSAS server and look to see what errors/events fire in there.  We had a problem where our public O365 domain was not the same as our internal domain so needed to perform some AD mapping to get it to work.

 

Tracing SSAS was very helpful to understand how far Power BI was getting when trying to talk to our cubes.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

The trace on SSAS server doesn't tell much. It just said: bad user name or invalid passord, which makes sense because the windows security log shows no user name given

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors