Enable Automatic tuning at the server level and turn Create Index to On.

Microsoft states that Azure SQL Database automatic tuning can create indexes automatically, verify performance improvement, and roll back changes if performance regresses. Server-level automatic tuning settings are applied to all databases on the server by default, unless a database has its own override.

Azure Portal Method — Recommended for Simulation

Step 1: Open the SQL logical server

Sign in to the Azure portal.

Search for SQL servers.

Open:

sql60152867

Do not open only db1. The task says all databases on sql60152867, so configure this at the server level.

Step 2: Open Automatic tuning

In the SQL server left menu:

Go to Intelligent Performance.

Select Automatic tuning.

Microsoft’s documented path is to open the Azure SQL server in the portal and select Automatic tuning from the menu.

Step 3: Enable automatic index creation

On the Automatic tuning page, configure:

Setting

Value

Create index

On

Drop index

Leave unchanged unless required

Force last good plan

Leave unchanged unless required

The only required setting is:

Create index = On

Do not confuse this with Drop index. The task only asks to ensure indexes are created automatically.

Step 4: Apply the setting

Select Apply or Save.

Wait for the portal confirmation.

The setting is now configured at the SQL logical server level.

Important Database Inheritance Check

This is the part people miss.

Server-level automatic tuning applies to databases that inherit from the server. Microsoft states that individual databases can override server-level automatic tuning settings. Therefore, if the simulation shows any database with custom automatic tuning settings, set that database to Inherit from server or manually set Create index = On for that database.

For each database, the correct inherited state should be:

Automatic tuning = Inherit from server

Create index = Inherited / On

If a database is set to:

Custom

Create index = Off

then the server-level setting might not affect that database. Fix it.

Database-Level T-SQL Method

Use this only if the portal is unavailable or you need to correct a specific database override.

Connect to each database and run:

ALTER DATABASE CURRENT

SET AUTOMATIC_TUNING (

CREATE_INDEX = ON

);

Microsoft documents this exact syntax for enabling the CREATE_INDEX automatic tuning option by T-SQL on an individual Azure SQL Database.

To make an individual database inherit server settings, run:

ALTER DATABASE CURRENT

SET AUTOMATIC_TUNING = INHERIT;

But this must be executed inside each database, not from master as a single server-wide T-SQL command.

Verification with T-SQL

Connect to a database and run:

SELECT

name,

desired_state_desc,

actual_state_desc

FROM sys.database_automatic_tuning_options

WHERE name = ' CREATE_INDEX ' ;

Expected result:

name CREATE_INDEX

desired_state_desc ON or DEFAULT

actual_state_desc ON

If desired_state_desc is DEFAULT, that usually means the database is inheriting the server-level setting. The key result is:

actual_state_desc = ON

SSMS Clarification

SSMS can be used to verify or configure the setting per database using T-SQL, but it is not the best tool for the requirement as written.

Because the task says:

all the databases on sql60152867

the correct primary method is:

Azure portal > SQL server > Automatic tuning > Create index = On

Final Exam-Lab Action

Configure this:

SQL server: sql60152867

Automatic tuning

Create index: On

Apply

Then verify databases are inheriting the server setting or have CREATE_INDEX = ON.

That completes the task.

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

Create an Azure Monitor metric alert rule on database db1 using the metric:

CPU percentage

Configure the condition as:

Aggregation: Average

Operator: Greater than

Threshold: 50

Aggregation granularity / Period: 1 minute

Frequency of evaluation: 1 minute

Evaluation period / Lookback window: 5 minutes

Action group email: admin@contoso.com

Azure SQL Database exposes CPU percentage as a platform metric, and Azure Monitor metric alerts can send notifications through action groups such as email. Microsoft describes SQL Database alerts as metric-based alerts that can send email when metrics such as CPU usage reach a defined threshold.

Azure Portal Method — Recommended for Simulation

Step 1: Open the db1 Azure SQL database

Sign in to the Azure portal.

Search for SQL databases.

Open the database named:

db1

Do not open the SQL logical server unless the alert needs to apply to all databases. This task is specifically for db1, so the alert scope must be the db1 database resource.

Step 2: Create a new alert rule

From the db1 database page:

In the left menu, select Alerts.

Select Create.

Select Alert rule.

Microsoft’s Azure Monitor workflow allows you to create an alert rule directly from the target resource. When you create it from a resource, the resource is automatically set as the alert scope.

Step 3: Confirm the alert scope

On the alert rule page, confirm the scope is the Azure SQL database:

db1

The resource type should be similar to:

SQL database

Microsoft.Sql/servers/databases

If the scope is the SQL server instead of the database, remove it and select the db1 database resource.

Step 4: Add the alert condition

Under Condition, select Add condition.

Choose the metric:

CPU percentage

Sometimes it appears as:

CPU percent

or metric name:

cpu_percent

For Azure SQL Database, CPU percentage represents CPU consumption toward the database workload limit, expressed as a percentage.

Step 5: Configure the signal logic

Configure the alert logic exactly like this:

Setting

Value

Threshold type

Static

Aggregation type

Average

Operator

Greater than

Threshold value

50

Unit

Percent

Aggregation granularity / Period

1 minute

Frequency of evaluation

1 minute

Evaluation period / Lookback window

5 minutes

This means Azure Monitor evaluates the CPU metric every minute, using one-minute metric samples, and fires the alert only when the average CPU percentage is greater than 50% across the five-minute evaluation window.

Be precise here. The task says:

average CPU percentage utilization is greater than 50 percent for five minutes sampled at one-minute intervals

So the correct choices are:

Average

Greater than 50

Every 1 minute

Over the last 5 minutes

Azure Monitor metric alert rules combine the monitored resource, the metric condition, and action groups that run when the condition is met.

Step 6: Create the email action group

Under Actions, select:

Create action group

Configure the basics:

Setting

Value

Subscription

Use the current subscription

Resource group

Use the lab resource group

Action group name

AG-db1-CPU-Email

Display name

db1CPU

Then go to Notifications.

Add a notification:

Setting

Value

Notification type

Email/SMS message/Push/Voice

Name

EmailAdmin

Email

admin@contoso.com

Select OK, then Review + create, then Create.

Action groups define the notification or automation action that runs when an alert fires. Microsoft documents email as a supported action group notification type.

Step 7: Configure alert rule details

Under Details, configure:

Setting

Value

Severity

2 or 3

Alert rule name

db1 CPU greater than 50 percent

Description

Alert when average CPU percentage for db1 is greater than 50 percent for 5 minutes.

Enable upon creation

Yes

Severity is usually not specified by the task, so any reasonable severity is acceptable. In an exam lab, I would use Severity 2 for CPU performance impact.

Step 8: Review and create

Select Review + create.

Confirm the condition says something equivalent to:

Whenever the average CPU percentage is greater than 50

Confirm the evaluation settings are:

Check every 1 minute

Lookback period 5 minutes

Confirm the action group sends email to:

admin@contoso.com

Select Create.

Verification

After creation:

Open db1.

Go to Alerts.

Select Alert rules.

Confirm the alert rule exists and is enabled.

Open the rule and verify:

Scope: db1

Signal: CPU percentage

Aggregation: Average

Operator: Greater than

Threshold: 50

Evaluation frequency: 1 minute

Window size: 5 minutes

Action group: admin@contoso.com

Azure CLI Method

Use this only if the simulation gives you Cloud Shell.

First get the database resource ID:

az sql db show \

--resource-group < resource-group-name > \

--server < sql-server-name > \

--name db1 \

--query id \

--output tsv

Create the action group:

az monitor action-group create \

--resource-group < resource-group-name > \

--name AG-db1-CPU-Email \

--short-name db1CPU \

--action email EmailAdmin admin@contoso.com

Create the metric alert:

az monitor metrics alert create \

--name " db1 CPU greater than 50 percent " \

--resource-group < resource-group-name > \

--scopes < db1-resource-id > \

--condition " avg cpu_percent > 50 " \

--window-size 5m \

--evaluation-frequency 1m \

--action AG-db1-CPU-Email \

--description " Alert when average CPU percentage for db1 is greater than 50 percent for 5 minutes. "

The metric name commonly used for Azure SQL Database CPU percentage in CLI/ARM contexts is:

cpu_percent

SSMS Clarification

SSMS is not the correct tool for this task.

Do not configure Database Mail. Azure SQL Database does not use SQL Server Agent/Database Mail in the same way as SQL Server on a VM or SQL Managed Instance. This requirement is an Azure Monitor metric alert requirement.

Correct tool:

Azure portal > db1 > Alerts > Create alert rule

Wrong tool:

SSMS Database Mail

Final Exam-Lab Configuration

Use this exact configuration:

Resource: db1

Alert type: Metric alert

Metric: CPU percentage

Aggregation: Average

Operator: Greater than

Threshold: 50

Aggregation granularity: 1 minute

Evaluation frequency: 1 minute

Evaluation period/window: 5 minutes

Action group notification: Email

Email recipient: admin@contoso.com

That completes the task.

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.

Basic Concept: This question tests high availability and disaster recovery design for Azure SQL, SQL Server on Azure VMs, and regional failure scenarios.

Why C is Correct: a distributed network name (DNN) resource is correct because it is the feature whose normal purpose matches the stated requirement. a distributed network name (DNN) resource is part of the availability or recovery design space, but the correct choice must satisfy the specified failover, restore, quorum, RPO, or RTO requirement. The scenario wording points to that specific behavior: You need to ensure that client applications can connect to FCI1.

Why A is Wrong: a virtual network name (VNN) resource is part of the availability or recovery design space, but the correct choice must satisfy the specified failover, restore, quorum, RPO, or RTO requirement. It does not meet the failover, restore, quorum, or cross-region continuity target stated in the question, even if it is valid in a different availability design.

Why B is Wrong: a Basic Azure Load Balancer is part of the availability or recovery design space, but the correct choice must satisfy the specified failover, restore, quorum, RPO, or RTO requirement. It handles a different resilience pattern and would not deliver the failover or recovery behavior required here.

Why D is Wrong: an Azure Standard Load Balancer is part of the availability or recovery design space, but the correct choice must satisfy the specified failover, restore, quorum, RPO, or RTO requirement. It does not meet the failover, restore, quorum, or cross-region continuity target stated in the question, even if it is valid in a different availability design.

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.

Enable Azure SQL Auditing at the server level on the Azure SQL logical server:

sql60152867

and configure the audit destination as the Azure Storage account:

sa60152867

This is the correct solution because the task says all databases on sql60152867. Database-level auditing would only apply to one database. Server-level auditing applies to all existing and newly created databases on the logical server. Microsoft states that server auditing policies apply to all existing and newly created databases on the server. The default auditing policy includes BATCH_COMPLETED_GROUP, which audits queries and stored procedures executed against the database, plus successful and failed authentication events.

Azure Portal Method — Recommended for Simulation

Step 1: Open the Azure SQL logical server

Sign in to the Azure portal.

Search for:

SQL servers

Open the SQL logical server named:

sql60152867

Be careful with the name. In your prompt, it appears as sql60l 52867, but the earlier tasks strongly indicate the real server name is probably:

sql60152867

Use the SQL logical server that hosts the databases, not a single SQL database.

Step 2: Open Auditing

On the SQL server page:

In the left menu, go to Security.

Select Auditing.

Microsoft’s setup path is to navigate to Auditing under the Security heading in either the SQL database or SQL server pane. For this task, you must use the SQL server pane because the requirement covers all databases.

Step 3: Enable server-level auditing

Set:

Enable Azure SQL Auditing = On

or:

Auditing = On

depending on the portal wording.

This enables the server-level audit policy.

Step 4: Select Storage as the audit destination

Under Audit log destination, select:

Storage

Then choose the storage account:

sa60152867

Microsoft states that Azure SQL auditing can write database events to an Azure storage account, Log Analytics workspace, or Event Hubs. For this simulation, the destination must be the storage account sa60152867, so do not choose Log Analytics or Event Hub unless the task separately asks for them.

Step 5: Configure storage authentication

If the portal asks for authentication type, use one of these depending on what is available in the lab:

Option

Use when

Managed Identity

Preferred if available

Storage access keys

Use if the lab expects the older/default storage-key method

Microsoft recommends managed identity for storage authentication because storage access keys are a security risk. However, many exam simulations still accept the default storage-key flow as long as the audit destination is correctly set to the required storage account.

Step 6: Leave default audit action groups enabled

Do not remove the default audit action groups.

The default Azure SQL auditing policy includes:

BATCH_COMPLETED_GROUP

SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP

FAILED_DATABASE_AUTHENTICATION_GROUP

BATCH_COMPLETED_GROUP is the key one here because it captures completed T-SQL batches, meaning the queries executed against the databases. Authentication and principal fields identify the login/user context. Microsoft states that the default auditing policy audits all queries and stored procedures executed against the database, plus successful and failed logins.

Step 7: Save the auditing configuration

Select:

Save

Wait for the portal notification that the auditing settings were saved successfully.

That completes the required configuration.

Verification

Verify from the Azure portal

Open the SQL server:

sql60152867

Go to:

Security > Auditing

Confirm:

Auditing: On

Destination: Storage

Storage account: sa60152867

Scope: Server-level

Verify storage output

After users execute queries, audit files should appear in the storage account.

Open storage account:

sa60152867

Go to Containers.

Open:

sqldbauditlogs

Azure SQL audit logs written to Azure Storage are stored in a container named sqldbauditlogs. Audit logs are written as .xel files and can be opened with SQL Server Management Studio.

What Information Is Captured?

The audit log includes the exact fields needed for this task.

Required information

Audit log field

Query executed

statement / statement_s

Database name

database_name / database_name_s

User context

database_principal_name / database_principal_name_s

Login context

server_principal_name / server_principal_name_s

Session principal

session_server_principal_name / session_server_principal_name_s

Time of execution

event_time / event_time_t

Client IP

client_ip / client_ip_s

Microsoft’s audit log format documents statement as the T-SQL statement that was executed, database_principal_name as the database user context, and server_principal_name as the current login.

SSMS Method — Viewing the Audit Logs

SSMS is not the main tool to enable Azure SQL auditing in this simulation, but it can be used to read the .xel audit files after they are written.

Step 1: Download or access the .xel files

From the storage account:

sa60152867 > Containers > sqldbauditlogs

Download the .xel audit file, or access it through supported tooling.

Step 2: Open the audit file in SSMS

Open SQL Server Management Studio.

Go to:

File > Open > File

Select the .xel audit file.

Review fields such as:

statement

database_principal_name

server_principal_name

database_name

event_time

client_ip

This verifies that the queries and the users that executed them are being captured.

Azure CLI Method

Use this only if Cloud Shell is available and you know the resource group names.

az sql server audit-policy update \

--resource-group < resource-group-name > \

--name sql60152867 \

--state Enabled \

--storage-account sa60152867

Depending on the environment, you may also need to specify the storage endpoint and access key, or use managed identity configuration. In the portal simulation, the UI normally handles this for you.

PowerShell Method

Use this if Azure PowerShell is available.

Set-AzSqlServerAudit `

-ResourceGroupName " < resource-group-name > " `

-ServerName " sql60152867 " `

-BlobStorageTargetState Enabled `

-StorageAccountResourceId " /subscriptions/ < subscription-id > /resourceGroups/ < storage-rg > /providers/Microsoft.Storage/storageAccounts/sa60152867 "

This configures auditing at the server level, which is the correct scope for all databases.

Final Exam-Lab Action

Configure this in the Azure portal:

SQL server: sql60152867

Security > Auditing

Auditing: On

Audit destination: Storage

Storage account: sa60152867

Default audit action groups: Enabled

Save

That captures queries and the users who executed them for all databases on the SQL logical server and stores the audit records in sa60152867.

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

To configure your user account as the Azure AD admin for the server named sql3700689S, you can use the Azure portal or the Azure CLI. Here are the steps for both methods:

Using the Azure portal:

Go to the Azure portal and select SQL Server – Azure Arc.

Select the server named sql3700689S and click on Active Directory admin.

Click on Set admin and choose your user account from the list of Azure AD users.

Click on Select and then Save to confirm the change.

You can verify the Azure AD admin by clicking on Active Directory admin again and checking the current admin.

Using the Azure CLI:

Install the Azure CLI and log in with your Azure account.

Run the following command to get the object ID of your user account: az ad user show --id < your-user-name > --query objectId -o tsv

Run the following command to set your user account as the Azure AD admin for the server: az sql server ad-admin create --server sql3700689S --object-id < your-object-id > --display-name < your-user-name >

You can verify the Azure AD admin by running the following command: az sql server ad-admin show --server sql3700689S

These are the steps to configure your user account as the Azure AD admin for the server named sql3700689S.