Cannot Add Managed Identity to Synapse Pool

Question

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:

2. 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?

Answer 1

Update 12/30/2020: This appears to be resolved now that Synapse is GA. Polybase and COPY INTO now work in a Synapse workspace SQL pool with a securely firewalled ADLS Gen 2 storage account. The updated instructions are published here.

There is one note at the bottom of the COPY INTO documentation with may be required on older Synapse workspaces for Polybase or COPY to work with a Managed Service Identity in a SQL Pool in a Synapse workspace.

If you have a Synapse workspace that was created prior to 12/07/2020, you may run into a similar error message when authenticating using Managed Identity: com.microsoft.sqlserver.jdbc.SQLServerException: Managed Service Identity has not been enabled on this server. Please enable Managed Service Identity and try again. Follow these steps to work around this issue by re-registering the workspace's managed identity:

1. Go to your Synapse workspace in the Azure portal
2. Go to the Managed identities blade
3. If the “Allow Pipelines” option is already checked, you must uncheck this setting and save
4. Check the "Allow Pipelines" option and save

As a side note I believe this same approach works for Serverless if you use a MSI. But if you want to use a pass through AAD auth then see this.

Old information as of August 2020 that’s now outdated: I had the same question and opened support case 120073024005140. The answer I got was that this Polybase or COPY INTO with MSI scenario isn’t yet working in Synapse workspaces but it is coming. For now you have to use other types of authentication like storage account key authentication and you have to leave the storage account firewall open. Or alternately your could use an older “Azure Synapse Analytics (formerly SQL DW)” SQL pool (no Synapse workspace and no Synapse studio) where this feature is working.