locked
why SQL 2014 database encrypted backups required different certificate & master keys different on servers in one network? RRS feed

  • Question

  • Hi All,

    I wanted to know why SQL 2014 database encrypted backups required different certificate & master keys different on each servers .cant we keep single them in one group server.

    Let me explain you in detailed way ,in my environment i have few server server names as-

    1. ABC

    2.ABC/SQL1

    3.XYZ

    4.XYZ/SQL2

    if i want to set up backup encryption for those server i have to create master key & certificate separately with same name and password on default as well as named instance.

    my question here is -

    1.even though we have to created master key & certificate separately on each server with same name & passwords.I am not able restore ABC server DB backups on another server BACKUPSTEST by creating  XYZ instance master key and certificate.?But if i use ABC db backups ,master key & certificate to restore on BACKUPTEST server it's working fine.

    please help me to understand the mechanism of encryption difference on server to server even though same password and certificates?

    Thanks in advance.


    mastanvali shaik

    Thursday, November 9, 2017 9:46 PM

All replies

  • Hi mastanvali shaik,

    >>I wanted to know why SQL 2014 database encrypted backups required different certificate & master keys different on each servers .cant we keep single them in one group server.

    I’m not sure what you mean here, backup encryption requires DMK and a server certificate that’s stored in the instance’s master database. And what do you mean by ‘one group server’??

    >>even though we have to created master key & certificate separately on each server with same name & passwords.I am not able restore ABC server DB backups on another server BACKUPSTEST by creating  XYZ instance master key and certificate.?

    Even if you have created DMK/certificate under same name/password, they are actually different as you have different thumbprint every time you create it. That’s exactly why you cannot restore a backup to another instance.

    To make the backup file transferrable, the steps are actually easier than you thought:
    1. DMK is not that relevant here, but you need to have one to protect the private key of the certificate. You don’t need to keep the same DMK across all instances, but it would be nice if you can take regular DMK backup and put it in a secure place.
    2. You do need create the exact same certificate on all instances. You need to create it on the first instance, then using backup/restore method to send it to other instances. Here’s a sample script and inline comments:
     
    ----Say we have 2 instances(INSTANCE1/2)
    ----On INSTANCE1, we create DMK/certificate, then take a encrypted backup
    
    ----Create DMK on INSTANCE1
    USE master;
    GO
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password01!';
    GO
    
    --Create Certificate
    USE master;
    GO
    CREATE CERTIFICATE SVRCert
    WITH SUBJECT = 'TESTBAK';
    GO
    
    ----Backup Certificate and private key
    
    BACKUP CERTIFICATE SVRCert TO FILE = 'C:\TESTBAK\SVRCert'
    WITH PRIVATE KEY
    (
        FILE = 'C:\TESTBAK\SVRCertPvk',
        ENCRYPTION BY PASSWORD = 'Password01!'
    );
    GO
    
    ----Take a encryped backup, and transfer certificate/private key to INSTANCE2
    
    /*
    	Your backup code here.
    */
    
    ----Switch to INSTANCE2
    ----Create DMK on INSTANCE2
    USE master;
    GO
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password01!';
    GO
    
    ----Restore Certificate/private key to INSTANCE2
    USE master;
    GO
    CREATE CERTIFICATE SVRCert
    FROM FILE = 'C:\TESTBAK\SVRCert'
    WITH PRIVATE KEY
    (
        FILE = 'C:\TESTBAK\SVRCertPvk',
        DECRYPTION BY PASSWORD = 'Password01!'
    );
    GO
    
    ----Restore backup file from INSTANCE1 to INSTANCE2
    /*
    	Your code here.
    */
    
    ----All done.




    If you have any other questions, please let me know.

    Regards,
    Lin

    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.


    Friday, November 10, 2017 5:52 AM
  • Hi Lin,

    Thanks for the response.

    I will short my question - why we can't keep unique master key & certificate on multiple servers ? while restoring backups we have to use master key & certificates associated to that server only .can we use different server key & certificate to restore different server db backups?

    let me know if you need anything on this.

    provide me if you have any Microsoft articles related to this .


    mastanvali shaik

    Friday, November 10, 2017 2:10 PM
  • Hi Lin,

    Thanks for the response.

    I will short my question - why we can't keep unique master key & certificate on multiple servers ? while restoring backups we have to use master key & certificates associated to that server only .can we use different server key & certificate to restore different server db backups?

    let me know if you need anything on this.

    provide me if you have any Microsoft articles related to this .


    mastanvali shaik

    I'm not sure i understand your question.

    If i understand this correctly, your original question was about how to restore encrypted backup that is taken on instance A to instance B, right? If so, all you need is to recreate a identical certificate on instance B, and encrypt the private key using DMK, that's all.

    If you have any other questions, please let me know.

    Regards,
    Lin




    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.

    Friday, November 10, 2017 3:10 PM
  • Thanks Lin.

    I got my answer from below explanation.

    Even if you have created DMK/certificate under same name/password, they are actually different as you have different thumbprint every time you create it. That’s exactly why you cannot restore a backup to another instance.


    mastanvali shaik

    Friday, November 10, 2017 3:41 PM
  • Hi mastanvali shaik,

    Glad to hear that, if it does answer your question, please mark the corresponding reply as answer as it would benefit other community members when they are reading this thread.

    If you have any other questions, please let me know.

    Regards,
    Lin

    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.

    Wednesday, November 22, 2017 6:18 AM