DP-300 Practice Test 24 — 15 Questions

Question 1

An Azure SQL Database contains personally identifiable information (PII). You need to mask the PII columns from non-administrative users while allowing administrators to see the actual data. Which feature should you use?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Dynamic Data Masking. Dynamic Data Masking (DDM) is the correct choice because it selectively obscures sensitive PII columns in query results for non-administrative users, while leaving the data unmasked for users with elevated permissions (e.g., db_owner or the UNMASK permission). This is achieved by defining masking rules on specific columns, such as email or phone number, without altering the underlying stored data.

Answer

Always Encrypted

Answer

Transparent Data Encryption (TDE)

Answer

Row-Level Security

Question 2

You have an Azure SQL Database with a heavy workload. You notice that the `PAGEIOLATCH_SH` wait is the top wait. Which performance issue does this indicate?

Accepted Answer

I/O subsystem bottleneck. The `PAGEIOLATCH_SH` wait type indicates that a query is waiting for a data page to be read from disk into the buffer pool. Since this is the top wait, it points to an I/O subsystem bottleneck where the storage cannot keep up with the demand for reading pages, causing performance degradation.

Answer

Blocking

Answer

CPU bottleneck

Answer

Memory pressure

Question 3

You are monitoring an Azure SQL Database and notice a pattern of high CPU usage during business hours. You need to identify the queries consuming the most CPU over the last 24 hours. Which dynamic management view should you query?

Accepted Answer

sys.dm_exec_query_stats. sys.dm_exec_query_stats (Option C) is the correct DMV because it returns aggregate performance statistics for cached query plans, including total CPU time (total_worker_time), execution count, and last execution time. By querying this view and ordering by total_worker_time descending, you can identify the queries that have consumed the most CPU over the last 24 hours, directly addressing the pattern of high CPU usage during business hours.

Answer

sys.dm_exec_requests

Answer

sys.dm_exec_sessions

Answer

sys.dm_os_performance_counters

Question 4

Your company is migrating a mission-critical application from SQL Server 2019 on-premises to Azure SQL Managed Instance. The database is 1.5 TB and has 50 databases that need to be migrated. The application uses SQL Server Agent jobs, Service Broker, and cross-database queries. The security requirements mandate that the migration must use Microsoft Entra ID authentication, and all data must be encrypted at rest using customer-managed keys in Azure Key Vault. The migration window is limited to 4 hours of downtime. You need to choose the migration method and configure the target environment. What should you do?

Accepted Answer

Use Azure Database Migration Service online migration, configure Managed Instance with customer-managed TDE, and enable Entra ID authentication.. Azure Database Migration Service (DMS) online migration supports minimal downtime for large databases (1.5 TB) and 50 databases, while maintaining transactional consistency for cross-database queries and Service Broker. Configuring the Managed Instance with customer-managed TDE (Transparent Data Encryption) using Azure Key Vault meets the encryption-at-rest requirement, and enabling Microsoft Entra ID authentication satisfies the security mandate. The online migration mode allows continuous synchronization during the migration window, with a final cutover within the 4-hour downtime limit.

Answer

Use Azure Database Migration Service offline migration with automatic cutover.

Answer

Deploy a new Managed Instance, configure TDE with customer-managed keys, and use transactional replication to synchronize data.

Answer

Use Azure backup to URL and restore to Managed Instance, then configure TDE with customer-managed keys.

Question 5

Which THREE of the following are required to configure Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault for Azure SQL Database?

Accepted Answer

The key encryption key must be stored in Azure Key Vault.. Transparent Data Encryption (TDE) with customer-managed keys requires the key encryption key (KEK) to be stored in Azure Key Vault. This KEK is used to protect the database encryption key (DEK), which is stored in the database boot record. Without the KEK in Key Vault, the customer-managed key scenario cannot be implemented, as Azure SQL Database must be able to access the key to perform encryption operations.

Answer

The Azure Key Vault must be in the same region as the database.

Answer

The SQL server must have an RBAC role assignment on the key vault.

Question 6

You are designing a disaster recovery strategy for an Azure SQL Database that requires a Recovery Point Objective (RPO) of less than 5 seconds and a Recovery Time Objective (RTO) of less than 1 hour. Which option should you choose?

Accepted Answer

Use active geo-replication and plan manual failover.. Active geo-replication provides a continuous replication stream with an RPO of less than 5 seconds and supports manual failover, which can achieve an RTO of less than 1 hour when the failover is initiated promptly. This option meets the strict RPO and RTO requirements by maintaining a readable secondary in a different Azure region that is kept nearly synchronized with the primary.

Answer

Use auto-failover groups with read-write failover policy.

Answer

Use geo-restore from geo-redundant backups.

Answer

Use long-term retention (LTR) backups with daily frequency.

Question 7

You are implementing row-level security (RLS) in Azure SQL Database to enforce data isolation for multiple tenants. Each tenant has a TenantID. The RLS predicate function uses the SESSION_CONTEXT variable set by the application. You need to ensure that users cannot bypass the RLS by setting arbitrary SESSION_CONTEXT values. What should you do?

Accepted Answer

Create a stored procedure that sets the SESSION_CONTEXT and grant the application only execute permission on that procedure; revoke SET permission from users. By creating a stored procedure that sets the SESSION_CONTEXT and granting the application only EXECUTE permission on that procedure while revoking SET permission from users, you prevent users from arbitrarily setting SESSION_CONTEXT values. This enforces that only the trusted application code can set the context used by the RLS predicate, ensuring tenants cannot impersonate other tenants.

Answer

Use dynamic data masking to hide the TenantID column

Answer

Encrypt the SESSION_CONTEXT values using Always Encrypted

Answer

Use a firewall rule to restrict access to the database to only the application IP addresses

Question 8

You are configuring a private endpoint for an Azure SQL Database. The exhibit shows the current network ACLs. You need to ensure that only traffic from a specific subnet in VNet1 is allowed, and all other traffic is denied. What should you do?

Accepted Answer

No changes needed; the configuration already meets the requirement.. The exhibit shows that the private endpoint is configured with a network ACL that has a deny-all default action and an explicit allow rule for the specific subnet in VNet1. Since private endpoints use network policies (like NSG rules) to filter traffic, and the ACL already denies all traffic except the allowed subnet, no changes are needed. The configuration meets the requirement because the private endpoint's network ACLs are evaluated in order, and the explicit allow for the subnet overrides the default deny for all other traffic.

Answer

Add an IP rule to allow the subnet's IP range.

Answer

Set ignoreMissingVnetServiceEndpoint to true.

Answer

Change defaultAction to Allow.

Question 9

You need to ensure that all connections to an Azure SQL Database use encryption. The application uses the JDBC driver. What should you configure in the connection string?

Accepted Answer

Add 'encrypt=true' and 'trustServerCertificate=false'. For JDBC connections to Azure SQL Database, setting 'encrypt=true' enforces TLS encryption for data in transit, and 'trustServerCertificate=false' ensures that the server's TLS certificate is validated against the trusted certificate authority (CA) chain, preventing man-in-the-middle attacks. This is the recommended configuration for secure connections to Azure SQL Database.

Answer

Add 'hostNameInCertificate=*.database.windows.net'

Answer

Add 'encrypt=false' and 'trustServerCertificate=true'

Answer

Add 'useEncryption=true' and 'validateServerCertificate=true'

Question 10

Your company has a strict policy that Azure SQL Database backups must be encrypted with customer-managed keys stored in Azure Key Vault. You configure TDE with AKV integration. After a key rotation, you find that long-running queries start failing with encryption errors. What is the most likely cause?

Accepted Answer

The previous key version was disabled or deleted in AKV.. When TDE is configured with Azure Key Vault (AKV) integration, the database uses the current key version from AKV to encrypt and decrypt data. If the previous key version is disabled or deleted during a key rotation, any long-running queries that still rely on that specific key version for decryption will fail with encryption errors. The database cannot automatically fall back to the new key version for in-flight operations that started before the rotation.

Answer

The service principal used for AKV access has expired.

Answer

The new key is in a different Azure region than the database.

Answer

The database is using service-managed TDE and cannot switch to customer-managed keys.

Question 11

You are configuring security for an Azure SQL Database that will be used by a web application. The application uses a connection string with SQL authentication. You need to protect the database from SQL injection attacks. Which two measures should you implement? (Choose two.)

Accepted Answer

Use parameterized queries in the application.. Parameterized queries ensure that user input is treated as data, not executable code, by separating SQL logic from input values. This prevents attackers from injecting malicious SQL statements into the query string, which is the primary defense against SQL injection attacks.

Answer

Enable Transparent Data Encryption (TDE).

Answer

Configure Dynamic Data Masking (DDM).

Answer

Enable Always Encrypted on sensitive columns.

Question 12

Your company has an Azure SQL Managed Instance that hosts multiple databases. You need to implement a solution to automatically detect and alert on potential SQL injection attacks. The solution must integrate with Microsoft Sentinel for incident response. What should you configure?

Accepted Answer

Enable Microsoft Defender for SQL on the Managed Instance. Microsoft Defender for SQL on Azure SQL Managed Instance provides built-in SQL injection detection and alerting, which can be integrated directly with Microsoft Sentinel for automated incident response and investigation. This is the correct solution because it offers native vulnerability assessment and threat detection tailored to SQL databases, meeting the requirement for both detection and Sentinel integration.

Answer

Enable Microsoft Purview Data Map for the Managed Instance

Answer

Configure Microsoft Defender XDR for SQL

Answer

Use Microsoft Intune to manage SQL security policies

Question 13

Refer to the exhibit. An Azure SQL Database in the Standard tier (S2: 50 DTU) is consistently showing high DTU consumption. Which action would most effectively reduce DTU usage?

Accepted Answer

Create an index on the tables accessed by Query 1234. Query 1234 is likely the primary contributor to high DTU consumption, as indicated by the exhibit (not shown here but implied). Creating an index on the tables it accesses can reduce the number of logical reads and improve query performance, directly lowering DTU usage without additional cost. This is the most effective action because it addresses the root cause—poor query performance—rather than masking the symptom with more resources.

Answer

Increase the log_write_percent by adjusting transaction log settings

Answer

Scale up to a higher service tier (e.g., S3)

Answer

Rebuild all indexes in the database

Question 14

You are designing a disaster recovery plan for an Azure SQL Managed Instance in the East US region. The plan must meet an RPO of 15 minutes and an RTO of 1 hour. You need to minimize costs while meeting these requirements. Which solution should you recommend?

Accepted Answer

Configure a failover group between two General Purpose instances in different regions.. A failover group between two General Purpose instances in different regions provides geo-replication with an RPO of seconds to minutes and an RTO of about 1 hour, meeting the requirements at minimal cost. Option A is incorrect because zone-redundant redundancy only protects against zonal failures within the same region, not regional disasters. Option B is incorrect because deploying a second instance with zone redundancy in the same region does not provide cross-region DR. Option D is incorrect because using Business Critical instances is more expensive and not required for the specified RPO/RTO.

Answer

Use a zone-redundant Business Critical instance.

Answer

Deploy a second instance in the same region with zone redundancy.

Answer

Configure a failover group between two Business Critical instances in different regions.

Question 15

You are configuring Azure SQL Database for a new application. The security policy requires that all connections use Microsoft Entra authentication and that the database blocks IP addresses from outside your corporate network. You also need to ensure that the application can connect without storing credentials in code. Which combination of features should you implement?

Accepted Answer

Microsoft Entra authentication, firewall rules, and managed identity. It satisfies all three requirements: Microsoft Entra authentication enforces identity-based access, firewall rules block IP addresses outside the corporate network, and a managed identity allows the application to connect without storing credentials in code by using a system-assigned or user-assigned identity to obtain an access token from Microsoft Entra ID.

Answer

Always Encrypted, VNet service endpoints, and SQL authentication

Answer

Transparent Data Encryption, IP firewall rules, and connection strings

Answer

Azure Defender for SQL, firewall rules, and service principal