How do I configure Azure Table storage to authenticate with user entra ID creds?

Question

Hello. I have a project where I have been tasked to store some simple data in Azure Table storage and then have our users query it via Excel.

But in Excel when you go to Get Data > From Azure > From Azure Table Storage and then provide the URL or storage account name the ONLY auth method it asks for is the Account Key. I would prefer if it just authenticated the users based on the Entra ID creds though.

My objective is for users to access the data securely. It seems like I need the Table storage to be accessible from All Networks because my users are remote and work from everywhere so I cannot lock down access to certain source IP CIDRs. Consequently, I don;t want to use Account Keys. All users have MFA so it seems like it would be great if the Table auth could be Entra ID.

Does anyone know of this is possible? Or maybe I am looking at this all wrong? I am open to any ideas/feedback.

Answer 1

Hi Fabio,

Unfortunately Excel only supports Account Key authentication which is why it is only prompting for that method. Your idea of using Entra ID credentials is correct, it is just Excel is blocking you.

In regards to networking, you could set up VPN Gateway and have users use Point-to-Site connection to connect to Azure, then use Private Endpoint to connect to storage account. That way you could at least disable public access on the storage account. They still would need to use Account Key.

Please click Accept Answer and upvote if the above was helpful.

Thanks.

-TP

Answer 2

Hello Fabio,

Greetings! Welcome to Microsoft Q&A forum.

I understand that you would like to get data from Azure table storage to excel.

Adding to above information, To retrieve data from Azure Table Storage into Excel, follow these steps:

In Excel 2016 or later:

Go to the Data tab.

Click Get Data > From Azure > From Azure Table Storage.

In Excel 2010 or 2013 (some editions):

Install the Power Query add-in if not already installed.

Go to the Power Query ribbon tab.

Click From Azure > From Microsoft Azure Table Storage.

• Follow the additional steps to connect to your Azure Table Storage.

Azure Blob Storage:

• To get data from Azure Blob Storage into Excel, You can use Power Query to connect to Azure Blob Storage.

In Excel 2016 or later:

Go to the Data tab.

Click Get Data > From Azure > From Azure Blob Storage.

Follow the prompts to specify the Blob Storage container and credentials.

You can also use Python or other programming languages to read CSV files stored in Azure Blob Storage and load them into Excel2.

From Excel to Azure Storage:

Azure Table Storage:

While it’s not directly supported by ADODB, you can use alternative methods:

Create an Azure Function that interacts with Azure Table Storage (CRUD operations).

Use VBA forms in Excel to make HTTP requests to the Azure Function, which then communicates with the table storage.

• This adds an extra layer but allows you to connect Excel with Azure Table Storage.

Azure Blob Storage:

To upload data from Excel to Azure Blob Storage:

Save your Excel data as a CSV file.

Use the Azure Storage SDK or other libraries to upload the CSV file to a Blob Storage container.

You can also use Python with the azure-storage-blob library to achieve this

refer - https://stackoverflow.com/questions/53784434/is-it-possible-to-connect-to-azure-table-storage-from-excel, https://techcommunity.microsoft.com/t5/excel/reading-csv-stored-in-azure-blob-storage-from-excel/m-p/103394, https://learn.microsoft.com/en-us/azure/storage/tables/authorize-access-azure-active-directory

Azure Storage supports using Microsoft Entra ID to authorize requests to blob data. With Microsoft Entra ID, you can use Azure role-based access control (Azure RBAC) to grant permissions to a security principal, which may be a user, group, or application service principal. The security principal is authenticated by Microsoft Entra ID to return an OAuth 2.0 token. The token can then be used to authorize a request against the Blob service.

Microsoft Entra Domain Services provides managed domain services such as domain join, group policies, LDAP, and Kerberos/NTLM authentication. These services are fully compatible with Active Directory Domain Services. For more information, see Microsoft Entra Domain Services

Overview of Azure Files identity-based authentication options for SMB access

Enable Microsoft Entra Domain Services authentication on Azure Files Choose how to authorize access to file data in the Azure portal(Use your Microsoft Entra account)

Understand authorization for data operations Authorize access to data in Azure Storage.

Please let us know if you have any further queries. I’m happy to assist you further.     

Please "Accept the answer” and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.