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

Question

Hello,

I'm facing the below error while I was trying to import data from a CSV file stored in blob storage to SQL Azure Database.

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

My storage account and SQL database are behind the firewall and reside in the same Vnet with private endpoints. I have added the SQL identity to the trusted Resource instances in the storage account and also granted the blob contributor access to the SQL identity still facing the issue.

Code:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE DATABASE SCOPED CREDENTIAL rawblobcred

WITH IDENTITY = 'SHARED ACCESS SIGNATURE',

SECRET = 'SAS Key' //without ?

CREATE EXTERNAL DATA SOURCE rawblobread

WITH (

TYPE = BLOB_STORAGE,

LOCATION = 'https://storage.blob.core.windows.net',

CREDENTIAL = rawblobcred

)

BULK INSERT [dbo].[TotalSale] FROM 'raw/sales.csv'

WITH (DATA_SOURCE = 'rawblobread', FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

Can you please help me with this? Thanks in advance!

Answer 1

You verified the Azure SQL Firewall,. Could you please verify you are allowing traffic from that VNET on the Azure Storage Account also?

This troubleshooting guide may be useful too.

Answer 2

Hi
PP007 •,

If public network access is disabled in the security (firewall) on the Azure Storage side, you cannot perform a BULK INSERT into Azure SQL Database using data on an Azure Storage blob. If the firewall on the Azure Storage side sets public network access to "Enable from all networks" or "Enable from selected virtual networks and IP addresses", BULK INSERT is possible from SQL Database. However, depending on the public network access settings on the Azure Storage side mentioned above, the advance preparation and setting items required for BULK INSERT differ, and each procedure is explained below. < when the firewall on the Azure Storage side sets public network access to "Enable from all networks"> You can use Shared Access Signatures (SAS) to access Azure Storage and perform BULK INSERTS from SQL Database.

1. Generate a SAS for Azure Storage from the Azure portal.
2. Click Generate SAS and Connection String to display the SAS token.
3. Make a note of the SAS token that appears.
4. Connect to SQL Database, add credentials and data sources, and perform BULK INSERTS.
5. Create an external data source by creating credentials from SAS tokens, as shown in the following example query.

USE THE CREDENTIALS YOU CREATED OR AN EXTERNAL DATA SOURCE TO PERFORM A BULK INSERT. Sample query Create authentication information using SAS tokens CREATE DATABASE SCOPED CREDENTIAL testCredential WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SAS token starting with SECRET = 'sv (leading ? except)'; Use credentials to register an external data source CREATE EXTERNAL DATA SOURCE testSource WITH ( TYPE = BLOB_STORAGE, LOCATION = 'https://<storageacount>.blob.core.windows.net/<container> ', CREDENTIAL = testCredential ); Perform BULK INSERT BULK INSERT tableA FROM 'file_name.csv' WITH (DATA_SOURCE = 'testSource', FORMAT = 'CSV');

In the firewall on the Azure Storage side, if you set public network access to "Enable from selected virtual network and IP address"

Managed Identity allows you to access Azure Storage and perform BULK INSERTS into SQL Database.

1.On the target SQL Database server, have system-assigned managed identity enabled. 2.In the network settings of the Storage Account,check Allow Azure services in the list of trusted services to access this storage account and save. 3.In Storage Account access control, grant "Storage Blob Data Reader Permissions" to the managed identity of the target SQL Database from the role assignment. 4.Connect to SQL Database, add credentials and data sources, and perform BULK INSERTS. Create a master key, create credentials as Managed Identity, and create an external data source, as shown in the following example query.

USE THE CREDENTIALS YOU CREATED OR AN EXTERNAL DATA SOURCE TO PERFORM A BULK INSERT.

Sample query Create a master key (if it already exists, there is no problem with the existing one) CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>'; Use Managed Identity to create credentials CREATE DATABASE SCOPED CREDENTIAL msi WITH IDENTITY = 'MANAGED IDENTITY'; Use credentials to register an external data source CREATE EXTERNAL DATA SOURCE storageAccount WITH (TYPE = BLOB_STORAGE, LOCATION = 'https://<storageacount>.blob.core.windows.net/<container> ', CREDENTIAL = msi ) Perform BULK INSERT BULK INSERT tableA FROM 'file_name.csv' WITH (DATA_SOURCE = 'storageAccount', FORMAT = 'CSV');

https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server?view=sql-server-ver16

Reference: https://learn.microsoft.com/en-us/answers/questions/1302540/bulk-insert-from-file-in-azure-storage-requires-en

Hope this helps.

Let us know if further queries.

Thanks