Failed to access snowflake remote file error 2200

jia si 21 Reputation points Microsoft Employee
2021-06-28T05:15:55.11+00:00

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.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,517 questions
0 comments No comments
{count} votes

Accepted answer
  1. HimanshuSinha-msft 19,376 Reputation points Microsoft Employee
    2021-06-28T19:52:28.703+00:00

    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 .

    109860-image.png

    The below linked service for Snowflake works for me .

    109945-image.png

    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

    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. jia si 21 Reputation points Microsoft Employee
    2021-06-29T03:45:00.557+00:00

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

    0 comments No comments

  2. jia si 21 Reputation points Microsoft Employee
    2021-06-29T03:45:36.66+00:00

    110097-staging-link.jpg

    0 comments No comments

  3. jia si 21 Reputation points Microsoft Employee
    2021-06-29T05:42:05.423+00:00

    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,

    0 comments No comments

  4. jia si 21 Reputation points Microsoft Employee
    2021-07-02T01:22:08.68+00:00

    Hi

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

    thanks for your help!

    SIJIA

    0 comments No comments