Exam DP-300 All QuestionsBrowse all questions from this exam
Go to Exam
Question 85

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:

    Correct 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/

Discussion
Backy

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

JG1984

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

sca88

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

scottytohotty

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.

scottytohotty

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

SamBalbij

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

GeoFlux121

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

AlterSchwede

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.

pankajacks

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

des2907

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

BrenFa101

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

palomino

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

Imtiaj

It should be = BDEF