DP-300 Practice Test 33 — 15 Questions

Question 1

You manage an Azure SQL Database named HRDB. The security team requires that all data in transit between the application and HRDB be encrypted, and that the database reject any connections using TLS versions below 1.2. You need to enforce this requirement with the least administrative effort. What should you do?

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

Accepted Answer

Set the 'Minimum TLS version' to 1.2 on the Azure SQL logical server.. The logical server's minimum TLS version setting enforces the required protocol for all connections to databases on that server. This is a server-level configuration that requires no application code changes and ensures clients using TLS 1.0 or 1.1 are rejected. TDE, firewall rules, and Always Encrypted address different security concerns and do not control the TLS version negotiated.

Answer

Configure a firewall rule to allow only the application's IP address.

Answer

Enable Always Encrypted on sensitive columns in HRDB.

Answer

Enable Transparent Data Encryption (TDE) on HRDB.

Question 2

You are the database administrator for a company that uses Azure SQL Database. The security team requires that all data at rest be encrypted with a customer-managed key (CMK) stored in Azure Key Vault, rather than the default service-managed key. You need to implement this requirement with the least administrative overhead. What should you do?

Accepted Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key by configuring the Azure SQL logical server to use a key from Azure Key Vault.. TDE with a customer-managed key at the logical server level is the correct approach because it applies to all databases on the server and uses Azure Key Vault for key storage, satisfying the security team's requirement for customer-managed keys. The other options either do not provide full database encryption, require per-column configuration, or apply to services outside Azure SQL Database.

Answer

Create a database master key (DMK) in each user database and encrypt it with a password.

Answer

Enable Always Encrypted with a column master key stored in Azure Key Vault for all columns in every table.

Answer

Configure Azure Storage Service Encryption (SSE) with a customer-managed key on the storage account that hosts the database files.

Question 3

Your Azure SQL Database is accessed by three separate applications. You must ensure that each application can connect only from its own set of IP addresses, that the addresses are managed centrally without editing each database, and that no application can reach the database over the public internet from any other address. What should you implement?

Accepted Answer

Create server-level firewall rules, one per application, containing that application's IP ranges. Server-level firewall rules are evaluated for the logical server and apply to all its databases, so they centralize address management while letting you define a distinct rule per application. Database-level rules scatter the configuration, the Azure services toggle admits too much, and service endpoints constrain to virtual network subnets rather than per-application address ranges.

Answer

Enable the Allow Azure services and resources to access this server rule and rely on database permissions per application

Answer

Create a database-level firewall rule in each database for the corresponding application's addresses

Answer

Configure a virtual network service endpoint and a network security group that permits the three application subnets

Question 4

Which TWO of the following are benefits of using Azure SQL Database failover groups compared to active geo-replication? (Choose Two.)

Accepted Answer

Failover groups allow you to fail over multiple databases simultaneously.. Option A is correct because a failover group is a container that groups several Azure SQL databases on a logical server, so a single failover operation moves all member databases together, whereas active geo-replication requires failing over each database individually. Option B is correct because failover groups expose read-write and read-only listener endpoints (e.g., <fg-name>.database.windows.net) that stay constant, so applications keep using the same connection string after a failover instead of updating the server name. Option C is not a distinguishing benefit: failover groups support automatic failover policies, but active geo-replication also allows automatic failover, so it is not unique to failover groups. Option D is not correct as a differentiator because active geo-replication also provides readable secondary replicas for read-only workloads. Option E is incorrect because failover groups still rely on geo-replicated storage/backups; they do not remove the need for geo-redundant storage.

Answer

Failover groups automatically fail over without any manual intervention.

Answer

Failover groups provide a readable secondary replica for read-only workloads.

Answer

Failover groups eliminate the need for geo-redundant storage.

Question 5

You are the database administrator for a company that uses Azure SQL Managed Instance named MI1 in the East US region. The company requires a disaster recovery plan that ensures the instance can be failed over to a secondary region with minimal data loss and minimal downtime. The solution must support read-only workloads on the secondary. You need to configure the disaster recovery solution. What should you do?

Accepted Answer

Configure an auto-failover group with a secondary managed instance in West US.. The requirement is for a disaster recovery solution that provides minimal data loss and downtime, and supports read-only workloads on the secondary. Auto-failover groups for Azure SQL Managed Instance are designed for this purpose, offering a readable secondary, automatic failover, and low RPO/RTO. The other options either do not provide a readable secondary, require manual failover, or are not applicable to managed instance.

Answer

Set up geo-redundant backups for MI1.

Answer

Use Azure Site Recovery to replicate the managed instance to West US.

Answer

Create a transactional replication subscriber in West US.

Question 6

You are the Azure SQL Database administrator for a financial services company. The compliance team requires that all data in transit between the application tier and Azure SQL Database be encrypted, and that the server enforce a minimum TLS version of 1.2. The application servers run Windows Server 2019 and use the Microsoft.Data.SqlClient provider. You need to configure the server so that only TLS 1.2 connections are accepted. What should you do?

Accepted Answer

Set the Minimum TLS version to 1.2 on the Azure SQL logical server's networking settings.. Azure SQL Database encrypts connections by default, but the minimum TLS version accepted by the gateway is controlled by the logical server's Minimum TLS version setting. Raising it to 1.2 causes the gateway to refuse any connection that negotiates a lower version, which is exactly what the compliance requirement demands. Firewall rules, private endpoints, and TDE address different concerns.

Answer

Configure a private endpoint for the Azure SQL Database and disable public network access.

Answer

Enable Transparent Data Encryption (TDE) on the database with a customer-managed key.

Answer

Add a firewall rule that allows only the application servers' IP addresses.

Question 7

You are the administrator for an Azure SQL Database. The security team requires that all authentication to the database use Microsoft Entra ID (formerly Azure AD) and that multi-factor authentication (MFA) be enforced. You need to configure the database to meet this requirement. What should you do first?

Accepted Answer

Set a Microsoft Entra ID admin for the Azure SQL logical server.. The first step to enable Microsoft Entra ID authentication for Azure SQL Database is to set a Microsoft Entra ID admin on the logical server. This admin can then manage Entra ID identities and create contained database users. MFA enforcement is achieved through Conditional Access policies in Microsoft Entra ID, but those policies only apply once Entra ID authentication is enabled. Therefore, setting the Entra ID admin is the prerequisite.

Answer

Enable Microsoft Entra ID authentication in the Azure SQL Database firewall settings.

Answer

Configure a Conditional Access policy to require MFA for all users.

Answer

Create a contained database user for each Microsoft Entra ID user.

Question 8

You manage an Azure SQL Database that contains a table with sensitive columns. You need to implement Dynamic Data Masking so that users in the 'Reporting' database role see masked values, while users in the 'DataEntry' role see unmasked values. You have created the masking rules. Which two actions should you perform to meet the requirement? (Choose two.)

Accepted Answer

Ensure the 'Reporting' role does not have the UNMASK permission.. Dynamic Data Masking is enforced through the UNMASK permission. Users without UNMASK see masked data; users with UNMASK see the original values. To allow the DataEntry role to see unmasked data, you grant UNMASK to that role. To ensure the Reporting role sees masked data, you must verify that the role (and its members) does not have UNMASK. These two actions together satisfy the requirement without altering the masking rules.

Answer

Alter the masking rules to use the 'default()' function for the sensitive columns.

Answer

Create a separate database user for each member of the 'Reporting' role.

Answer

Enable auditing on the database to track who views masked data.

Question 9

You administer an Azure SQL Database that contains a table named dbo.Employees with columns for Social Security Number and salary. Company policy requires that support staff querying the table see only the last four digits of the Social Security Number and a masked salary value, while the payroll application, which connects with a different login, must see the actual values. You need to implement this with the least administrative effort and without changing the application queries. What should you do?

Accepted Answer

Apply Dynamic Data Masking rules to the SSN and salary columns and grant the support staff login SELECT on the table.. Dynamic Data Masking is designed for exactly this scenario: it masks column values for users who lack the UNMASK permission while leaving the data intact and visible to privileged logins. Because masking is applied by the engine during query execution, no application changes are needed, and the payroll login with UNMASK sees the real values.

Answer

Encrypt the SSN and salary columns with Always Encrypted and distribute the column master key only to the payroll application.

Answer

Create a view that returns masked values and grant support staff SELECT on the view instead of the table.

Answer

Create a row-level security policy that filters rows based on the support staff login.

Question 10

Which TWO actions are required to enable Microsoft Entra ID authentication for an Azure SQL Database?

Accepted Answer

Set an Microsoft Entra ID admin for the Azure SQL Server.. Option B is correct because enabling Microsoft Entra ID authentication for Azure SQL Database requires provisioning a Microsoft Entra ID administrator at the Azure SQL logical server level, which establishes the trust relationship between the server and the Entra ID tenant. Option C is correct because after the Entra ID admin is set, you must create contained database users in the target database that are mapped to Entra ID identities (for example, CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER), since Entra principals authenticate at the database level via contained users rather than server-level logins. Option A is incorrect because enabling SQL Server authentication only is the opposite of what is needed and does not enable Entra ID authentication. Option D is incorrect because assigning the SQL Server Contributor RBAC role grants Azure management-plane permissions, not data-plane authentication rights inside the database. Option E is incorrect because there is no separate 'Microsoft Entra ID integration' toggle to enable on the SQL server; the Entra ID admin setting itself establishes the integration.

Answer

Enable SQL Server authentication only.

Answer

Assign the SQL Server Contributor role to the Entra ID users.

Answer

Enable Azure AD integration on the SQL server.

Question 11

Which THREE of the following are required to automate schema deployments to Azure SQL Database using Azure DevOps? (Select exactly three.)

Accepted Answer

A release pipeline with a 'Azure SQL Database deployment' task.. Option A is correct because the 'Azure SQL Database deployment' task in an Azure DevOps release pipeline is the built-in mechanism that executes the DACPAC/BACPAC against the target Azure SQL Database, making it essential for automation. Option B is correct because a SQL database project (.sqlproj) declaratively defines the schema and is compiled into a DACPAC, which is the artifact the deployment task consumes to apply schema changes. Option C is correct because the pipeline needs an Azure service connection (ARM service connection) with sufficient permissions to authenticate and deploy to the Azure SQL Database. Option D is not required because the Azure SQL Database deployment task uses SqlPackage/DACPAC deployment natively and does not depend on a separate schema compare tool. Option E is not required because Microsoft-hosted agents already include the necessary SQL tooling, so a self-hosted agent with SQL tools is optional rather than mandatory.

Answer

A schema compare tool to generate deployment scripts.

Answer

A self-hosted build agent with SQL tools installed.

Question 12

You are the database administrator for a financial services company using Azure SQL Database. The security team mandates that all administrative activities on the SQL logical server be performed using just-in-time (JIT) access with approval workflows, and that permanent elevated permissions be eliminated. You need to implement this requirement with the least amount of custom development. What should you use?

Accepted Answer

Microsoft Entra Privileged Identity Management (PIM) for Azure resources, assigning the SQL Server Contributor role as eligible.. Microsoft Entra Privileged Identity Management (PIM) is the native Azure service for just-in-time privileged access. By assigning roles such as SQL Server Contributor as eligible, administrators must activate the role with approval, and the assignment is time-bound. This eliminates permanent elevated permissions and meets the JIT and approval requirements with minimal custom development. Auditing, custom functions, and contained users do not provide the required approval workflows and JIT activation.

Answer

Custom Azure Functions that grant and revoke database roles based on approval emails.

Answer

SQL Database contained users with temporary passwords that expire after a short period.

Answer

Azure SQL Database auditing with Log Analytics alerts for administrative actions.

Question 13

Which THREE metrics should you monitor to proactively detect potential performance issues in an Azure SQL Database?

Accepted Answer

Log IO percentage (sys.dm_db_resource_stats). Option A (Log IO percentage via sys.dm_db_resource_stats) is correct because this DMV reports resource utilization such as log write percentage against the service-tier limits, so sustained high log IO percentage signals throttling and impending performance degradation. Option D (Wait statistics via sys.dm_os_wait_stats) is correct because aggregating wait types reveals where sessions are blocked or stalled (for example PAGEIOLATCH, CXPACKET, or WRITELOG), which is the standard method for diagnosing the root cause of slow performance. Option E (Query Store for query performance regressions) is correct because Query Store persists query plans, runtime statistics, and execution history, letting you proactively detect plan regressions and parameter-sniffing issues before users report them. Option B (Log backup frequency) is not a performance metric for Azure SQL Database, since log backups are managed automatically by the platform and their frequency is not exposed as a tunable performance indicator. Option C (Database size and growth rate) is a capacity-planning metric rather than a real-time performance signal, so it does not proactively reveal latency, blocking, or resource-throttling issues.

Answer

Log backup frequency

Answer

Database size and growth rate

Question 14

You are planning a disaster recovery strategy for an Azure SQL Database that is part of a failover group. The application requires that after a failover, the database is accessible with minimal downtime and without data loss. Which THREE components are essential for this configuration? (Select three.)

Accepted Answer

Secondary database in a paired region with data synchronization. Option B is correct because a failover group requires a secondary database in a paired Azure region that continuously synchronizes data from the primary, which enables minimal-downtime failover with no data loss (RPO near zero). Option C is correct because the failover group listener endpoint provides a stable read-write connection string that automatically redirects clients to the current primary after failover, so applications avoid reconfiguration and downtime is minimized. Option E is correct because the failover group itself must be configured between the primary and secondary servers, defining the databases, replication topology, and automatic/manual failover policies that make the DR strategy work. Option A is not essential here because geo-redundant backup storage supports backup/restore recovery, not the low-RTO, no-data-loss failover provided by failover groups. Option D is not essential because Azure Traffic Manager is a DNS-based global traffic routing service and does not manage Azure SQL Database replication or failover; the failover group listener already handles client redirection.

Answer

Geo-redundant backup storage

Answer

Azure Traffic Manager with priority routing

Question 15

You are monitoring an Azure SQL Database. You need to identify which two metrics are most important for detecting a memory pressure issue. Which TWO should you select?

Accepted Answer

Memory grants pending. Memory grants pending (B) is a key indicator of memory pressure because it counts the number of queries waiting for a workspace memory grant; a sustained nonzero value means SQL Server cannot satisfy concurrent memory requests, directly signaling memory contention. Page life expectancy (C) measures how long pages stay in the buffer pool, and a low or declining PLE indicates that data pages are being evicted too quickly, which is a classic symptom of buffer pool memory pressure. Together these two metrics isolate memory-related stress rather than I/O or CPU behavior. Log IO percentage (A) and Data IO percentage (E) reflect storage throughput/latency, and CPU percentage (D) reflects processor utilization, so none of them directly diagnose memory pressure.

Answer

Log IO percentage

Answer

CPU percentage

Answer

Data IO percentage