August 29, 2019 at 12:00 am
Comments posted to this topic are about the item Key Rotation in TDE
August 29, 2019 at 7:59 am
It’s a good article but needs the following pointers
In the hierarchy section you stated that the DMK protects the certificate stored in the master database, this is not completely correct.The DMK protects only the private key of the certificate which is an asymmetric key itself. The public key is freely distributed and not a secret
In the beginning of the rotate certificate section you stated that the certificate is automatically protected by the DMK. This will depend on how the certificate was created, if the
ENCRYPTION BY PASSWORD
clause was supplied, you are overriding the protection by the DMK and instead, would need to supply the password each and every time you access the certificate.
In the handling database restores, never keep the certificates private key backup with the database backup files. Separate them and store the certificates and private key backups securely.
Remember, do not restore master database across instances, it’s not designed to restores across instances and can cause issues especially around encryption at the root levels.
Always backup the certificates and store them and their passwords securely.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 29, 2019 at 3:42 pm
Thanks for the notes. I've never seen the certificate protected by password in TDE, just the DMK. I assume doing so would ensure that the whenever the instance started up and the db opened, the certificate password would be needed? Wouldn't that be impractical in most cases?
As far as backups, because of the issues of finding them over time, I've always keep the certificate backup with the database backup. You may disagree, but the loss from simple mistakes with the files is much more likely, IMHO, than the loss from theft. Even with the files, the password to create them isn't available.
August 29, 2019 at 4:16 pm
Thanks for the notes. I've never seen the certificate protected by password in TDE, just the DMK. I assume doing so would ensure that the whenever the instance started up and the db opened, the certificate password would be needed? Wouldn't that be impractical in most cases?
yes but doesn’t stop people doing it and it leaves no area of ambiguity if you cover all aspects.
I've always keep the certificate backup with the database backup. You may disagree, but the loss from simple mistakes with the files is much more likely, IMHO, than the loss from theft. Even with the files, the password to create them isn't available.
separate them out and store them securely is best practice and reduces the surface Atack area
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 3, 2019 at 3:32 pm
As mentioned in the post, key management is definitely the hardest part of an encryption strategy, and one that many people get wrong. It is important to use an external key manager (which is why Microsoft has introduced EKM) and manage encryption keys separately from the encrypted data. Aside from being a security best practice, many compliance regulations require it. Full disclosure, I work with a key management vendor, but would be happy to help develop vendor neutral content about EKM and key management (which we produce a lot of - view our Definitive Guide to SQL Server Encryption & Key Management as an example).
September 3, 2019 at 9:08 pm
Luke, happy to have you submit some vendor neutral content if you'd like.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply