Answered by:
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 workedTuesday, 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- Proposed as answer by roncansan Monday, June 24, 2019 2:19 PM
Monday, April 4, 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