Data Flow Error in Azure Synapse Analytics Workspace - "Managed Service Identity has not been enabled on this server. Details at sink1"

Lokesh 211

Problem Statement - Doing some hands-on with Azure Synapse Analytics (Workspace preview). Added a data flow to extract the data from data lake gen 2 account and transfer it into SQL pool. Get a error saying that managed service Identity is not enabled on this server.

Details - Here is how the source and sink for the data flow looks like
Source Dataset - Delimited Text
Sink Dataset - Azure Synapse Analytics (formerly SQL DW)

Previewing the data works fine both at the source and the sink level (after enabling data flow debug)

However, the pipeline/data flow errors out with the following details

Used managed identity while connecting to the azure data warehouse and looks like it is set up properly on the service

Connecting DWHoptions at the workspace level

I have tested the same pipeline via ADF service and everything runs smoothly there.

Any suggestions.

Thanks in advance

Lokesh

HarithaMaddi-MSFT 10,126 Reputation points

2020-08-05T11:53:58.96+00:00
Hi @Lokesh ,

Welcome to Microsoft Q&A Platform.

Kindly check and let us know if the below user is already created, if not created, please create and let us know if it is working.

Creating user and assign dbowner role with managed identity id of data factory as below in Synapse DW by connecting using Active Directory ID

CREATE USER [firstworkspace20200725] FROM EXTERNAL PROVIDER;

EXEC sp_addrolemember db_owner, [firstworkspace20200725];
Lokesh 211 Reputation points

2020-08-05T14:23:07.4+00:00

Hi Haritha

Thanks for replying.

I am getting the following error while running the script

Principal 'firstworkspace20200725' could not be created. Only connections established with Active Directory accounts can create other Active Directory users.
HarithaMaddi-MSFT 10,126 Reputation points

2020-08-05T16:35:12.907+00:00

Hi @Lokesh ,

Thanks for sharing. Error message says that the Active Directory account is not used to login into Azure Synapse while running the command. Can you please configure Active Directory Admin on Synapse and use it to login and run these queries. Please let us know if it helps!
King, Dan 1 Reputation point

2020-08-14T19:02:38.013+00:00

I am having the same issue! It is pretty frustrating.....
Jaryd 181 Reputation points

2020-08-18T00:29:21.663+00:00

Hello, I am having this same exact issue.

Only happens with Dataflows. Only happens in Synapse Studio.

I was wondering if there is any update? We are just starting to use Synapse Studio.

Pipeline Run ID: 051835c8-6b17-4f4f-9727-f3f216759a0c
HarithaMaddi-MSFT 10,126 Reputation points

2020-08-18T04:51:19.343+00:00

Thanks @King, Dan and @Jaryd for sharing the details. Sorry for the inconvenience because of this issue.

Our product team confirmed that the Managed Identity from Synapse Studio functionality has issues and bug fix is going on. Please check in next month, thanks for your patience!
Oliver Rivett-Carnac 6 Reputation points

2020-10-08T10:27:30.867+00:00
Hi there,

Has the issue with Managed Identity talking to Azure Synapse SQL pool been resolved?
I am trying all the recommended settings for Managed Identities and have created the required User on the SQL database but I get the same error:

Job failed due to reason: at Sink '': shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: Managed Service Identity has not been enabled on this server. Please enable Managed Service Identity and try again.

I have tried everything and it does not seem to work as expected / documented.

Regards

Oliver
Steven Neumersky 6 Reputation points

2021-06-10T19:25:50.917+00:00

Same issue here, and this is preventing us from publicizing Microsoft as a secure enabler of a potentially multi-billion dollar project for which I am doing a POC. This needs to be prioritized.

2 answers

Charl 11 Reputation points

2020-08-20T15:17:29.847+00:00

The managed identity is currently limited within the Synapse Workspace for SQL Pools, it is currently active and the exception will not be returned that it is not enabled however we are still pending the configuration change at a storage level to fully enable it. The functionality should be fully available by the end of this month if all goes well.

The managed identity in this instance is actually used for authentication to storage and allows for a more secure method of authentication as we do not have to be concerned about secrets and key's being shared or which expire. The Identity is a Principal which is created in AD for the server which would then be granted access to the storage. This works as expected for the other services in the workspace.
Please sign in to rate this answer.

2 people found this answer helpful.
charl roux 6 Reputation points

2020-09-08T14:22:18.277+00:00

The deployment has been pushed back for some time. Currently I would expect release sometime in the next few weeks.

Rudolph Harms 26 Reputation points

2020-11-05T10:32:51.927+00:00

Hi, has this been resolved? also struggling with this in synapse.
The recommendations in this articel https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-sql-data-warehouse#managed-identity
did not resolve it

Anonymous

2021-05-09T20:35:33.967+00:00

When is this going to be released @Charl ? We still get this error and yes, it seems at the storage level. See above comment.
Sign in to comment
HarithaMaddi-MSFT 10,126 Reputation points

2020-08-06T08:43:04.287+00:00
Hi @Lokesh ,

Thanks for sharing the details. Below are snaps depicting the setup and login into Synapse DW using Active Directory ID. Documentation related to setting up managed identity from Azure Data Factory in Synapse using the queries mentioned can be found in this link.

Please let us know if this resolves the issue. We would be glad to assist further in case if issue persists.

CREATE USER [firstworkspace20200725] FROM EXTERNAL PROVIDER; EXEC sp_addrolemember db_owner, [firstworkspace20200725];
Please sign in to rate this answer.

1 person found this answer helpful.
Lokesh 211 Reputation points

2020-08-06T12:17:02.977+00:00

Thanks Haritha for listing out the steps, I was able to run the script and was able to grant dbowner role to workspace.

However still getting same error.

Operation on target Dataflow1_short failed: {"StatusCode":"DFExecutorUserError","Message":"at Sink 'sink1': com.microsoft.sqlserver.jdbc.SQLServerException: Managed Service Identity has not been enabled on this server. Please enable Managed Service Identity and try again.","Details":"at Sink 'sink1': com.microsoft.sqlserver.jdbc.SQLServerException: Managed Service Identity has not been enabled on this server. Please enable Managed Service Identity and try again."}

Regards
Lokesh

Lokesh 211 Reputation points

2020-08-06T12:17:16.917+00:00

Few questions please -

Why do we need a managed identity here since the pipeline and sink are under same service/resource essentially. We have another pipeline (same workspace) which uses copy activity and same sink and runs fine without any errors.

The data flow if run from a seperate ADF service (for same sink), does nt give any error (used SQL authentication at the sink).

Hope it makes sense. Please ask in case of any quesries.

Regards
Lokesh

HarithaMaddi-MSFT 10,126 Reputation points

2020-08-06T14:05:45.777+00:00

Hi @Lokesh ,

Thanks for sharing the result. I am sorry that you are experiencing the issue. I reproduced and after running the contained user in Synapse, this worked for me. If not already tried, can you please close data factory and reopen to test the same. If the issue still persists, kindly share the pipeline run id for us to investigate further with the internal team.

As per my understanding, Managed Identity is used primarily to manage authenticating to Azure resources without passing credentials using integration with Azure Active Directory as securing the credentials while passing to Azure resources is challenging. In case of SQL Authentication, we need to provide credentials (username and password) to be able to connect to the resource. Hope this clarifies the query.

Lokesh 211 Reputation points

2020-08-07T08:08:21.577+00:00

Sorry Haritha, I just wanted to make sure that we are on the same page. I encounter the problem only while running the pipeline from azure synapse analytics or azure synapse studio and it seems the issue persists only when you use data flow.

On running it via ADF I don't get any errors.

Run id - 6406def8-00ec-4090-9e14-2e3cef3571a7

Let me know if a zoom call is feasible, we can discuss the issue on the call. My id is er.lokeshsharma08@Stuff .com

HarithaMaddi-MSFT 10,126 Reputation points

2020-08-10T14:51:19.467+00:00

Hi @Lokesh ,

As per our discussion, I was not able to reproduce the issue from Azure Synapse Studio as well. I have reached out to Product team for further inputs. I will update you once we hear back with more details on the same.

Thanks for your patience.

HarithaMaddi-MSFT 10,126 Reputation points

2020-08-11T07:36:47.567+00:00

Hi @Lokesh ,

This issue looks strange. For a deeper investigation and immediate assistance on this issue, if you have a support plan you may file a support ticket, else could you send an email to AzCommunity [at] microsoft [.] com with the below details, I will enable a one-time free support request for your subscription.

Subject of the mail: <Attn - Haritha : Data Flow Error in Azure Synapse Analytics Workspace - "Managed Service Identity has not been enabled on this server. Details at sink1">

Thread URL:

Subscription ID:

Looking forward for your mail to assist you further. Thanks for your patience.

HarithaMaddi-MSFT 10,126 Reputation points

2020-08-11T08:31:23.857+00:00

Thank you @Lokesh for reaching out.

We have enabled for one-time free technical support. I hope you have received one on one support to work towards a resolution on this matter.

Once the issue is sorted out with the support, please do share the resolution, which might be beneficial to other community members reading this thread.

Anonymous

2021-05-05T21:28:54.903+00:00

9 months later, this doesn't appear to be fixed? Still getting this error having followed all the instructions on the Microsoft site here: https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-sql-data-warehouse#managed-identity

Managed Service Identity has not been enabled on this server. Please enable Managed Service Identity and try again.","failureType":"UserError","target":"LoadIdentity","errorCode":"DFExecutorUserError"

Have tried restarting the service, closing and reopening studio, pausing and unpausing the DW instance, nothing seems to work.
Sign in to comment