Getting error code 2200 error : 'Odbc Operation Failed' while copying data from ADF to snowflake

Question

Error : Operation on target CopySnowflake failed: ErrorCode=UserErrorUnclassifiedError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Odbc Operation Failed.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.IndexOutOfRangeException,Message=region_group,Source=System.Data,'

I am trying to copy data from ADF to Snowflake table. I tested the connection, it's successful. I have checked the mapping, it is correct as well. I couldn't find any solution online.

Please help me to know how to fix this issue

EDIT: I am getting this new error now

Operation on target Copy_3j9 failed: Failure happened on 'Sink' side. ErrorCode=FileForbidden,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error occurred when trying to upload a blob, detailed message: topbusiness.txt,Source=Microsoft.DataTransfer.ClientLibrary,''Type=Microsoft.WindowsAzure.Storage.StorageException,Message=The remote server returned an error: (403) Forbidden.,Source=Microsoft.WindowsAzure.Storage,StorageExtendedMessage=Server failed to authenticate the request. Make sure the value of Authorization header is formed correctly including the signature. RequestId:005f8bc6-001e-004c-32a1-b4d23d000000 Time:2021-09-28T19:46:41.7182559Z,,''Type=System.Net.WebException,Message=The remote server returned an error: (403) Forbidden.,Source=Microsoft.WindowsAzure.Storage,'

Kindly help me to find the fix for both

Answer 1

When creating the snowflake account, I had not specified any cloud provider. I created another snowflake account by specifying Azure as the cloud provider and tried with that. It worked!

Answer 2

The error indicates that the problem is not with Snowflake connection, but with Azure Storage. Most probably you have the "Enable staging" option selected from the data upload activity. Check if you have a valid connection to Azure storage (maybe your SAS key has expired), or disable this option.

Reference: ADF Staged copy

Enable staging option

Answer 3

1. If you are using any organizational network, please ensure to whitelist URLs as the connection to the Azure storage account might get blocked by the any organization proxy. To whitelist URLs*.blob.core.windows.net on the proxy while trying to join Azure blob storage.

2. Azure Portal -> Storage Account -> Networking -> Check Allow Access From (All Networks / Selected Networks) If it is "Selected Networks" - It means the storage account is firewall enabled. If the storage account is firewall enabled, please check the ADF is whitelisted to access.

Because "All authorised requests must contain the Coordinated Universal Time (UTC) timestamp for the request," 403 is frequently observed when authorising using a shared key.

3. The storage services make sure a request isn't more than 15 minutes old when it arrives at the service. This protects against a variety of security threats, including replay attacks. When this check fails, the server issues a 403 error code (Forbidden). In this case, check your server's data-time.