locked
SQL Server TDE database refresh RRS feed

  • Question

  • Hello All,

    I need help with restoring a TDE enabled database from Prod server to Dev server.

    both source and destination servers are enabled with TDE already but has different certificates. I can't drop the certificate on Dev server as there are other databases enabled with TDE, not sure what options I have now to refresh Prod DB to DEV.

    I kindly request to give any options if you have in your mind.

    Thanks,

    Shyam.

    Monday, January 4, 2016 10:00 PM

Answers

  • You could try the following:

    On PROD:

    Create a cert to be used in DEV

    • ALTER DEK encryption by newCert on the DB that will be moved to DEV
    • Backup newCert and the DB

    on DEV:

    • Restore newCert
    • Restore the DB

    I hope this helps,

    -Raul Garcia

    SQL Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Monday, January 4, 2016 11:42 PM
  • You will need to create the live certificate on the dev server.

    Step 1. Backup Production Certificate

    BACKUP CERTIFICATE [tde_1] TO FILE = 'master_tde_1.cer' 
    WITH PRIVATE KEY (FILE = 'master_tde_1.pvk',ENCRYPTION BY PASSWORD = '<enterStrongPasswordHere>');
    GO

    Step 2. Create certificate on dev server from backup.

    ***

    Note certificate name does not have to match live, and we can rename it in dev. We are concerned with the thumbprint of a certificate not the name. SELECT * FROM sys.certificates

    ***


    CREATE CERTIFICATE [tde_1_live] FROM FILE = 'master_tde_1.cer' WITH PRIVATE KEY (FILE = 'master_tde_1.pvk',DECRYPTION BY PASSWORD = '<enterStrongPasswordHere>'); GO

    Note that there are obvious security implications for doing this, in that production data is then readily available in development. 


    Jon

    Tuesday, January 5, 2016 12:50 AM

All replies

  • You could try the following:

    On PROD:

    Create a cert to be used in DEV

    • ALTER DEK encryption by newCert on the DB that will be moved to DEV
    • Backup newCert and the DB

    on DEV:

    • Restore newCert
    • Restore the DB

    I hope this helps,

    -Raul Garcia

    SQL Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Monday, January 4, 2016 11:42 PM
  • You will need to create the live certificate on the dev server.

    Step 1. Backup Production Certificate

    BACKUP CERTIFICATE [tde_1] TO FILE = 'master_tde_1.cer' 
    WITH PRIVATE KEY (FILE = 'master_tde_1.pvk',ENCRYPTION BY PASSWORD = '<enterStrongPasswordHere>');
    GO

    Step 2. Create certificate on dev server from backup.

    ***

    Note certificate name does not have to match live, and we can rename it in dev. We are concerned with the thumbprint of a certificate not the name. SELECT * FROM sys.certificates

    ***


    CREATE CERTIFICATE [tde_1_live] FROM FILE = 'master_tde_1.cer' WITH PRIVATE KEY (FILE = 'master_tde_1.pvk',DECRYPTION BY PASSWORD = '<enterStrongPasswordHere>'); GO

    Note that there are obvious security implications for doing this, in that production data is then readily available in development. 


    Jon

    Tuesday, January 5, 2016 12:50 AM