June 20, 2019 at 12:28 pm
Mandate coming down from corporate is that we need to start encrypting our databases. We generally have no issues with that except for the fact that they're trying to push TDE on us. We've got some sandbox servers (fortunately) that we can use to test this concept and have just discovered that high availability groups won't allow encrypted databases to be added via the wizard. No problem, I says. Let's Google the solution!
Only every solution I find requires setting up a certificate in SQL Server and encrypting the databases in SQL server with that certificate. What we really have is a third party cryptographic provider that stores the certs in its separate database (not on the SQL Server) and then we install the software, create the provider based on that .dll, create the asymmetric key, etc. All fine and dandy until it comes time to add the database to the AG.
The certificate has no entry in sys.certificates. We already have the credential and asymmetric key in the proper tables though. And the databases are encrypted already. So what I'm trying to figure out is if there's a link for adding databases to AGs when the TDE is third party. My google fu fails me.
Any thoughts on keywords or links anyone has on this? Or thoughts on how to get the certificate local to add?
Or am I out of luck on this one? If so, I need to know so we can go back to corporate on this and find another encryption solution (we have a few in mind, but need to try out the corporate mandated solution first).
June 20, 2019 at 12:45 pm
You should have a look at Extensible Key Management if you can use EKM first depends on your SQL Server Version and second on your Edition Edition and finally it depends on your 3rd Party Vendor Application if this will be supported by SQL Server.
One example of such externally managed certificate store being used for TDE would be Azure Key Vault.
June 20, 2019 at 12:54 pm
I'll keep that in mind. But right now I have to make the good ol' college try at finding a solution to the problem in front of me. If there is no solution, I can move on to your suggestion as well as the other things we have in mind.
Does anyone else have any other thoughts?
EDIT: Reading up on your linked article and a few others, it seems we've got a EKM setup or one very much like it. But the articles don't give me any hints on how to get these databases added to an AG. I'm beginning to think it's not possible because I would almost need the master key / private key on the SQL server to do it, which obviates the use of an EKM setup and opens up the security.
Meaning the CIA triad is right on the nose here. We can have integrity and availability or integrity and security (confidentiality) but not all three.
June 20, 2019 at 2:37 pm
well adding to my last post have a look here aswell, again I am completely unaware of 3rd Vendor Options but Azure Data Vault is yet another EKMS so the idea presented in the article should still apply with other EKMS Systems, too.
Here is an example how to set up TDE enabled AAGs with Azure Data Vault as EKMS, in theory your Vendor should have some sort of connector which you should be able to use, similar to how ADV is accessed?
June 20, 2019 at 4:26 pm
I think as Drew mentioned, you need a key created from your EKM provider. That would be something your EKM system gives you sample code on how to do this. Once you've done that, the code would be for TDE:
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY BRANDI_KEY;
GO ?
Where BRANDI_KEY is the asymmetric key you created from your EKM. This needs to be installed on every node in the AG, then when you set up the replicas, and they get data from a restore or seeding, they can decrypt the DEK since they'll have the asymm key in master
Note, your asymm key shouldn't be in master.sys.certificates, but in master.sys.asymmetric_keys
June 21, 2019 at 9:55 am
AHA! Corporate finally responded to my questions and has documentation on how to add the encrypted database to the AG.
They also have documentation saying add the databases to the AG *before* encrypting it. So 2 options that I get to test.
So... good news for them. Bad news for me. I can't use this as an excuse to push back on TDE.
June 21, 2019 at 4:38 pm
No reason to push back on TDE. It's low impact, easy, and it prevents incredibly silly losses of data due to someone copying a backup file to the wrong place.
June 21, 2019 at 6:03 pm
We're using a high volume vendor solution that has on and off performance issues. I'm worried about the impact TDE would have on a 2 data center, 4 node (2 in each DC) availability group that already has strain on the system (in SQL 2012). Something that hits performance for every I/O operation is not something I'm keen on introducing to the mix if we can just encrypt the backup drives and the data / log file drives instead.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply