ErrorCode=SnowflakeFailToAccess when Copy data from Snowflake to azure blob storage through private endpoint

Question

We have snowflake hosted in AWS and try to copy the data to Azure blob storage using private endpoint.

• We created a linked service to snowflake account. This linked service does not have a private endpoint to snowflake. It connects through a public runtime with snowflake credential.
• The second linked service is for the azure blog storage through a private endpoint on a private integration runtime as the public network access is blocked in this storage. We configured the SAS token with full permissions for this linked service connection

When attempted to do the copy from snowflake to the storage, it failed with the following error

ErrorCode=SnowflakeFailToAccess,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Snowflake fails to access remote file. Please make sure you grant proper access permission to Snowflake in Azure Blob storage when using shared access signature. To learn more about this, see https://docs.snowflake.com/en/user-guide/data-load-azure-config.html#option-2-generating-a-sas-token.,Source=Microsoft.DataTransfer.Common,''Type=System.Data.Odbc.OdbcException,Message=ERROR [42501] Failed to access remote file: access denied. Please check your credentials,Source=SnowflakeODBC_sb64.dll,'

The copy data will pass if switching to allow public network access so this proves that the SAS token has the correct permissions

We tried to look into whitelisting the virtual network IP, however, given that this snowflake account is in AWS, this option does not work.

Is there another viable approach we could try without making the storage public in Azure?

Will whitelisting snowflake vnet cidr block or snowflake storage integration be an option for this cross cloud connection scenario?

Answer 1

Hello Kaylee Jacques,

Thank you for posting your query here!

If all permissions have already been granted to the Snowflake Service Principal, this error is likely a result of a configured firewall in the Azure Portal and the user not allowing the Snowflake VNet Subnet IDs. In order to resolve this error, you need to allow the VNet Subnet IDs. Permissions error during COPY INTO from Azure Storage Location - "Failed to access remote file: access denied. Please check your credentials" (snowflake.com)

To resolve such issues which involve Storage Container set up with Firewall, the IP ranges of the Cloud provider where Snowflake is deployed should be whitelisted on the Azure side.
To get the details for the IP ranges, you may look up the IP Ranges for the corresponding cloud provider details:

For AWS: https://ip-ranges.amazonaws.com/ip-ranges.json

Or else to resolve this, try below steps:

Enable firewall to all networks or whitelist your IP if you are giving permission to selected IP's.
Generate SAS token with appropriate permission and use it in ADF (try with changing key 1 to key 2 and the generate).

Please note that even if you connect Snowflake with Azure blob storage containers using storage integration, you will need to set network access rule to allow traffic from all networks. Connecting Snowflake to Azure Container | by Snowflake Wiki | Medium

The following guide from Snowflake elaborates on the steps to allowing VNet subnet IDs if Azure storage firewall is configured to block all unauthorized traffic to your Azure storage account.

The error can also be caused by missing access permissions on the source (Azure Blob Storage) when executing the Snowflake COPY command. Please make sure that you have granted proper access permissions to Snowflake in Azure Blob Storage Since you're using a shared access signature (SAS) for authentication in your Azure Blob Storage linked service, please make sure that the SAS token has the necessary permissions and IP addresses allowed for Snowflake. Refer to this article for more information on generating a SAS token with the correct permissions.

Further reference: https://learn.microsoft.com/en-us/azure/data-factory/connector-snowflake?tabs=data-factory

If you have any further queries on Azure platform, please let us know. I’m happy to assist you further.

Please do not forget to "Accept the answer” and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.