DP-300 Practice Test 18 — 15 Questions

Question 1

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 2

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database backup policy. The database is used for a reporting workload that is updated daily. The compliance team requires that point-in-time restore (PITR) be available for the past 30 days. What action should you take?

Accepted Answer

Change the retentionDays property to 30.. The retentionDays property in the ARM template controls the point-in-time restore (PITR) retention period. Currently set to 14 days, it must be changed to 30 days to meet the compliance requirement. Option B is incorrect because diffBackupIntervalInHours specifies the interval for differential backups, not retention. Option C is incorrect because 14 days does not satisfy the 30-day requirement. Option D is incorrect because long-term retention (LTR) is for archival purposes beyond PITR and does not replace the PITR retention setting.

Answer

Change the diffBackupIntervalInHours to 24.

Answer

No action is needed; the current policy meets the requirement.

Answer

Add a long-term retention policy with a weekly retention of 30 days.

Question 3

You need to ensure that all users accessing Azure SQL Database from outside the corporate network are required to use multi-factor authentication (MFA). What should you configure?

Accepted Answer

Configure a Conditional Access policy in Microsoft Entra ID.. Conditional Access policies in Microsoft Entra ID (formerly Azure AD) allow you to enforce MFA based on network location, device state, or risk level. By configuring a policy that targets the Azure SQL Database application and requires MFA for all access from outside the corporate network, you meet the requirement without altering the database or server configuration.

Answer

Enable Azure RBAC for the SQL server.

Answer

Create an Azure Policy to require MFA.

Answer

Turn on Transparent Data Encryption (TDE).

Question 4

Your company runs a mission-critical Azure SQL Database in the East US region. To meet an RPO of 5 seconds and an RTO of 30 minutes in the event of a regional outage, which deployment option should you choose?

Accepted Answer

Failover groups with auto-failover policy. Failover groups with auto-failover policy provide automatic failover with an RPO of 5 seconds (default) and RTO of 30 minutes. Active geo-replication requires manual failover, which does not meet the RTO. Zone-redundant configuration only protects within a region. Backup restore has much higher RPO/RTO.

Answer

Zone-redundant configuration

Answer

Point-in-time restore

Answer

Active geo-replication with manual failover

Question 5

You are managing an Azure SQL Database that is experiencing intermittent performance degradation. Query Store shows that a specific query's execution plan changed, causing increased CPU usage. You need to ensure consistent performance without rewriting the application. What should you do?

Accepted Answer

Force the previous query plan using Query Store. Force the previous query plan using Query Store. This approach directly addresses the root cause by locking the query to a known good plan, ensuring consistent performance without application changes. Option A is incorrect because increasing the DTU/service tier may temporarily improve performance but does not fix the plan regression. Option B is incorrect because creating a missing index recommendation may help but does not guarantee the query will use the previous plan. Option C is incorrect because dropping and recreating the index is disruptive and may not force the plan to revert.

Answer

Increase the DTU/service tier of the database

Answer

Create a missing index recommendation

Answer

Drop and recreate the index used by the query

Question 6

You need to audit all successful and failed login attempts on an Azure SQL Database. Which feature should you enable?

Accepted Answer

SQL Server Audit with a server-level audit specification that includes SUCCESSFUL_LOGIN_GROUP and FAILED_LOGIN_GROUP.. SQL Server Audit is the correct feature because it allows you to capture both successful and failed login attempts at the server level by defining a server audit specification that includes the SUCCESSFUL_LOGIN_GROUP and FAILED_LOGIN_GROUP audit action groups. These groups specifically log authentication events, which is exactly what is needed to audit all login attempts. Microsoft Defender for SQL, Vulnerability Assessment, and regulatory compliance dashboards do not provide granular login event auditing.

Answer

Microsoft Defender for SQL threat detection.

Answer

SQL Vulnerability Assessment.

Answer

Enable Microsoft Defender for Cloud's regulatory compliance dashboard.

Question 7

You are configuring security for an Azure SQL Database. You need to ensure that only traffic from a specific virtual network and a specific set of public IP addresses can connect to the database. Which two features should you enable?

Accepted Answer

VNet service endpoints and firewall rules. To restrict access to an Azure SQL Database to traffic from a specific virtual network and a specific set of public IP addresses, you need to combine VNet service endpoints with firewall rules. VNet service endpoints allow you to restrict inbound traffic from a specific subnet in a virtual network, while firewall rules (IP-based) allow you to specify allowed public IP address ranges. Together, they provide a layered network security approach that meets the requirement.

Answer

Microsoft Entra ID authentication and firewall rules

Answer

Advanced Threat Protection and VNet service endpoints

Answer

Private endpoint and VNet service endpoints

Question 8

You are configuring an Azure SQL Database elastic pool for a SaaS application. The pool will host 50 databases with varying workloads. You need to minimize cost while ensuring performance meets baseline requirements. Which tier and configuration should you choose?

Accepted Answer

DTU-based elastic pool with 200 DTUs. A DTU-based elastic pool offers a cost-effective solution for hosting 50 databases with varying workloads. DTU pools provide a shared resource model (eDTUs) that automatically balances capacity across databases, minimizing cost while meeting baseline performance requirements. Serverless tier (Option C) is not supported for elastic pools, making it invalid. Hyperscale (Option A) is designed for large, highly scalable databases and is overkill and costly. Provisioned vCore-based pool (Option B) with 2 vCores may be insufficient for 50 databases and is generally more expensive than DTU-based pools for variable workloads.

Answer

Hyperscale tier with 4 vCores

Answer

Provisioned tier with General Purpose and 2 vCores

Answer

Serverless tier with General Purpose and auto-pause enabled

Question 9

Your organization uses Azure SQL Database and wants to restrict access to only specific on-premises IP addresses. The database has a public endpoint. Which security feature should you configure?

Accepted Answer

Set firewall rules to allow specific on-premises IP ranges.. To restrict access to specific on-premises IP addresses, you should configure firewall rules to allow those IP ranges. Setting a firewall rule ensures that only traffic from allowed IP addresses can reach the database. Option C directly addresses this requirement.

Answer

Enable 'Allow Azure services and resources to access this server' in the firewall settings.

Answer

Enable Always Encrypted with secure enclaves.

Answer

Create a virtual network service endpoint for SQL.

Answer

Configure a private endpoint for the database.

Question 10

You are configuring Azure SQL Database for a multi-tenant application. Each tenant's data is stored in a separate database. You need to ensure that a tenant admin can only manage their own database and not other databases on the same logical server. What is the best approach?

Accepted Answer

Create a contained database user with db_owner role in each tenant's database and use Microsoft Entra authentication.. Creating a contained database user with the db_owner role in each tenant's database, using Microsoft Entra authentication, ensures that the tenant admin can only manage their own database. Contained database users are scoped to the individual database, not the logical server, so they cannot access other databases on the same server. This aligns with the principle of least privilege for multi-tenant isolation.

Answer

Use a server-level firewall rule to restrict access to the tenant's IP.

Answer

Create a server-level login and assign it as db_owner on all databases.

Answer

Create a database-level firewall rule for each tenant database.

Question 11

You are reviewing an Azure Resource Manager template snippet for configuring long-term backup retention for an Azure SQL Database. The deployment fails with an error indicating the storage account is not accessible. What is the most likely cause?

Accepted Answer

The SAS token has expired or is invalid. The error indicates that the storage account is not accessible. In the template, a SAS token is used to authenticate access to the storage container for long-term backup retention. The most common reason for this error is that the SAS token has expired or is invalid, as SAS tokens have an expiration date. Therefore, Option C is correct. Option A is incorrect because the server name mismatch would cause a different error. Option B is incorrect because the URI format may be valid, but the token is the issue. Option D is incorrect because cross-region storage is supported for long-term retention.

Answer

The server name in the template does not match the actual server

Answer

The storage container URI is incorrectly formatted

Answer

The database is not in the same region as the storage account

Question 12

Refer to the exhibit. Which action should you take to improve performance?

Accepted Answer

Force plan 2 using the query store.. When Query Store shows that a specific plan (Plan 2) performs significantly better than the currently used plan, forcing that plan via Query Store is the targeted fix. Query Store's 'Force Plan' feature pins the optimizer to the known-good plan, immediately restoring performance without changing database tier or schema. This is the least invasive and most precise remediation for a plan-regression issue.

Answer

Enable automatic tuning for the query.

Answer

Increase the database's DTU/DTU level.

Answer

Drop and recreate the query's indexes.

Question 13

Your company uses Azure SQL Database and wants to automatically detect and alert on potential SQL injection attacks. Which Azure service should you enable?

Accepted Answer

Microsoft Defender for Cloud (formerly Azure Security Center) with Advanced Threat Protection for Azure SQL Database. Microsoft Defender for Cloud with Advanced Threat Protection for Azure SQL Database is the correct choice because it specifically detects anomalous activities indicating SQL injection attempts, such as unusual SQL queries or patterns, and triggers security alerts. Azure SQL Auditing only logs database events for compliance and forensic analysis, not real-time threat detection. SQL Vulnerability Assessment identifies configuration weaknesses but does not monitor for active attacks. Azure Policy enforces compliance rules but lacks intrusion detection capabilities.

Answer

Azure SQL Auditing

Answer

SQL Vulnerability Assessment

Answer

Azure Policy

Question 14

You have an Azure SQL Database that stores sensitive customer data. You need to ensure that the data is encrypted at rest using a customer-managed key stored in Azure Key Vault. What should you configure?

Accepted Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key from Azure Key Vault.. Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault encrypts the database at rest, using a key that you control and rotate independently. This meets the requirement for encryption at rest with a customer-managed key, as TDE performs real-time I/O encryption and decryption of the data and log files without requiring application changes.

Answer

Configure dynamic data masking (DDM).

Answer

Implement row-level security (RLS) to restrict access.

Answer

Enable Always Encrypted for the sensitive columns.

Question 15

You are deploying Azure SQL Database for a multi-tenant application. Each tenant's data must be isolated. You need to ensure that tenants cannot access each other's data even if there is a SQL injection vulnerability. Which security feature should you implement?

Accepted Answer

Implement row-level security (RLS) with a security policy that filters rows by tenant ID.. Row-level security (RLS) is the correct choice because it enforces data isolation at the database engine level by filtering rows based on a tenant ID predicate. Even if a SQL injection vulnerability allows an attacker to execute arbitrary queries, RLS ensures that only rows belonging to the attacker's tenant are returned, preventing cross-tenant data access. This is a defense-in-depth measure that works regardless of application-layer flaws.

Answer

Use Always Encrypted to encrypt sensitive columns.

Answer

Configure Azure SQL Database auditing to monitor cross-tenant access.

Answer

Enable Transparent Data Encryption (TDE) on the database.