Lesson Learned #133: Running Bulk Insert command using Managed Identity credential.

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Today, I worked on a service request that our customer needs to know how to run a bulk insert for reading CSV file using Managed Identity credential. Following I would like to share with you how I configure this.

We have to configure two elements: Azure SQL Database and Storage Account.

Storage Account:

First of all, we need to have a blob storage of general-purpose v2 type.
Using PowerShell, you need to register your Azure SQL server with Azure Active Directory, running the following commands:

Connect-AzAccount Select-AzSubscription -SubscriptionId <subscriptionId> Set-AzSqlServer -ResourceGroupName your-database-server-resourceGroup -ServerName your-SQL-servername -AssignIdentity

Under your storage account, navigate to Access Control (IAM), and select Add role assignment. Assign Storage Blob Data Contributor Azure role to the server hosting your Azure SQL Database which you've registered with Azure Active Directory (AAD) previously.

Azure SQL Database:

Open SQL Server Management Studio and connect to the database.
Open a new query and run the following commands.
Create the database credential:

CREATE DATABASE SCOPED CREDENTIAL MyCredential WITH IDENTITY = 'Managed Identity'

Create the data source:

CREATE EXTERNAL DATA SOURCE [MyDataSource] WITH ( TYPE = BLOB_STORAGE, LOCATION = 'https://storageaccountname.blob.core.windows.net/backup', CREDENTIAL = MyCredential );

Create the Dummy Table:

CREATE TABLE [dbo].[MyDummyTable] (ID INT)

Bulk Insert command:

BULK INSERT [dbo].[MyDummyTable] FROM 'info.txt' WITH (DATA_SOURCE = 'MyDataSource' , FIELDTERMINATOR = '\t')

It is possible that running bulk insert command you could get an Access denied issue, I saw that after adding the Identity and the RBAC of the storage account these operations take some minutos to be effective.

Enjoy!

Leave a Reply Cancel reply