none
How to back multiple master database key DEK on SQL with TDE Enable RRS feed

  • Question

  • Hi,

    I have an SQL instance with multiple data bases. Each of them are TDE encrypted.

    I know that all data bases use the same service master key, SMK, to encrypt tempdb.  I have back up the SMK using:

    BACKUP SERVICE MASTER KEY
    TO FILE = 'tmp-path.key'
    ENCRYPTION BY PASSWORD = 'temp-password';


    Now, how do I back up the distinct database master key, DMK? Each data base use a different one, but the SQL statement doesn't allow to specify which one to back up. Next, is the command I'm running.

    BACKUP MASTER KEY
    TO FILE = 'tmep-path.key'
    ENCRYPTION BY PASSWORD = 'temp-passowrd'

    I though that by:

    use [specific-db];
    GO

    It will back up an specific one, but the command won't run. You need to be on master, to run the command.

    Thanks



    Monday, June 24, 2019 3:20 PM

All replies

  •  

    Hi roncansan,

     

    Do you use the SMK to encrypt all the DMK? The DMK must be open and, therefore, decrypted before it is backed up. If it is encrypted with the service master key, the DMK does not have to be explicitly opened. But if the DMK is encrypted only with a password, it must be explicitly opened.

     

    I suggest you use ssms and tsql combination to back up DMK :

     

    -- Creates a backup of the "AdventureWorks2012" master key. Because this master key is not encrypted by the service master key, a password must be specified when it is opened. 

    USE AdventureWorks2012;  

    GO 

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'sfj5300osdVdgwdfkli7';  

     

    BACKUP MASTER KEY TO FILE = 'c:\temp\exportedmasterkey'  

        ENCRYPTION BY PASSWORD = 'sd092735kjn$&adsg';  

    GO

     

    For more datails, please refer to Back Up a Database Master Key

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, June 25, 2019 3:31 AM