Cannot bulk load because the file "SQLfiles.csv" could not be opened. Operating system error code 5(Access is denied.)

Question

Hi All,
I am working on BULK INSERT of a csv file from Azure Blob Storage into the table in Azure SQL Database. The environment is as follows:

1. Azure storage is in subscription1.
2. SQL database sqldb is in subscription2.
3. Virtual network vnet2 is in subscription2 and has Microsoft.Storage and Microsoft.SQL services attached.
4. SQL Server sqlsrv holding sqldb database is in subscription2 and is connected to the vnet2 in subscription2, so essentially has an access to the microsoft storage services.
5. Azure storage in subscription1 is connected to vnet2 in subscription2.
6. Container is called container1 and contains a file file1.csv.

I have created SAS with following permissions: ss=b&srt=sco&sp=rl.

Then I have created the database scoped credential:
create database scoped credential dbcred with Identity = 'SHARED ACCESS SIGNATURE', secret = '####ss=b&srt=sco&sp=rl###' and I have removed "?" from the begining of the token.

Next, I have created the external data source in sqldb:

create external data source extds
with (
location = 'https://<azure storage>.blob.core.windows.net/container1',
credential = dbcred ,
type = BLOB_STORAGE
)

I then run command:

insert into dbo.btlBItest
from 'file1.csv'
with (data_source = 'extds')

I get the following error:
Cannot bulk load because the file "file1.csv" could not be opened. Operating system error code 5(Access is denied.).

I know people had similar issues listed on different threads, but they were related to wrong SAS token or to "?" at the beginning of the token. I can actually access the container, just cannot open the file. I have read and list permissions and access level set to service, container and object. I also know the storage account is visible judging from the error. I can also access and read that file using Azure Storage Explorer with the same SAS token as in the above database credential.
Would someone be able to reconstruct the situation?

Answer 1

@Emmebaragezi - Two items to look at:

1. Check the firewall setting on the storage account (see below, but is essentially enabling access from all networks).
2. Add SQL Database instance name to the insert statement per the BULK INSERT Syntax, as follows:

{database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }

Where your statement:

insert into dbo.btlBItest  
from 'file1.csv'  
with (data_source = 'extds')  

Should read:

insert into <database>.dbo.btlBItest  
from 'file1.csv'  
with (data_source = 'extds')  

Upon closer inspection, I see that the Create an external data source for bulk operations retrieving data from Azure Storage (link) is correct. So, either try adding the database name to the BULK INSERT statement as I suggested or, the issue is a firewall setting per the TechCommunity troubleshooting blog referenced below:

"If you are getting an error 5 (Access Denied), make sure that you have not denied access to your Azure Blob Storage account using the firewall:"

See image for additional detail.

Additional resources:

• T-SQL: Bulk Insert Azure CSV Blob into Azure SQL Database (link)
• Troubleshooting BULK INSERT and OPENROWSET issues on Azure SQL Database (link)

Answer 2

DO a simple check and see if you have "accidentally" given an end date of the shared access signature that has now passed.

/BK