On-premises data gateway (standard mode) doesn't work with excel local files

[Cado_one]

Hi,

In the past I used to get data from excel files stored in the local server of my company with the on-premise data gateway (personal mode) and everything was going great. But because I now need to have access to this files on different accounts, I recently changed the personal gateway for the standard mode. And here the troubles begins :

I fulfilled every step to set up the gateway, however, see the error returned below :

Code d'erreur sous-jacent: -2147467259
Message d'erreur sous-jacent: Les informations d'identification fournies pour la source File ne sont pas valides. (Emplacement de la source : \\files\exploitation\exploitation général\10 - gmao\00 - kpi\fichier_import_donnees\stocks\itemtrans.xlsx)
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.CredentialError.DataSourceKind: File
Microsoft.Data.Mashup.CredentialError.DataSourcePath: \\files\Exploitation\Exploitation général\10 - GMAO\00 - KPI\Fichier_import_donnees\Stocks\ITEMTRANS.xlsx
Microsoft.Data.Mashup.CredentialError.Reason: AccessUnauthorized
Microsoft.Data.Mashup.MashupSecurityException.DataSources: [{"kind":"File","path":"\\\\files\\Exploitation\\Exploitation général\\10 - GMAO\\00 - KPI\\Fichier_import_donnees\\Stocks\\ITEMTRANS.xlsx"}]
Microsoft.Data.Mashup.MashupSecurityException.Reason: AccessUnauthorized
URI du cluster: WABI-WEST-EUROPE-redirect.analysis.windows.net
ID d'activité: 2a3e7793-5dbd-4bea-95a1-3a7a6d0ac4d6
ID de demande: 900dbbdf-4213-d519-ebb3-b1bc8f12f34d
Heure: 2020-12-07 08:36:10Z

In english it says something like "Credentials for the File source are not valid". But I shouldn't need credentials to access this data, the gateway is on a local computer that can access to the server and it used to work with no credentials with the personal mode gateway.

I checked the path and it's okay. Even the test connexion works fine in the gateway settings in PBI Service.

Does anyone has an idea of what could be wrong ? I'd be glad to read and search on any clue you might have 

Thanks in advance,

Cado

[V-lianl-msft]

Hi @Cado_one ,

According to my understanding, this may be a file path issue. Refer to this thread.

https://community.powerbi.com/t5/Power-Query/Scheduled-refresh-for-excel-file-with-on-premise-gatewa... 

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

[Cado_one]

Hi @V-lianl-msft ,

Thank your for the answer. I checked the path on PBI desktop and on PBI service and didn't notice any differences...

The "AccessUnauthorized" mention in the error message is weird to me. With personal gateway I didn't need any credentials and now with standard gateway there are login and password fields beside the path. Could this be related ?

Best regards,

Cado

[V-lianl-msft]

Hi @Cado_one ,

Yes, if you confirm that there are no problems with the path, are your credentials configured correctly?

Can windows credentials be used to connect data sources to solve this problem?

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

[Cado_one]

Hi @V-lianl-msft 

I'm not sure of the credentials to use because I don't need any particular credentials to access the file.

Should I use the e-mail address registered in the gateway ? Or the account used to log on the computer ? I tried with both and I now got the following error :

Underlying error message: Impossible de charger le fichier ou l'assembly 'Microsoft.AnalysisServices.AdomdClient, Version=19.10.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' ou une de ses dépendances. Le fichier spécifié est introuvable.
DM_ErrorDetailNameCode_UnderlyingHResult: -2147024894
URI du cluster: WABI-WEST-EUROPE-redirect.analysis.windows.net
ID d'activité: 74101cf5-6f89-4af0-ad9a-6081a3d252d2
ID de demande: 22e11112-c937-b60b-0914-13e787a9937e
Heure: 2020-12-16 08:57:03Z

If I try to translate it says something like "Could not load file or assembly '...' File not found."

Cheers,

Cado

[V-lianl-msft]

Hi @Cado_one ,

Try to update to the latest version and make sure that only one gateway is running.

Based on your description, you are connecting to a local file. Please try to configure with window credentials.

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

[Cado_one · Accepted solution]

Hi @V-lianl-msft 

I've just found the solution. The problem was caused by a wrong username in credentials. This username must respect the formula "DOMAIN\WindowsLoginUser" in the gateway settings of PBI Service.

Thank you very much for your help.

I wish you happy end of the year celebrations 🙂

Cado

[Qusai94]

Hi @Cado_one,
I have the same problem, however it is still not clear for me which username and password I should use in the Standard gateway so that I can have the access to the local file.
Thanks in advance!
Qusai