none
TDE for multiple databases

    Question

  • Hello All,

    i have question, i have configured the TDE for one of the database by creating a master and certificate.

    Now the question ihave is i need to configure TDE for other 5 databases on the same server, so will it be the process of create seperte certificate for each database, or can i use the same certificate for all the 5 databases.

    Thanks in Advance

    Tuesday, March 29, 2016 8:20 AM

Answers

  • Hello - You can also use the same Certificate for all the remaining databases:

    USE DB2
    GO
    CREATE DATABASE ENCRYPTION KEY 
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE cert1
    GO
    USE DB3
    GO
    CREATE DATABASE ENCRYPTION KEY 
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE cert1
    


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Marked as answer by MSSQL_NEW Tuesday, March 29, 2016 10:15 AM
    Tuesday, March 29, 2016 8:47 AM

All replies

  • Hello - You can also use the same Certificate for all the remaining databases:

    USE DB2
    GO
    CREATE DATABASE ENCRYPTION KEY 
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE cert1
    GO
    USE DB3
    GO
    CREATE DATABASE ENCRYPTION KEY 
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE cert1
    


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Marked as answer by MSSQL_NEW Tuesday, March 29, 2016 10:15 AM
    Tuesday, March 29, 2016 8:47 AM
  • when the sql server installed firstly it automatically stores the master key . if you create another master key it will generate but replace the existing one. it means you cannot keep separate each key for separate database

    Tuesday, March 29, 2016 9:04 AM
  • Thank you all for the reply, one more last question.

    we intend to setup mirroring on these databases, so do we need to take the backup of these certificates for each database and then restore it.

    Tuesday, March 29, 2016 9:28 AM
  • Thanks manu and jason, it worked
    Tuesday, March 29, 2016 10:15 AM
  • We have to distinguish between the Master key and the Certificate or Asymmetric key protecting the encryption key itself.

    The latter can be different for every database.

    The former is always a single one.

    The Certificates have to be backed up and restored on any server where the databases are to be restored as for Availability Groups, Database Mirroring and Logshipping.

    hope that clears it up a bit


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform
    www.SarpedonQualityLab.com | www.andreas-wolter.com

    Monday, April 04, 2016 8:29 PM
  • Forgive my newbie question but using this approach of added the same cert1 into multiple databases. The is_master_key_encrypted_by_server flag for my second db is still set to 0. While the First DB and Master have a 

    is_master_key_encrypted_by_server set to 1 in master.sys.databases. What am I missing here?

    select [name], is_master_key_encrypted_by_server, is_encrypted from master.sys.databases

    USE DB2
    GO
    CREATE DATABASE ENCRYPTION KEY 
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE cert1
    GO
    USE DB3
    GO
    CREATE DATABASE ENCRYPTION KEY 
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE cert1
    
    Monday, May 14, 2018 5:48 PM