Failed to access snowflake remote file error 2200

Question

when try to query data from Snowflake into an Azure Data Explorer using Azure Data Factory connector. There is an error message as following:

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,'

have no issues querying the data using the Snowflake console.

Answer 1

Hello @jia si ,
Thanks for the ask and using the Microsoft Q&A platform .
From the error it looks to be the user which is setup in the linked service is not able to access a file . I am not sorry but why it is complaining about a file , have you enabled staging Option , if yes can you please check the credential there ? Sharing the snapshot below .

The below linked service for Snowflake works for me .

Please do let me know how it goes .
Thanks
Himanshu
Please do consider clicking on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Answer 2

Hi, Himanshu

Thanks for your support!

In the first time, It was failed to test staging account link service, and then we refreshed SAS token of blob, the connection issue of staging account was resolved.

But when we tried again to run pipeline (snowflake to blob) there was also an error message as following :

REST request for URL https://funpluspocstorage.blob.core.windows.net/adfstaging/7c01b243-b63d-497f-9df3-c1ffd62ea1d8/SnowflakeExportCopyCommand/data_0_3_0.csv?F0GStWuSk3FJox6y1MBIsdqx7LwkQWwampcieD3fkaU%3D failed: HTTP error (http error) - code=401 remoteError='NoAuthenticationInformation' remoteErrorMessage='<?xml version="1.0" encoding="utf-8"?><Error><Code>NoAuthenticationInformation</Code><Message>Server failed to authenticate the request. Please refer to the information in the www-authenticate header. RequestId:3d968647-c01e-00a9-6096-6c5e9f000000 Time:2021-06-29T03:26:08.3709634Z</Message></Error>'

Answer 3

Answer 4

Hi Himanshu,

It seems the link between snowflake to blob is normal, but it is failed to sync data from blob to Adx.

错误代码
2200
故障排除指南
失败类型
用户配置问题
详细信息
Failure happened on 'Sink' side. ErrorCode=UserErrorKustoWriteFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Write to Kusto failed with following error: An error occurred for source: 'DataReader'. Error: 'One or more errors occurred.'.,Source=Microsoft.DataTransfer.Runtime.KustoConnector,''Type=Kusto.Ingest.Exceptions.IngestClientException,Message=An error occurred for source: 'DataReader'. Error: 'One or more errors occurred.',Source=Kusto.Ingest,''Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column 'REVENUE' contains an invalid value '20.6323'. Cannot convert '20.6323' to type 'Int64'.,Source=Microsoft.DataTransfer.Common,''Type=System.OverflowException,Message=Value was either too large or too small for an Int64.,Source=mscorlib,

Answer 5

Hi

We refreshed SAS of the blob, the issue has been resolved.

thanks for your help!

SIJIA