Exam DP-300 topic 2 question 5 discussion

Actual exam question from Microsoft's DP-300

Question #: 5
Topic #: 2

DRAG DROP -
You have an Azure SQL database that contains a table named Employees. Employees contains a column named Salary.
You need to encrypt the Salary column. The solution must prevent database administrators from reading the data in the Salary column and must provide the most secure encryption.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:

Show Suggested Answer

Suggested Answer:

Step 1: Create a column master key
Create a column master key metadata entry before you create a column encryption key metadata entry in the database and before any column in the database can be encrypted using Always Encrypted.
Step 2: Create a column encryption key.
Step 3: Encrypt the Salary column by using the randomized encryption type.
Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns.
Note: A column encryption key metadata object contains one or two encrypted values of a column encryption key that is used to encrypt data in a column. Each value is encrypted using a column master key.
Incorrect Answers:
Deterministic encryption.
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 columns. However, it may also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column, especially if there's a small set of possible encrypted values, such as True/False, or North/South/East/West region.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine

by klever at Feb. 25, 2021, 7:28 p.m.

Comments

Submit Cancel

klever

Highly Voted 4 years, 4 months ago

Here to key phrase is "most secure" encryption that is why the answer is correct. Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns.

upvoted 19 times

voodoo_sh

6 months, 2 weeks ago

T-SQL for it: -- Create a Column Master Key: CREATE COLUMN MASTER KEY MyColumnMasterKey WITH ( KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT', KEY_PATH = 'https://<your-key-vault-name>.vault.azure.net/keys/<your-key-name>' ); -- Create a Column Encryption Key: CREATE COLUMN ENCRYPTION KEY MyColumnEncryptionKey WITH VALUES ( COLUMN_MASTER_KEY = MyColumnMasterKey, ALGORITHM = 'RSA_OAEP', ENCRYPTED_VALUE = <encrypted_value> ); -- Encrypt the Salary Column with Randomized Encryption: ALTER TABLE Employees ALTER COLUMN Salary ADD ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = MyColumnEncryptionKey, ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256' );

upvoted 1 times

...

Raffer

Highly Voted 4 years, 3 months ago

Worth noting that if a similar question requires that the encrypted column be able to be used for point lookups, equality joins, grouping or indexing of the column, then Deterministic Encryption would be the answer.

upvoted 9 times

...

yster

Most Recent 3 years, 4 months ago

Deterministic encryption: so that statistics can still be performed on the salary column. It's not the most secure option. The best option for this scenario.

upvoted 1 times

Natali

11 months, 1 week ago

Random is correct answer. Assume that the encryption is deterministic. You are a db user, and you are not allowed to see salaries values of employees. But you are allowed to group the rows by salary. So you are able to see, which people earn the same amount. This is already not secure enough. Assume now that you are allowed to see salaries. Why would you want to index them? Why would you lookup a person by its salary? does not seem like a valid case. Random works just fine. Why would you lookup on salary

upvoted 1 times

...

erssiws

3 years, 11 months ago

I think that the provided answers are correct.

upvoted 4 times

...

DBA101

3 years, 11 months ago

https://docs.microsoft.com/en-us/azure/azure-sql/database/always-encrypted-azure-key-vault-configure?tabs=azure-powershell

upvoted 1 times

...

fnmag

4 years ago

I can't see how this will prevent Administrators from reading everything. It seems that something is missing

upvoted 2 times

cusman

3 years, 3 months ago

Always On Encryption is missing. In real-world, there would be Security Admin that has access to the keys, and Database Admin that would have access to the database. Only the business application(s) that need access to such sensitive data would be able to get both the keys needed from Key Vault and the Data needed from Database to be able to read / update the data.

upvoted 2 times

...