Unable to set up On-premises gateway configuration for excel file local access

[aAmirkHan]

Hi,

I am using an Excel Superstore dataset located on my PC drive. I have published it and installed an on-premises gateway on my machine. However, when I try to add a gateway and put all the required details then it shows me below error.

However, in Windows username, I put the username that I found from cmd by typing - whoami 

Windows password: I enter my four-digit laptop pin that I use every day for logging.

Unable to create connection for the following reason: Invalid connection credentials.

Details: Aamir_Gateway: The credentials provided for the File source are invalid. (Source at c:\users\moham\onedrive\documents\practice\power bi\rakesh nag ji\sample - superstore.xlsx.)

Please have this information handy if you choose to create a support ticket.

Session Id: fe0296ac-e1e1-43d5-a502-86b98acf3140 RequestId: c2e1ed0a-c75f-4803-ba1e-027c6ed3f3fb

Cluster URI: https://api.powerbi.com Status code: 400

Time: Mon Apr 28 2025 23:22:16 GMT+0530 (India Standard Time)

 Please advise.

@amitchandak  @danextian @lbendlin @Idrissshatila 

[v-venuppu]

Hi @aAmirkHan ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[v-venuppu]

Hi @aAmirkHan ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-venuppu · Accepted solution]

Hi @aAmirkHan ,

Thank you for your response!!

As it is already mentioned that Your Excel file is stored in a OneDrive-managed folder, even though it's on your C: drive. This can create permission and access issues for the On-premises Data Gateway, especially when it's trying to impersonate your Windows user.

You can try following steps:

1.Create a simple folder outside of OneDrive, such as: C:\PowerBIData\

Then move your Excel file to: C:\PowerBIData\sample-superstore.xlsx

2. Update the File Path in Your Power BI Desktop Report
Open your .pbix file in Power BI Desktop.

Go to Transform Data > Data Source Settings.

Change the file path to the new one (C:\PowerBIData\...).

3. Ensure Gateway Service Runs Under Your Windows User
Open the On-premises Data Gateway configuration tool.

Go to Service Settings.

Change the service account to run under your Windows user: aamir-pc\moham

and enter your actual Windows account password (Not your 4-digit PIN - the full user password).

If you don’t know it, you may need to reset it via Windows Account Settings > Sign-in options.

• If you really want to keep using OneDrive, consider using OneDrive for Business and connect via the SharePoint folder connector in Power BI - that approach works without a gateway.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

 Thank you

[v-venuppu]

Hi @aAmirkHan ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[aAmirkHan]

Hi @v-venuppu ,

I could not find any solution to my question. However, I still want to understand about the file path. I have only C drive in my machine, there is no other drive that I have created like a D or E drive, etc. My file location is on C drive. Please suggest. Below is the file location for your reference.

c:\users\moham\onedrive\documents\practice\power bi\rakesh nag ji\sample - superstore.xlsx.)

Thanks

Aamir

[GilbertQ]

Hi @aAmirkHan 

If you are still wanting to use your PC to host the file, make sure that you put the file in a file location that the power BI Gazteway account has got access to. It might also be a permission issue where the file is currently located.

[aAmirkHan]

Hi @GilbertQ ,

Thank you for your response, however, I still want to understand about the file path. I have only C drive in my machine, there is no other drive that I have created like a D or E drive, etc. My file location is on C drive. Please suggest. Below is the file location for your reference.

c:\users\moham\onedrive\documents\practice\power bi\rakesh nag ji\sample - superstore.xlsx.)

[aAmirkHan]

Hi @GilbertQ ,

I have tried it once again, where I am trying to create connection between SQL Server. However this time it shows me this error.

Unable to create connection for the following reason: The on-premises data gateway's service account failed to impersonate the user.



Details: Aamir_Gateway: Received error payload from gateway service with ID 2531745: Error logging on username 'aamir-pc\moham'.

Please advise on this.

[v-venuppu]

Hi @aAmirkHan ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @lbendlin @GilbertQ  for the prompt response.

“The on-premises data gateway’s service account failed to impersonate the user.”

Indicates that the gateway is running under a service account (e.g., NT SERVICE\PBIEgwService) that does not have permissions to act as your Windows user (aamir-pc\moham) to access:

Local files (like Excel on C:\)

SQL Server using Windows Authentication

Here are some Troubleshooting steps to solve the issue:

 Change Gateway to Run Under a Domain or Local Admin Account

1.Open the On-premises Data Gateway app on your PC.

2.Go to Service Settings.

3.Change the service account to:

• A domain user (if in domain environment), or
• Your Windows user: aamir-pc\moham

4.Restart the gateway service.

Make sure this account has:

• Read access to the Excel file location
• Permission to connect to SQL Server

Switch to Basic Authentication for SQL Server

If you can, enable SQL Authentication on your SQL Server and use that instead:

• Server: aamir-pc\sqlexpress
• Username: SQL login (e.g., sa)
• Password: (set in SQL Server Management Studio)

This avoids impersonation and works better with the gateway.

Move Excel File Out of OneDrive Folder

Your Excel file is in:

c:\users\moham\onedrive\documents\...

That’s a OneDrive-managed folder, which can interfere with gateway access.

Move it to a standard local folder like:

C:\DataSources\sample-superstore.xlsx

Then grant read access to the gateway service account.

If you're not able to configure impersonation or authentication easily:

• Move the Excel to OneDrive for Business or SharePoint Online
• Connect via Power BI Service using a cloud connector (no gateway needed)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

 

[lbendlin]

Do not use your local file storage.  You will not want the Power BI Service to reach down into your PC.

Put that Excel file on a SharePoint or OneDrive.  That removes the need for a gateway.

[aAmirkHan]

Hi @lbendlin ,

Thank you for your response. I do not want to move my Excel file to OneDrive or a SharePoint folder. However, I want to use the connection between my Power BI Service and Excel.

Please advise.

[lbendlin]

I do not want you to suffer the consequences of that approach.  Do not use local file storage, ever.  

I hope someone else can help you further.