This article is contributed. See the original author and article here.
Again quick post about error and mitigation.
So as it is publicly documented ( today, Oct – 2020) managed identities are not currently supported on the SQL Pools under Synapse workspace. I mentioned the date, because this may change. But so far that is the current scenario.
So suppose you are using the ADF pipeline ( in or out Synapse workspace) but you are connecting to a SQL Pool under synapse workspace.
You may hit this issue:
Managed Service Identity has not been enabled on this server.
Or full error message:
This limitation is documented under the following links:
Are there any limitations with COPY using Synapse workspaces (preview)?
Authenticating using Managed Identity (MSI) is not supported with the COPY statement or PolyBase (including when used in pipelines). You may run into a similiar error message:
com.microsoft.sqlserver.jdbc.SQLServerException: Managed Service Identity has not been enabled on this server. Please enable Managed Service Identity and try again.
So here is your scenario: You configured SQL Server user to connect to the database trying to avoid the managed identity problem but still if you enable sink stagging it hits this problem. If you do not enable the sinking stage as you have a large number of rows to load it will take a long time to run because the insert will be executed row by row.
Here is the reason:
Staged copy by using PolyBase: To use this feature, create an Azure Blob Storage linked service or Azure Data Lake Storage Gen2 linked service with account key or managed identity authentication that refers to the Azure storage account as the interim storage.
- Change the authentication method of the staging store linked service to key or service principal auth. The point is avoiding the managed identities but still enable the sinking stage.
Step by Step:
This the storage account configuration with the account key. Further, I will enable the Sink stage using this storage account which is also the source of my data.
And………. it Worked:
Failure configuration :
Here the storage account is using managed identity authentication.
Once I try to run it failed as it follows:
Thanks to the case collaboration of ADF team Yassine Mzoughi, Darius Ciubotariu and Synapse team Jackie Huang and Olga Guzheva.
That is it!
Liliam C Leme
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.