'Cannot Add Managed Identity to Synapse Pool

I am running an Azure Synapse workflow through the Synapse studio and running into this error:

{
    "errorCode": "2200",
    "message": "ErrorCode=FailedDbOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Please make sure SQL DW has access to ADLS Gen2 account,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Managed Service Identity has not been enabled on this server. Please enable Managed Service Identity and try again.,Source=.Net SqlClient Data Provider,SqlErrorNumber=105096,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=105096,State=1,Message=Managed Service Identity has not been enabled on this server. Please enable Managed Service Identity and try again.,},],'",
    "failureType": "UserError",
    "target": "Copy data1",
    "details": []
}

If I go into Azure Powershell and inspect the pool, I see that this is substantiated by the null entry in Identity:

ResourceGroupName        : workspacemanagedrg-c6475066-bbe3-4c02-866c-7556d5e92e0b
ServerName               : <mydw>
Location                 : eastus2
SqlAdministratorLogin    : <myadmin>
SqlAdministratorPassword : <mypw>
ServerVersion            : 12.0
Tags                     : {}
Identity                 :
FullyQualifiedDomainName : <mydw>.database.windows.net

There are two things that are peculiar about this:

  1. My Synapse workspace has a managed identity associated with it already: enter image description here
  1. I'm getting a permission denied when trying to run the Powershell command as documented in this question which says

Set-AzSqlServer: The client '[email protected]' with object id 'guid' has permission to perform action 'Microsoft.Sql/servers/write' on scope '/subscriptions/mysubscription/resourceGroups/myrg/providers/Microsoft.Sql/servers/mydw'; however, the access is denied because of the deny assignment with name 'c6475066-bbe2-4c03-866c-7556d5e92e9b' and Id 'c6475066bbe24c03866c7556d5e92e9b' at scope '/subscriptions/mysubscription/resourceGroups/myrg'.

I have verified that this Managed Identity does have access to my data source (ADLS Gen2) and when I test the connections in the studio, they all work.

How do I assign the managed identity from my Synapse workspace to my sql pool that I've created?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source