0

There are many posts in the web showing the basics of this technology: the data is encrypted everywhere, apart of the client, a Column Encryption Key (CEK) is used to encrypt or decrypt the data by the client.

  1. The CEK is stored in the client machine? In the connection string of the application? Is it visible by the client (=the employee)? For example we hire a new secretary, and he should have access to the sensitive (and encrypted) data. We give him a new laptop, grant him read/write privileges, install the application on his new laptop, and what else? "Telling" him what is the CEK? Storing the CEK somewhere in the registry?

  2. What is the role of the Column Master Key (CMK): To create new CEKs and restore them in a case they were lost? To let only one authorized person to hold the key of the whole system?

Bogdan Sahlean
  • 19,233
  • 3
  • 42
  • 57
Geri Reshef
  • 397
  • 1
  • 6
  • 17

1 Answers1

3

The Column Encryption Keys (CEK) are actually stored in SQL Server, along with the data, but they can't be used without the Column Master Key (CMK). It is the CMK that the client applications need to decrypt the data. The CMK can be stored in the Windows Certificate Store of client machines, or in an Azure Key Vault.

If you use the "Encrypt Columns" wizard in SSMS then it will generate both types of key for you. It will ask where you want to keep the CMK (Windows Certificate Store or Azure Key Vault) and lets you choose whether to have one CEK per encrypted column, or a single CEK shared by all columns. It also lets you reuse an existing CMK, if there is one.

An application needing to decrypt the data then needs access to the CMK (more on that in a moment) and the following addition to the connection string:

Column Encryption Setting=Enabled;

This setting can also be changed programatically if required:

connStringBuilder.ColumnEncryptionSetting = SqlConnectionColumnEncryptionSetting.Enabled;

How the application accesses the CMK depends on where it's stored:

  • If the CMK is stored in the Windows Certificate Store then an Always Encrypted enabled driver will pick it up automatically (the CEK contains details of the CMK required to use it).

  • If the CMK is stored in an Azure Key Vault then a few changes are required to register the Azure Key Vault provider.

I haven't used the Windows Certificate Store, but I'd guess that you would give a new computer access to the encrypted columns by exporting the CMK from the store on an existing computer and importing it into the store on the new one.

Jonathan Sayce
  • 9,359
  • 5
  • 37
  • 51
  • Hi Jonathan, Just to be sure: the CEK is stored on the server, the CMK is stored somewhere else (on the client or in Azure), the user should have access to the CMK. If an unauthorized person accesses the database or gets a backup of it, he will not be able to decrypt the encrypted columns without getting an access to the CMK too. Finally, is the CEK stored in the database or in the server? If we restore it on another instance or machine, would we have to restore in addition the CEK or would we find it in the database itself? Thank you again! – Geri Reshef Jun 11 '16 at 06:03
  • Hi @Geri - yes, that right. The CEK is stored in the database, so will be restored when the database is restored. – Jonathan Sayce Jun 11 '16 at 09:20