question

Loky-5050 avatar image
Loky-5050 asked ·

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

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

15496-3.png

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

Connecting DWHoptions at the workspace level
15483-2.png

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



Any suggestions.



Thanks in advance

Lokesh




azure-synapse-analytics
3.png (29.9 KiB)
1.png (66.7 KiB)
2.png (78.8 KiB)
7 comments
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Loky-5050,

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];

0 Votes 0 · ·
Loky-5050 avatar image Loky-5050 HarithaMaddi-MSFT ·

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.


0 Votes 0 · ·

Hi @Loky-5050,

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!

0 Votes 0 · ·

I am having the same issue! It is pretty frustrating.....

0 Votes 0 · ·

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

0 Votes 0 · ·

Thanks @KingDan-0000 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!

0 Votes 0 · ·

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


0 Votes 0 · ·
HarithaMaddi-MSFT avatar image
HarithaMaddi-MSFT answered ·

Hi @Loky-5050,

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];

16076-activedirectoryadminsetup.png16092-sqlactivedirectorylogin.png




7 comments Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

1 Vote 1 · ·

Few questions please -
1. 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.
2. 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

0 Votes 0 · ·

Hi @Loky-5050,

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.

0 Votes 0 · ·
Show more comments
charlroux avatar image
charlroux answered ·

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.

2 comments Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

1 Vote 1 · ·

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

0 Votes 0 · ·