'Not able to validate external location because The remote server returned an error: (401) Unauthorized.' Not able to validate external location because The remote server returned an error: (401) Unauthorized

Prabhakar Oruganti 1 Reputation point
2022-11-28T13:18:05.007+00:00

1) I loaded data from SAP table to ADLS gen2 as parquet using ADF data copy.
2) Create the external table in the synapse dedicated pool using create external table script (by right click and creating external table respective dedicated synapse pool)/
3) successfully select/retrieve the data (synapse SQL query ) from the external table in the synapse.
4) create the lookup activity by pointing to the external table as a source.

then the issue started, the external table failed to query the data from synapse external tables but when I switch to a normal table lookup is a success for other tables.

below is the error

A database operation failed with the following error: 'Not able to validate external location because The remote server returned an error: (401) Unauthorized.'
Not able to validate external location because The remote server returned an error: (401) Unauthorized., SqlErrorNumber=105215,Class=16,State=1,
Activity ID: 228af88b-50d0-4391-a3b0-0085047930a7

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,395 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,599 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Prabhakar Oruganti 1 Reputation point
    2022-11-30T10:02:42.577+00:00

    Hi Martin,

    Thanks for your reply.

    let me add more details here.

    we have the below services running under one subscription.

    1) ADLS Gen2 Storage account
    2) Synapse workspace with dedicated SQL
    3) ADF workspace

    after executing the below steps it started working

    CREATE DATABASE SCOPED CREDENTIAL msi_cred
    WITH IDENTITY = 'Managed Service Identity' ;

    --Create external data source with abfss:// scheme for connecting to your Azure Data Lake Store Gen2 account

    CREATE EXTERNAL DATA SOURCE ext_datasource_with_abfss
    WITH
    ( TYPE = HADOOP ,
    LOCATION = 'abfss://<ContainerName@<StorageAcountName>.dfs.core.windows.net' ,
    CREDENTIAL = msi_cred
    ) ;

    --Create external data source with above data source name( ext_datasource_with_abfss)

    CREATE EXTERNAL TABLE [ext_sap].[syn_es_sap_mchb]
    (
    [MANDT] nvarchar NULL,
    <* Rest of the columns>
    )
    WITH (DATA_SOURCE = [ext_datasource_with_abfss], LOCATION = N'raw/es/sap/MCHB', FILE_FORMAT = [SynapseParquetFormat], REJECT_TYPE = VALUE, REJECT_VALUE = 0 )
    GO

    To answer some of your questions

    Q) you have both a Lookup pipeline activity, and a Synapse dedicated SQL(?), targeting the same external table in ALDS gen2.

    Ans) yes, lookup activity was created in the ADF pipeline.

    Q) One works fine, but the other does not. Did the broken one work before, or did it never work?

     Ans) not sure I understand the question here. after creating the external table, we create the data copy activity connecting the source to the external table which created in synapse and then try to preview the source data set then its failed

    Q) I'm unclear whether it is the lookup or the SQL or on-demand SQL which is failing.

    Ans) actually data preview was failing when connecting the external table as a source.

    Q) Is the "ADF data copy" run in the same workspace as the Synapse SQL query, or is it an actual, separate ADF?

    Ans) we have both Synapse and ADF workspaces. it's separate

    anyways with the above-executed steps working fine, now we are able to connect the synapse external table(ADLS pointing source) from ADF/Synapse pipelines as source tables.

    Thanks for your reply.

    Thanks,
    Prabhakar

    0 comments