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!

 

 

 

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.