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

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

Thanks manu and jason, it worked

Answer 5

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

Answer 6

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