azure synapse new Link connection error: Failed to enable Synapse Link on the source due to 'The database master key is missing.'.

sekhemrekhutawysobekhotep 41 Reputation points
2023-03-20T16:40:18.0666667+00:00

I am creating a new azure synapse 'Link connection' ... after defining everything including source SQL server ( which lives on NON azure ) and destination Dedicated SQL pool I hit the Start button and get error

Failed to enable Synapse Link on the source due to 'The database master key is missing.'.

This is after it show a successful Test connection to the source SQL linked service

Anyone know the cause ?

After I got this error I ran following in the source SQL server

create master key encryption by password = 'foobar'

and reran the Start yet get same error

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,425 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. BhargavaGunnam-MSFT 26,496 Reputation points Microsoft Employee
    2023-03-28T15:26:24.7166667+00:00

    Hello sekhemrekhutawysobekhotep,

    Welcome to the MS Q&A platform.

    Please correct me if my understanding is wrong. You are trying to create an Azure synapse for SQL and getting the error.

    The error message indicates that the database master key is missing on the source SQL server. The Synapse Link service requires access to these keys to establishing a secure connection to the source SQL server.

    Please verify that the database master key was created successfully by running the following command.

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

    if the database is encrypted using TDE, you may need to enable Synapse Link on the source SQL server with the TDE option.

    ALTER DATABASE <database_name> SET ENCRYPTION ON;

    Then please try enabling Synapse Link again

    0 comments