'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:
- 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 |
|---|

