ADF can access External Table in Synapse

Question

ADF can access External Table in Synapse

Shobna Lata 20

I am trying to do a copy activity in ADF that reference an external table in Synape (which references a parquet file in Storage accountV2). This runs fine in Synapse however I get the following error which I run it through an ADF pipeline.

Any help on how to fix this issue please?

I have added my synapse workspace, ADF to the storage blob contributor role.

Error details

Copy data1

Error codeSqlOperationFailed

Failure typeUser configuration issue

Activity ID9be0b6f0-eb02-4335-9cc0-1e4d2f9d8ce7

DetailsFailure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Not able to validate external location because The remote server returned an error: (401) Unauthorized.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Not able to validate external location because The remote server returned an error: (401) Unauthorized.,Source=.Net SqlClient Data Provider,SqlErrorNumber=105215,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=105215,State=1,Message=Not able to validate external location because The remote server returned an error: (401) Unauthorized.,},],'

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-08-22T00:15:05.8766667+00:00

@Shobna Lata Just checking in to see if the below information was helpful. If it answers your query, please do click Accept Answer and Yes for "was this answer helpful", as it might be beneficial to other community members reading this thread. If you have any further query, do let us know.

Thank you
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-08-22T21:24:50.1166667+00:00

@Shobna Lata

We received your feedback that the answer provided on the thread was not helpful.

Kindly let us know what we could have done better to improve the answer and make your engagement experience good. We are here to help you and strive to make your experience better and greatly value your feedback.

I have provided a detailed answer which has clear steps for which you are looking for. Kindly follow the steps provided in my answer where I suggested to create Database Scoped credential and then use that credential to create your external data source and then create the external tables using that external data source and then try to access the external table from ADF pipeline.

If my response is helpful to resolve your issue, you may consider re-surveying/rating for the engagement you received on the thread. Your feedback is very important to us. In case if you have further questions, please feel free to let us know.

Looking forward to you reply. Much appreciate your feedback!

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-08-23T17:00:44.6066667+00:00

@Shobna Lata We still have not heard back from you. Just wanted to check if the below information was helpful? If it answers your query, please do click Accept Answer and Yes for "was this answer helpful", as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

Thank you

Accepted answer

1 additional answer

Your answer

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-08-22T00:15:05.8766667+00:00

@Shobna Lata Just checking in to see if the below information was helpful. If it answers your query, please do click Accept Answer and Yes for "was this answer helpful", as it might be beneficial to other community members reading this thread. If you have any further query, do let us know.

Thank you
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-08-22T21:24:50.1166667+00:00

@Shobna Lata

We received your feedback that the answer provided on the thread was not helpful.

Kindly let us know what we could have done better to improve the answer and make your engagement experience good. We are here to help you and strive to make your experience better and greatly value your feedback.

I have provided a detailed answer which has clear steps for which you are looking for. Kindly follow the steps provided in my answer where I suggested to create Database Scoped credential and then use that credential to create your external data source and then create the external tables using that external data source and then try to access the external table from ADF pipeline.

If my response is helpful to resolve your issue, you may consider re-surveying/rating for the engagement you received on the thread. Your feedback is very important to us. In case if you have further questions, please feel free to let us know.

Looking forward to you reply. Much appreciate your feedback!

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-08-23T17:00:44.6066667+00:00

@Shobna Lata We still have not heard back from you. Just wanted to check if the below information was helpful? If it answers your query, please do click Accept Answer and Yes for "was this answer helpful", as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

Thank you

Answer 1

@Shobna Lata Welcome to Microsoft Q&A forum and thanks for reaching out here.

In addition to Amira Bedhiafi inputs, please validate below prerequisites if you are accessing External tables from ADF.

While creating the external data source, have you used any Database Scoped Credential ? In case if you haven't created any Database Scoped Credential then please create a database scoped credential with `Managed Service Identity
Then use that credential for creating the external data source as shown below:

-- Create a database scoped credential with Managed Service Identity.
CREATE DATABASE SCOPED CREDENTIAL NYCGreenTaxiCredentialADF
WITH
  IDENTITY = 'Managed Service Identity';


-- Create External Data Source with Credential created in previous step.
IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'NYCGreenTaxiData') 
	CREATE EXTERNAL DATA SOURCE [NYCGreenTaxiData] 
	WITH (
		LOCATION = 'abfss://<containerName>@sharedADLSGen2.dfs.core.windows.net',
		CREDENTIAL = NYCGreenTaxiCredentialADF 
	)
GO

After creating the external data source using credential, then create the external table using this external data source.
Then go to you ADF linked service and set up the Synapse Analytics Linked service configuration using SQL auth credentials and try to preview the data. (Please ensure that your Synapse Workspace Managed identity has Blob storage contributor role assigned)

I have reproduced the error message you have experienced by creating an External Data source without database scoped credentials and it throws the same error message. Then in order to overcome the above error, I have created a Database scoped credential using `Managed Service Identity and then used that credential to create the external data source and then was able to access the external table from ADF without any issues.

Hope this info helps. Let us know if you have further query.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

I can see from the error message you are having that it is related to permissions issue since you are trying to access the external location (the Storage Account V2) where the parquet file is stored.

Two cases apply :

If you're accessing storage through private endpoints, ensure that the managed private endpoint in Synapse is approved and correctly configured.
If you are using Storage Account Keys or Shared Access Signature (SAS) tokens to authenticate, make sure they are valid and have the correct permissions.

You also mentioned adding the Synapse workspace and ADF to the Storage Blob Contributor role. Can you make sure the Azure Data Factory's Managed Identity also has the Storage Blob Data Reader role on the Storage Account V2, as this could give it the necessary permissions to read the data.

Check also that the firewall rules in both the Storage Account and Synapse Workspace allow communication between the services. If there are IP restrictions in place, you may need to add the necessary IP addresses to the allow list.

Share via

ADF can access External Table in Synapse

1 additional answer

Your answer