On-Premise Gateway not passing logged in users EffectiveUserName to Analysis Services

[mkhan77]

Hi All first post on forum!.

I configured an on premise gateway on a client's server recently. They have a SSAS multidimensional cube that needs to be queried. I have defined a Security model using SSAS Roles with filters on certain dimensions.

The issue is similar to the one posted on the thread https://community.powerbi.com/t5/Integrations-with-Files-and/Enterprise-Gateway-with-Analysis-Servic...

The solution suggested in this thread worked for a test server running in our envrionment but it does not resolve the issue on the clients servers.

I have reviewed both server configs, Role definition, UPN, user mappings, server admins groups etc. All seem to be in order but still our TestUser@DomainName.com a member in RoleX1 with filter on DimensionY1 still gets served unfiltered data in power bi visual tile.

I checked the logs and it seems the effectiveusername passed on to the SSAS is the service account "powerbiservice@DomainName.com" setup for gateway. This is a local admin and explains why the role filters do not work. I have pasted a log extract with domain name masked. Any help would be appreciated.

DM.EnterpriseGateway Information: 0 : 2017-01-05T12:30:36.4866288Z DM.EnterpriseGateway               5db53854-d68a-4bbd-921e-fd8df0c6852b              e2cb0ce7-94bf-69a7-4061-62dcd91ca6c8    MGPP    52f1e89f-212a-4e48-bf4f-ca5acef8f97e  E5E3865A [DM.GatewayCore] Deserialized OpenConnectionRequest, executing
DM.EnterpriseGateway Information: 0 : 2017-01-05T12:30:36.7678722Z DM.EnterpriseGateway               cb497c64-f1c1-4da3-8414-f009bca74837               e2cb0ce7-94bf-69a7-4061-62dcd91ca6c8    MGCC    52f1e89f-212a-4e48-bf4f-ca5acef8f97e  44469028 [DM.GatewayCore] Db pool opening raw database connection to [MSOLAP.5: <pi>MSOLAP.5:provider=MSOLAP.5;data source=ssasservername.DomainName\mssqlbi;initial catalog=CubeName;timeout=180;effectiveusername=TestUser@DomainName.Com;sspropinitappname=PowerBI;CustomData=TestUser@DomainName.Com:[Windows] Encrypted Credential information omitted</pi>]
DM.EnterpriseGateway Information: 0 : 2017-01-05T12:30:37.5335265Z DM.EnterpriseGateway               cb497c64-f1c1-4da3-8414-f009bca74837               e2cb0ce7-94bf-69a7-4061-62dcd91ca6c8    MGCC    52f1e89f-212a-4e48-bf4f-ca5acef8f97e  33F9C374 [DataMovement.PipeLine.GatewayDataAccess] Replace effective user name in adomd connection string from powerbiservice@DomainName.com to powerbiservice@DomainName.com.
DM.EnterpriseGateway Information: 0 : 2017-01-05T12:30:38.1428705Z DM.EnterpriseGateway               b7ff0eba-d247-4f21-9036-33cf3782b88f e2cb0ce7-94bf-69a7-4061-62dcd91ca6c8               MGCC    52f1e89f-212a-4e48-bf4f-ca5acef8f97e  44469028 [DM.GatewayCore] Db pool opening raw database connection to [MSOLAP.5: <pi>MSOLAP.5:provider=MSOLAP.5;data source=ssasservername.DomainName\mssqlbi;initial catalog=CubeName;timeout=180;effectiveusername=TestUser@DomainName.Com;sspropinitappname=PowerBI;CustomData=TestUser@DomainName.Com:[Windows] Encrypted Credential information omitted</pi>]
DM.EnterpriseGateway Information: 0 : 2017-01-05T12:30:38.1741200Z DM.EnterpriseGateway               b7ff0eba-d247-4f21-9036-33cf3782b88f e2cb0ce7-94bf-69a7-4061-62dcd91ca6c8               MGCC    52f1e89f-212a-4e48-bf4f-ca5acef8f97e  33F9C374 [DataMovement.PipeLine.GatewayDataAccess] Replace effective user name in adomd connection string from powerbiservice@DomainName.com to powerbiservice@DomainName.com.
DM.EnterpriseGateway Information: 0 : 2017-01-05T12:30:38.2366207Z DM.EnterpriseGateway               e55690f8-4813-4227-b6cd-73a7daddd607             e2cb0ce7-94bf-69a7-4061-62dcd91ca6c8    MGCC    52f1e89f-212a-4e48-bf4f-ca5acef8f97e  44469028 [DM.GatewayCore] Db pool opening raw database connection to [MSOLAP.5: <pi>MSOLAP.5:provider=MSOLAP.5;data source=ssasservername.DomainName\mssqlbi;initial catalog=CubeName;timeout=180;effectiveusername=TestUser@DomainName.Com;sspropinitappname=PowerBI;CustomData=TestUser@DomainName.Com:[Windows] Encrypted Credential information omitted</pi>]
DM.EnterpriseGateway Information: 0 : 2017-01-05T12:30:38.2366207Z DM.EnterpriseGateway               cf219118-989e-48d2-9792-86bd9b96f942              e2cb0ce7-94bf-69a7-4061-62dcd91ca6c8    MGCC    52f1e89f-212a-4e48-bf4f-ca5acef8f97e  44469028 [DM.GatewayCore] Db pool opening raw database connection to [MSOLAP.5: <pi>MSOLAP.5:provider=MSOLAP.5;data source=ssasservername.DomainName\mssqlbi;initial catalog=CubeName;timeout=180;effectiveusername=TestUser@DomainName.Com;sspropinitappname=PowerBI;CustomData=TestUser@DomainName.Com:[Windows] Encrypted Credential information omitted</pi>]
DM.EnterpriseGateway Information: 0 : 2017-01-05T12:30:38.3147466Z DM.EnterpriseGateway               e55690f8-4813-4227-b6cd-73a7daddd607             e2cb0ce7-94bf-69a7-4061-62dcd91ca6c8    MGCC    52f1e89f-212a-4e48-bf4f-ca5acef8f97e  33F9C374 [DataMovement.PipeLine.GatewayDataAccess] Replace effective user name in adomd connection string from powerbiservice@DomainName.com to powerbiservice@DomainName.com.
DM.EnterpriseGateway Information: 0 : 2017-01-05T12:30:38.3460062Z DM.EnterpriseGateway               cf219118-989e-48d2-9792-86bd9b96f942              e2cb0ce7-94bf-69a7-4061-62dcd91ca6c8    MGCC    52f1e89f-212a-4e48-bf4f-ca5acef8f97e  33F9C374 [DataMovement.PipeLine.GatewayDataAccess] Replace effective user name in adomd connection string from powerbiservice@DomainName.com to powerbiservice@DomainName.com.
DM.EnterpriseGateway Information: 0 : 2017-01-05T12:30:38.6272415Z DM.EnterpriseGateway               055b6158-4224-4671-a590-0e7d87eb8bca            e2cb0ce7-94bf-69a7-4061-62dcd91ca6c8    MGPP    52f1e89f-212a-4e48-bf4f-ca5acef8f97e  A98CFF93 [DM.GatewayCore] Deserialized GetSchemaDataSetRequest, executing

[v-qiuyu-msft]

Hi @mkhan77,

A lot of issues can surface when the gateway version is out of date. Please try to update the data gateway to the latest version and see if you can reproduce the issue.

Best Regards,
Qiuyun Yu

[mkhan77]

Thank you for the suggestion. I have reinstalled the Gateway as suggested but the issues remains unresolved. Now I am getting a connection refused. I suspect the issue is the UPN is for EffectiveUserName is not being recognised as valid.

The client has multiple domains in thier AD. But all Office365 accounts are registered as USERNAME@DOMAINNAME.COM.

I have added the required UPN mapping so all power bi users translate from USERNAME@DOMAINNAME.COM to USERNAME@DOMAINNAME.INT

This mapping is working as I can see the gateway logs are showing me the expected EffectiveUserName. But the SASS server is refusing the connection.

The on premise gateway is configured to run using credentials of a user in one of the other internal domains SERVICEACCOUNT@AD.DOMAINNAME.COM

The accounts seem to be recognised on the servers for both the domains so I am assuming the domain trusts are configured correctly. And I can browse the cube data on the SSAS machine with users from both domains

Though not sure why the connection is refused for this valid user. Any suggestions would be appreciated.

[v-qiuyu-msft]

Hi @mkhan77,

I'm trying to involve senior engineers to look into this issue. You patience is greatly appreciated.

Best Regards,
Qiuyun Yu

[mkhan77 · Accepted solution]

Hi Thanks,

I believe the issue was related to the UPN suffix and Domain Trusts between AD.DOMAINNAME.COM and DOMAINNAME.INT.

I ran a test with a new non admin user from AD.DOMAINNAME.COM and the role filters worked. The client IT reconfigured the Trusts between thier two domains and the custom UPN replace was removed from gateway settings.

Now the users USERNAME@DOMAINNAME.COM from powerbi are allowed through the gateway and the UPN for these users in the DOMAINNAME.INT is working as desired i.e. @DOMAINNAME.COM

Thanks for your help anyway. The issue can be closed and resolution was to review and reconfigure the domain trusts so the UPN is cross domain authentication works correctly on SSAS server.