Please create a master key in the database or open the master key in the session before performing this operation

@Ade Darby

The error you are encountering indicates that the database master key needed for cryptographic operations is either missing or not accessible in the session when running your query against the Azure Synapse Lake database. This master key is essential for encrypting or decrypting sensitive data.

Steps that help you to resolve the issue:

Check if the master key exists - Run the following SQL query in Synapse:

SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##'; 

If no results are returned, create the master key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourSecurePassword123!'; 

Ensure the master key is opened in the session - If the master key exists, modify your query to include:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourSecurePassword123!'; 

Verify your database scoped credentials - If your query accesses external tables, check if the required credentials exist:

SELECT * FROM sys.database_scoped_credentials; 

If missing, recreate them using:

CREATE DATABASE SCOPED CREDENTIAL MyCredential WITH IDENTITY = 'Managed Identity';

Ensure Synapse Managed Identity has access - If your query involves Azure Data Lake Storage, ensure your Synapse workspace managed identity has the Storage Blob Data Reader role on the storage account.

Security Note - Be cautious with the password you use for the master key. It should be stored securely and not hardcoded in scripts or applications.

Once you have made sure the master key is properly created and opened, your operation should proceed without the error. If you continue to have issues, feel free to reach out for further assistance.

Please refer to the similar thread below which will help you to get some useful insights.

• Please create a master key in the database or open the master key in the session before performing this operation

Additionally, please refer the Microsoft documentation.

• MSSQLSERVER_15581

I hope this information helps. Please do let us know if you have 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.