Azure @ Enterprise - Managed Identity formerly MSI to bulk insert from Azure Data Lake Gen2 (ADLS Gen2) to Azure SQL Database

Requirement

Bulk insert a CSV format file from Azure Data lake Gen2 to Azure SQL using the system-assigned managed identity (Managed Service Identity) as the authentication mechanism.

Official link

As per the Microsoft link, it is not supported. The most secure way seems the SAS token-based database scoped credential.

BULK INSERT and BACKUP/RESTORE statements cannot use Managed Identity to access Azure storage

Verifying the above

The first step is to create a system-assigned managed identity to the Azure SQL Server. As of writing this post, it seems there is no way to do this from the Azure portal unless we use PowerShell. Below goes the step.

The command is az sql server update -g <resource group> -s <sql server name> -i

The next step is to create Azure Data lake Gen 2 account. How to create that is skipped in this post as that is already available on the internet. The next step is to give the required permissions to the SQL Server's identity to access the Azure Data lake Gen 2 account.

The permissions can be more restricted based on the use case. Now lets us see how the SQL scripts look like.

It works. 

We got an undocumented feature to work that allows Azure SQL to connect to Azure Data Lake Gen2 account using system-assigned managed identity.

Please note that the uploading of CSV and the creation of the table are not included as those are obvious steps. Make sure the CSV and the table structure match. The SQL can be downloaded from here.

Links

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-scoped-credential-transact-sql?view=sql-server-ver15
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-storage-files-storage-access-control?tabs=managed-identity#database-scoped-credential
https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2016#f-importing-data-from-a-file-in-azure-blob-storage
https://docs.microsoft.com/en-us/sql/relational-databases/import-export/examples-of-bulk-access-to-data-in-azure-blob-storage?view=sql-server-ver15