1

After setting up Always Encrypted, I can see a certificate in my Current User enter image description here

when selecting

select * from sys.column_master_keys

under the key_path I see this location, but there is no location specified for encryption keys. So is just this certificate export enough? Does it contain both master and encryption keys together in the same certificate?

Thanks

2

After setting up Always Encrypted, I can see a certificate in my Current User

Correct, you've stated that the Column Master Key (CMK) will be created/stored there. Most likely this was done via the GUI to generate a new key and save it there. There are other options as well for the CMK.

[...] under the key_path I see this location, but there is no location specified for encryption keys.

Correct, that was the location used when executing the metadata update to SQL Server for where the CMK exists. Since SQL Server doesn't go out and touch that Certificate (which shouldn't be in the server) it only knows what to send back to the client driver as metadata. The certificate holds an asymmetric key pair that is used to encrypt and decrypt the Column Encryption Key (CEK) which is stored inside of SQL Server in the same database.

So is just this certificate export enough?

Assuming AE is setup correctly, the client only needs that certificate, yes. There are obviously database level permissions that are required to get said metadata but overall the process is moot if you don't have a copy of the CMK.

Does it contain both master and encryption keys together in the same certificate?

No, as I stated, there is the CMK which is the certificate you've created and then there is the CEK which does the actual encryption and decryption of the data.

You'll want to go back and read a bit more about Always Encrypted and how it works as this is all explained in the documentation.

  • Thanks a lot. So it turn that CEK is a usual asymmetric key saved in the database? So why it is not visible under sys.asymmetric_keys? @SeanGallardy – igelr Jul 17 at 10:52
  • 1
    @ElGrig It's specifically for AE, so you'll want to query sys.column_encryption_keys. – Sean says Remove Sara Chipps Jul 17 at 12:26

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.