Error when setting up a data source connection to Excel: Unexpected behavior

[D4imi0n]

Hello everyone,

I hope you have an idea for my current problem.
I have published a dataset via Power BI Desktop. The data comes
via direct query from an OnPremise SQL Server. A data gateway is set up on a server.
After the administrator has customized the gateway and cloud connections in Power Service to the running data gateway, the
semantic model can be refreshed. The report on this semantic model works as expected.

Problem:

An Excel file has been added to the Power BI desktop model. The Excel file is located on a network drive

u:/project/data

pointing to:

//xyx/project/data

After publishing to the Power BI service, it is not possible to set up the connection to Excel.
We have tried it with different user authorizations. Highest authorization (Power BI Admin / Fabric Admin).
The connection is created, but you cannot address it under Gateway and cloud connections for the relevant semantic model.

The connection was created with the path: //xyx/project/data. The path specification: u:/project/data runs into an error.

Verification:

• Gateway is set up and status: Online
• Under Connections you will find the entry for Excel with the status: Online.
• Right next to it is the entry for SQL Server OnPremise with status: Online.
• The path: //xyx/project/data can be accessed from the gateway server with the user that was stored in the Power BI service.

Note:

I have reproduced the described steps on two other tenants and everything works as expected there. Connection to the SQL server and then the connection to the Excel file is successful. Only with this one tenant it does not work. The tenant's data is located in West Europe (Netherlands).

I would appreciate any ideas.

Thank you

[D4imi0n]

Hello @GilbertQ,

we have not yet been able to solve the problem. However, I have gained many insights.

1. after the automatic switch to Fabric GA in the tenant of two of my customers, the connections to files were switched to personal.
   
2. file accesses that worked before are no longer usable after the changeover and can neither be changed nor newly created.
   
3. we switched the files in Power BI Desktop to a Sharepoint source and were then able to switch the connection to a web connection in the Power BI Service.
   

Two of my customers show exactly the same behavior. Files on network drives can no longer be accessed although they have always worked. Accesses were checked etc.
I think this is a general problem.

Is there anyone else facing this problem?

[GilbertQ]

Hi @D4imi0n 

It certainly sounds like an issue related to the Gateway setup, because it is working in other tenants.

Can you confirm that when you are logged into the Gateway that it can access the network path?

Also you might need to log into the Gateway server with the same credentials that you are using to create the data source to see if this account has got access to the network share?

[D4imi0n]

Hi @GilbertQ,

thanks for your feedback and sorry for the late response.

We have checked the following things:

1. access of the AD account (Power BI Admin) to the gateway server (success)
2. access of the AD account (Power BI Admin) to the network drive and the file (success)

But what I can check again is the exact account of the Gateway service account.
Does it have to have access to the network drive?

[GilbertQ]

Hi @D4imi0n 

You are 100% right, you would need to change the account used by the On-Premise gateway to use one of your AD accounts that would have access to the file share.

This will then allow the gateway to connect to the file share using the service account.

Change the on-premises data gateway service account | Microsoft Learn

[D4imi0n]

Hi @GilbertQ,

thank you for your input . I will try to clarify this today.
I'll get back to you as soon as possible.