Azure Data Factory Power Query with Azure Storage Private Endpoint not working

Question

Hi All,

I'm trying to run a "Power Query" in "Azure Data Factory" on an Excel file on an Azure Storage. For security reasons I'm running an "Azure Hosted Integration Runtime" with "Managed Virtual Network" and a "Managed Private Endpoint" deployed to the managed virtual network. The "Storage Account" network settings is set to "Enabled from selected virtual networks and IP addresses"

I'm having a linked service with an Azure Storage Account Key, not stored in an Key Vault (for debugging simplicity) and accessing the data via the private endpoint.

I'm having a dataset that references the linked service. I'm able to preview the excel data in the dataset.

When I now create a power query I get the error message "We could not evaluate this query due to invalid or missing credentials."

When I set the "Storage Account" network settings to "Enabled from all networks" the power query works without any problems.

What am I missing? Is power query on ADF supported with Managed Private Endpoints?

thanks

Peter

Answer 1

@Peter
I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that ""The question author cannot accept their own answer. They can only accept answers by others", I'll repost your solution in case you'd like to accept the answer.

Ask: Why am I getting the error message We could not evaluate this query due to invalid or missing credentials" when trying to run a Power Query in Azure Data Factory on an Excel file stored in Azure Storage using a Managed Private Endpoint, and how can I resolve this issue?

Solution:
so, I found a workaround - it's not my favorite but it works, and it does not leave the storage account open for everyone...

In the Storage Account Network Settings, you can give access to a Resource Instance:

IMPORTANT: You have to use a System Assigned Managed Identity, give this Managed Identity access rights on you storage account and also in the Linked Service use this System Assigned MI to authenticate. A User Assigned MI will not work.

Drawback - it does not use the Managed Private Endpoints...

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.