Exam DP-200 topic 20 question 1 discussion

Actual exam question from Microsoft's DP-200

Question #: 1
Topic #: 20

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You need to configure data encryption for external applications.
Solution:
1. Access the Always Encrypted Wizard in SQL Server Management Studio
2. Select the column to be encrypted
3. Set the encryption type to Randomized
4. Configure the master key to use the Windows Certificate Store
5. Validate configuration results and deploy the solution
Does the solution meet the goal?

A. Yes
B. No

Show Suggested Answer

Suggested Answer: B 🗳️
Use the Azure Key Vault, not the Windows Certificate Store, to store the master key as it must be used by external applications.
Note: The Master Key Configuration page is where you set up your CMK (Column Master Key) and select the key store provider where the CMK will be stored.
Currently, you can store a CMK in the Windows certificate store, Azure Key Vault, or a hardware security module (HSM).

However, if you use the Windows Certificate Store for external applications to use the key, the external application must run on the same computer where you ran the Always Encrypted wizard, or you must deploy the Always Encrypted certificates to the computer running the external application.
Reference:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted-azure-key-vault https://docs.microsoft.com/en-us/azure/azure-sql/database/always-encrypted-certificate-store-configure

by Luke97 at May 25, 2020, 8:32 p.m.

Comments

Submit Cancel

Luke97

Highly Voted 5 years, 1 month ago

Right answer but wrong reason. - Encryption type should be Deterministic instead of Randomized. Master Key should be stored in Windows Certificate Store as security requirements ask to manage all keys internally on-premises.

upvoted 22 times

watata

4 years, 4 months ago

why deterministic?

upvoted 1 times

...

dumpsm42

Highly Voted 4 years, 6 months ago

hi to all, we must read very well and always the question-text. in this case => "...certificates and keys are internally managed in an on-premises storage." so keyvault cant be, for my thinking and more: data should be queried and joined and etc so we cant choose a random encryption method, it must be deterministic so these 2 points sets the right answer regards

upvoted 7 times

...

syu31svc

Most Recent 4 years, 7 months ago

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 On-premises storage as requirement Answer is No

upvoted 4 times

dumpsm42

4 years, 6 months ago

100% agree. and the text says it "...Both internal and external client application run complex joins, equality searches and group-by clauses..."

upvoted 2 times

...

apandey

4 years, 11 months ago

Deterministic Vs Randomised only define whether you want to use the column in lookups/joins but there are no requirements around the same, so answer should be 'yes' for this one and for the next question as well it is 'yes'. Windows certificate is required and not key vault as keys must be stored on premise.

upvoted 4 times

...

VJ8

5 years ago

.. & Since the queries results include equality search, a deterministic encryption needs to be used and the reason for answer 'No'

upvoted 3 times

Treadmill

4 years, 11 months ago

B - NO: Issue: Both internal and external client application run complex joins, equality searches and group-by clauses. Because some systems are managed externally, the queries will not be changed or optimized by Contoso Existing Data Protection and Security compliances require that all certificates and keys are internally managed in an on-premises storage. Reasoning: 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. Azure Key Vault is a cloud service that provides a secure store for secrets. https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15#selecting--deterministic-or-randomized-encryption https://docs.microsoft.com/en-us/azure/key-vault/secrets/quick-create-portal

upvoted 6 times

...

VJ8

5 years ago

Master Key can be stored in Azure key Vault or Windows Certificate Store.

upvoted 3 times

...