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.
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.
And.......... it Worked:
Failure configuration :
Here the storage account is using managed identity authentication.
Once I try to run it failed as it follows:
Update here: 10.12.2020: COPY INTO (Transact-SQL) - (Azure Synapse Analytics) - SQL Server | Microsoft Docs
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:
- Go to your Synapse workspace in the Azure portal
- Go to the Managed identities blade
- If the “Allow Pipelines” option is already checked, you must uncheck this setting and save
- Check the "Allow Pipelines" option and save
That is it!
Liliam C Leme
UK engineer.