Key Rotation in TDE

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716238

    Comments posted to this topic are about the item Key Rotation in TDE

  • Perry Whittle

    SSC Guru

    Points: 233794

    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.

     

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Emmitt Albright

    SSC Enthusiast

    Points: 183

    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.

  • Perry Whittle

    SSC Guru

    Points: 233794

    Emmitt Albright wrote:

    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.

    Emmitt Albright wrote:

    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

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • luke.probasco

    Grasshopper

    Points: 22

    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).

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716238

    Luke, happy to have you submit some vendor neutral content if you'd like.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply