DP-300 Practice Test 29 — 15 Questions

Question 1

You are configuring security for an Azure SQL Database that will be accessed by multiple applications. Each application uses a separate service principal managed in Microsoft Entra ID. You need to ensure that each service principal has the minimum required permissions to access only its own set of tables. What should you implement?

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

Accepted Answer

Create a contained database user for each service principal and grant SELECT, INSERT, UPDATE, DELETE on specific tables.. It creates a contained database user for each service principal (mapped to the Microsoft Entra ID identity) and grants only the specific table-level permissions (SELECT, INSERT, UPDATE, DELETE) required for that application. This follows the principle of least privilege by avoiding broad database roles and ensuring each service principal can only access its own set of tables.

Answer

Create a contained database user for each service principal and grant the db_owner role.

Answer

Create a server-level login for each service principal and assign db_datareader role in the database.

Answer

Configure a server-level firewall rule for each service principal IP address.

Question 2

You run the Azure CLI command shown in the exhibit for an Azure SQL Database named SalesDB. The output shows that the security alert policy is disabled. You need to enable Microsoft Defender for SQL, including vulnerability assessments, for this database. What should you do?

Accepted Answer

Run 'az sql server update --name sql-sales --resource-group rg-sales --enable-defender-for-sql true'.. Enabling Microsoft Defender for SQL, including vulnerability assessments, is a server-level configuration in Azure SQL. The command 'az sql server update --enable-defender-for-sql true' activates Defender for SQL on the server, which automatically applies to all databases on that server, including SalesDB. The exhibit shows a database-level security alert policy is disabled, but the required feature is enabled at the server scope, not per-database.

Answer

Run 'az sql db update --name SalesDB --resource-group rg-sales --server sql-sales --enable-defender-for-sql true'.

Answer

Run 'az sql db threat-policy update --name SalesDB --resource-group rg-sales --server sql-sales --state Enabled'.

Answer

Configure a server firewall rule to allow Azure services.

Question 3

You have an Azure SQL Managed Instance that is experiencing performance degradation. You suspect a query is causing excessive blocking. You need to identify the blocking chain and the resource holding the lock. Which DMV should you query?

Accepted Answer

sys.dm_tran_locks and sys.dm_os_waiting_tasks. To identify the blocking chain and the specific resource holding the lock, you need to combine lock metadata with wait information. sys.dm_tran_locks shows current locks and their resource types (e.g., RID, KEY, PAGE, OBJECT), while sys.dm_os_waiting_tasks reveals which sessions are waiting on those locks and the blocking session ID. Together, these DMVs allow you to trace the blocking chain from the blocked session back to the blocker and pinpoint the exact resource causing contention.

Answer

sys.dm_exec_requests

Answer

sys.dm_exec_query_stats

Answer

sys.dm_tran_active_snapshot_database_transactions

Question 4

Your company uses Azure SQL Database and needs to restrict access to a specific column containing credit card numbers. Only users with the 'CreditCardViewer' role should see the full number; others should see only the last four digits. Which feature should you implement?

Accepted Answer

Dynamic Data Masking. Dynamic Data Masking (DDM) is the correct choice because it allows you to obfuscate sensitive data in query results without changing the underlying database. You can define a mask on the credit card column that shows only the last four digits to users without the 'CreditCardViewer' role, while users with that role can be granted the UNMASK permission to see the full value.

Answer

Always Encrypted

Answer

Row-Level Security

Answer

Column-level security with GRANT

Question 5

You are configuring Azure SQL Database for an e-commerce application that experiences variable traffic. You need to ensure that the database can automatically scale resources based on demand without manual intervention. The solution must also support scaling to zero compute when not in use to save costs. Which Azure SQL Database offering should you use?

Accepted Answer

Azure SQL Database serverless.. Azure SQL Database serverless is the correct choice because it automatically scales compute resources based on demand and supports pausing the database when idle, effectively scaling to zero compute to save costs. This aligns perfectly with the requirements of variable traffic and cost optimization without manual intervention.

Answer

Azure SQL Database elastic pool.

Answer

Azure SQL Database Hyperscale.

Answer

Azure SQL Database Business Critical tier.

Question 6

You are deploying Azure SQL Database for a new application that requires predictable performance and minimal latency. You need to choose a service tier that provides dedicated resources and allows scaling independently for compute and storage. Which service tier should you select?

Accepted Answer

Business Critical. The Business Critical service tier is designed for applications requiring predictable performance and minimal latency, as it provides dedicated resources (CPU and memory) and uses local SSD storage for the database. It also supports independent scaling of compute and storage, allowing you to adjust DTUs or vCores without affecting storage capacity, which aligns with the requirement for independent scaling.

Answer

General Purpose

Answer

Basic

Answer

Hyperscale

Question 7

Your Azure SQL Database uses Always Encrypted to protect sensitive columns. You need to allow a reporting application to query encrypted columns without having access to the column encryption key. What should you configure?

Accepted Answer

Enable Always Encrypted with secure enclaves (Intel SGX) and create an enclave-enabled key.. Enabling Always Encrypted with secure enclaves (Intel SGX) allows the reporting application to perform computations on encrypted columns (e.g., equality, pattern matching) without ever exposing the column encryption key to the application. The enclave decrypts the data inside a trusted execution environment, so the application only sees the results of queries, not the raw encryption keys. This meets the requirement of querying encrypted columns without key access.

Answer

Rotate the column master key to a hardware security module (HSM).

Answer

Remove the Always Encrypted configuration from the columns.

Answer

Provide the application with the column master key and column encryption key.

Question 8

Your company is planning to deploy a new application that uses Azure SQL Database with a Hyperscale service tier. The application requires near-instantaneous scaling of compute resources without downtime. The database will handle large data volumes (over 10 TB). Additionally, the application team wants to use the latest database features, including JSON support and graph queries. Which considerations should you evaluate before choosing Hyperscale?

Accepted Answer

Hyperscale does not support cross-database queries or elastic queries.. Azure SQL Database Hyperscale does not support cross-database queries or elastic queries. This limitation is critical for the application team if they need to query across multiple databases in a single query, which is a common requirement for distributed applications. The Hyperscale architecture separates compute from storage, making cross-database query capabilities unavailable, unlike other service tiers such as General Purpose or Business Critical.

Answer

Hyperscale does not support graph data features such as node and edge tables.

Answer

Hyperscale does not support zone-redundant configuration for high availability.

Answer

Hyperscale does not support JSON functions like JSON_VALUE and JSON_QUERY.

Question 9

A company manages an Azure SQL Database that stores sensitive customer data. The security team mandates that all connections to the database use Azure Active Directory (Azure AD) authentication and that no SQL authentication logins exist. You are tasked with implementing this requirement. What should you do first?

Accepted Answer

Set an Azure Active Directory admin for the Azure SQL Database server.. Before you can enforce Azure AD-only authentication, you must first designate an Azure AD admin for the Azure SQL Database server. This admin is the only identity that can manage Azure AD users and permissions in the database, and once set, you can then remove or disable SQL authentication logins. Without an Azure AD admin, there is no way to authenticate or manage Azure AD principals within the database, making the transition impossible.

Answer

Set the server's 'Public network access' to 'Disabled'.

Answer

Remove the server admin login from the master database.

Answer

Deny the CONNECT permission to all SQL authentication logins.

Question 10

Which TWO of the following are valid methods to secure data in transit for Azure SQL Database?

Accepted Answer

Configure the server to enforce TLS 1.2 by setting minimal TLS version.. Azure SQL Database allows you to enforce a minimum TLS version, such as TLS 1.2, which ensures that all client connections use at least that protocol version. This directly secures data in transit by preventing downgrade attacks and blocking older, less secure TLS versions like TLS 1.0 or 1.1.

Answer

Enable Always Encrypted on sensitive columns.

Answer

Deploy Azure Firewall in front of the database.

Answer

Apply a Network Security Group (NSG) to the subnet.

Question 11

Your organization has an Azure SQL Database server. You need to ensure that only applications running on Azure virtual machines in a specific virtual network can connect to the database. Which THREE actions should you take?

Accepted Answer

Add a virtual network rule for the specific subnet of the VMs.. Adding a virtual network rule for the specific subnet of the VMs allows Azure SQL Database to accept traffic only from that subnet when a service endpoint is enabled. This restricts access to the database to only those applications running on Azure VMs within the specified virtual network, ensuring network-level isolation.

Answer

Add a service endpoint for Azure SQL Database to the virtual network.

Answer

Set the 'Allow Azure Services and resources to access this server' setting to ON.

Question 12

You are designing an automated backup strategy for Azure SQL Database. Which TWO actions satisfy a requirement to maintain daily backups for 30 days and ensure recoverability if the Azure region becomes unavailable?

Accepted Answer

Enable geo-redundant backup storage for the server.. Enabling geo-redundant backup storage (RA-GRS) for Azure SQL Database ensures that automated backups are replicated to a paired Azure region. This satisfies the requirement for recoverability if the primary region becomes unavailable, as backups can be restored from the secondary region. Combined with setting point-in-time restore retention to 30 days (Option C), you maintain daily backups for the full 30-day retention period, meeting both requirements.

Answer

Deploy a read-scale replica in a different Azure region.

Answer

Set up a long-term retention policy with a retention of 30 days.

Answer

Configure active geo-replication to a secondary region.

Question 13

You are reviewing an ARM template for creating a new Azure SQL Database. The template uses the above JSON to create a database named 'db2' from 'db1'. The source database 'db1' is currently in a failed state due to a storage issue. What will be the result of deploying this template?

Accepted Answer

The deployment will fail because db1 is not in a recoverable state.. The ARM template creates a new database by copying from a source database. Azure SQL Database requires the source database to be in an online and healthy state to perform a copy operation. Since db1 is in a failed state due to a storage issue, it is not accessible for copying, so the deployment will fail.

Answer

It will create an empty database because the source is not accessible.

Answer

It will delete db1 and create db2 as a replacement.

Answer

It will create a new database by recovering db1 to its last known good state.

Question 14

Your company is migrating on-premises SQL Server databases to Azure SQL Database. As part of security compliance, you must ensure that all data at rest is encrypted using customer-managed keys stored in Azure Key Vault. Which Azure SQL Database feature should you enable?

Accepted Answer

Transparent Data Encryption with customer-managed keys in Azure Key Vault. Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault is the correct feature because it encrypts SQL Server, Azure SQL Database, and Azure Synapse data files at rest using a customer-controlled key stored in Azure Key Vault. This meets the compliance requirement for encrypting all data at rest with customer-managed keys, as TDE performs real-time I/O encryption and decryption of the database, backups, and transaction log files without requiring application changes.

Answer

Dynamic Data Masking

Answer

Always Encrypted

Answer

Row-Level Security

Question 15

A company uses Azure SQL Managed Instance. They need to automate index maintenance for all databases in the instance. The solution must minimize administrative overhead and use built-in Azure features. What should you do?

Accepted Answer

Create an elastic job agent with a T-SQL script for index maintenance targeting all databases.. An elastic job agent is a built-in Azure feature designed specifically for automating administrative tasks across multiple databases in Azure SQL Managed Instance. It allows you to create a T-SQL script for index maintenance and target all databases in the instance with minimal overhead, as it manages scheduling, retries, and target group membership natively.

Answer

Use Azure Automation with a PowerShell runbook that connects to each database and runs index maintenance.

Answer

Configure a SQL Agent job on the instance to run index maintenance on the master database.

Answer

Use Azure Data Factory to schedule a stored procedure execution for each database.