DP-300 Practice Test 19 — 15 Questions

Question 1

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

Accepted Answer

Azure SQL Auditing. Azure SQL Auditing is the correct feature because it tracks database events, including failed login attempts, and writes them to an audit log in an Azure Storage account, Log Analytics workspace, or Event Hubs. This allows you to review and analyze authentication failures for security and compliance purposes. The audit logs capture the exact timestamp, source IP address, and the specific error message for each failed login, such as 'Login failed for user'.

Answer

Azure SQL Advanced Threat Protection

Answer

Azure SQL Database Vulnerability Assessment

Answer

Dynamic Data Masking

Question 2

Your company has an Azure SQL Database that is accessed by multiple applications. You need to implement a security solution that meets the following requirements:
- Each application must have its own database user with specific permissions.
- All authentication must use Microsoft Entra ID.
- You need to be able to rotate credentials for each application without impacting other applications.
- The solution must support automatic credential rotation for service principals.

What should you do?

Accepted Answer

Create a Microsoft Entra ID service principal for each application, store the client secret in Azure Key Vault, and create a contained database user mapped to each service principal.. Creating a separate Microsoft Entra ID service principal per application, storing each client secret in Key Vault, and mapping each to a contained database user gives per-application identity, Entra-only authentication, independent credential rotation, and Key Vault's automatic rotation support. Contained users live in the database, so no server-level login is needed and permissions are scoped per app.

Answer

Use managed identities for each Azure resource and assign permissions to the database.

Answer

Create a single Microsoft Entra ID service principal for all applications and assign different database roles.

Answer

Create SQL logins and users for each application with strong passwords, and configure password rotation policies.

Question 3

Refer to the exhibit. You have configured the automatic tuning policy as shown. After a week, you notice that an index has been dropped automatically, causing a critical query to run slowly. What should you do to prevent this in the future while still benefiting from automatic tuning?

Accepted Answer

Set the dropIndex option state to Disabled in the tuning policy.. The dropIndex option in the automatic tuning policy controls whether SQL Server/Azure SQL can automatically drop indexes it deems unused. Setting dropIndex to Disabled preserves the createIndex and forceLastGoodPlan tuning actions while preventing the risky automatic drop that broke the critical query. This is the targeted fix that keeps automatic tuning benefits without the destructive behavior.

Answer

Manually create the dropped index and mark it as a required index.

Answer

Enable Query Store to track index usage.

Answer

Disable automatic tuning entirely.

Question 4

Which THREE are valid methods to implement disaster recovery for Azure SQL Database? (Select three.)

Accepted Answer

Failover groups. Options A, C, and E are correct. Failover groups and active geo-replication are built-in disaster recovery features for Azure SQL Database that allow failover to a secondary region. Geo-restore (point-in-time restore to a different region) is also a valid DR method by restoring from geo-replicated backups. Option B (Always On availability groups) is a feature for SQL Server on VMs or on-premises, not for Azure SQL Database. Option D (long-term backup retention) is a backup retention capability, not a disaster recovery method.

Answer

Always On availability groups

Answer

Long-term backup retention

Question 5

You have an Azure SQL Database with active geo-replication. You need to monitor the replication lag to ensure the RPO is met. Which metric should you monitor?

Accepted Answer

Replication lag. Active geo-replication in Azure SQL Database continuously ships transaction log records from the primary to the secondary. The 'Replication lag' metric specifically measures the delay between the primary and secondary, which directly maps to your Recovery Point Objective (RPO). Monitoring this metric lets you verify that the secondary is keeping up and that you won't lose more than the acceptable amount of data in a failover.

Answer

DTU consumption

Answer

Data IO percentage

Answer

Deadlocks

Question 6

Which TWO of the following are valid methods to connect to Azure SQL Database securely?

Accepted Answer

Connect using Azure AD authentication with multi-factor authentication.. Option A is correct because Azure SQL Database natively supports Microsoft Entra ID (Azure AD, now Microsoft Entra ID) authentication, and enforcing multi-factor authentication adds a strong identity-verification layer that eliminates static password-only credential risks. Option C is correct because a private endpoint assigns a private IP address from your virtual network to the Azure SQL logical server, so traffic traverses the Microsoft backbone via Azure Private Link instead of the public internet, removing public exposure. Option B is wrong because shared access keys are an Azure Storage construct (used for blobs, queues, tables, files), not an authentication mechanism for Azure SQL Database. Option D is wrong because connecting over the public IP without encryption (no TLS) exposes credentials and data in transit and is not a secure method. Option E is wrong because SQL authentication with a simple password is weak, lacks MFA, and is vulnerable to brute-force and credential-stuffing attacks.

Answer

Connect using a shared access key from Azure Storage.

Answer

Connect directly using the server's public IP address without encryption.

Answer

Connect using SQL authentication with a simple password.

Question 7

Your company has an Azure SQL Database that uses active geo-replication to a secondary region. The primary database is hit by a logical corruption error. You need to restore the database to a point before the corruption occurred with minimal data loss. What should you do?

Accepted Answer

Restore the primary database to a point in time before the corruption using automated backups.. Logical corruption requires point-in-time restore from automated backups to a time before the corruption occurred. Active geo-replication replicates the corruption to the secondary, so it cannot be used to recover. Restoring the primary database to a point in time is the correct approach to minimize data loss.

Answer

Fail over to the secondary region and then fail back.

Answer

Use the geo-replicated secondary to recover the data.

Answer

Seeding the secondary from a backup of the primary.

Question 8

Your Azure SQL Database is configured with Active Geo-Replication to a secondary region for disaster recovery. During a routine failover drill, you notice that after failover, the application cannot connect to the new primary because the login credentials fail. The logins are contained in the master database. What is the most likely cause?

Accepted Answer

The SQL logins in the master database are not replicated to the secondary server.. When Active Geo-Replication is configured for Azure SQL Database, the secondary server is a separate logical server in a different region. The master database, which contains server-level logins, is not replicated as part of geo-replication; only the user databases are replicated. Therefore, after a failover, the new primary server does not have the server-level logins from the original primary, causing authentication failures for applications using those logins.

Answer

The DNS name of the secondary server changed after failover.

Answer

The firewall rules on the secondary server do not allow connections from the application IP.

Answer

The application uses contained database users, which are not replicated.

Question 9

You are migrating an on-premises SQL Server 2012 database to Azure SQL Managed Instance. The database is 5 TB and uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. What is the best approach to migrate while preserving TDE?

Accepted Answer

Back up the certificate and private key to a .pfx file, restore the .pfx to the target managed instance, then restore the database backup.. TDE in SQL Server relies on a certificate (or asymmetric key) that must be present in the target instance to decrypt the database backup. By backing up the certificate and private key to a .pfx file and restoring it to Azure SQL Managed Instance, you ensure the target has the necessary encryption keys to read the backup. Azure SQL Managed Instance supports restoring TDE-protected backups only if the corresponding certificate is first restored into the master database.

Answer

Use Azure Data Studio to import the certificate directly from the local machine store during migration.

Answer

Disable TDE on the source database, migrate the backup, then enable TDE on the target.

Answer

Create a master key in the target managed instance and then restore the database; the certificate will be imported automatically.

Question 10

Your company uses Azure SQL Database. You need to ensure that all connections to the database use TLS 1.2 or higher. Currently, some client applications are connecting using TLS 1.0. What should you do?

Accepted Answer

Set the 'Minimum TLS version' property of the logical server to 1.2.. Setting the 'Minimum TLS version' property of the Azure SQL logical server to 1.2 enforces that all incoming connections must use TLS 1.2 or higher. This server-level setting overrides any client-side configuration, blocking connections that attempt to use TLS 1.0 or 1.1. It is the simplest and most effective way to enforce the minimum TLS version across all client applications without requiring changes to each client.

Answer

Configure the server firewall to block non-TLS traffic.

Answer

Set the 'tls_version' database parameter to 1.2 in the master database.

Answer

Update the client applications to only use TLS 1.2.

Question 11

Drag and drop the steps to configure automatic tuning for an Azure SQL Database in the correct order.

Accepted Answer

Select the Azure SQL Database, then navigate to Automatic tuning settings, then enable the desired options, then save changes.. Automatic tuning is configured via the portal by selecting the database, navigating to automatic tuning, enabling options, and saving.

Answer

Navigate to Automatic tuning settings, then select the Azure SQL Database, then enable the desired options, then save changes.

Answer

Select the Azure SQL Database, then enable the desired options, then navigate to Automatic tuning settings, then save changes.

Answer

Enable the desired options, then select the Azure SQL Database, then navigate to Automatic tuning settings, then save changes.

Question 12

Drag and drop the steps to configure geo-replication for an Azure SQL Database in the correct order.

Accepted Answer

Create secondary server in another region, then configure the secondary database, then initiate replication, then verify the replication link.. Geo-replication requires a secondary server in another region, then configuring the secondary database, initiating replication, and verifying the link. Failover is a separate step.

Answer

Create secondary server in another region, then initiate replication, then configure the secondary database, then verify the replication link.

Answer

Initiate replication, then create secondary server in another region, then configure the secondary database, then verify the replication link.

Answer

Create secondary server in another region, then configure the secondary database, then verify the replication link, then initiate replication.

Question 13

Your company wants to implement transparent data encryption (TDE) for an Azure SQL Database using a customer-managed key stored in Azure Key Vault. Which TWO prerequisites must be met? (Choose two.)

Accepted Answer

The Azure SQL Server must have a system-assigned managed identity.. Option A is correct because Azure SQL TDE with a customer-managed key (BYOK) requires the logical Azure SQL Server to have a managed identity (system-assigned or user-assigned) that can authenticate to Azure Key Vault; the system-assigned managed identity is the standard prerequisite for granting the server access to the key. Option B is correct because the Key Vault must grant that SQL Server identity the required key permissions — typically get, wrapKey, and unwrapKey — via an access policy (or Azure RBAC role assignment) so the server can wrap and unwrap the TDE protector key. Option C is not required: a column master key belongs to Always Encrypted, not TDE, which uses a database encryption key (DEK) protected by a server-level TDE protector. Option D is incorrect because the Key Vault and SQL Server can be in the same region; there is no requirement that they be in different regions. Option E is incorrect because TDE key configuration does not require taking the database offline; the operation is performed online without database downtime.

Answer

The database must contain a column master key.

Answer

The Key Vault must be in a different region than the SQL Server.

Answer

The database must be taken offline during key configuration.

Question 14

You manage an Azure SQL Database that is accessed by several applications. You need to implement the principle of least privilege for database access. Which three actions should you take? (Choose three.)

Accepted Answer

Create contained database users instead of server-level logins.. Contained database users are authenticated directly within the database, independent of the server-level logins. This aligns with the principle of least privilege by avoiding the need for server-level permissions, which would grant broader access across the server. In Azure SQL Database, contained users are the recommended approach for database-level access control, as they limit the blast radius of a compromised credential to a single database.

Answer

Add users to the db_datareader role.

Answer

Configure firewall rules to restrict IP addresses.

Question 15

You are troubleshooting a performance issue on an Azure SQL Database. Which TWO actions should you prioritize to identify the root cause of high resource consumption?

Accepted Answer

Review the Query Store Top Resource Consuming Queries report.. To identify the root cause of high resource consumption, you should use diagnostic tools that analyze query performance. The Query Store's Top Resource Consuming Queries report (D) provides historical insight into which queries consumed the most resources. Additionally, querying sys.dm_exec_query_stats (E) allows you to find queries with high total_worker_time, indicating CPU-intensive queries. Options A (rebuilding indexes) and B (changing recovery model) are corrective actions, not diagnostic. Option C (scaling to a higher service tier) is a reactive mitigation that does not identify the root cause. Therefore, options D and E are the correct prioritized actions.

Answer

Rebuild all indexes to improve query performance.

Answer

Change the database recovery model to Simple.

Answer

Scale the database to a higher service tier to mitigate the issue.