Access Blob Storage from Azure SQL Server using a Managed Identity

Question

Greetings,

As the title suggests I am trying to bulk insert data from a file in Blob Storage in an Azure SQL database using an external data source with a credential using a managed identity. However the bulk insert fails saying the sql server doesn't have access to the blob storage file. I followed the steps from this question from the Microsoft forum:
https://learn.microsoft.com/en-us/answers/questions/215160/access-azure-blob-storage-via-azure-sql-database-t?source=docs

I also found this article mentioning that using managed identities for this type of operation is no longer supported:
https://medium.com/microsoftazure/firewall-protected-azure-storage-and-azure-sql-database-load-data-using-t-sql-bulk-insert-command-9895b258e480

So my question is, is it possible to use Managed Identities or do I have to stick to SAS tokens as that is already tested and working ?

Regards

Answer 1

Hello Dimitar Grozev,

Greetings! Welcome to Microsoft Q&A Platform.

Managed Identity allows you to access Azure Storage and perform BULK INSERTS into SQL Database.

• In the firewall on the Azure Storage side, if you set public network access to "Enable from selected virtual network and IP address."

1.On the target SQL Database server, have system-assigned managed identity enabled.
2.In the network settings of the Storage Account,check Allow Azure services in the list of trusted services to access this storage account and save.
3.In Storage Account access control, grant "Storage Blob Data Reader Permissions" to the managed identity of the target SQL Database from the role assignment.
4.Connect to SQL Database, add credentials and data sources, and perform BULK INSERTS. Create a master key, create credentials as Managed Identity, and create an external data source, as shown in the following example query.

USE THE CREDENTIALS YOU CREATED OR AN EXTERNAL DATA SOURCE TO PERFORM A BULK INSERT.

Sample query Create a master key (if it already exists, there is no problem with the existing one) CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>'; Use Managed Identity to create credentials CREATE DATABASE SCOPED CREDENTIAL msi WITH IDENTITY = 'MANAGED IDENTITY'; Use credentials to register an external data source CREATE EXTERNAL DATA SOURCE storageAccount WITH (TYPE = BLOB_STORAGE, LOCATION = 'https://<storageacount>.blob.core.windows.net/<container> ', CREDENTIAL = msi ) Perform BULK INSERT BULK INSERT tableA FROM 'file_name.csv' WITH (DATA_SOURCE = 'storageAccount', FORMAT = 'CSV');

reference docs - https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server?view=sql-server-ver16,

https://learn.microsoft.com/en-us/sql/relational-databases/import-export/examples-of-bulk-access-to-data-in-azure-blob-storage?view=sql-server-ver16, https://techcommunity.microsoft.com/t5/azure-sql-blog/loading-files-from-azure-blob-storage-into-azure-sql-database/ba-p/386133,

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-credential-transact-sql?view=sql-server-ver15

• Ensure the Managed Identity has the necessary role assignments, such as “Storage Blob Data Contributor” or “Storage Blob Data Reader,” depending on the level of access required.
• Check the external data source and database scoped credentials are configured correctly in your SQL database.
• check the network settings and confirm that the SQL server is allowed to access the storage account.

Using Managed Identities for bulk insert operations from Azure Blob Storage into Azure SQL Database can be challenging due to various permissions and configurations required. The error you’re encountering suggests that the SQL server is unable to access the Blob Storage file, which is often a result of insufficient permissions or incorrect setup. From the information available, it seems that there have been changes in the support for Managed Identities with Azure SQL. While some sources suggest using Managed Identities, others indicate that using Shared Access Signatures (SAS) tokens is the more reliable method.

Given that SAS tokens are already tested and working in your case, it might be more practical to continue using them. However, if you prefer to use Managed Identities please try using the above method.

when the firewall on the Azure Storage side sets public network access to "Enable from all networks" - You can use Shared Access Signatures (SAS) to access Azure Storage and perform BULK INSERTS from SQL Database.

Hope this answer helps. 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.