Access Azure Blob Storage via Azure SQL Database through Managed Identity

Question

I am trying to connect to Azure Blob storage via Azure SQK database through Managed Identity based on the below set of steps:

1. Assigned an Identity to the Server

2. Gave access to the Server on Blob storage as contributor

3. Executed the below queries

   Create Master Key

    CREATE DATABASE SCOPED CREDENTIAL MSI WITH IDENTITY = 'Managed Service Identity';
   
   
        CREATE EXTERNAL DATA SOURCE [BlobStorage] WITH
    (  
        TYPE = BLOB_STORAGE,
        LOCATION = 'https://<<blobnm>>.blob.core.windows.net/<<containerNm>>',
        CREDENTIAL = MSI
    )
   
    create table test
    (
    c1 varchar(5),
    c2 varchar(4)
    )
   
    BULK INSERT test from 'poly.csv' WITH ( DATA_SOURCE = 'BlobStorage',FORMAT='csv',FIRSTROW = 2 );
   

But I am getting the below error :

Cannot bulk load because the file "msi/poly.csv" could not be opened. Operating system error code 86(The specified network password is not correct.)

So can anyone tell me what I am missing out ?

Answer 1

There are many reasons for this error. I have listed some reasons as follows:

1. Check whether the SAS key has expired? And please check the Allowed permissions.

2. Did you delete the question mark when you create the SECRET?

CREATE DATABASE SCOPED CREDENTIAL UploadInvoices
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2019-12-12******2FspTCY%3D'

I also tried the following test, it works well. My csv file has no headers.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '***';
go

CREATE DATABASE SCOPED CREDENTIAL UploadInvoices
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2019-12-12&ss=bfqt&srt=sco&sp******%2FspTCY%3D'; -- dl


CREATE EXTERNAL DATA SOURCE MyAzureInvoices
    WITH (
        TYPE = BLOB_STORAGE,
        LOCATION = 'https://***.blob.core.windows.net/<container_name>',
        CREDENTIAL = UploadInvoices
    );

BULK INSERT production.customer
FROM 'bs140513_032310-demo.csv'
WITH
    (
        DATA_SOURCE = 'MyAzureInvoices',
        FORMAT = 'CSV',
        ERRORFILE = 'load_errors_TABLE_B',
        ERRORFILE_DATA_SOURCE = 'MyAzureInvoices',
        FIRSTROW = 2
    )
GO