Getting Not able to validate external location because The remote server returned an error: (401) Unauthorized error while executing stored procedure in Synapse dedicated pool using ADF

Question

Hi Team,

I've created a stored procedure in Synapse dedicated SQL pool, to load data from an external table(pointing to a Parquet file in Data lake) to a managed table in the same dedicated SQL pool. I'm able to execute the stored procedure without any errors in Synapse studio. But when I 'm trying to execute the same SP using ADF , I'm getting the below error message

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

I'm using SQL server authentication to connect to Synapse using ADF Linked service. Also, apart from this SP I have another SP which takes couple of parameters and inserts an entry in the log table and that is executing fine when its invoked from ADF.

I've tried creating external data source as mentioned below as well with no luck:

CREATE DATABASE SCOPED CREDENTIAL AdlsCredentials
WITH IDENTITY = 'SHARED ACCESS SIGNATURE' ,
SECRET = '{yourSecret}'

CREATE EXTERNAL DATA SOURCE {yourEDSname}
WITH
(
LOCATION = 'https://{youradls}.blob.core.windows.net/{yourpath}/' ,
CREDENTIAL = AdlsCredentials
) ;

Could you please let me know what can be done to fix this issue, we have a major production deployment next week and this is a key piece of work any advice would be really helpful.

Thanks,
Sridhar

Answer 1

Hi @Varanasi, Sridhar ,

Thanks for posting query in Microsoft Q&A Platform.

Could you please check your SAS token has required permissions? Make sure you selected proper values under Allowed resource type and Allowed permissions while generating SAS token.

Also, try giving storage blob data contributor role to managed identity of Synapse workspace.

Please let me know how it goes.