none
TDE enabled Database RRS feed

  • Question

  • Hi,

      We have enabled TDE in our environment and had taken backup of Master Key, Certificate,Private Key.  We have tested the restoration  of the TDE enabled DB on Dev env by restoring Master Key and Private Key successfully. I have couple of queries regarding Master Key, please help me in understandign the following..

    1) When we enabled TDE, what is the use of Master Key backup as we are creating a new Master Key on other instance and able to restore the certificate and private Key along with encrypted user Database.

    2) For changing the password for Master Key using the below command(googled), please let me know if we have any other command for changing the password other than regenerating.

    ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'new_password';

    3) IF we change the Master Key password on source database with above command should we create a new backup for certificate and private Key? or old backup would suffice. Ideally wanted to know what is the best practice to be followed if we are changing the Master Key Password to new one.

    Regards,

    Raj



    • Edited by Raje14 Tuesday, July 9, 2019 1:39 AM
    Tuesday, July 9, 2019 1:33 AM

Answers

  • 1./*The Database Master Key is the root of the database encryption hierarchy in SQL Server. It potentially can be required to access all your encrypted data*/

    2. Not I am aware of

    3. Sure , you must create a new backup the new key

    https://www.sqlserverscience.com/security/data-security/filesystem-security-tde-keys-certificates/?utm_medium=referral&utm_source=dba.stackexchange.com&utm_campaign=233040


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Tuesday, July 9, 2019 5:15 AM
    Answerer


  • 1) When we enabled TDE, what is the use of Master Key backup as we are creating a new Master Key on other instance and able to restore the certificate and private Key along with encrypted user Database.

    There is no need to backup master key it hardly has any role in TDE. The two main components are Database Encryption key(DEK) which encrypts database and the certificate which is used to protect DEK. These 2 must be backed up and restored on destination


    2) For changing the password for Master Key using the below command(googled), please let me know if we have any other command for changing the password other than regenerating.

    That is not changing the password but regenerating the key with new password.  See BOL document


    3) IF we change the Master Key password on source database with above command should we create a new backup for certificate and private Key? or old backup would suffice.

    You do not need to change master key password there is hardly any benefit with that. Like I stated above the 2 main things are DEK and certificate. Master key can be created new each time while restoring.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP



    Tuesday, July 9, 2019 7:27 AM
    Moderator

All replies

  • 1./*The Database Master Key is the root of the database encryption hierarchy in SQL Server. It potentially can be required to access all your encrypted data*/

    2. Not I am aware of

    3. Sure , you must create a new backup the new key

    https://www.sqlserverscience.com/security/data-security/filesystem-security-tde-keys-certificates/?utm_medium=referral&utm_source=dba.stackexchange.com&utm_campaign=233040


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Tuesday, July 9, 2019 5:15 AM
    Answerer


  • 1) When we enabled TDE, what is the use of Master Key backup as we are creating a new Master Key on other instance and able to restore the certificate and private Key along with encrypted user Database.

    There is no need to backup master key it hardly has any role in TDE. The two main components are Database Encryption key(DEK) which encrypts database and the certificate which is used to protect DEK. These 2 must be backed up and restored on destination


    2) For changing the password for Master Key using the below command(googled), please let me know if we have any other command for changing the password other than regenerating.

    That is not changing the password but regenerating the key with new password.  See BOL document


    3) IF we change the Master Key password on source database with above command should we create a new backup for certificate and private Key? or old backup would suffice.

    You do not need to change master key password there is hardly any benefit with that. Like I stated above the 2 main things are DEK and certificate. Master key can be created new each time while restoring.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP



    Tuesday, July 9, 2019 7:27 AM
    Moderator
  • Thanks Uri Dimant..
    Tuesday, July 9, 2019 7:36 AM
  • Hi

    >>>No not required.

    Do you mean that if we use regenerating we do not require a backup the key?

    if I have created a new pass then I must take a new backup?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, July 9, 2019 7:38 AM
    Answerer
  • Uri I have updated my original answer I do not think it servers any benefit to regenerated master key unless you are building new machine

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, July 9, 2019 7:41 AM
    Moderator
  • Hello Shashank,

          Thanks for clarifications..  Could you please clarify the below two highlighted queries. 

    There is no need to backup master key it hardly has any role in TDE. The tow main components are Database Encryption key(DEK) which encrypts database and the certificate which is used to protect DEK.

    Even Database Encryption Key is not needed while restoring the backups on new machine.. I assume it enables encryption on user database.. am I correct?


    2) For changing the password for Master Key using the below command(googled), please let me know if we have any other command for changing the password other than regenerating.

    ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'new_password';

    That is not changing the password but regenerating the key with new password. 

    Any way to change the password rather than regenerating as we have to change the passwords once in an year and worried about the impact.

     Regards,

    Raj

    Tuesday, July 9, 2019 7:44 AM
  • Hi

    >>>No not required.

    Do you mean that if we use regenerating we do not require a backup the key?

    if I have created a new pass then I must take a new backup?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    I had tested by regenerating new password for master key and restored the database backups on dev env where earlier we had created old master key and password and it worked.. hence got doubt why is the backup of Master Key is needed.

    Even Certificate, PrivateKey backups are required only once I hope as after changing the master key password without restoring new backup of cert in Dev env am able to restore user backups..

    Tuesday, July 9, 2019 7:49 AM

  • Even Database Encryption Key is not needed while restoring the backups on new machine.. I assume it enables encryption on user database.. am I correct?

    Yes only certificate and private key is needed. When you restore TDE enabled backup on other database you can create new master key with password you like. See This link for steps

    USE master
    GO
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'D1ffPa$$w0rd'


    Any way to change the password rather than regenerating as we have to change the passwords once in an year and worried about the impact.

    I need to try this but I dont think so you can change password without regenerating because if you change password things need to be decrypted and encrypted back



    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, July 9, 2019 7:56 AM
    Moderator