ADF to Synapse Pool: Please enable Managed Service Identity and try again

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

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:

Sink_pool.png

 

 

This limitation is documented under the following links:

 https://docs.microsoft.com/en-us/answers/questions/58750/data-flow-error-in-azure-synapse-analytics-workspa.html

 

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.

https://docs.microsoft.com/en-us/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-latest

 

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.

https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-sql-data-warehouse

 

 

Workaround:

 

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

 

Success Scenario

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.

Account_key_storage.png

 

enablestage_storage.png

 

And.......... it Worked:

suceed.png

Failure configuration :

 

Here the storage account is using managed identity authentication.

storage_account_managed.png

Once I try to run it failed as it follows:

failure_adf.png

 

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

UK engineer.

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.