OnPrem Gateway New Data Source Error: Service Account failed to impersonate the user

[wi11iamr]

I have a PowerBI report which uses a dataset from an Azure Analysis Services tabular database (CatMan), which in turn models it's data from an Azure VM with a SQL Server Data Warehouse (SQLDW). This server is joined to our domain, and hosts the On Premises Data Gateway (GWY_Cellnet). This OnPrem Data Gateway is configured to run under the domain credentials (DOMAIN\%service.account%)

As part of the AAS instance that was established, we seemingly also have another gateway (CCASGATEWAY-SEA) that I can see in Azure itself. I do not see this gateway as part of my Gateway Clusters in PowerBI (refer screenshot), although it does appear in the SQLDW's OnPrem gateway settings (refer screenshot). I'm not sure if this has anything to do with my problem just yet.

Being that the Power BI report's datasource is in the cloud, it hasn't required a Data Source to be created within the SQLDW's On-Premises Data Gateway. I would however like to implement Row Level Security against the AAS CatMan database for external users and to do so I will be mapping the external users to a replacement user account, who's row level access I will control from within AAS using DAX.

To implement this User Name Mapping, I need to setup a Data Source for CatMan in the Power BI Service. However, when I try to create a new datasource in PowerBI, I get the error DMTS_PublishDatasourceToClusterErrorCode

GWY_Cellnet:

The on-premises data gateway's service account failed to impersonate the user.

Admittedly, I wonder how much of my issue relates to having local domain credentials for the GWY_Cellnet service (E.g. DOMAIN\%service.account%) but that any connection to the AAS instance needs to use Azure AD credentials.

I've tried configuring GWY_Cellnet to use the AAD credentials instead, but this seemingly cannot work as the OnPrem Gateway server is not part of that domain?

I'm firstly not sure whether an OnPrem Gateway running under DOMAIN credentials can in fact access (aka impersonate?) that which is required to access the AAS instance. I've found some articles talking to Kerberos issues and some registry changes etc, but am wary of doing this before I better understand the issue.

CCASGATEWAY-SEA (Azure OnPrem Gateway)OnPrem Data Gateway (GWY_Cellnet) also shows the new "Azure Gateway" (CCASGATEWAY-SEA)Error when adding AAS datasource to GWY_Cellnet OnPrem Data GatewayThe new "Azure Gateway" (CCASGATEWAY-SEA) does not appear in the Gateway Cluster Settings

[v-stephen-msft]

Hi @wi11iamr ,

Sorry for my late reply.

I found this similar post which you can refer to:

on-premises data gateway's service account failed to impersonate the user

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wi11iamr]

Thanks @v-stephen-msft . I had found that post and see quite a variety of scenarios/solutions. I've reviewed again and since managed to do the following:

1. Create local AD domain account: DomainName\PowerBIAdmin
2. Added DomainName\PowerBIAdmin as Windows Administrator on the Gateway server, and configured the OnPrem Data Gateway service to run under this account
3. In Local Group Policy (gpsedit.msc) on the Gateway server, I've added the DomainName\PowerBIAdmin account to [Impersonate a client after authentication]. 
4. Create Azure AD account: PowerBIAdmin@DomainName, with same password as local AD domain account (I'm naievely working on the hope that having the same Name and Password across the local AD and Azure AD accounts will somehow help 😅)
5. Configured the Azure AD account as a server administrator of Azure Analysis Services
6. Restarted the On Prem Data Gateway service

When I now try to create the new Datasource in PowerBI Service,, I use the Azure AD account (PowerBIAdmin@DomainName) and now receive the following error:

GWY_Cellnet: An invalid connection string has at least one of the passed arguments which does not meet the parameter specification. Please check the data source connection string.
Underlying error message: Authentication failed: User ID and Password are required when user interface is not available.