Exam DP-200 topic 3 question 1 discussion

Actual exam question from Microsoft's DP-200

Question #: 1
Topic #: 3

DRAG DROP -
You manage security for a database that supports a line of business application.
Private and personal data stored in the database must be protected and encrypted.
You need to configure the database to use Transparent Data Encryption (TDE).
Which five actions should you perform in sequence? To answer, select 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 master key -
Step 2: Create or obtain a certificate protected by the master key
Step 3: Set the context to the company database
Step 4: Create a database encryption key and protect it by the certificate
Step 5: Set the database to use encryption
Example code:
USE master;

GO -
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>'; go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate'; go
USE AdventureWorks2012;

GO -

CREATE DATABASE ENCRYPTION KEY -

WITH ALGORITHM = AES_128 -
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

GO -
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;

GO -
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption

by ZodiaC at May 18, 2021, 5:15 p.m.

Comments

Submit Cancel

mbravo

Highly Voted 4 years ago

The only option that can't be part of the solution is the - CREATE CERTIFICATE AND THEN CREATE THE MASTER KEY because the the master key must be in place before we can create the certificate. Therefore the correct steps (given these options) 1. set context to master database 2. create master key using password 3. set context to company database 4. create the database encryption key 5. enable encryption Missing step between 2 and 3 where we would create a certificate Documentation: USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>'; go CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate'; go USE AdventureWorks2012; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyServerCert; GO ALTER DATABASE AdventureWorks2012 SET ENCRYPTION ON; GO

upvoted 13 times

...

MC_06

Most Recent 4 years ago

1. create a master key 2. create a certificate protected by the master key. 3. create database encryption key 4. Set the database company to use encryption. 5. enable TDE Reference: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15

upvoted 2 times

...