Synapse Dataflow when enable SQLDW staging, pipeline raise Master Key error

Question

Synapse Dataflow when enable SQLDW staging, pipeline raise Master Key error

Xia 0

Hello,

I'm creating pipeline with data flow in Azure Synapse with staging enabled on SQLDW source. When I debug and try to preview data of the source, I have error: Blob storage staging properties should be specified. But I did configured Staging linked service and storage folder in debug settings. I set the same staging settings in Copy Activity, test connection succeed and it debugs well with success. When I debug the pipeline with this dataflow, the error message on dataflow is: Job failed due to reason: at Source 'SourceName': Please create a master key in the database or open the master key in the session before performing this operation. So it's a problem from Blob linked service or database ? As I don't need any master key for encryption in my SQL database. What should I do ?

Amira Bedhiafi 33,071 Reputation points Volunteer Moderator

2023-08-22T10:20:08.57+00:00
Since you've already configured the Staging linked service and storage folder, this error might be related to a permissions issue or a misconfiguration. Here's what you can do:

Check that the necessary permissions (read, write, etc.) are granted for the Blob Storage.

Ensure that the Blob Storage account key or managed identity used in the linked service is correctly configured.

Validate that the path provided in the staging settings is correct and accessible.

The second error message seems related to the SQL database, where it asks to create or open a master key. This doesn't necessarily relate to encryption; it might be needed for the credentials to access the external data source (Blob Storage in this case). Here's what you might need to do:

If you haven't already created a master key in the SQL database, you'll need to execute the CREATE MASTER KEY command in the SQL database where you're attempting to stage the data.

If the master key already exists but is not open for the session, you may need to execute the OPEN MASTER KEY command.

If neither of the above steps applies, and you're sure that you don't need a master key, you may want to review the configuration of the pipeline and the linked services to make sure that the error isn't stemming from a different issue.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-08-22T21:50:27.5966667+00:00

Hello Xia,

Welcome to the Microsoft Q&A forum.

Sorry for the inconvenience.

Multiple customers are facing this issue. Could you please let us know your synapse region?

Meanwhile, I am checking with the Product team on this issue and will get back to you as soon as I hear from them.

I appreciate your patience.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-08-22T23:31:37.1366667+00:00

Hello Xia,

The issue is mitigated. Can you please try to pause/resume and then retry again?

Please let me know if you still see the error.

1 answer

Your answer

Amira Bedhiafi 33,071 Reputation points Volunteer Moderator

2023-08-22T10:20:08.57+00:00

Since you've already configured the Staging linked service and storage folder, this error might be related to a permissions issue or a misconfiguration. Here's what you can do:

Check that the necessary permissions (read, write, etc.) are granted for the Blob Storage.

Ensure that the Blob Storage account key or managed identity used in the linked service is correctly configured.

Validate that the path provided in the staging settings is correct and accessible.

The second error message seems related to the SQL database, where it asks to create or open a master key. This doesn't necessarily relate to encryption; it might be needed for the credentials to access the external data source (Blob Storage in this case). Here's what you might need to do:

If you haven't already created a master key in the SQL database, you'll need to execute the CREATE MASTER KEY command in the SQL database where you're attempting to stage the data.

If the master key already exists but is not open for the session, you may need to execute the OPEN MASTER KEY command.

If neither of the above steps applies, and you're sure that you don't need a master key, you may want to review the configuration of the pipeline and the linked services to make sure that the error isn't stemming from a different issue.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-08-22T21:50:27.5966667+00:00

Hello Xia,

Welcome to the Microsoft Q&A forum.

Sorry for the inconvenience.

Multiple customers are facing this issue. Could you please let us know your synapse region?

Meanwhile, I am checking with the Product team on this issue and will get back to you as soon as I hear from them.

I appreciate your patience.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-08-22T23:31:37.1366667+00:00

Hello Xia,

The issue is mitigated. Can you please try to pause/resume and then retry again?

Please let me know if you still see the error.

Answer 1

Bhargava-MSFT 31,261 Microsoft Employee Moderator

Hello Xia,

Root cause: This was due to an outage at the Azure data center.

Mitigation: A fix has been applied to restore the functionality. End users must pause/resume the dedicated SQL pool and try again.

If this answers your question, please consider accepting the answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions.

BenH 1 Reputation point

2024-02-21T18:59:05.0833333+00:00

Hi BharagavaGunnam, I'm experiencing a similar issue after pausing and restarting multiple times. I have a simple data flow that is attempting to update a table in synapse dedicated sql. I was trying to use a managed identity but kept getting errors that I could not use the managed identity. I swapped to an app authentication and now get an error that I need a master key. Is there anything I can do to resolve this? I update tables all the time using pipelines but the data flow will not update the tables.

Share via

Synapse Dataflow when enable SQLDW staging, pipeline raise Master Key error

1 answer

Your answer