You have a database named DB1 that contains two tables.

You need to encrypt one column in each table by using the Always Encrypted feature.

The solution must support groupings on encrypted columns.

Which t

wo actions should you perform? Each correct answer presents part of the solution.

NOTE:

Each correct selection is worth one point.

A. Encrypt both columns by using deterministic encryption.

B. Provision a symmetric key by using Transact-SQL.

C. Encryp

t both columns by using randomized encryption.

D. Provision column master keys and column encryption keys by using Microsoft SQL Server Management Studio (SSMS).

A: Use deterministic encryption for columns that will be used as search or groupi

ng parameters, for example a government ID number.

Deterministic encryption always generates the same encrypted value for any given plain text value. Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted co

lumns.

D: Always Encrypted uses two types of keys: column encryption keys and column master keys. A column encryption key is used to encrypt data in an encrypted column. A column master key is a key-protecting key that encrypts one or more column

encryption keys.

Incorrect Answers:

B: A column encryption key (CEK), is a content encryption key (i.e. a key used to protect data) that is protected by a CMK.

All Microsoft CMK store providers encrypt CEKs by using RSA with Optimal Asymmetric Encryption

Padding (RSA-OAEP) with the default parameters specified by RFC 8017 in Section A.2.1.

C: Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents searching, grouping, indexin

g, and joining on encrypted columns.

References: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-2017