Synapse CopyInto with ErrorFolder Operating system error code 5 (Access is denied)

Question

I'm trying to use "Copy Into" to bulk load data into Synapse from a CSV file.

Here is the command I used.

COPY INTO dbo.[StageProducts2]
FROM 'https://<<mystorageaccount>>.blob.core.windows.net/data-files/Instacart/products.csv'
WITH (
FILE_TYPE='CSV',
CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='<<my storage account key>>'),
FIRSTROW=2,
FIELDQUOTE = '"',
FIELDTERMINATOR=',',
MAXERRORS = 100,
ERRORFILE = '/errorsfolder'
)

And I'm getting the following error.

Cannot bulk load because the file "https://<<mystorageaccount>>.blob.core.windows.net/data-files/errorsfolder/_rejectedrows/20201015_112602/QID988_1.Row.Txt" could not be opened. Operating system error code 5(Access is denied.).
Cannot bulk load because the file "https://<<mystorageaccount>>.blob.core.windows.net/data-files/errorsfolder/_rejectedrows/20201015_112602/QID988_1.Error.Txt" could not be opened. Operating system error code 5(Access is denied.).

I'm using the storage account key which should have full permissions to the storage account. And I'm logged in using db admin account from SQL Mgt. Studio. So I'm not sure why this error is occurring.

Here is some sample data.

product_id,product_name,aisle_id,department_id
25,Salted Caramel Lean Protein & Fiber Bar,3,19
26,Fancy Feast Trout Feast Flaked Wet Cat Food,41,8
27,Complete Spring Water Foaming Antibacterial Hand Wash,127,11
28,Wheat Chex Cereal,121,14
29,Fresh Cut Golden Sweet No Salt Added Whole Kernel Corn,81,15
30,"Three Cheese Ziti, Marinara with Meatballs",38,1
105,"Easy Grab 9\""x13\"" Oblong Glass Bakeware",10,17

I have also tried passing in errorfile_credential parameter and get the same error still.

COPY INTO dbo.[StageProducts2]
FROM 'https://<<mystorageaccount>>.blob.core.windows.net/data-files/Instacart/products.csv'
WITH (
FILE_TYPE='CSV',
CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='<<my storage account key>>'),
FIRSTROW=2,
FIELDQUOTE = '"',
FIELDTERMINATOR=',',
MAXERRORS = 100,
ERRORFILE = 'https://<<mystorageaccount>>.blob.core.windows.net/data-files/Instacart/errorsfolder',
ERRORFILE_CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='<<my storage account key>>')
)

I originally wanted to use Polybase but because of limitation with handling escape characters, I'm trying to use Copy Into as an alternative. See link for more info on this. @HarithaMaddi-MSFT

Appreciate the help. Thank you.

Answer 1

Hello @satish umapathy ,
Thanks for the ask and also using the Microsoft Q&A.

Apologies , I was able to repro this & the PG team have also confirmed that there is a bug which is actively worked upon fix this issue .I will keep you updated on this . Since the ERRORFILE is an optional parameter , you can go without that for the time being .

COPY INTO dbo.[StageProducts2]  
FROM 'https://<<mystorageaccount>>.blob.core.windows.net/data-files/Instacart/products.csv'  
WITH (  
FILE_TYPE='CSV',  
CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='<<my storage account key>>'),  
FIRSTROW=2,  
FIELDQUOTE = '"',  
FIELDTERMINATOR=',',  
MAXERRORS = 100  
)  

Thanks Himanshu

Answer 2

Hi @HimanshuSinha-msft ,
we are also receiving the error message

Cannot bulk load because the file "https://<our storage account>.dfs.core.windows.net/<path to file>" could not be opened. Operating system error code 5(Access is denied).  

In our case we are performing a select on a View (on Synapse SQL Serveless), which is reading an openrowset from the ADLS Gen2 (Delta Lake format).

We are completely using private endpoint connections between synapse and the storage account in our setup.

As we disabled the firewall for the public access temporary the issue was gone.
So, the message "Access is denied" can also mean networking issues, that a networking connection is not allowed.

But this is not a nice error message and when I read access I would assume it means access rights on the storage.
I think in case the Serverless SQL Engine has some network issues it probably should be mentioned in the future in the error messages.
That would be great. Maybe you can forward this to the product group.

Thank you in advance.
Cheers,
Marco

Answer 3

Is there any update on this? Does anyone know of a support ticket to get this fixed?