Additional Information

With SQL Server 2016 SP1 we have the availability to use Always Encrypted functionality with the Standard Edition of the server. One is able to create a certificate in the Windows Certification Store and mapped this certificate to COLUMN MASTER KEY - then, use the CMK to protect COLUMN ENCRYPTION KEY used for encrypting data in particular column.

Using Windows Certification Store is handy:

  • because other Extensible Key Management providers are for the Enterprise Edition (like Hardware Security Modules - HSM)
  • the access to the certificates is manage in central prlace
  • we skip to levels of the encryption hierarchy (using Service Master Key and Database Master Key)

Case scenario

The Always Encrypted functionality is used to encrypted all data in a particular column. I need to encrypted only some of the data, so I need to use a symmetric key or asymmetric key. I want to protect them with a certificate which is stored in the Windows Certification Store in order to control the access to the keys from there. Is this possible to create such certificate?

  • Could you elaborate on how you want the key to be opened? Is it using a certificate on the client computer? – David Browne - Microsoft Jan 4 '18 at 18:01
  • @DavidBrowne-Microsoft We have option to create a certificate using a file. I want instead of using a file as a source to use the windows certification store. – gotqn Jan 4 '18 at 20:45
  • Not with SQL Server directly, but you can code your application to use the certificate in the certificate store to do the encryption/decryption without SQL Server being involved. Always Encrypted is part of the driver which does the encryption and decryption, not the database engine itself. – Sean Gallardy Jan 4 '18 at 22:08
  • I'm still not sure whether or not you want server-side decryption. But in any case for this scenario I would almost rather use two columns. One encrypted and one not, and work across them in a view or in client code. – David Browne - Microsoft Jan 4 '18 at 22:13
  • @SeanGallardy-Microsoft That's sounds good. Can you show me how I can use the Always Encrypted functions to encrypt values or give me a link to example/documentation? – gotqn Jan 5 '18 at 6:43

Your Answer

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

Browse other questions tagged or ask your own question.