March 16, 2020 at 12:14 pm
Hi All,
I have some questions related to SQL 2008 EE feature Transparent Data encryption (TDE).
While implementing TDE we create a certificate in master database as follows and immediately take the backup of it.
use master
go
CREATE CERTIFICATE TDECert WITH SUBJECT = 'Test TDE Certificate'
go
Question 1)
By default expiry date of the certificate is 1 year.Now let’s say, my certificate is expired, then what happens?
Even further, since certificate is expired, if I say, ALTER DATABASE <DBname> SET ENCRYPTION OFF; will I get back my original plaintext or my data is LOST? For certificate expiry date what are the measures to be taken care ? Usually, what value is set for expiry_Date?
Question 2) For some reason, If I lost my certificate or it got corrupted , how can i get my original data ?
Will ALTER DATABASE <DBname> SET ENCRYPTION OFF; would help in any way or no use?
Question 3) if the database is already in AG. let's say db1, now I implemented TDE on primary , before enabling the TDE on primary, do i need to remove that database from AG?,
then implement TDE and then add it back to AG?
Thanks,
Sam
March 16, 2020 at 5:34 pm
I can't answer question 3 but for
#1. Its not a big deal, it will warn you that your certificate is expired and you can update the certificate like this
<create new certificate like you did the first time>
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE <your certificate here>
#2 I can't say I've had a certificate get corrupted, I usually keep multiple copies of a cert anyways so if that were to happen I'd just replace the cert where ever it got corrupted at. I don't think that the problem you're thinking could happen would happen though.. the certificate is mainly to prevent the database from just being restored somewhere else, it only encrypts the data at rest, I could be wrong but I don't think the certificate thumbprint on the data can get corrupted and prevent you from querying the data where it already exists. When you do go to remove encryption completely, you have to not only set encryption off but you need to drop the key from the database as well. You'll need to do this for the AG step as well.
March 16, 2020 at 6:43 pm
As I understand it, if you lose the certificate, you can't read the data, period. Back up that certificate and make sure you can restore it!
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 16, 2020 at 7:13 pm
From my understanding Scott, the only way that would happen is if you dropped the certificate.. which I don't think you can even do if the database is still encrypted. You'd have to do something like
Unencrypt, drop the database encryption key, and drop the certificate and then somehow restore the database without having the certificate still there? I'm not really sure that this can happen.
But that being said, yes, absolutely back up your certificate.
What can happen is your server crashes and you cant restore your database because you don't have a copy of the certificate.
March 16, 2020 at 7:39 pm
Ultimately any part of a disk drive file can be corrupted (or attacked?). A bad controller, etc., can cause unexpected corruption.
Again, it's my understanding that there is no way to recover the data if you don't have the certificate. Just something to keep in mind.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 16, 2020 at 8:47 pm
Yeah absolutely, if you have some kind of drive failure, then you're screwed if you havent backed it up.
March 18, 2020 at 8:24 am
For question #1 another thing to keep in mind is that expiration date is a property that SQL Server does not honor. So even if the certificate is expired TDE will continue to function.
For #2 it is always good to backup the cert. In the case of a DR/restore scenario where you may need to restore that data to somewhere the cert wasn't already present it is vital you have the cert.
For #3 I am guessing OP was talking about setting up TDE on a db that is in an AG? We do this in our environment. TDE can be setup both before or after the db is setup in an AG. The only things to keep in mind are that the cert needs to be on all AG instances in order for TDE to be setup properly, and if TDE is setup before putting the db in an AG you will have to add the db to the AG yourself through scripts; the wizard cannot account for TDE-enabled databases.
Joie Andrew
"Since 1982"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply