DP-300 Practice Exam 59 — 20 Questions

Question 1

Your company uses Azure SQL Database with a server-level Microsoft Entra ID admin. You need to implement a solution where database-level roles are automatically assigned based on the user's group membership in Microsoft Entra ID. What should you use?

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

Accepted Answer

Create database users from Microsoft Entra ID groups and grant roles to those users.. Azure SQL Database supports creating database users mapped to Microsoft Entra ID (formerly Azure AD) groups. By creating a user for the Entra ID group and then granting database roles to that group user, all members of the group automatically inherit the assigned permissions. This directly satisfies the requirement for role assignment based on group membership without custom scripting or triggers.

Answer

Use Azure RBAC to assign roles to the Entra ID groups.

Answer

Configure a SQL Server Agent job to update database roles based on group membership.

Answer

Create a DDL trigger that assigns roles when users log in.

Question 2

Refer to the exhibit. You are reviewing a script that is executed as part of a data migration to Azure SQL Database. The script attempts to insert a row with a specific OrderID into an Orders table that has an identity column. The script runs successfully in a test environment but fails in production with an error. The production environment has the same schema. What is the most likely cause?

Accepted Answer

The user running the script does not have ALTER permission on the table. The script uses SET IDENTITY_INSERT ON to allow explicit insertion into an identity column. In Azure SQL Database, this requires ALTER permission on the table, not just INSERT. The test environment likely granted the user ALTER, but production did not, causing the failure despite identical schema.

Answer

The table in production does not have an identity column

Answer

A different session is already using SET IDENTITY_INSERT ON for the same table

Answer

The identity column in production has a different seed value

Question 3

Your organization uses Azure SQL Database and wants to implement a defense-in-depth strategy. You have already enabled Transparent Data Encryption (TDE) and firewall rules. You need to add an additional layer of security that protects against unauthorized access to the physical storage files (e.g., if someone gains access to the storage account). What should you enable?

Accepted Answer

Infrastructure encryption (double encryption) using platform-managed keys. Infrastructure encryption (double encryption) using platform-managed keys adds a second layer of encryption at the storage infrastructure level, below the TDE layer. This protects data at rest even if the physical storage files (e.g., Azure Storage blobs) are accessed directly, because the storage infrastructure itself is encrypted with a separate, platform-managed key. This fulfills the defense-in-depth requirement for protecting against unauthorized access to physical storage.

Answer

Dynamic Data Masking

Answer

Microsoft Defender for SQL

Answer

Always Encrypted with secure enclaves

Question 4

Your organization has a regulatory requirement to audit all data modifications in an Azure SQL Database. You enable Azure SQL Database auditing and configure it to send logs to a Log Analytics workspace. However, you notice that DELETE operations on a specific table are not being audited. What is the most likely cause?

Accepted Answer

The audit action group does not include 'DATABASE_OPERATION_GROUP'. Azure SQL Database auditing uses audit action groups to define which operations are logged. By default, the audit policy includes 'SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP', 'FAILED_DATABASE_AUTHENTICATION_GROUP', and 'BATCH_COMPLETED_GROUP', but 'DATABASE_OPERATION_GROUP' is required to capture DDL and DML operations like DELETE. If this group is not explicitly added to the audit specification, DELETE operations on specific tables will not be recorded, even though general auditing is enabled.

Answer

The diagnostic settings for the database are misconfigured

Answer

Audit logs are being written to a storage account instead of Log Analytics

Answer

The table has been configured to ignore auditing for read operations

Question 5

You are the database administrator for a company that uses Azure SQL Managed Instance. You need to allow a specific application to connect to the database using a service principal. The application authenticates with Microsoft Entra ID. What should you configure?

Accepted Answer

Create a contained database user mapped to the Microsoft Entra service principal.. A contained database user mapped to a Microsoft Entra ID service principal allows the application to authenticate directly to the database using its Microsoft Entra identity, without requiring a SQL Server login. This is the correct approach because Azure SQL Managed Instance supports Microsoft Entra authentication for service principals, enabling token-based authentication from applications that authenticate with Microsoft Entra ID.

Answer

Enable Always Encrypted and configure column master key with the service principal.

Answer

Add a server-level firewall rule with the application's IP address.

Answer

Create a SQL authentication login and user for the application.

Question 6

Your company has an Azure SQL Managed Instance in the General Purpose tier. You need to configure a failover group for disaster recovery. The secondary managed instance must be in a different region and must also be used for read-only workloads. During a failover, you want to minimize data loss. Which configuration should you use?

Accepted Answer

Create a failover group and configure the secondary instance to allow read-only connections.. Failover groups for Azure SQL Managed Instance allow you to configure the secondary instance to allow read-only connections, enabling it to serve read-only workloads. This minimizes data loss by using automatic replication with a Recovery Point Objective (RPO) of less than 1 second. Option A (zone redundancy) provides high availability within a region, not disaster recovery across regions. Option C (log shipping) is not a built-in feature for managed instances and does not support automatic failover. Option D (active geo-replication) is not supported for managed instances; failover groups are the recommended solution.

Answer

Enable zone redundancy on the primary and secondary instances.

Answer

Configure log shipping between the primary and secondary instances.

Answer

Use active geo-replication instead of a failover group.

Question 7

You need to protect Azure SQL Database from SQL injection attacks. Which THREE of the following measures should you implement?

Accepted Answer

Enable Microsoft Defender for SQL to detect and alert on SQL injection.. Microsoft Defender for SQL provides advanced security capabilities, including vulnerability assessment and threat detection. It specifically monitors for anomalous activities that indicate a SQL injection attack, such as unusual login attempts or queries that match known injection patterns, and can alert administrators or trigger automated responses.

Answer

Implement dynamic data masking to hide sensitive data from unauthorized users.

Answer

Enable Transparent Data Encryption (TDE) to encrypt the database at rest.

Question 8

Your organization has a compliance requirement to automatically classify and protect sensitive data in Azure SQL Database. You need to configure Microsoft Purview to scan and classify the database. Which THREE actions are required?

Accepted Answer

Run a scan to discover and classify sensitive data.. Running a scan is the primary action that discovers and classifies sensitive data in Azure SQL Database using Microsoft Purview. The scan applies the classification rules from the scan rule set to identify patterns like credit card numbers or social security numbers, and then labels the data accordingly for compliance.

Answer

Create a Microsoft Entra ID application and grant it access to the database.

Answer

Enable Microsoft Defender for SQL to automatically classify data.

Question 9

You are configuring Azure SQL Database for a financial application that must meet PCI DSS compliance. The database contains credit card numbers stored in a column encrypted with Always Encrypted. You need to ensure that database administrators cannot view the plaintext credit card numbers while allowing application users with the proper credentials to access them. What should you implement?

Accepted Answer

Use Always Encrypted with secure enclaves and store the column master key in Azure Key Vault with access granted only to the application. Always Encrypted with secure enclaves ensures that credit card numbers are encrypted at all times, including during computation, and that the column master key is stored in Azure Key Vault with access granted only to the application. This prevents database administrators (DBAs) from viewing plaintext data because they never have access to the column master key, while authorized application users can decrypt the data via the client driver. This meets PCI DSS requirements for protecting cardholder data at rest and in use.

Answer

Use column-level encryption with a certificate stored in the database

Answer

Enable Transparent Data Encryption (TDE)

Answer

Implement Dynamic Data Masking (DDM)

Question 10

Your organization requires that all Azure SQL Database administrators use multi-factor authentication (MFA) when connecting. Which authentication method must be used?

Accepted Answer

Microsoft Entra ID authentication with Conditional Access policy. Microsoft Entra ID authentication combined with a Conditional Access policy is required to enforce multi-factor authentication (MFA) for Azure SQL Database administrators. Conditional Access policies can mandate MFA as a condition for authentication, which is not possible with SQL Server authentication, certificate-based authentication, or Windows authentication alone. This method integrates with Microsoft Entra ID (formerly Azure AD) to provide the necessary security controls.

Answer

SQL Server authentication

Answer

Certificate-based authentication

Answer

Windows authentication

Question 11

Refer to the exhibit. You run these commands in an Azure SQL Database. What is the result?

Accepted Answer

A Microsoft Entra ID user is created and granted read access to the database.. The commands create a user in an Azure SQL Database mapped to a Microsoft Entra ID (formerly Azure AD) identity. The CREATE USER statement with FROM EXTERNAL PROVIDER creates a user that corresponds to an Entra ID user or group. The ALTER ROLE statement then adds this user to the db_datareader database role, granting read access to all tables and views. Therefore, option D correctly describes the outcome: a Microsoft Entra ID user is created and granted read access.

Answer

The user is created but not granted any permissions.

Answer

The commands fail because Entra ID users cannot be created in Azure SQL Database.

Answer

A SQL Server authentication user is created and granted read access.

Question 12

You are migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database is 500 GB and requires a disaster recovery solution with an RPO of 15 seconds and automatic failover to a secondary region. Which configuration meets these requirements?

Accepted Answer

Create an auto-failover group with a secondary instance in a paired region using synchronous replication.. Auto-failover groups with a secondary instance in a paired region using synchronous replication meet the requirements for automatic failover and an RPO of 15 seconds for Azure SQL Managed Instance. Option A is incorrect because geo-redundant backup storage and geo-restore do not provide automatic failover or a low RPO. Option B is incorrect because active geo-replication is not supported for SQL Managed Instance; it is used for Azure SQL Database. Option C is incorrect because log shipping is not supported for SQL Managed Instance.

Answer

Enable geo-redundant backup storage and configure geo-restore.

Answer

Configure active geo-replication to a secondary instance in another region.

Answer

Set up log shipping to a secondary instance in another region.

Question 13

Your company is migrating on-premises SQL Server databases to Azure SQL Managed Instance. You need to ensure that database backups are encrypted at rest using customer-managed keys stored in Azure Key Vault. You also need to allow the backup service to access the keys. What should you configure?

Accepted Answer

Enable Transparent Data Encryption (TDE) with customer-managed keys and grant the managed instance's system-assigned managed identity 'get', 'wrapKey', and 'unwrapKey' permissions on the key vault.. Transparent Data Encryption (TDE) with customer-managed keys (CMK) in Azure SQL Managed Instance encrypts database backups at rest. To allow the Azure backup service to access the key for backup encryption, the managed instance's system-assigned managed identity must be granted 'get', 'wrapKey', and 'unwrapKey' permissions on the Azure Key Vault where the CMK is stored. This ensures that backups are encrypted using the customer-controlled key, meeting the requirement for encryption at rest with customer-managed keys.

Answer

Use Always Encrypted with column master key stored in Azure Key Vault.

Answer

Configure Azure Backup for SQL Server in Azure VM and use Backup Center to manage encryption.

Answer

Configure server-level firewall rules to allow Azure services to access the server.

Question 14

You have an Azure SQL Database that uses a firewall rule allowing access from a specific range of IP addresses. A developer reports that they cannot connect from a new IP address that falls outside the allowed range. You need to temporarily allow the developer's IP address for 24 hours without affecting existing rules. What should you do?

Accepted Answer

Add a new firewall rule at the server level that allows the developer's IP address.. Azure SQL Database firewall rules are configured at the server level (the logical server) to control inbound access. Adding a new server-level firewall rule for the developer's specific IP address allows temporary access without modifying or removing the existing range-based rule. This approach is the standard method for granting time-limited access to a single IP while preserving all other firewall configurations.

Answer

Configure a point-to-site VPN connection for the developer.

Answer

Update the existing firewall rule to include the developer's IP address.

Answer

Modify the database-level firewall rule to include the developer's IP.

Question 15

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 client driver must retrieve the column encryption key (which is stored encrypted) and unwrap it using the column master key. It may also need to wrap newly generated column encryption keys during key rotation or initial setup. Therefore, the developer's Microsoft Entra identity requires Get, Unwrap Key, and Wrap Key permissions on the column master key in Azure Key Vault. Option B correctly includes all three.

Answer

Get, Decrypt, and Unwrap Key.

Answer

Get and Decrypt.

Answer

Get, Sign, and Verify.

Question 16

You are optimizing a data warehouse workload on Azure SQL Database. The workload involves large batch inserts and nightly aggregations. You notice that the transaction log is growing excessively during the batch inserts, causing performance degradation. You need to reduce log growth without affecting data consistency. What should you do?

Accepted Answer

Use bulk insert operations with TABLOCK hint to enable minimal logging.. Using minimally logged operations (e.g., bulk insert with TABLOCK) reduces log space for large imports under the full recovery model, but requires specific conditions. Option A is wrong because simple recovery model is not supported in Azure SQL Database (only FULL, BULK_LOGGED is not available). Option C is wrong because partitioning does not reduce log growth. Option D is wrong because increasing log size only accommodates growth, does not prevent it.

Answer

Change the database recovery model to Simple.

Answer

Create a partition function and scheme to spread the inserts.

Answer

Increase the maximum log size of the database.

Question 17

Your Azure SQL Database is configured with the Hyperscale service tier. You observe that log write latency is consistently high, affecting transaction throughput. What is the most likely cause and the recommended mitigation?

Accepted Answer

High log generation rate is causing log rate governance throttling; reduce the log generation rate by batching transactions.. In Hyperscale, log rate is governed to protect secondary replicas; high latency indicates throttling. Option A is wrong because Hyperscale uses local SSD for log, not PIOPS. Option B is wrong because log rate governance affects all log writes. Option D is wrong because scaling up compute doesn't increase log throughput limits.

Answer

The log IOPS is limited by the disk performance; increase the provisioned IOPS.

Answer

The compute replica is undersized; scale up the compute to increase log throughput.

Answer

The log write latency is due to network congestion; move the database to a different region.

Question 18

You are reviewing an ARM template for Azure SQL Database. The exhibit shows the database settings. You notice the database is not being automatically paused. What is the most likely explanation?

Accepted Answer

The database uses VBS enclaves which are incompatible with serverless. Auto-pause is only supported for General Purpose serverless databases, and the use of VBS enclave (preferredEnclaveType: "VBS") indicates Always Encrypted with secure enclaves, which is not supported with serverless. Option A is wrong because minCapacity 0.5 is valid for serverless. Option B is wrong because licenseType BasePrice does not affect auto-pause. Option C is wrong because autoPauseDelay 60 minutes is valid; the default is 60.

Answer

The minCapacity is set too low

Answer

The autoPauseDelay is set to 60 minutes

Answer

The licenseType is set to BasePrice

Question 19

You have an Azure SQL Database that needs to be accessed by an application with a static public IP address. You want to allow only that IP address to connect. What should you configure?

Accepted Answer

Create a server-level firewall rule with the application's IP.. A server-level firewall rule explicitly allows inbound connections from a specific client IP address to the Azure SQL Database logical server. Since the application has a static public IP, configuring a server-level firewall rule with that IP is the simplest and most direct method to restrict access to only that address, while still using the public endpoint.

Answer

Create a virtual network rule for the database.

Answer

Create a private endpoint for the database.

Answer

Create a database-level firewall rule with the application's IP.

Question 20

You are configuring managed backup for an Azure SQL Managed Instance as shown in the exhibit. What is the purpose of this configuration?

Accepted Answer

To configure automated backups of the managed instance to Azure Blob Storage.. The configuration shown in the exhibit is for managed backup, which automatically schedules and stores full, differential, and transaction log backups of the Azure SQL Managed Instance to Azure Blob Storage. This ensures that backups are retained and available for restore operations without manual intervention, fulfilling the core purpose of automated backups to Azure Blob Storage.

Answer

To enable geo-replication for the managed instance.

Answer

To set up disaster recovery across Azure regions.

Answer

To configure point-in-time restore for the managed instance.