unable to access CSV file from azure block storage

Question

Good Morning,
i have set the azure block storage to import CV Excel file into MSSQL Database.
it was working fine using the procedure below:

BULK INSERT [dbo].[WCities CA and US]
FROM 'vulnerability-assessment/Date Master88.csv'
WITH (
DATA_SOURCE = 'MyAzureBlobStorage250522',
FORMAT = 'CSV',
      
FIRSTROW = 2)

But suddenly and while run the above procedure, it failed by giving me the message down:

Msg 4861, Level 16, State 1, Procedure dbo.LoadCSVintoAzureSQL, Line 6 [Batch Start Line 0]
Cannot bulk load because the file "vulnerability-assessment/Date Master88.csv" could not be opened. Operating system error code 5(Access is denied.).

i have generated the SAS and followed the steps below:

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'PASSWORD';
GO

ALTER DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential WITH IDENTITY = 'SHARED ACCESS SIGNATURE',SECRET = 'sv=2021-06-08&ss=bfqt&srt=sco&sp=rwdlacupiytfx&se=2022-09-24T22:52:07Z&st=2022-09-24T14:52:07Z&spr=https&sig=vuTwh%2FBhRgelqiNzTuQfPOiYe05mZBvTHngP9T2cBnU%3D';
GO

CREATE EXTERNAL DATA SOURCE [MyAzureBlobStorage250522] WITH (TYPE = BLOB_STORAGE, LOCATION = N'https://sqlvabv2xhcbe3bbn4.blob.core.windows.net/vulnerability-assessment', CREDENTIAL = [MyAzureBlobStorageCredential])
GO

but still the same issues,
Please advise since this procedure is important for me to import so many excel files into MSSQL.
Thanks a lot

Answer 1

@Maurice Faraj Thank you for reaching out to Microsoft Q&A.

This issue seems related to SQL so I am going to move it to the respective service area. In the meanwhile, please refer to this similar thread- https://stackoverflow.com/questions/14555262/cannot-bulk-load-operating-system-error-code-5-access-is-denied

Answer:

This error appears when you are using SQL Server Authentication and SQL Server is not allowed to access the bulk load folder.

So giving SQL server access to the folder will solve the issue.

Here is how to: Go to the folder right click ->properties->Security tab->Edit->Add(on the new window) ->Advanced -> Find Now. Under the users list in the search results, find something like SQLServerMSSQLUser$UserName$SQLExpress and click ok, to all the dialogs opened.

Please let me know if this helps. Thank you!
Please let us know if you have any more questions and we will be glad to assist you further. Thank you!

Remember:

Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.

Want a reminder to come back and check responses? Here is how to subscribe to a notification.

Answer 2

hi,
i am using azure SQL database and csv file inside the block storage of azure.
i had built script below to read CSV from block storage and import data into sql table.
it was working fine before .

BULK INSERT [dbo].[WCities CA and US]
FROM 'vulnerability-assessment/Date Master88.csv'
WITH (
DATA_SOURCE = 'MyAzureBlobStorage250522',
FORMAT = 'CSV',
      
FIRSTROW = 2)

Answer 3

Hi,
Any Solution for the above issue.
Thanks,

Answer 4

i have recreated the steps below but the same issues.
Before, inserting the CSV files from block storage was working fine .
strange i do not know what happened.

SELECT * FROM sys.database_scoped_credentials
drop DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
drop EXTERNAL DATA SOURCE MyAzureBlobStorage
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2021-06-08&ss=bfqt&srt=sco&sp=rwdlacupiytfx&se=2022-10-03T21:07:12Z&st=2022-10-03T13:07:12Z&spr=https&sig=RuNmCowJ1ns7MQWoj2DTMKJQDjBT%2FDXyuVgNiX0cGU4%3D';

create EXTERNAL DATA SOURCE [MyAzureBlobStorage250522]
WITH (TYPE = BLOB_STORAGE,
LOCATION = N'https://sqlvabv2xhcbe3bbn4.blob.core.windows.net/vulnerability-assessment'
,CREDENTIAL = MyAzureBlobStorageCredential);

/****** Object: StoredProcedure [dbo].[LoadGWCSVintoAzureSQL999] Script Date: 10/3/2022 9:24:15 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[LoadGWCSVintoAzureSQL999]
AS
BEGIN
TRUNCATE table [dbo].[table_2]
BULK INSERT [dbo].[table_2]
FROM 'vulnerability-assessment/book1.csv'
WITH (
DATA_SOURCE = 'MyAzureBlobStorage250522',
FORMAT = 'CSV',

FIRSTROW = 2)

END;

Answer 5

hi,
any solution for this issue??
I appreciate to have your assistance Since i can not use importing from Azure Block storage anymore.
Thanks,