Azure Blob to SQL Server Bulk Insert - Don't have file access rights

Question

I am trying to bulk insert a flat file from Blob Storage to a SQL Server Database. Here is what I have done:

1. Created a secure connection to Azure Blob and SQL Server using the master access key on the overall storage account. The connection is pointed at the specific container the files get staged "load-to-sql".

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'XYZ' 

CREATE DATABASE SCOPED CREDENTIAL ohhyeaa 
WITH IDENTITY = 'SHARED ACCESS SIGNATURE' 
SECRET = 'access policy key from the entire container' 

IF EXISTS ( SELECT * FROM sys.external_data_sources) 
DROP EXTERNAL DATA SOURCE MyAzureBlobStorage 
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage 
WITH ( TYPE = BLOB_STORAGE, LOCATION = 'CONTAINERaccount.blob.core.windows.net/load-to-sql', CREDENTIAL= MyAzureBlobStorageCredential) 

2. File is loaded into a container

3. Execute:

    BULK INSERT [dbo].file.csv
    FROM 'StagingFolder_DataToSQL/project/date/file.csv' 
    WITH (FORMAT = 'CSV', FIRSTROW = 2, FIELDTERMINATOR = '|', 
    KEEPIDENTITY, DATA_SOURCE = 'MyAzureBlobStorage', batchsize = 300000)
   

This gives me the following error:

Msg 4860, Level 16, State 1, Line 1 Cannot bulk load. The file "StagingFolder_DataToSQL///.csv" does not exist or you don't have file access rights.

I am the owner of the server and the specific database where data is getting loaded.

Are there access settings that I need to define in the Azure portal to allow for the file to load?

I have been successful with this process in a different Azure environment and it worked great. Help is greatly appreciated. Thanks!

Answer 1

Cannot bulk load. The file "StagingFolder_DataToSQL///.csv" does not exist or you don't have file access rights.

The Cause of error is the access token you are using it doesn't have read permission on the object that should be loaded, or the file path is incorrect/Dose not exist file path is Case sensitive.

Get Container scoped SAS token with appropriate permissions:

My SQL Code:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'strong_password' 

CREATE DATABASE SCOPED CREDENTIAL ohhyeaa7 
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'Container_scoped_SAS_token' 

IF EXISTS ( SELECT * FROM sys.external_data_sources) 
DROP EXTERNAL DATA SOURCE MyAzureBlobStorage 
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage 
WITH ( TYPE = BLOB_STORAGE, LOCATION = 'https://<Storage_Account_name>.blob.core.windows.net/<Container_name>', CREDENTIAL= ohhyeaa7) 

BULK INSERT dbo.student
FROM 'stagged/sample/file3.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2, FIELDTERMINATOR = ',', 
KEEPIDENTITY, DATA_SOURCE = 'MyAzureBlobStorage', batchsize = 300000)