Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 30 of 212

20 C H A P T E R 2 | Better security Note When creating column encryption keys, you should not use an unencrypted value as the ENCRYPTED_VALUE parameter of the CREATE COLUMN ENCRYPTION KEY statement. Otherwise, you compromise the benefits of Always Encrypted by making data vulnerable to attack. The CREATE COLUMN ENCRYPTION KEY command accepts a minimum of one VALUE block, and a maximum of two VALUE blocks. Two VALUE blocks should be used when rotating encryption keys, either because a key has expired or because it has become compromised. Two keys should exist within the database long enough for all connected applications to download the new encryption keys from the database. Depending on the application design and client connectivity, this process may take minutes or months. Generating new encrypted values Given that the value is encrypted, how can new encrypted values be generated? The easiest way is to use SSMS to open the New Column Encryption Key dialog box shown in Figure 2-3, select the correct column master key definition, provide a name for the new encryption key, and then click the Script button at the top of the dialog box. This selection gives you the full CREATE COLUMN ENCRYPTION KEY statement, including a new random encrypted value. You can then add this new value as a second encryption key and thereby easily rotate the encryption keys. Creating a table with encrypted values After creating the column master key definition and column encryption keys, you can create the table to hold the encrypted values. Before you do this, you must decide what type of encryption to use, which columns to encrypt, and whether you can index these columns. With the Always Encrypted feature, you define column sizes normally, and SQL Server adjusts the storage size of the column based on the encryption settings. After you create your table, you might need to change your application to execute commands on this table using Always Encrypted. In this section, we describe the choices you have when creating your table and adapting your application. Encryption types Before creating a table to contain encrypted values, you must first make a choice about each column to be encrypted. First, will this column be used for looking up values or just returning those values? If the column is going to be used for lookups, the column must use a deterministic encryption type, which allows for equality operations. However, there are limitations on searching for data that has been encrypted by using the Always Encrypted feature. SQL Server 2016 supports only equality operations, which include equal to, not equal to, joins (which use equality), and using the value in the GROUP BY clause. Any search using LIKE is not supported. Additionally, sorting data that is encrypted using Always Encrypted must be done at the application level, as SQL Server will sort based on the encrypted value rather than the decrypted value. If the column is not going to be used for locating records, then the column should use the randomized encryption type. This type of encryption is more secure, but it does not support searches, joins, or grouping operations. CREATE TABLE statement for encrypted columns When creating tables, you use the normal CREATE TABLE syntax with some additional parameters within the column definition, as shown in Example 2-3. Three parameters are used within the ENCRYPTED WITH syntax for the CREATE TABLE statement. The first of these is the ENCRYPTION_TYPE parameter, which accepts a value of RANDOMIZED or DETERMINISTIC. The second is the ALGORITHM

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016