Unlimited Access

Get Unlimited Contributor Access to the all ExamTopics Exams!
Take advantage of PDF Files for 1000+ Exams along with community discussions and pass IT Certification Exams Easily.

Get Unlimited Access

Microsoft Discussions

Exam DP-300 topic 2 question 20 discussion

Actual exam question from Microsoft's DP-300

Question #: 20
Topic #: 2

[All DP-300 Questions]

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 2 years ago

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

upvoted 15 times

SamBalbij

8 months, 1 week ago

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

upvoted 2 times

...

JG1984

1 year, 2 months ago

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

upvoted 6 times

sca88

2 months, 3 weeks ago

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

upvoted 1 times

scottytohotty

6 days, 11 hours 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

8 hours, 54 minutes 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 2 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 11 times

...

AlterSchwede

Most Recent 9 months, 1 week 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

1 year, 5 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

10 months, 4 weeks ago

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

upvoted 1 times

...

des2907

1 year, 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

2 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

2 years, 1 month ago

It should be = BDEF

upvoted 1 times

...

Unlimited Access

Exam DP-300 topic 2 question 20 discussion

Comments

Backy

SamBalbij

JG1984

sca88

scottytohotty

scottytohotty

GeoFlux121

AlterSchwede

BrenFa101

palomino

des2907

pankajacks

Imtiaj

Get IT Certification

New Version GCP Professional Cloud Architect Certificate & Helpful Information

The 5 Most In-Demand Project Management Certifications of 2019