Cannot copy data from Snowflake into Azure Blob

0
votes

I am trying to copy data from Snowflake into an Azure Blob using Azure Data Factory.

The role I am using has select permissions on the table, and I have no issues querying the data using the Snowflake console.

I am also able to copy into the targeted blob from other sources (in Azure) using the same SAS token.

This is the query I have, generated by Azure Data Factory, (with specifics omitted)

COPY INTO 'azure://****.blob.core.windows.net/snowflake-stage/********-****-****-****-************/SnowflakeExportCopyCommand/'
FROM (select * from MYSCHEMA.MYTABLE)
CREDENTIALS = (AZURE_SAS_TOKEN = '****')
FILE_FORMAT = (type = CSV COMPRESSION = GZIP RECORD_DELIMITER = '
' FIELD_OPTIONALLY_ENCLOSED_BY = '"' ESCAPE = '\\' NULL_IF = '')
HEADER = TRUE
SINGLE = FALSE
OVERWRITE = TRUE
MAX_FILE_SIZE  = 268435456

And this is the error I am getting:

ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [42501] Failed to access remote file: access denied.

Please check your credentials,Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,''Type=System.Data.Odbc.OdbcException,Message=ERROR [42501] Failed to access remote file: access denied. Please check your credentials,Source=Snowflake,'

Are there more Snowflake permissions that I need in order to do this kind of copy? Or is this perhaps an issue with the write-permissions to the Azure container?

1
azure-data-factorysnowflake-cloud-data-platformazure-blob-storageazure-container-service
Is snowflake-stage within your URL a stage name or the container name? – Abhi Reddy
Are you sure that token you are using has write access to the container? – Mike Walton
@AbhiReddy it is the container name. – Jordan Holcombe
@MikeWalton yes. This does appear to be related to the access to the container and not an issue on the Snowflake side though. I've marked the "solution" in the answer. – Jordan Holcombe

1 Answers

0
votes

The "solution" for this indicates a likely bug for the permissions on the blob itself.

Switching the container permissions to public, then back to private again fixes the issue.