Exam DP-300 All Questions

View all questions & answers for the DP-300 exam

Exam DP-300 topic 2 question 20 discussion

Actual exam question from Microsoft's DP-300

Question #: 20
Topic #: 2

DRAG DROP -
You have an Azure SQL database named DB1. DB1 contains a table that has a column named Col1.
You need to encrypt the data in Col1.
Which four actions should you perform for DB1 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:

Use the following steps for column level encryption:
1. Create a database master key (Step 1)
2. Create a self-signed certificate for SQL Server (Step 2)
3. Configure a symmetric key for encryption (Step 3)
4. Encrypt the column data (this includes Open the symmetric key - Step 4)
5. Query and verify the encryption
Step 1: Create a database master key
Create a database master key for column level SQL Server encryption
In this first step, we define a database master key and provide a password to protect it. It is a symmetric key for protecting the private keys and asymmetric keys.
Step 2: Create a certificate.
Create a self-signed certificate for Column level SQL Server encryption
In this step, we create a self-signed certificate using the CREATE CERTIFICATE statement. You might have seen that an organization receives a certificate from a certification authority and incorporates into their infrastructures. In SQL Server, we can use a self-signed certificate without using a certification authority certificate.
Step 3: Create a symmetric key.
Configure a symmetric key for column level SQL Server encryption.
In this step, we will define a symmetric key that you can see in the encryption hierarchy as well. The symmetric key uses a single key for encryption and decryption as well.

Step 4: Open the symmetric key -
Data encryption.
Let's encrypt the data in this newly added column.
In a query window, open the symmetric key and decrypt using the certificate. We need to use the same symmetric key and certificate name that we created earlier
Etc.
Reference:
https://www.sqlshack.com/an-overview-of-the-column-level-sql-server-encryption/

by Imtiaj at June 5, 2022, 9 p.m.

Comments

Submit Cancel

Backy

Highly Voted 3 years ago

Create a certificate Create a symmetric key Open the symmetric key Update Col1

upvoted 17 times

voodoo_sh

6 months, 2 weeks ago

Create a database master key Create a certificate (fails without created db master key) Create a symmetric key (encrypted by cert.) Open the symmetric key (5th - Update Col1) T-SQL: CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Best_Password789)'; CREATE CERTIFICATE MyCertificate WITH SUBJECT = 'My Encryption Certificate'; CREATE SYMMETRIC KEY MySymmetricKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE MyCertificate; OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY CERTIFICATE MyCertificate; UPDATE YourTable SET Col1 = EncryptByKey(Key_GUID('MySymmetricKey'), Col1);

upvoted 3 times

...

SamBalbij

1 year, 8 months ago

Update col1 is not correct: instead alter table add column. create database master key should be the first step.

upvoted 2 times

...

JG1984

2 years, 2 months ago

https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-ver16

upvoted 7 times

sca88

1 year, 2 months ago

Thanks for this useful documentation link! So the answer provided is correct

upvoted 1 times

scottytohotty

1 year ago

Make sure you read the whole doc... the question is asking to encrypt, not to make sure the db is ready for encryption. You don't need to create the database master key. Steps provided by Backy appear correct.

upvoted 1 times

scottytohotty

12 months ago

Re-evaluating, I think the given answer is correct. Update col1 is problematic with the steps in the article.

upvoted 1 times

...

GeoFlux121

Highly Voted 3 years ago

I believe Create column master key is for ALWAYS ENCRYPTED, so B is not part of this. Looks like provided answer is correct. 1. Create database master key 2. CREATE CERTIFICATE 3. CREATE SYMMETRIC KEY 4. Open symmetric key 5. Update data (have to open symmetric key first, according to sample script on MS article) https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-ver16

upvoted 12 times

...

AlterSchwede

Most Recent 1 year, 9 months ago

5 steps will be needed, not 4. 1. Create database master key 2. CREATE CERTIFICATE 3. CREATE SYMMETRIC KEY 4. Open symmetric key 5. Update data but number 5 (update col) will definitely be needed, whereas step 1 - create database master key - might have already been done? bad question if you could fail on all points even if all 4 answers are in the correct order.

upvoted 4 times

...

BrenFa101

2 years, 4 months ago

How do you know from the wording of the question that the answer is not the Always Encrypted process?

upvoted 1 times

palomino

1 year, 10 months ago

IMHO - I don't see any options for randomized or deterministic amongst the choice of answers

upvoted 1 times

...

des2907

2 years, 7 months ago

I think the answer is ok. At least it follows the definition provided in this examplo https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-ver16#example-encrypt-with-symmetric-encryption-and-authenticator

upvoted 2 times

...

pankajacks

3 years ago

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-ver16#example-encrypt-with-symmetric-encryption-and-authenticator

upvoted 4 times

...

Imtiaj

3 years, 1 month ago

It should be = BDEF

upvoted 1 times

...