Pass the Microsoft Certified: Azure Database Administrator Associate DP-300 Questions and answers with ExamsMirror

Scenario: The sales data, including the documents in JSON format, must be gathered as it arrives and analyzed online by using Azure Stream Analytics. The analytics process will perform aggregations that must be done continuously, without gaps, and without overlapping.

Tumbling window functions are used to segment a data stream into distinct time segments and perform a function against them, such as the example below. The key differentiators of a Tumbling window are that they repeat, do not overlap, and an event cannot belong to more than one tumbling window.

Azure Database Migration service

Box 1: Premium 4-VCore

Scenario: Migrate the SERVER1 databases to the Azure SQL Database platform.

Minimize downtime during the migration of the SERVER1 databases.

Premimum 4-vCore is for large or business critical workloads. It supports online migrations, offline migrations, and faster migration speeds.

Configure Dynamic Data Masking on the email column in:

SalesLT.Customer

The column is normally:

EmailAddress

Use the built-in masking function:

email()

Microsoft documents that Dynamic Data Masking hides sensitive data in query results for nonprivileged users without changing the stored data. The built-in email() masking function exposes the first letter and returns the masked format aXXX@XXXX.com, which exactly matches the requirement.

Method 1 — SSMS / T-SQL Method

This is the fastest and most reliable method.

Step 1: Connect to db1

Open SQL Server Management Studio.

Connect to the Azure SQL logical server that hosts db1.

Open a query window against database:

db1

Step 2: Apply the email mask

Run:

ALTER TABLE [SalesLT].[Customer]

ALTER COLUMN [EmailAddress]

ADD MASKED WITH (FUNCTION = ' email() ' );

This adds a Dynamic Data Masking rule to the EmailAddress column. The actual email address remains stored in the table, but users without permission to view unmasked data will see the masked value. Microsoft’s documented syntax for adding an email mask is ALTER COLUMN Email ADD MASKED WITH (FUNCTION = ' email() ' ).

Step 3: Verify that the column is masked

Run:

SELECT

OBJECT_SCHEMA_NAME(mc.object_id) AS schema_name,

OBJECT_NAME(mc.object_id) AS table_name,

c.name AS column_name,

mc.masking_function

FROM sys.masked_columns AS mc

JOIN sys.columns AS c

ON mc.object_id = c.object_id

AND mc.column_id = c.column_id

WHERE OBJECT_SCHEMA_NAME(mc.object_id) = ' SalesLT '

AND OBJECT_NAME(mc.object_id) = ' Customer '

AND c.name = ' EmailAddress ' ;

Expected result:

schema_name SalesLT

table_name Customer

column_name EmailAddress

masking_function email()

Step 4: Test as a non-administrative user

If you have a test user, run:

EXECUTE AS USER = ' TestUser ' ;

SELECT TOP (10)

EmailAddress

FROM SalesLT.Customer;

REVERT;

Expected output should look like:

aXXX@XXXX.com

bXXX@XXXX.com

cXXX@XXXX.com

A user with administrative privileges, db_owner, or UNMASK permission can still see the original email value. Microsoft states that users with administrative rights such as server admin, Microsoft Entra admin, and db_owner can view the original unmasked data.

Method 2 — Azure Portal Method

Use this if the simulation expects portal configuration.

Step 1: Open db1

Sign in to the Azure portal.

Search for SQL databases.

Open database:

db1

Step 2: Open Dynamic Data Masking

From the db1 page:

Security > Dynamic Data Masking

Microsoft states that for Azure SQL Database, Dynamic Data Masking can be configured in the Azure portal from the SQL database configuration pane under Security > Dynamic Data Masking.

Step 3: Add a masking rule

Add a mask for the email column:

Setting

Value

Schema

SalesLT

Table

Customer

Column

EmailAddress

Masking field format

Email

Masking function

email()

Then select:

Save

The portal may show the mask type simply as:

Email

That is the correct option because it maps to the email() masking function.

Important Permission Check

Dynamic Data Masking only affects users who do not have permission to view unmasked data.

If a non-administrative user was previously granted UNMASK, remove it:

REVOKE UNMASK TO [UserName];

Or, if a role was granted UNMASK, revoke it from the role:

REVOKE UNMASK TO [RoleName];

Do not grant UNMASK to normal users. UNMASK allows users to bypass masking and see the original values. Microsoft documents that UNMASK permission controls whether users can view masked or original data.

Final Exam-Lab Action

Run this against db1:

ALTER TABLE [SalesLT].[Customer]

ALTER COLUMN [EmailAddress]

ADD MASKED WITH (FUNCTION = ' email() ' );

Then verify:

SELECT

OBJECT_SCHEMA_NAME(object_id) AS schema_name,

OBJECT_NAME(object_id) AS table_name,

name AS column_name,

masking_function

FROM sys.masked_columns

WHERE OBJECT_SCHEMA_NAME(object_id) = ' SalesLT '

AND OBJECT_NAME(object_id) = ' Customer '

AND name = ' EmailAddress ' ;

The task is complete when non-administrative users querying SalesLT.Customer.EmailAddress see masked email values such as:

aXXX@XXXX.com

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

Create an Azure Private Endpoint for the Azure SQL logical server that hosts db1, place the private endpoint in VNET1, and integrate it with the private DNS zone:

privatelink.database.windows.net

This is the correct solution because Azure SQL Database is a PaaS service. You do not assign a private IP directly to db1. Instead, Azure creates a private endpoint network interface in the virtual network. That private endpoint receives a private IP address from a subnet in VNET1, and clients in VNET1 use that private IP path to reach the SQL server. Microsoft defines a private endpoint as a network interface that uses a private IP address from your virtual network to connect privately to a Private Link resource such as Azure SQL Database.

Azure Portal Method — Recommended for Simulation

Step 1: Identify the SQL logical server that hosts db1

Sign in to the Azure portal.

Search for SQL databases.

Open db1.

On the database overview page, identify the Server name.

The private endpoint is created for the Azure SQL logical server, not for the database object alone. For Azure SQL Database, the Private Link resource type is:

Microsoft.Sql/servers

and the target subresource is:

sqlServer

Microsoft lists Azure SQL Database private endpoint DNS configuration under Microsoft.Sql/servers with subresource sqlServer.

Step 2: Open the SQL server networking page

Open the Azure SQL logical server that hosts db1.

In the left menu, go to:

Security > Networking

Select the Private access tab.

Select Create a private endpoint.

Microsoft’s Azure SQL private endpoint workflow is performed from the SQL server resource under Networking > Private access, where you can create or manage private endpoint connections.

Step 3: Configure the private endpoint basics

On the Create private endpoint page:

Setting

Value

Subscription

Use the lab subscription

Resource group

Use the lab resource group

Name

pe-db1-sql

Region

Same region as VNET1, if possible

The name is not exam-critical. The critical part is that the endpoint is associated with VNET1 and the SQL server that hosts db1.

Step 4: Configure the target resource

On the Resource tab, configure:

Setting

Value

Connection method

Connect to an Azure resource in my directory

Resource type

Microsoft.Sql/servers

Resource

SQL logical server that hosts db1

Target sub-resource

sqlServer

Do not choose storage, VM, managed instance, or any unrelated resource type. This is Azure SQL Database, so the target subresource must be sqlServer.

Step 5: Configure VNET1 and subnet

On the Virtual Network tab:

Setting

Value

Virtual network

VNET1

Subnet

Select an available subnet in VNET1

Private IP configuration

Dynamic is fine unless the lab requires static

Azure will create a network interface for the private endpoint and assign it a private IP address from the selected subnet. Microsoft notes that the network interface page for the private endpoint shows the private IP address assigned to the private endpoint connection.

Step 6: Configure private DNS integration

On the DNS tab:

Enable private DNS zone integration.

Use or create the private DNS zone:

privatelink.database.windows.net

Link the private DNS zone to:

VNET1

This is not optional in a clean exam solution. Without DNS integration, clients may still resolve the SQL server name to the public endpoint instead of the private endpoint. Microsoft states that DNS is critical because it resolves the private endpoint IP address, and for Azure SQL Database the recommended private DNS zone is privatelink.database.windows.net.

Step 7: Review and create

Select Review + create.

Confirm:

Resource: SQL logical server hosting db1

Target subresource: sqlServer

Virtual network: VNET1

Private DNS zone: privatelink.database.windows.net

Select Create.

After deployment, the SQL server will have a private endpoint connection associated with VNET1.

Step 8: Approve the private endpoint connection if required

In most same-directory deployments, approval may be automatic. If approval is pending:

Open the SQL logical server.

Go to:

Networking > Private access

Select the pending private endpoint connection.

Select Approve.

Microsoft documents that SQL administrators can approve or reject private endpoint connections from the SQL server private access page.

Step 9: Optional but recommended — Disable public network access

The task only says you need to connect by private IP from VNET1. It does not explicitly say to block public access. But if the exam expects private-only access, then disable public access after the private endpoint works.

On the SQL logical server:

Go to:

Security > Networking > Public access

Set Public network access to:

Disabled

or select:

Deny public network access

Save.

Be careful: Microsoft states that adding a private endpoint does not automatically block public routing to the logical server. Public access must be denied separately if you want private-only access.

How to Connect from SSMS

You should connect from a machine that is inside VNET1, such as an Azure VM joined to VNET1.

Step 1: Test DNS from a VM in VNET1

From a VM in VNET1, run:

nslookup < sql-server-name > .database.windows.net

Expected result: the name should resolve through the private endpoint path and return a private IP address from VNET1’s address space.

Microsoft explains that connection URLs do not change; DNS resolution is overridden so the existing service FQDN resolves to the private endpoint private IP address.

Step 2: Connect with SSMS

In SSMS, connect using the normal Azure SQL server name:

< sql-server-name > .database.windows.net

Then select database:

db1

Use normal SQL authentication or Microsoft Entra authentication.

Do not type the raw private IP address into SSMS unless the lab specifically forces it. For Azure SQL, the correct operational pattern is to connect to the SQL server FQDN and allow private DNS to resolve that FQDN to the private endpoint IP. Direct IP connection can cause TLS/certificate name problems because the server certificate matches the DNS name, not the private IP.

Verification

The task is complete when all of these are true:

Private endpoint exists for the SQL logical server hosting db1.

Target subresource is sqlServer.

The private endpoint is deployed into VNET1.

A private IP address is assigned to the private endpoint NIC.

Private DNS zone privatelink.database.windows.net exists.

The private DNS zone is linked to VNET1.

The SQL server FQDN resolves to the private endpoint private IP from inside VNET1.

SSMS can connect to db1 from a VM or client connected to VNET1.

Final Exam-Lab Action

Use the Azure portal and configure:

SQL server hosting db1

> Networking

> Private access

> Create private endpoint

Resource type: Microsoft.Sql/servers

Target subresource: sqlServer

Virtual network: VNET1

Private DNS zone: privatelink.database.windows.net

Then connect from a VM or client in VNET1 using:

< sql-server-name > .database.windows.net

That is the correct way to ensure db1 is reachable through a private IP address on VNET1.

SQL injection attacks are a type of cyberattack that exploit a vulnerability in the application code that interacts with the database. An attacker can inject malicious SQL statements into the user input, such as a form field or a URL parameter, and execute them on the database server, resulting in data theft, corruption, or unauthorized access1.

To protect all the databases on sql37006S95 from SQL injection attacks, you need to follow some best practices for securing your application and database layers. Here are some of the recommended steps:

Use parameterized queries or stored procedures to separate the SQL code from the user input. This will prevent the user input from being interpreted as part of the SQL statement and avoid SQL injection23.

Validate and sanitize the user input before passing it to the database. This will ensure that the input conforms to the expected format and type, and remove any potentially harmful characters or keywords4.

Implement least privilege access for the database users and roles. This will limit the permissions and actions that the application can perform on the database, and reduce the impact of a successful SQL injection attack5.

Enable Advanced Threat Protection for Azure SQL Database. This is a feature that detects and alerts you of anomalous activities and potential threats on your database, such as SQL injection, brute force attacks, or unusual access patterns. You can configure the alert settings and notifications using the Azure portal or PowerShell.

These are some of the steps to protect all the databases on sql37006S95 from SQL injection attacks.

To prevent db1 from reverting to the last known good query plan, you need to disable the automatic plan correction feature for the database. This feature is enabled by default and allows the Query Store to detect and fix plan performance regressions by forcing the last good plan1. However, if you want to test the performance of different plans without interference from the Query Store, you can turn off this feature by using the ALTER DATABASE SCOPED CONFIGURATION statement2.

Here are the steps to disable the automatic plan correction feature for db1:

Connect to db1 using SQL Server Management Studio, Azure Data Studio, or any other tool that supports Transact-SQL statements.

Open a new query window and run the following command: ALTER DATABASE SCOPED CONFIGURATION SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = OFF); GO

This command will disable the automatic plan correction feature for db1 and allow the Query Optimizer to choose the best plan based on the current statistics and parameters3.

To verify that the automatic plan correction feature is disabled for db1, you can query the sys.database_scoped_configurations catalog view. The value of the force_last_good_plan column should be 0 for db1.

These are the steps to disable the automatic plan correction feature for db1.

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.

To enable change data capture (CDC) for db1, you need to run the stored procedure sys.sp_cdc_enable_db in the database context. CDC is a feature that records activity on a database when tables and rows have been modified1. CDC can be used for various scenarios, such as data synchronization, auditing, or ETL processes2.

Here are the steps to enable CDC for db1:

Connect to db1 using SQL Server Management Studio, Azure Data Studio, or any other tool that supports Transact-SQL statements.

Open a new query window and run the following command: EXEC sys.sp_cdc_enable_db; GO

This command will enable CDC for the database and create the cdc schema, cdc user, metadata tables, and other system objects for the database3.

To verify that CDC is enabled for db1, you can query the is_cdc_enabled column in the sys.databases catalog view. The value should be 1 for db1.

These are the steps to enable CDC for db1

To generate an email alert to admin@contoso.com when CPU percentage utilization for db1 is higher than average, you can use the Azure portal to create an alert rule based on the CPU percentage metric. Here are the steps to do that:

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

Select Alerts in the Monitoring section and click on New alert rule.

In the Condition section, click Add and select the CPU percentage metric.

In the Configure signal logic page, set the threshold type to Dynamic. This will compare the current metric value to the historical average and trigger the alert when it deviates significantly1.

Set the operator to Greater than, the aggregation type to Average, the aggregation granularity to 1 minute, and the frequency of evaluation to 5 minutes.

Click Done to save the condition.

In the Action group section, click Create and enter a name and a short name for the action group.

In the Notifications section, click Add and select Email/SMS message/Push/Voice.

Enter admin@contoso.com in the Email field and click OK.

Click OK to save the action group.

In the Alert rule details section, enter a name and a description for the alert rule, choose a severity level, and make sure the rule is enabled.

Click Create alert rule to create the alert rule.

This alert rule will send an email to admin@contoso.com when the CPU percentage utilization for db1 is higher than average. You can also add other actions to the alert rule, such as calling a webhook or running an automation script