2

I am using the following PowerShell code to create a certificate that can be used as Column Master Key for Always Encrypted feature:

New-SelfSignedCertificate -Subject "AlwaysEncryptedSecurityLevelNormalV001" -CertStoreLocation Cert:LocalMachineMy -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage KeyEncipherment -KeySpec KeyExchange -KeyLength 2048 -NotAfter (Get-Date).AddYears(100)

Then I export the certificate and import it into the machine where the SQL Server is running. During the export I am disabling the option to export the certificate private key. So, if one try to export it, he/she will get the following:

enter image description here

This is nice because the certificate private key is protected for export.

I am wondering is there any cases where I may need to export only the certificate without the privet key in the context of Always Encrypted feature (as the work of this certificate it to protect the column encryption keys only) ?

| improve this question | |
2

Then I export the certificate and import it into the machine where the SQL Server is running.

That defeats the purpose of using Always Encrypted if the server that is running SQL Server also has the certificate to decrypt all the data.

Please, please, please don't do this! Put it on the client machine or a "tools" or "intermediate loading" machine to get the data in and out. Then remove the key and keep it only on the client machines that are locked down.

I'd also keep, in the back of your mind, a solution for when a client machine is lost/stolen/breached (laptop), or a server is compromised. How do you find out, how long do you have to rotate CMKs, do you know how to rotate CMKs, etc.

I am wondering is there any cases where I may need to export only the certificate without the privet key in the context of Always Encrypted feature (as the work of this certificate it to protect the column encryption keys only) ?

The private key is what keeps everything safe, so no private key then no decryption. I can't think of any times when this would be useful.

| improve this answer | |
  • Thanks for confirming the step from the export is not needed (maybe it should not be displayed at all). Microsoft are selling Always Encrypted as alternative of EKM for Standard Edition and it is targeting the cloud - if you are using Enterprise edition, you can use TDE or EKM, on standard you get only Always Encrypted. From the docs, you can see how many examples there are about using Azure Key Vault as EKM and almost nothing for Windows Certification Store. Its obvious for me that best practices there are for cloud solutions, not on premises solutions, where you own all the machines. – gotqn Mar 19 '18 at 8:19
  • @gotqn I wouldn't go that far with the thinking. TDE and Always Encrypted tackle two very different problems. One is at rest only and one is at rest, in memory, and in transit protection. The certificate store can only be used with AE - TDE doesn't support it, so again can't compare. EKM is used because we don't officially endorse any 3rd party providers and it's our product that anyone can use. – Sean Gallardy - Retired User Mar 19 '18 at 13:12
  • It's perfectly fine for me. that MS does not to support 3rd party EKM providers officially - I prefer using only Microsoft solutions when possible. It's a pity that one can use Windows Certification Store for storing certificates used by Always Encrypted, but not able to use the Cert Store when it comes to backup encryption or SYMMETRIC KEY for example. If you want to have these certificates out of the database, you need to use EKM, provided by other company, and it's possible only for enterprise. – gotqn Mar 19 '18 at 13:25
  • @gotqn Just a clarification, we support but we don't endorse 3rd party EKMs. The reason that a certificate store can be used in AE is because the client driver does the encryption and decryption, SQL Server doesn't do anything with the certificate itself but only stores the metadata of where it's stored (path) and the thumbprint (unique identifier in the path). – Sean Gallardy - Retired User Mar 19 '18 at 13:42
  • Yes, I know and this is perfect. Why not to have this for backup encryption and symmetric(asymmetric) keys, also? I hope, someday we will have it. – gotqn Mar 19 '18 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.