none
cannot create master key/certificate in master DB for TDE RRS feed

  • Question

  • Hello

    Based on the guidance at https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/create-a-database-master-key?view=sql-server-ver15, it looks it is needed to create the master key and certificate in master DB for TDE enablement.

    However, it looks I cannot use the the SQL server login account to create the master key and certificate in master DB (see enclosed command output), and I also cannot use the SQL server login account to create another master DB access login account with "CONTROL" privilege.

    There are three questions:

    1. If the "create the master key and certificate in master DB" is necessary for TDE?

    2. since I can create the master key and certificate in my new created DB, does it mean the TDE can be deployed per DB specific DEK and provider (such as different DB has its specific key for data encryption and using different certification for encryption key protector with different protector key management methods, local vs BYOK)?

    3. If the "create the master key and certificate in master DB" is necessary for TDE, then how to gaint the permission to do it? 

    Thank you very much. 

    master> CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
    Time: 0.253s
    Msg 15247, Level 16, State 1, Line 1
    User does not have permission to perform this action.
    master> CREATE CERTIFICATE ServerCert WITH SUBJECT = 'DEK Certificate';
    Time: 0.252s
    Msg 15247, Level 16, State 1, Line 1
    User does not have permission to perform this action.

    Friday, November 22, 2019 4:08 PM

Answers

All replies

  • Hi luck4zhang,

    Are you trying to enable TDE in an Azure sql datanase? Because by default microsoft will enable TDE on Azure SQL Databases. If you want to use your own key, you will have to buy a device that will create a key or you can use azure key vault to create the key and use TDE with Bring your own key option.

    regards!

    Friday, November 22, 2019 4:38 PM
  • Firstly, thank you very much for the response. 

    Based on my understand, please correct me if wrong, the BYOK is "bring" the DEK protector from external source (such as Azure vault) not the actual "data encryption key".

    I am not trying to use BYOK but just want to know:

    if create the master key and certificate in master DB is necessary,

    - if yes, then how to deal with the challenges from the master permission

    - if not, then does it mean different database can have its dedicated DEK and DEK protector

    Thank you very much.

     
    Friday, November 22, 2019 5:17 PM
  • Hi luck4zhang,

    Based on my understand, please correct me if wrong, the BYOK is "bring" the DEK protector from external source (such as Azure vault) not the actual "data encryption key". -> Yes, you are right

    if create the master key and certificate in master DB is necessary, -> No, it is not neccessary since TDE is already enabled by default and the certificate and master key is protected by Microsoft.

    - if not, then does it mean different database can have its dedicated DEK and DEK protector -> Yeap, TDE is enabled by default. Here is you have link https://docs.microsoft.com/en-us/azure/sql-database/transparent-data-encryption-azure-sql?tabs=azure-portal (you will have to enable TDE on very old databases).

    Hope this helps!

    Friday, November 22, 2019 5:27 PM
  • Thank you very much, Gonzalo.
    • Marked as answer by luck4zhang Saturday, November 23, 2019 4:56 AM
    Friday, November 22, 2019 5:40 PM