How do I get a data flow to work to add lines to a synapse dedicated sql pool?

Question

How do I get a data flow to work to add lines to a synapse dedicated sql pool?

BenH 1

I am attempting to run some ETL work in data factory utilizing the data flow. When I test the data flow everything works fine. When I add the data flow to my pipeline in data factory I consistently get an error message:

Job failed due to reason: com.microsoft.dataflow.broker.InvalidOperationException: Storage with user assigned managed identity authentication as staging is not supported, storage type is:'AzureBlobStorage'. I've attempted to follow this page:
https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-blob-storage?tabs=data-factory#Supported%20capabilities But I can't seem to get the data flow to work to add rows to my table. Can anyone help me understand exactly what I'm supposed to do here to get the data flow to work? Thanks! Ben

PRADEEPCHEEKATLA 90,641 Reputation points Moderator

2024-02-26T07:15:19.88+00:00

@BenH - Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

Your answer

PRADEEPCHEEKATLA 90,641 Reputation points Moderator

2024-02-26T07:15:19.88+00:00

@BenH - Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

PRADEEPCHEEKATLA 90,641 Moderator

@BenH - Thanks for the question and using MS Q&A platform.

It seems like you are encountering an error message while running a data flow in Azure Data Factory. The error message indicates that the storage with user assigned managed identity authentication as staging is not supported.

When using Azure Blob linked service in data flow, managed identity or service principal authentication isn't supported when account kind is empty or "Storage". Specify the proper account kind, choose a different authentication, or upgrade your storage account to general purpose v2.

User's image

To resolve this issue, you can use a different authentication to create an Azure Data Lake Storage Gen2 or Azure Blob Storage linked service, then use it as staging in mapping data flows.

Here are the steps to create a new linked service with a different authentication:

In the Azure Data Factory UI, click on the Author & Monitor hub.
Click on the Connections tab and then click on New.
Select the type of storage you want to use as your linked service.
Follow the prompts to create the linked service with the appropriate authentication method.
Once you have created the linked service, you can use it as the staging location for your data flow.

After creating the linked service, you can update your data flow to use the new linked service as the staging location.

For more details, refer to Copy and transform data in Azure Blob Storage by using Azure Data Factory or Azure Synapse Analytics

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

BenH 1 Reputation point

2024-02-19T12:13:37.15+00:00

Thank you for the attempt here to answer my question. I have attempted to change the linked service but that just leads to this error:
Please create a master key in the database or open the master key in the session before performing this operation.
BenH 1 Reputation point

2024-02-19T12:16:47.9133333+00:00

Thank you for the help with this issue. I have attempted to utilize the app registration in entra and add that to the linked service. Once I do that I get another error message:
Please create a master key in the database or open the master key in the session before performing this operation.
PRADEEPCHEEKATLA 90,641 Reputation points Moderator

2024-02-20T04:04:09.01+00:00
@BenH - It seems like you are encountering a new error message after updating the linked service with the app registration in Azure AD. The error message indicates that you need to create a master key in the database or open the master key in the session before performing the operation.

If your query fails with the error message Please create a master key in the database or open the master key in the session before performing this operation., it means that your user database has no access to a master key at the moment. Most likely, you created a new user database and haven't created a master key yet.

To resolve this problem, create a master key with the following query:

CREATE MASTER KEY [ ENCRYPTION BY PASSWORD ='strongpasswordhere' ];

For more details, refer to https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/resources-self-help-sql-on-demand?tabs=x80070002#create-a-master-key-in-the-database-or-open-the-master-key-in-the-session-before-performing-this-operation.

Hope this helps. Do let us know if you any further queries.

Share via

How do I get a data flow to work to add lines to a synapse dedicated sql pool?

1 answer

Your answer