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

by | Sep 10, 2020 | Uncategorized | 0 comments

This article is contributed. See the original author and article here.

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!

 

 

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment