Azure Synapse - Linked Service to SQL Dedicated Pool

Question

Azure Synapse - Linked Service to SQL Dedicated Pool

Aayush Suresh Jain 151

I have a Synapse Workspace built with SQL dedicated pool. I have also created a linked service to the dedicated pool which connects correctly. The synapse workspace is configured with Managed Virtual Network. I need to create an integration dataset for one of the external table created in the SQL Dedicated Pool. I am able to get the schema load. User's image

However when I try to preview data, I am getting the following error. It is able to load schema, so I am not sure what did I miss? How can I preview the data? User's image

Bhargava-MSFT 31,356 Reputation points Microsoft Employee Moderator

2023-04-10T20:53:11.6966667+00:00

Hello Aayush Suresh Jain, As per the error message, it seems that there is an issue with the authentication of the external table. The error message indicates that the remote server returned a 401 unauthorized error, which suggests that the credentials used to access the external table are not valid. Dedicated SQL defaults to AAD pass-through when accessing the storage, so can you make sure you have storage blob data contributor permissions to synapse managed Identity? Can you please follow the below document and see if it helps. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-external-tables
Aayush Suresh Jain 151 Reputation points

2023-04-11T14:28:07.68+00:00

@Bhargava-MSFT The synapse managed identity have the Storage Blob Data Contributor role. For the linked service to the Dedicated SQL Server, we are using SQL Server Authentication as the way to connect. The linked service is getting connected. The schema for the table also gets populated. I am just not able to preview the external tables, the tables(not external) which I created for our use, I am able to preview the data for those using the same Linked Service.
Bhargava-MSFT 31,356 Reputation points Microsoft Employee Moderator

2023-04-14T16:16:45.99+00:00

Hello Aayush Suresh Jain, I am checking to see if you got a chance to try the above steps. Please let me know if you have any further questions.

3 answers

Your answer

Bhargava-MSFT 31,356 Reputation points Microsoft Employee Moderator

2023-04-10T20:53:11.6966667+00:00

Hello Aayush Suresh Jain, As per the error message, it seems that there is an issue with the authentication of the external table. The error message indicates that the remote server returned a 401 unauthorized error, which suggests that the credentials used to access the external table are not valid. Dedicated SQL defaults to AAD pass-through when accessing the storage, so can you make sure you have storage blob data contributor permissions to synapse managed Identity? Can you please follow the below document and see if it helps. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-external-tables
Aayush Suresh Jain 151 Reputation points

2023-04-11T14:28:07.68+00:00

@Bhargava-MSFT The synapse managed identity have the Storage Blob Data Contributor role. For the linked service to the Dedicated SQL Server, we are using SQL Server Authentication as the way to connect. The linked service is getting connected. The schema for the table also gets populated. I am just not able to preview the external tables, the tables(not external) which I created for our use, I am able to preview the data for those using the same Linked Service.
Bhargava-MSFT 31,356 Reputation points Microsoft Employee Moderator

2023-04-14T16:16:45.99+00:00

Hello Aayush Suresh Jain, I am checking to see if you got a chance to try the above steps. Please let me know if you have any further questions.

Answer 1

Hello Aayush Suresh Jain, It seems that the issue is specific to previewing data from external tables. Since you can preview data from non-external tables using the same linked service, the connection and authentication settings for the SQL Dedicated Pool are likely correct. Can you please check the below:

Verify the external data source used for the external table has the correct authentication settings, such as the shared access signature or storage account key
Ensure that the external table is correctly defined, including the correct file format, data source, and schema
Check if there are any firewall or network restrictions on the external storage account that might be blocking access from the Synapse Workspace.
I hope this helps. Please let me know if you have any further questions.

Answer 2

Aayush Suresh Jain 151

@Bhargava-MSFT I had to modify the identity created for external tables and that worked. Thanks for the help!

Bhargava-MSFT 31,356 Reputation points Microsoft Employee Moderator

2023-04-18T18:19:23.2433333+00:00

Hello Aayush Suresh Jain, Glad to know that you were able to resolve the issue and thanks for sharing the details.

Answer 3

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Share via

Azure Synapse - Linked Service to SQL Dedicated Pool

3 answers

Your answer