DP-300 Practice Exam 52 — 20 Questions

Question 1

Your Azure SQL Managed Instance stores sensitive healthcare data. You need to restrict access to the database from public networks while allowing a specific on-premises application to connect. The on-premises network has a static public IP address. What is the most secure way to configure connectivity?

Accepted Answer

Enable the public endpoint and create a firewall rule allowing only the on-premises static IP address. Enabling the public endpoint with a firewall rule that restricts access to only the on-premises static IP address is the most secure way to allow a specific external application to connect while blocking all other public traffic. This approach leverages Azure SQL Managed Instance's built-in public endpoint firewall, which evaluates source IP addresses against configured rules before allowing connections, ensuring that only the trusted on-premises application can reach the sensitive healthcare data.

Answer

Enable the public endpoint without any firewall rules

Answer

Configure a service endpoint on the VNet where the Managed Instance is deployed

Answer

Disable the public endpoint and configure a point-to-site VPN for the on-premises application

Question 2

You are configuring Microsoft Defender for SQL for Azure SQL Database. You need to ensure that alerts are sent to the security operations team via email and also integrated with Microsoft Sentinel. What should you configure?

Accepted Answer

In Microsoft Sentinel, connect the Azure SQL Database data source using the built-in connector.. Option A is correct because Microsoft Sentinel provides a built-in connector specifically for Azure SQL Database that directly ingests security alerts from Microsoft Defender for SQL. This connector enables seamless integration without requiring additional components, and you can configure email notifications for the security operations team directly within the Defender for SQL alert settings. This satisfies both requirements: alerts are sent via email and streamed to Sentinel for centralized monitoring and incident response.

Answer

Use Azure Logic Apps to forward Defender for SQL alerts to Sentinel.

Answer

Configure a diagnostic setting on the SQL server to stream logs to a Log Analytics workspace used by Sentinel.

Answer

Configure the alert rule in Defender for SQL to send email to the security team.

Question 3

Which TWO actions are valid for implementing column-level encryption in Azure SQL Database using Always Encrypted? (Choose two.)

Accepted Answer

Use randomized encryption for columns that will not be searched.. Always Encrypted supports two encryption types: deterministic and randomized. Randomized encryption is valid for columns that will not be searched because it encrypts the same plaintext into different ciphertexts each time, preventing pattern-based attacks. This makes it suitable for sensitive data like credit card numbers or personal identifiers that only need to be decrypted for use, not queried with equality predicates.

Answer

Store the column encryption key in the database.

Answer

Use a hash of the column value for encryption.

Answer

Encrypt an entire row by specifying a row-level encryption key.

Question 4

Your organization needs to comply with a regulation that requires data to be encrypted at rest using a customer-managed key stored in Azure Key Vault. You have an Azure SQL Database. What should you configure?

Accepted Answer

Enable Transparent Data Encryption (TDE) and use a customer-managed key in Azure Key Vault as the TDE protector.. Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault meets the requirement for data at rest encryption using a customer-controlled key. TDE encrypts the database files (data and log files) at rest, and by using a customer-managed key as the TDE protector, you retain control over key rotation and revocation, satisfying regulatory compliance.

Answer

Use Azure Policy to enforce encryption at rest.

Answer

Enable Always Encrypted and store the column master key in Azure Key Vault.

Answer

Configure Azure Storage Service Encryption for the database files.

Question 5

You are a database administrator for an Azure SQL Database. You need to allow a user to restore a database from a backup to a new database, but the user should not have permission to delete the original database. What is the minimum permission required?

Accepted Answer

CREATE DATABASE permission on the logical server. The minimum permission required to restore a database from a backup to a new database in Azure SQL Database is the CREATE DATABASE permission on the logical server. This is because restoring a backup to a new database is essentially creating a new database, and the user does not need any additional permissions on the original database to perform this action. The db_backupoperator and db_owner roles grant excessive permissions on the original database, while the SQL Server Contributor role is an Azure RBAC role that provides broader management capabilities beyond what is needed.

Answer

db_backupoperator database role

Answer

db_owner database role

Answer

SQL Server Contributor role

Question 6

You have an Azure SQL Database with Always Encrypted enabled for sensitive columns. You need to grant a developer the ability to encrypt data using a column master key stored in Azure Key Vault. What permissions must be assigned to the developer's Microsoft Entra identity in the key vault?

Accepted Answer

Get, Wrap Key, and Unwrap Key.. To encrypt data with Always Encrypted, the developer's application must be able to retrieve the column master key metadata (Get) and then use the key to encrypt a column encryption key. This requires the Wrap Key operation, which generates a new encrypted column encryption key. Unwrap Key is needed only for decryption, not encryption. Therefore, Get and Wrap Key are the minimum permissions for encryption, making Option B correct.

Answer

Get, Decrypt, and Unwrap Key.

Answer

Get and Decrypt.

Answer

Get, Sign, and Verify.

Question 7

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 8

Which TWO actions should you perform to enable automatic tuning for an Azure SQL Database?

Accepted Answer

Enable automatic tuning at the Azure SQL Database server level. Automatic tuning in Azure SQL Database requires enabling it at both the server level (to set the inheritance policy) and the database level (to apply tuning options like FORCE_LAST_GOOD_PLAN or CREATE_INDEX). Option B is correct because enabling at the server level allows you to define a default tuning policy that can be inherited by all databases, which is a prerequisite for per-database tuning. Option C is correct because you must also explicitly enable automatic tuning at the database level to override or confirm the inherited settings for that specific database.

Answer

Set the database compatibility level to 150

Answer

Enable Query Store

Answer

Configure performance recommendations

Question 9

Your company uses Azure SQL Managed Instance. You need to configure a server-level firewall rule to allow access from a specific Azure service, but you want to minimize the attack surface. What is the best practice?

Accepted Answer

Set the firewall rule to allow Azure services and resources to access this server.. Option C is correct because enabling the 'Allow Azure services and resources to access this server' firewall rule is the best practice for Azure SQL Managed Instance when you need to allow access from a specific Azure service while minimizing the attack surface. This setting uses a special firewall rule that permits connections from any Azure service (including those within the same region) without exposing the server to all public IP addresses, as it relies on Azure's internal network boundaries and does not open the server to the entire internet. It is more secure than allowing all IPs (0.0.0.0/0) because it restricts access to only Azure-originated traffic, and it is simpler than manually specifying IP ranges, which can change dynamically.

Answer

Set the firewall rule to allow all IP addresses (0.0.0.0/0).

Answer

Disable public endpoint and use private endpoint.

Answer

Create a firewall rule with the specific IP range of the Azure service.

Question 10

You need to encrypt sensitive columns in an Azure SQL Database table so that data is encrypted at rest and in transit between the application and database. Which feature should you use?

Accepted Answer

Always Encrypted. Always Encrypted is the correct choice because it encrypts sensitive data both at rest in the database and in transit between the application and the database. It ensures that encryption keys are never revealed to the database engine, so data remains encrypted throughout the entire data path, including during query execution. This meets the requirement for encryption at rest and in transit.

Answer

Row-Level Security

Answer

Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking

Question 11

You need to audit all successful and 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 both successful and failed login attempts, and writes them to an audit log in your Azure Storage account, Log Analytics workspace, or Event Hubs. This meets the requirement to audit all authentication events for security and compliance purposes.

Answer

Transparent Data Encryption (TDE)

Answer

SQL Vulnerability Assessment

Answer

Diagnostic settings

Question 12

Which TWO are valid methods to connect to an Azure SQL Database without exposing a public endpoint?

Accepted Answer

Use a service endpoint. Option C is correct because a service endpoint extends your virtual network private address space and the identity of your VNet to Azure SQL Database over a direct connection on the Azure backbone network. This allows you to secure your logical SQL server to accept traffic only from a specific subnet, eliminating the need for a public endpoint while still using the public endpoint's DNS name internally.

Answer

Use Always Encrypted

Answer

Use a public endpoint with a firewall rule

Answer

Use a site-to-site VPN gateway and connect to public endpoint

Question 13

Which TWO actions should you take to secure Azure SQL Database against SQL injection attacks?

Accepted Answer

Use parameterized queries in application code. Parameterized queries (Option D) and stored procedures with parameters (Option E) are both effective defenses against SQL injection because they separate SQL code from user input. When using parameterized queries, the database engine treats input as data only, never as executable code, which prevents attackers from injecting malicious SQL statements. This is a fundamental secure coding practice recommended by Microsoft for Azure SQL Database.

Answer

Enable Transparent Data Encryption

Answer

Enable auditing for all database operations

Answer

Configure firewall rules to allow only trusted IP addresses

Question 14

Which TWO metrics should you monitor in Azure SQL Database to detect a potential memory pressure issue?

Accepted Answer

Page Life Expectancy. Page Life Expectancy (PLE) is a key metric that indicates how long a data page remains in the buffer pool before being evicted. A consistently low PLE (e.g., below 300 seconds) signals that pages are being flushed too quickly due to memory pressure, often from insufficient buffer pool memory. Memory Grants Pending tracks the number of queries waiting for a memory grant to execute; a non-zero value indicates that the server cannot allocate enough memory to satisfy query workspace requirements, directly pointing to memory pressure.

Answer

Average CPU percent

Answer

Log IO

Answer

Data IO

Question 15

You need to automate the deployment of Azure SQL Database logical servers and databases using Bicep. What is the best practice for storing the administrative password securely?

Accepted Answer

Reference the password from Azure Key Vault using the getSecret function. Option A is correct because Azure Key Vault is the recommended secure storage for secrets like administrative passwords in Azure deployments. Using the `getSecret` function in Bicep allows you to reference a secret from Key Vault at deployment time without exposing the password in the Bicep file or deployment logs, aligning with Azure security best practices and the principle of least privilege.

Answer

Use the adminPassword property with a generated password

Answer

Use an environment variable in the deployment script

Answer

Store the password as a plain text parameter in the Bicep file

Question 16

Which TWO of the following are benefits of using Azure SQL Database failover groups compared to active geo-replication alone?

Accepted Answer

Failover of multiple databases in a single group.. Failover groups extend active geo-replication by allowing you to manage failover for a group of databases as a single unit. This simplifies the failover process when you have multiple databases that must be failed over together to maintain application consistency. Option B is correct because failover groups support the coordinated failover of multiple databases, which active geo-replication alone does not.

Answer

Support for manual failover.

Answer

Readable secondary replicas for read-scale workloads.

Answer

Synchronous data replication to the secondary.

Question 17

Which TWO methods can be used to automate index maintenance in Azure SQL Database?

Accepted Answer

Azure Automation runbooks executing T-SQL scripts. Option A is correct because Azure Automation runbooks can execute T-SQL scripts against Azure SQL Database using the Invoke-SqlCmd cmdlet or similar, enabling scheduled index maintenance tasks such as rebuilding or reorganizing indexes. This method is fully supported in Azure SQL Database, which lacks SQL Server Agent, and allows for flexible, cloud-native automation.

Answer

SQL Server Agent jobs

Answer

Azure Data Factory pipelines

Answer

Automatic tuning

Question 18

Which THREE considerations are important when planning a migration from SQL Server on-premises to Azure SQL Managed Instance?

Accepted Answer

File tables and FILESTREAM.. Option A is correct because Azure SQL Managed Instance does not support FILESTREAM or FileTables. These features rely on the NTFS file system for storing BLOB data, which is not available in the managed instance environment. Any migration using FILESTREAM or FileTables must be refactored to use alternative storage, such as Azure Blob Storage with the remote BLOB store (RBS) or separate file storage.

Answer

SQL Server Integration Services (SSIS) packages.

Answer

Use of contained databases.

Question 19

Which TWO benefits does the Hyperscale service tier of Azure SQL Database provide?

Accepted Answer

Up to 100 TB of database storage.. Option C is correct because the Hyperscale service tier of Azure SQL Database supports up to 100 TB of database storage, which is significantly larger than the 4 TB limit of the General Purpose tier and the 4 TB limit of the Business Critical tier. This massive storage capacity is achieved through a distributed architecture where data is stored in page servers with a shared disk model, allowing near-instantaneous scaling of storage without downtime.

Answer

Built-in in-memory OLTP support.

Answer

Zone-redundant configuration by default.

Answer

Zero data loss in all scenarios.

Question 20

You are deploying an Azure SQL Database that will be used by a global application. You need to ensure that read-intensive workloads are offloaded from the primary database to improve performance. Which feature should you enable?

Accepted Answer

Read scale-out.. Read scale-out (A) is the correct feature because it allows you to offload read-only workloads to a read-only replica of the Azure SQL Database. By enabling the 'Read scale-out' property, the database automatically routes connections with `ApplicationIntent=ReadOnly` to a secondary replica, freeing the primary from read-intensive queries and improving overall performance for write operations.

Answer

Automatic tuning.

Answer

Connection pooling.

Answer

Active geo-replication.