hyperlinkazure-data-factoryazure-synapsesink

Azure DataFactories link to Synapse Analytics sqloperationfailed error

I try to create linked service in Data Factory from ADLS to Synapse sql serverless sql pool but I get error:

SqlOperationFailed
A database operation failed with the following error: 'External table 'dbo.Ren' is not accessible because location does not exist or it is used by another process.'
External table 'dbo.Ren' is not accessible because location does not exist or it is used by another process., SqlErrorNumber=16562,Class=16,State=1,

I couldn't fix it. I also tried using Data Studio and received the same error. I attempted to reconnect, recreate the synapse, and recreate everything. Additionally, I tried to disconnect the Data Lake storage link from the synapse but was unable to do so.

Solution 
  •  'External table 'dbo.Ren' is not accessible because location does not exist or it is used by another process.'
External table 'dbo.Ren' is not accessible because location does not exist or it is used by another process.,

    Make sure the Data Factory has access to the ADLS account data, as it may be the reason for the above error. To access ADLS data, the ADF workspace should have the storage blob data contributor role in the ADLS account. To add the role, follow the procedure below:

    1. Go to Access Control of the storage account, click on Add, and select Add Role Assignment as shown below:

      enter image description here

    2. Search for the Storage Blob Data Contributor role, select it, and click on Next as shown below:

      enter image description here

    3. Check managed identity, select the ADF managed identity, and click on Review + Assign as shown below:

      enter image description here

    4. After assigning the role, create a user for the Data Factory and assign a role to it in the serverless SQL pool using the code below:

      CREATE USER [<ADFName>] FROM EXTERNAL PROVIDER
ALTER ROLE db_owner ADD MEMBER [ADFName]

    5. Create a linked service with managed identity authentication as shown below:

      enter image description here

    6. Create a dataset with the linked service for the dbo.Ren table, and you will be able to read the table successfully as shown below:

      enter image description here