CODEWITHSUNDEEP
sql-serversql-server-2016always-encrypted
Geri Reshef
Geri Reshef

Reputation: 407

SQL Server: Always Encrypted

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?

Upvotes: 0

Views: 1160

Answers (1)

Jonathan Sayce
Jonathan Sayce

Reputation: 9659

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.

Upvotes: 3

Related Questions