Search code examples
sql-serversslencryptiontde

Confusion about TDE and AlwaysOn Availability Groups

This is my first ever post so I'll be brief. I have two SQL 2014 Enterprise servers; one primary and one secondary replica. This is a multi-tenancy deployment with separate databases per tenant. (No shared databases or shared schema). I currently have one AlwaysOn Availability Group up and running (more to follow). I have a TDE requirement. I have an SSL certificate issued by our CA. It is installed on bother SQL servers. I have converted it to .PVK format. I'm ready to implement database encryption using the same .PVK on both servers

Areas of confusion:

  1. Do I have to export the SSL certificate thumbnail to the secondary servers registry the same way I do in a normal quorum-based fail-over cluster?
  2. I have to create a master key on the primary SQL server before I can create a new TDE certificate from file. I don't understand how the secondary replica server factors into this case. I need the encrypted databases to be accessible on the secondary server. Since I'm dealing with different master databases, do I have to create another master key on the secondary server? Can I still use the same .PVK certificate on the secondary server for TDE?

I would appreciate any insights or guidance. Thanks a lot. DC

Solution

  • So, it helps to know what's going on with TDE and where all the moving parts live. Let's start at the bottom and work our way up.

    1. The database encryption key lives in the user database. It will fail over with the database when the AG moves to another node.

    2. The encryption key is encrypted by the server certificate that lives in the master database. In order for it to be decrypted after a failover, the certificate that protects it needs to be available on any node that attempts to decrypt it.

    3. Certificates in general are protected by the database master key (DMK). Since master is just another database (albeit a special one), it just needs a DMK in order to protect the certificate.

    4. Though not relevant for this discussion, the DMK is protected by the service master key (SMK). The SMK is generated for you when the instance is installed. If you're ever in a situation where you have a DMK that will need to be decrypted somewhere other than the server where the database was created, make sure you have that SMK handy. One thing that I've done recently is make sure that the SMKs match on all nodes in an AG (using backup service master key and restore service master key). That way, when a user database fails between nodes, the DMK can be decrypted automatically by the SMK on the new node (since it's the same SMK). As to why it's not relevant for this discussion, your master databases won't be failing between nodes.

    And as far as needing to import the cert into the registry, I haven't had to do that in my experience.