DP-300 Practice Exam 60 — 20 Questions

Question 1

Your company uses Azure SQL Database with Microsoft Defender for Cloud. You receive an alert indicating a potential SQL injection attack. The alert shows that the attack originated from the IP address of your company's VPN gateway. You have verified that no legitimate users are using the VPN at that time. What should you do to immediately stop the attack while preserving legitimate access?

Accepted Answer

Remove the firewall rule that allows the VPN gateway IP address. Option C is correct because the attack is originating from the VPN gateway's IP address, and the firewall rule allowing that IP is the only entry point for the attacker. By removing that specific firewall rule, you immediately block the malicious traffic while preserving legitimate access through other firewall rules (e.g., for on-premises or other VPN ranges). This directly stops the SQL injection attack at the network layer without affecting other users or services.

Answer

Disable the public endpoint on the SQL Database

Answer

Change the SQL Database server admin password

Answer

Add the attacker's IP address to a block list in Microsoft Defender for Cloud

Question 2

Your organization uses Azure SQL Database with Azure SQL Managed Instance for a multi-tenant SaaS application. You need to ensure that each tenant's data is isolated and that a compromised tenant cannot access other tenants' data. What is the most secure approach?

Accepted Answer

Deploy a separate database per tenant, configure server-level firewall rules per tenant IP, and use Microsoft Entra authentication with application roles.. Option A is correct because deploying a separate database per tenant provides the strongest isolation boundary at the storage and compute layer. Combining server-level firewall rules that restrict access to each tenant's IP range and using Microsoft Entra authentication with application roles ensures that even if one tenant's credentials are compromised, the attacker cannot access another tenant's database. This multi-layered approach (network + authentication + authorization) is the most secure for multi-tenant isolation in Azure SQL Managed Instance.

Answer

Use dynamic data masking to obfuscate tenant IDs in query results.

Answer

Implement row-level security (RLS) with a tenant ID filter on all tables.

Answer

Use column-level encryption with Azure Key Vault for sensitive columns.

Question 3

Which TWO of the following are required steps to configure Azure SQL Database to use a customer-managed key (CMK) for Transparent Data Encryption (TDE) with Azure Key Vault? (Choose two.)

Accepted Answer

Set the TDE protector to the Key Vault key via the Azure portal or T-SQL. Option B is correct because setting the TDE protector to the Key Vault key is the step that actually enables customer-managed key (CMK) encryption for Azure SQL Database. This can be done via the Azure portal, PowerShell, Azure CLI, or T-SQL (using ALTER DATABASE SCOPED CONFIGURATION SET TDE_PROTECTOR). Without this step, the database continues to use a service-managed key.

Answer

Create a user-assigned managed identity for the SQL Database server

Answer

Store the encryption key in the SQL Database server's hardware security module (HSM)

Answer

Ensure the SQL Database server is inside the same virtual network as the Key Vault

Question 4

You are a database administrator for a financial services company. You have deployed an Azure SQL Database and configured auditing using the JSON policy shown in the exhibit. After a security incident, you need to review all successful and failed login attempts to the database. However, you notice that login events are not being captured in the audit logs. What is the most likely reason?

Accepted Answer

The audit actions and groups do not include login events. The JSON policy shown in the exhibit defines audit actions and groups, but it does not include the `SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP` or `FAILED_DATABASE_AUTHENTICATION_GROUP` action groups. These groups are required to capture both successful and failed login attempts (authentication events) in Azure SQL Database. Without them, login events are not recorded in the audit logs, regardless of other settings.

Answer

The audit logs are being sent to Azure Monitor instead of blob storage

Answer

The retention days are set too high, causing logs to be truncated

Answer

Auditing is disabled at the server level

Question 5

Refer to the exhibit. You are reviewing the audit settings for an Azure SQL Database. What will this configuration do?

Accepted Answer

Audit logs are written to Azure Monitor (Log Analytics).. The exhibit shows that the audit destination is set to 'Log Analytics' (Azure Monitor), which means audit logs will be sent to a Log Analytics workspace. This configuration does not require a storage endpoint because Log Analytics is the designated target. Option B correctly identifies that audit logs are written to Azure Monitor (Log Analytics).

Answer

Auditing is disabled because no storage endpoint is provided.

Answer

Only failed authentication attempts are audited.

Answer

Audit logs are written to an Azure Storage account.

Question 6

Your company uses Azure SQL Database with Microsoft Entra ID authentication. You need to ensure that only users with specific Azure AD roles can access the database. What should you configure?

Accepted Answer

Create contained database users mapped to Azure AD identities and grant appropriate permissions.. Option D is correct because Azure SQL Database supports contained database users mapped to Azure AD identities, which allows you to grant granular permissions (e.g., db_datareader, db_owner) to specific Azure AD users or groups. This ensures that only those with the appropriate Azure AD roles (via group membership or direct assignment) can access the database, without relying on SQL Server logins or firewall rules.

Answer

Configure an Azure SQL Database firewall rule to allow only Azure AD IP addresses.

Answer

Assign the SQL Server Contributor role to Azure AD users.

Answer

Create SQL authenticated users with strong passwords.

Question 7

You are reviewing a PowerShell script that configures auditing for an Azure SQL Database. The script sets an audit rule with the specified parameters. After running the script, you notice that SELECT operations are not being audited. What is the most likely cause?

Accepted Answer

The AuditActionGroup specified does not capture SELECT operations.. The script likely specifies an AuditActionGroup that does not include the group responsible for capturing SELECT operations. In Azure SQL Database auditing, SELECT operations are captured by the SUCCESSFUL_SCHEMA_OBJECT_ACCESS_GROUP or similar action groups. If the configured AuditActionGroup omits this group, SELECT statements will not be logged, even though other operations may be audited correctly.

Answer

The retention days are set too low, causing logs to be overwritten.

Answer

The storage endpoint is incorrectly formatted.

Answer

The storage account access key is invalid.

Question 8

You are the DBA for a company that uses Azure SQL Managed Instance to host multiple databases for different departments. The security team has mandated that:

- All connections to the managed instance must be encrypted using TLS 1.2 or higher.
- SQL Server authentication must be disabled; only Microsoft Entra authentication is allowed.
- A dedicated audit log must be created for each database to track all DDL changes and all failed login attempts.
- The audit logs must be stored in a central Azure Storage account with 180-day retention.
- Database administrators (DBAs) should not be able to view or modify the audit logs.

You have already set the minimal TLS version to 1.2 and disabled SQL Server authentication. What should you do next to meet the remaining requirements?

Accepted Answer

Enable database-level auditing on each database with audit action groups SCHEMA_OBJECT_CHANGE_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP. Configure the audit log destination to be a central Azure Storage account. Set retention to 180 days. Grant the DBAs 'Storage Blob Data Reader' role on the storage account but not 'Contributor' or 'Owner'.. Option C is correct because database-level auditing with the specified action groups (SCHEMA_OBJECT_CHANGE_GROUP for DDL changes and FAILED_DATABASE_AUTHENTICATION_GROUP for failed logins) meets the requirement to track DDL changes and failed login attempts per database. Configuring the audit log destination to a central Azure Storage account with 180-day retention satisfies the storage and retention requirement. Granting DBAs only the 'Storage Blob Data Reader' role prevents them from viewing or modifying the audit logs, as they cannot delete or overwrite blobs, while still allowing read access if needed.

Answer

Create a SQL Server Agent job on the managed instance that captures DDL changes and failed logins into a table. Grant DBAs access to the table.

Answer

Enable server-level auditing with the same action groups and send logs to a central storage account. Use Azure RBAC to deny DBAs access to the storage account.

Answer

Use Azure SQL Auditing with Azure Monitor Log Analytics workspace. Configure the workspace with 180-day retention. Grant DBAs 'Log Analytics Reader' role.

Question 9

You are a database administrator for a healthcare company that uses Azure SQL Database with Hyperscale tier. The database contains patient records and is critical for operations. You need to automate the process of refreshing the staging database from the production database every night. The refresh process must occur during a maintenance window from 2:00 AM to 4:00 AM. The solution must use point-in-time restore to ensure consistency and must minimize the storage costs. Additionally, the automation must notify the operations team if the refresh fails. What should you do?

Accepted Answer

Create an Azure Automation runbook that performs a point-in-time restore of the production database to a new database, then renames the databases to swap staging. Schedule the runbook during the maintenance window and configure alerts for failure.. Option C is correct. Azure Automation runbooks can schedule a point-in-time restore of the production database to a new database, then swap the names to make it the staging database. This uses the Hyperscale tier's fast restore capabilities and minimizes storage by not keeping multiple copies. Option A is incorrect because Elastic Database Jobs cannot perform restore operations. Option B is incorrect because Data Factory is for ETL, not database restore. Option D is incorrect because using export/import is slower and more expensive than PITR.

Answer

Use Elastic Database Jobs to run a T-SQL script that uses RESTORE DATABASE from a backup file.

Answer

Use Azure Data Factory to copy data from production to staging using a copy activity.

Answer

Use Azure SQL Database export to BACPAC from production and import to staging using Azure Automation.

Question 10

You are the database administrator for a large e-commerce company that uses Azure SQL Database for its transactional systems. The environment consists of 100 databases spread across 10 elastic pools in different regions. You need to implement an automated solution to perform the following tasks every night: (1) Run integrity checks (DBCC CHECKDB) on all databases, (2) Rebuild indexes with fragmentation > 30%, (3) Update statistics with full scan for databases that have had significant data changes (>20% of rows). The solution must minimize manual intervention, provide centralized logging, and be resilient to failures (e.g., if one database fails, the others should continue). Which approach should you use?

Accepted Answer

Create an Elastic Database Job with step scripts for each maintenance task, targeting all databases, and configure retry logic.. Option B is correct because Elastic Database Jobs can run T-SQL scripts across multiple databases with built-in retry and error handling, and can be scheduled. Option A (SQL Agent) is not available on Azure SQL Database. Option C (Azure Automation) requires custom error handling. Option D (Azure Data Factory) is not designed for database maintenance tasks.

Answer

Create a SQL Agent job on each server to run a maintenance script.

Answer

Use Azure Data Factory pipelines with a ForEach activity to execute stored procedures.

Answer

Use Azure Automation runbooks with Invoke-SqlCmd to loop through each database.

Question 11

You are a database administrator for a SaaS company that uses Azure SQL Database with elastic pools. The company has hundreds of databases (one per tenant). You need to automate the deployment of schema changes (e.g., adding new columns, creating indexes) across all tenant databases. The changes must be deployed in a rolling fashion to avoid affecting all tenants at once. The automation must track which databases have been updated and allow for rollback of individual tenant databases if needed. Additionally, the solution must integrate with Azure DevOps CI/CD pipelines. What should you do?

Accepted Answer

Develop an Azure SQL Database project in Visual Studio, generate a DACPAC, and use Elastic Database Jobs with a custom tracking table to apply the DACPAC to each tenant database in batches. Integrate with Azure DevOps to trigger the job after build.. Option D is correct. Using a combination of Azure SQL Database project (DACPAC) for schema definition and Elastic Database Jobs for targeted deployment allows rolling updates per tenant. Azure DevOps can trigger the jobs. Option A is incorrect because Azure Automation is not designed for multi-tenant schema deployment. Option B is incorrect because Data Factory is for data movement, not schema deployment. Option C is incorrect because SQL Agent is not available in Azure SQL Database.

Answer

Use Azure Data Factory with a ForEach activity to execute stored procedures in each database.

Answer

Create an Azure Automation runbook that connects to each database sequentially and runs ALTER TABLE statements.

Answer

Configure SQL Agent jobs on each database to run the schema changes.

Question 12

You are a database administrator for a logistics company that uses Azure SQL Managed Instance. The instance hosts a database that tracks shipments. You need to automate the process of updating statistics for all tables in the database every night. The update should only include tables that have had more than 10% of rows modified since the last statistics update. The automation must be efficient and not rebuild statistics unnecessarily. Additionally, the solution should be self-contained within the Managed Instance and not rely on external services. What should you do?

Accepted Answer

Create a SQL Agent job with a T-SQL script that uses sys.dm_db_stats_properties to identify tables needing statistics updates based on modification counters, then executes UPDATE STATISTICS only on those tables.. Option B is correct. SQL Agent is available on Managed Instance and can execute a stored procedure that checks modification counters (sys.dm_db_stats_properties) and updates only those statistics that meet the threshold. Option A is incorrect because Elastic Database Jobs is not available for Managed Instance. Option C is incorrect because Azure Automation would require a hybrid worker and is less efficient. Option D is incorrect because automatic tuning does not update statistics based on modification percentage; it only manages indexes.

Answer

Enable automatic tuning for the database to automatically update statistics.

Answer

Use Azure Automation with a PowerShell runbook that connects to the instance and runs sp_updatestats.

Answer

Use Elastic Database Jobs to schedule a T-SQL script that updates all statistics unconditionally.

Question 13

You are a database administrator for a company that uses Azure SQL Database. You need to configure a diagnostic setting to send database metrics to a Log Analytics workspace for long-term analysis. The solution should be cost-effective and include metrics like CPU percentage, data IO, and log IO. What should you do?

Accepted Answer

In the Azure portal, add a diagnostic setting for the database to stream 'AllMetrics' to a Log Analytics workspace.. Option C is correct because diagnostic settings can stream metrics to Log Analytics. Option A is wrong because Query Store does not send metrics to Log Analytics. Option B is wrong because DMVs do not stream automatically. Option D is wrong because SQL Insights is a paid solution.

Answer

Enable Azure SQL Insights (preview) for the database.

Answer

Enable Query Store and configure it to export to Log Analytics.

Answer

Create a T-SQL job that periodically inserts sys.dm_db_resource_stats into a table in Log Analytics.

Question 14

You are managing an Azure SQL Database in the General Purpose service tier with 100 DTUs. The database supports an e-commerce application. Over the past week, you notice that CPU usage frequently reaches 100% during peak hours, causing query timeouts. You have identified that the most expensive query is a SELECT statement that joins five tables and returns aggregated sales data. You need to reduce CPU pressure without changing the service tier or adding indexes. What should you do?

Accepted Answer

Use Query Store to identify and force a more efficient execution plan.. Option A is correct because enabling Query Store and forcing a plan that reduces CPU is a non-intrusive way to improve performance. Option B is wrong because it increases DTUs, which changes service tier. Option C is wrong because it adds indexes. Option D is wrong because scaling up is changing tier.

Answer

Scale up to the Business Critical tier.

Answer

Increase the DTUs to 200.

Answer

Add a nonclustered index on the join columns.

Question 15

You are responsible for a set of Azure SQL Databases that are used by different departments in your organization. The databases are deployed in an elastic pool with Standard tier (eDTU 200). Usage patterns show that the marketing database uses high CPU during the day, while the sales database uses high IO at night. You want to optimize costs while ensuring each database gets the resources it needs. What should you do?

Accepted Answer

Configure minimum and maximum eDTU per database in the pool. Option B is correct because configuring minimum and maximum eDTU per database ensures that each database gets guaranteed resources when needed, while still sharing the pool. Option A is wrong because moving to separate standalone databases would increase cost. Option C is wrong because using a vCore pool instead of DTU does not solve the issue inherently. Option D is wrong because adding more databases to the pool increases contention.

Answer

Migrate the pool to a vCore-based elastic pool

Answer

Add more databases to the pool to spread the load

Answer

Move each database to a standalone DTU tier

Question 16

You have a SQL Server on Azure VM that hosts a mission-critical database. The VM is in a single availability zone. You need to achieve a 99.99% SLA for the SQL Server instance. Which two actions should you perform? (Choose two.)

Accepted Answer

Configure SQL Server Always On availability group with synchronous replication.. Option A and C are correct. Deploying two VMs in an availability set (or across zones) and configuring an Always On availability group provides high availability and meets the SLA. Option B is wrong because a single VM with premium disks does not achieve 99.99%. Option D is wrong because FCI with single VM does not provide high availability. Option E is wrong because backup redundancy does not affect availability.

Answer

Enable geo-redundant backup storage.

Answer

Configure a failover cluster instance using Azure shared disks.

Answer

Use a single VM with premium SSD disks.

Question 17

Refer to the exhibit. An administrator wants to ensure that during a regional outage, the failover group automatically fails over without data loss if possible. What is the current configuration gap?

Accepted Answer

The backup storage redundancy is Local, which does not provide geo-redundancy for backups.. Automatic failover with data loss grace period is set to 60 minutes. The backup redundancy is Local, which means backups are stored only in the primary region. If a regional disaster occurs, backups might be lost. Geo-redundant backups would be needed to recover in the secondary region without data loss.

Answer

The failover policy is set to Automatic, but the grace period is too long.

Answer

The secondary managed instance mi2 is in the same region as the primary.

Answer

The read-only endpoint failover policy is Disabled, which prevents read-only traffic after failover.

Question 18

Refer to the exhibit. An Azure SQL Database is receiving Intelligent Insights degradation alerts. Which action should be taken first?

Accepted Answer

Implement automatic tuning recommendations. The database is hitting DTU limits (100% CPU and data IO). The alert recommends scaling up or optimizing queries. Since CPU and data IO are both high, query optimization is the better first step to avoid cost increase.

Answer

Increase the maximum storage size

Answer

Change the service tier to BusinessCritical

Answer

Scale up to Standard S3 (100 DTU)

Question 19

Refer to the exhibit. An administrator tries to restore the database OrdersDB to a point in time 2025-03-14 10:00 UTC and receives error 406. What is the most likely reason?

Accepted Answer

The automated backup frequency is 12 hours, so point-in-time restore is not supported.. Error 406 in Azure SQL Database indicates that the requested restore point is not available because it is outside the backup retention period. The automated backup frequency is 12 hours, so backups are taken every 12 hours. The last backup before 2025-03-14 10:00 would be at 2025-03-14 02:00. The next backup after that is at 2025-03-14 14:00. Since the restore point 10:00 falls between backups, the database cannot be restored to that exact time unless transaction log backups are available. However, the error suggests that the point is not within the retention window. The retention is 7 days, so 2025-03-14 is within 7 days from 2025-03-15. But the backup frequency of 12 hours means that the minimum restore point granularity is 12 hours. The requested time is not aligned with any available backup or log backup. The error 406 specifically means 'The database is not in a state that allows recovery to the specified point in time.' This can happen if the database is in a geo-replication relationship or if the point is not covered by log backups. In this case, the automated backup frequency is 12 hours, so there are no log backups for point-in-time restore. The only available restore points are the full/differential backups at 02:00 and 14:00. Therefore, the requested time 10:00 is not available.

Answer

The database is currently in a geo-replication secondary role.

Answer

The long-term retention policy interferes with point-in-time restore.

Answer

The requested restore point is outside the 7-day retention period.

Question 20

Refer to the exhibit. An administrator creates a geo-replica for SalesDB. After 30 minutes, the replication state remains SEEDING. What is the most likely cause?

Accepted Answer

The primary database SalesDB is not zone-redundant.. The exhibit shows zoneRedundant is false. Geo-replication requires zone redundancy enabled on the primary database because Azure SQL Database geo-replication is only supported for zone-redundant databases.

Answer

The secondary server sqlsrv2 is in a different region than the primary.

Answer

The recovery model is set to Full instead of Simple.

Answer

The secondary database is being used as a read-only replica.