How can I enable Synapse Link on a source azure serverless sql db to connect to dedicated sql pool..

Michael Minto 25 Reputation points
2024-07-01T06:11:32.8433333+00:00

Context

Our team is planning to regularly refresh our external valuations dedicated pool using staging data from our serverless sql database. To implement this I have created the dedicated sql pool and the azure sql db is ready with plenty of data. The user name of my sql db and my synapse workspace are the same, which both have administrative rights.

Replicating Error

The last step is to create the Link Connection between the source db and target pool. This has been problematic as I keep getting an error message when starting the link connection: "Only active directory users can impersonate other active directory users."

Error details

Failed to enable Synapse Link on the source due to 'Failed to enable the source database: Could not update the metadata. The failure occurred when executing the command 'SetSynapseLink(Value = 1)'. The error/state returned was 33171/1: 'Only active directory users can impersonate other active directory users.'. Use the action and error to determine the cause of the failure and resubmit the request.'.

After extensive searching online I have yet to resolve this.
I can be reached at michael@commercialcollective.com.au
Could you assist please.

Kind Regards

Michael Minto

Data Engineer

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,613 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Harishga 5,510 Reputation points Microsoft Vendor
    2024-07-01T15:37:49.2066667+00:00

    Hi @Michael Minto
    Welcome to Microsoft Q&A platform and thanks for posting your question here.

    You are trying to create a Link Connection between your source SQL database and target dedicated SQL pool. However, you are encountering an error message stating that “Only active directory users can impersonate other active directory users.” This error happens because the operation requires an Active Directory user account to perform the necessary impersonation for the Link Connection.

    To resolve this, first, ensure that the user account you are using to create the Link Connection is an Active Directory user with the necessary permissions. Next, verify that the Active Directory configuration is correct, and that the user has the required impersonation rights. Finally, refer to the Azure Synapse Link documentation for any specific requirements or troubleshooting steps related to AD user impersonation.

    By following these steps, you should be able to address the error and successfully create the Link Connection.

    Reference
    https://video2.skills-academy.com/en-us/azure/synapse-analytics/synapse-link/troubleshoot/troubleshoot-sql-azure-active-directory#symptom
    https://video2.skills-academy.com/en-us/azure/azure-sql/database/logins-create-manage?view=azuresql

    Hope this helps. Do let us know if you any further queries.

    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.