locked
restore TDE encrypted database to another TDE enabled server RRS feed

  • Question

  • Hi,

    I am doing a POC on implementing TDE. I could set up TDE and also enable TDE on Principal database and its working fine.

    One requirement that i could not achieve and also not easily appearing on my google search is below

    Instance1:

    Has own masterKey and certificate and an encrypted DB and its backup is TEST.bak

    Instance2:

    Has its own masterKey and its own certificate and an encrypted DB and its backup is SAMPLE.bak

    How can i restore a TEST.bak in instance2 because instance2 is already having a certificate and i am getting below error if try to create certificate from certificate backup taken from Instance1.

    A certificate with name 'TDEins2Cert' already exists or this certificate already has been added to the database

    (but this does not exist actually in sys.certificates)

    Similar issue when i try to restore SAMPLE.bak to Instance1

    I know if the certificate is same on both Instance1 and Instance 2 restore will work, i wanted to know about scenario when both are different .

    Basically i wanted to understand, if TDE backups from different sql instances(sources) with different certificates can be restored in same instance

    Thanks in advance


    • Edited by udhayan Wednesday, March 14, 2018 12:21 PM
    Wednesday, March 14, 2018 12:17 PM

Answers

  • Hi udhayan,

    >>Basically i wanted to understand, if TDE backups from different sql instances(sources) with different certificates can be restored in same instance

    It is supported, Transparent Data Encryption (TDE) uses Database Encryption Key (DEK) to protect data, DEK is protected by the certificate or asymmetric key, we just need to move the certificate or asymmetric key that is used to open the DEK, when we backup the certificate, the certificate is protected by the private key file and password rather than database master key. On the new server, the certificate will be protected by the database master key on the new server.

    Here is a case which using the different database master key on original and target server, it finally do it successfully, please refer to it: https://www.mssqltips.com/sqlservertip/3572/recovering-a-sql-server-tde-encrypted-database-successfully/

    >>A certificate with name 'TDEins2Cert' already exists or this certificate already has been added to the database

    Which version of SQL Server did you use? How did you restore the certificate?

    Best Regards,

    Teige


    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.


    • Edited by Teige Gao Thursday, March 15, 2018 7:24 AM
    • Proposed as answer by Teige Gao Monday, March 26, 2018 9:26 AM
    • Marked as answer by udhayan Tuesday, March 27, 2018 4:59 AM
    Thursday, March 15, 2018 7:15 AM
  • Hi Udhayan,

    it will work like others have described. There must be a failure when you've exported/imported the certificate. As Teige has mentioned - the database / backup has a DEK which is protected by the certificate. You need the certificate to restore a TDE encrypted database on a secondary / other server.

    It is perfectly described here:

    https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/move-a-tde-protected-database-to-another-sql-Server


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    • Marked as answer by udhayan Tuesday, March 27, 2018 4:59 AM
    Monday, March 26, 2018 11:20 AM

All replies

  • AFAIK if a database is encrypted by cert1 certificate when you restore it on different machine you have to USE cert1 certificate otherwise restore is not possible

    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

    Thursday, March 15, 2018 7:01 AM
  • Hi udhayan,

    >>Basically i wanted to understand, if TDE backups from different sql instances(sources) with different certificates can be restored in same instance

    It is supported, Transparent Data Encryption (TDE) uses Database Encryption Key (DEK) to protect data, DEK is protected by the certificate or asymmetric key, we just need to move the certificate or asymmetric key that is used to open the DEK, when we backup the certificate, the certificate is protected by the private key file and password rather than database master key. On the new server, the certificate will be protected by the database master key on the new server.

    Here is a case which using the different database master key on original and target server, it finally do it successfully, please refer to it: https://www.mssqltips.com/sqlservertip/3572/recovering-a-sql-server-tde-encrypted-database-successfully/

    >>A certificate with name 'TDEins2Cert' already exists or this certificate already has been added to the database

    Which version of SQL Server did you use? How did you restore the certificate?

    Best Regards,

    Teige


    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.


    • Edited by Teige Gao Thursday, March 15, 2018 7:24 AM
    • Proposed as answer by Teige Gao Monday, March 26, 2018 9:26 AM
    • Marked as answer by udhayan Tuesday, March 27, 2018 4:59 AM
    Thursday, March 15, 2018 7:15 AM
  • Hi Teige,

    Sorry its been a long time i replied to my post.

    I tested in SQL Server 2012 and i backed up certificate from source and used create certificate in destination .

    Thanks

    Udhayan

    Monday, March 26, 2018 10:47 AM
  • Hi Shanky,

    Ya i can understand that concept . My requirement is

    Instance A --> cert1 --> DB1(Encrypted) 

    Instance B --> cert2 --> DB2(Encrypted) -->DB2.bak(encrypted)

    Instance C --> cert3 --> DB3(Encrypted) -->DB3.bak(encrypted)

    Instance D --> cert4 --> DB4(Encrypted) -->DB4.bak(encrypted)

    How can i restore DB2.bak , DB3.bak, DB4.bak in Instance A  because i need to create respective certificates cert2,cert3 and cert4 

    Will i be able to create multiple certificates in Instance A,because i attempted to create a second certificate and i got below error which i posted in my first email.

    A certificate with name 'TDEins2Cert' already exists or this certificate already has been added to the database


    Monday, March 26, 2018 10:57 AM
  • Hi Udhayan,

    it will work like others have described. There must be a failure when you've exported/imported the certificate. As Teige has mentioned - the database / backup has a DEK which is protected by the certificate. You need the certificate to restore a TDE encrypted database on a secondary / other server.

    It is perfectly described here:

    https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/move-a-tde-protected-database-to-another-sql-Server


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    • Marked as answer by udhayan Tuesday, March 27, 2018 4:59 AM
    Monday, March 26, 2018 11:20 AM
  • OK thanks Uwe . could be any miss from my side during the POC.. let me try again. 

    Thanks guys for your time.

    Tuesday, March 27, 2018 4:59 AM