Read Synapse Data - Azure Dataflow

Question

Hi, I'm trying to read data from a synapse table but it's throwing me an error. I've used this method before but never faced such issue.

Answer 1

Hello 39861377,

The error message indicates that the database master key is missing on the source SQL server You need to create a database master key in the database. The key is encrypted using a password.

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

You can verify the presence of database master key with the following command.

SELECT * FROM sys.symmetric_keys WHERE name LIKE '%DatabaseMasterKey%';

if the database is encrypted using TDE, you may need to enable encryption on the source SQL server

ALTER DATABASE <database_name> SET ENCRYPTION ON;

To re-create the database master key and all the keys it protects, use the regenerate option Below syntax;

ALTER MASTER KEY <alter_option>

<alter_option> ::=
    <regenerate_option> | <encryption_option>

<regenerate_option> ::=
    [ FORCE ] REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

<encryption_option> ::=
    ADD ENCRYPTION BY { SERVICE MASTER KEY | PASSWORD = 'password' }
    |
    DROP ENCRYPTION BY { PASSWORD = 'password' }

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-service-master-key-transact-sql?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/t-sql/statements/create-master-key-transact-sql?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-and-database-encryption-keys-database-engine?view=sql-server-ver16 A similar thread has been discussed below: https://stackoverflow.com/questions/46373723/please-create-a-master-key-in-the-database-or-open-the-master-key-in-the-session Please review this and let me know if you have any further questions. I hope this helps in resolving the issue