Microsoft DP-300 - Administering Relational Databases on Microsoft Azure

Use SQL Server Management Studio Always Encrypted Wizard and encrypt:

Database: db1

Schema: SalesLT

Table: Customer

Column: LastName

Encryption feature: Always Encrypted

Column master key store: Windows Certificate Store

Do not use Transparent Data Encryption. TDE encrypts the database at rest, but this task specifically requires Always Encrypted, which protects selected columns and keeps the encryption keys outside the database engine. Microsoft states that Always Encrypted uses column encryption keys to encrypt column data and column master keys to protect those column encryption keys. Column master keys are stored outside the database system, such as in the Windows certificate store.

Method 1 — SSMS Always Encrypted Wizard

This is the correct method for the simulation.

Step 1: Open SSMS and connect to Azure SQL Database

Open SQL Server Management Studio.

Connect to the Azure SQL logical server that hosts db1.

Use a SQL admin account or Microsoft Entra admin account.

In Options > Connection Properties, select database:

db1

Connect.

If SSMS cannot connect, go to the Azure portal and add your client IP address under the SQL server firewall/networking settings.

Step 2: Open the table column

In Object Explorer:

Expand Databases.

Expand db1.

Expand Tables.

Expand:

SalesLT.Customer

Expand Columns.

Locate:

LastName

Microsoft confirms that the Always Encrypted Wizard can be launched at the database, table, or individual column level. For one column, the cleanest path is to launch it directly from the column.

Step 3: Launch the Always Encrypted Wizard

Right-click the LastName column, then select:

Encrypt Column

or, depending on the SSMS version:

Always Encrypted Wizard

Alternative path:

Right-click db1 > Tasks > Always Encrypted Wizard

Then manually select:

SalesLT.Customer.LastName

Step 4: Select the LastName column for encryption

On the Column Selection page:

Find:

SalesLT.Customer.LastName

Select the checkbox for LastName.

Set the encryption type.

Use:

Randomized

unless the lab specifically requires searching or equality filtering on LastName.

Reason: Randomized encryption is stronger because identical plaintext values produce different ciphertext values. Deterministic encryption allows equality lookups, joins, grouping, and indexing, but leaks more pattern information because identical plaintext values produce identical encrypted values. Microsoft describes deterministic encryption as query-friendly but more pattern-revealing, while randomized encryption is more secure but does not support normal searching/grouping/joining without secure enclaves.

For this task, the requirement is only to encrypt the LastName column, so Randomized is the safer default.

Step 5: Choose or create a Column Encryption Key

For Encryption Key, select a new key such as:

CEK_Auto1

or create a new column encryption key if one does not already exist.

This is the key that encrypts the data in the LastName column. Microsoft states that a column encryption key encrypts the data in encrypted columns, and the column master key encrypts/protects the column encryption key.

Select Next.

Step 6: Configure the Column Master Key in Windows Certificate Store

On the key configuration page, create a new Column Master Key.

Use settings like:

Setting

Value

Column master key name

CMK_WindowsCert or default generated name

Key store

Windows Certificate Store

Certificate location

Current User

Certificate

Generate new certificate

Column encryption key

CEK_Auto1 or default generated CEK

In many SSMS versions, the wizard creates both:

CMK_Auto1

CEK_Auto1

That is acceptable as long as the CMK key store is Windows Certificate Store.

Microsoft states that SQL Server Management Studio supports column master keys stored in the Windows Certificate Store, and that a column master key can be a certificate stored in Windows Certificate Store.

Step 7: Run the wizard

On the final wizard pages:

Review the configuration.

Choose:

Proceed to finish now

or:

Run immediately

Select Finish.

SSMS will generate the column master key metadata, column encryption key metadata, and perform the data encryption operation. Microsoft explains that the wizard can encrypt selected columns and can generate a new column master key and column encryption key when needed.

During encryption, SSMS may temporarily create a new table, copy data, encrypt the selected column, and swap the table back, depending on whether secure enclaves are used. Microsoft notes that the wizard can move data out of the database and perform cryptographic operations inside the SSMS process when secure enclave in-place encryption is not used.

Verification Steps

Step 1: Confirm Always Encrypted metadata exists

Run this in db1:

SELECT

name,

key_store_provider_name,

key_path

FROM sys.column_master_keys;

You should see a column master key that uses the Windows certificate store provider.

Then run:

SELECT

name

FROM sys.column_encryption_keys;

You should see the column encryption key created by the wizard.

Step 2: Confirm LastName is encrypted

Run:

SELECT

t.name AS table_name,

c.name AS column_name,

c.encryption_type_desc,

cek.name AS column_encryption_key

FROM sys.columns AS c

JOIN sys.tables AS t

ON c.object_id = t.object_id

LEFT JOIN sys.column_encryption_keys AS cek

ON c.column_encryption_key_id = cek.column_encryption_key_id

WHERE t.name = ' Customer '

AND SCHEMA_NAME(t.schema_id) = ' SalesLT '

AND c.name = ' LastName ' ;

Expected result:

table_name: Customer

column_name: LastName

encryption_type_desc: RANDOMIZED

column_encryption_key: CEK_Auto1 or similar

If you selected deterministic encryption, the expected value will be:

DETERMINISTIC

The key requirement is that encryption_type_desc is no longer NULL.

Step 3: Test viewing the encrypted column

Open a new SSMS query connection without Always Encrypted enabled and run:

SELECT TOP 10 LastName

FROM SalesLT.Customer;

You should not see normal plaintext values.

Then reconnect with Always Encrypted enabled:

In SSMS, select Connect > Database Engine.

Select Options.

Go to Additional Connection Parameters.

Add:

Column Encryption Setting=Enabled

Connect again.

Run:

SELECT TOP 10 LastName

FROM SalesLT.Customer;

A client that has access to the Windows certificate/private key should be able to decrypt the values. Microsoft explains that the database stores encrypted data and key metadata, while client-side components with access to the column master key perform encryption and decryption.

Important Exam Notes

Do not choose Azure Key Vault

The task explicitly says:

You must use the Windows Certificate Store.

So the column master key should not be stored in Azure Key Vault.

Wrong:

Azure Key Vault

Correct:

Windows Certificate Store

Do not use TDE

TDE is not column-level Always Encrypted. It encrypts database files/logs at rest, but users and administrators querying the database still see plaintext if they have SQL permissions.

Correct technology:

Always Encrypted

Correct key store:

Windows Certificate Store

Correct target column:

SalesLT.Customer.LastName

To ensure that all queries executed against dbl are captured in the Query Store, you need to enable the Query Store feature for the database and set the query capture mode to ALL. The Query Store feature provides you with insight on query plan choice and performance for Azure SQL Database1. The query capture mode controls whether all queries or only a subset of queries are tracked2.

Here are the steps to enable the Query Store and set the query capture mode to ALL for the database dbl:

Using the Azure portal:

Go to the Azure portal and select your Azure SQL Database server.

Select the database dbl and click on Query Performance Insight in the left menu.

Click on Configure Query Store and turn on the Query Store switch.

In the Query Capture Mode dropdown, select All and click on Save.

Using Transact-SQL statements:

Connect to the Azure SQL Database server and the database dbl using SQL Server Management Studio or Azure Data Studio.

Run the following command to enable the Query Store for the database: ALTER DATABASE dbl SET QUERY_STORE = ON;

Run the following command to set the query capture mode to ALL for the database: ALTER DATABASE dbl SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL);

These are the steps to ensure that all queries executed against dbl are captured in the Query Store.

The lifecycle management policy lets you:

Delete blobs, blob versions, and blob snapshots at the end of their lifecycles

Scenario: Contoso requirements for the sales transaction dataset include:

Implement a surrogate key to account for changes to the retail store addresses.

A surrogate key on a table is a column with a unique identifier for each row. The key is not generated from the

table data. Data modelers like to create surrogate keys on their tables when they design data warehouse models. You can use the IDENTITY property to achieve this goal simply and effectively without affecting load performance.

Box 1: Hash

Scenario:

Ensure that queries joining and filtering sales transaction records based on product ID complete as quickly as possible.

A hash distributed table can deliver the highest query performance for joins and aggregations on large tables.

Box 2: Round-robin

Scenario:

You plan to create a promotional table that will contain a promotion ID. The promotion ID will be associated to a specific product. The product will be identified by a product ID. The table will be approximately 5 GB.

A round-robin table is the most straightforward table to create and delivers fast performance when used as a staging table for loads. These are some scenarios where you should choose Round robin distribution:

When you cannot identify a single key to distribute your data.

If your data doesn’t frequently join with data from other tables.

When there are no obvious keys to join.

Basic Concept: This question tests choosing the correct Azure migration approach by matching the source platform, target service, downtime tolerance, and administrative effort.

Why the selected answer is Correct: The selected values fit this scenario because they apply the required Azure SQL configuration at the correct scope and sequence: The solution must use the highest level of security when connecting to storage1.

Why the alternate choices are Wrong: The alternate selections would either use a migration tier that does not match the downtime requirement, choose a network resource that does not support the migration path, or add an unnecessary deployment component.

Box 1: Azure AD authentication

Azure Active Directory authentication supports Multi-Factor authentication through Active Directory Universal Authentication.

Box 2: Contained database users

Azure Active Directory Uses contained database users to authenticate identities at the database level.

Columnstore tables and indexes are always stored with columnstore compression. You can further reduce the size of columnstore data by configuring an additional compression called archival compression.

Note: Columnstore — The columnstore index is also logically organized as a table with rows and columns, but the data is physically stored in a column-wise data format.

Box 1: CREATE EXTERNAL TABLE

An external table points to data located in Hadoop, Azure Storage blob, or Azure Data Lake Storage. External tables are used to read data from files or write data to files in Azure Storage. With Synapse SQL, you can use external tables to read external data using dedicated SQL pool or serverless SQL pool.

Syntax:

CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }

( < column_definition > [ ,...n ] )

WITH (

LOCATION = ' folder_or_filepath ' ,

DATA_SOURCE = external_data_source_name,

FILE_FORMAT = external_file_format_name

Box 2. OPENROWSET

When using serverless SQL pool, CETAS is used to create an external table and export query results to Azure Storage Blob or Azure Data Lake Storage Gen2.

Example:

AS

SELECT decennialTime, stateName, SUM(population) AS population

FROM

OPENROWSET(BULK ' https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=*/*.parquet ' ,

FORMAT= ' PARQUET ' ) AS [r]

GROUP BY decennialTime, stateName

GO