DP-300 Implement a secure environment Practice Test 8 — 15 Questions

Question 1

You manage an Azure SQL Database that contains a table named Customers with a column named CreditCardNumber that stores sensitive data. You need to implement Always Encrypted with secure enclaves to allow pattern matching on the CreditCardNumber column. The application connects using a client driver that supports enclaves. What should you do first?

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

Accepted Answer

Configure an attestation provider and set the server's enclave type to VBS or SGX.. Always Encrypted with secure enclaves requires the server to have an enclave configured and attested. The first step is to set the enclave type (VBS or SGX) and configure attestation. This allows the database engine to perform computations on encrypted data within the enclave. Once the enclave is set up, you can create enclave-enabled keys and encrypt columns to support pattern matching.

Answer

Alter the CreditCardNumber column to use deterministic encryption.

Answer

Enable Transparent Data Encryption (TDE) on the database.

Answer

Create a column master key (CMK) in Azure Key Vault and a column encryption key (CEK) in the database.

Question 2

Your organization uses Azure SQL Database with Microsoft Entra authentication. A developer reports that a new contained database user created with CREATE USER [appuser@contoso.com] FROM EXTERNAL PROVIDER cannot connect, though the same user can sign in to the Azure portal. You confirm the user exists in the database. What is the most likely cause?

Accepted Answer

The logical server's Microsoft Entra admin has not been configured, so Entra authentication is not enabled for the server.. Microsoft Entra authentication for Azure SQL Database depends on a Microsoft Entra admin being assigned to the logical server. That admin assignment enables the server to trust Entra tokens. If no admin is configured, token-based logins fail regardless of database-level principals, which matches the symptom of an existing user who cannot connect.

Answer

The user needs the db_datareader role granted before any connection can be established.

Answer

The database's firewall is blocking the developer's client IP address.

Answer

The contained user must be created with a password before Entra logins are allowed.

Question 3

You manage an Azure SQL Database that contains a table named Customers with a column named CreditCardNumber that stores sensitive data. You need to implement Always Encrypted with secure enclaves to allow pattern matching on the CreditCardNumber column. The application connects using a client driver that supports enclaves. What should you do first?

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

Accepted Answer

Ensure the application's connection string does not include Column Encryption Setting=Enabled.. Always Encrypted ensures that the database engine never sees plaintext data. Decryption occurs on the client side, and only if the client driver has Column Encryption Setting=Enabled and can access the column master key. By omitting this setting from the connection string, the application cannot decrypt the CreditCardNumber column, even though it can still query the table and read other columns. This meets the requirement without unnecessary permission changes.

Answer

Revoke the SELECT permission on the CreditCardNumber column from the application's SQL login.

Answer

Deny the application's SQL login the UNMASK permission on the CreditCardNumber column.

Answer

Grant the application's SQL login the VIEW ANY COLUMN ENCRYPTION KEY DEFINITION permission.

Question 4

You administer an Azure SQL Database named HRDB. Auditors require that anyone who can view the contents of the Salary column be tracked, and that the tracking survive a T-SQL TRUNCATE of the audit log table. You enable SQL Auditing to a storage account. Which audit action group must be enabled to satisfy the requirement?

Accepted Answer

SELECT on the Salary column only, using a database audit specification. Auditing SELECT against a specific column is the mechanism that records who read that data, and because audit records are written to the storage account or Log Analytics workspace rather than a user table, truncating a table inside the database cannot remove them. Authentication and batch groups describe connections and statement execution, not column-level reads, so they do not meet the auditors' evidence requirement.

Answer

DATABASE_OBJECT_ACCESS_GROUP

Answer

BATCH_COMPLETED_GROUP

Answer

SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP

Question 5

You have an Azure SQL Database that contains a table with sensitive data. You need to implement a solution that logs all queries that access this table, including the query text and the user who executed it, and stores the logs in an Azure Storage account for auditing. What should you configure?

Accepted Answer

Enable Azure SQL Database auditing and set the audit log destination to the Azure Storage account.. Azure SQL Database auditing is designed for compliance and security auditing. It can log a wide range of events, including successful and failed connections, and queries if configured. The logs can be sent to Azure Storage, which satisfies the requirement. This is a built-in feature that requires no application changes and provides a centralized audit trail.

Answer

Enable Query Store on the database and configure it to retain query text and user information.

Answer

Configure Advanced Threat Protection to send alerts to the security team.

Answer

Create a SQL Server Audit specification using Extended Events and write to a file target.

Question 6

You are the database administrator for an Azure SQL Database. The security team requires that all user access to the database be audited, and that audit logs be retained for 90 days. You need to configure auditing to meet these requirements. Which two actions should you perform? (Choose two.)

Accepted Answer

Enable auditing on the Azure SQL server and set the retention period to 90 days.. To audit all user access and retain logs for 90 days, you should enable auditing at the server level and configure it to write to Azure Storage with a retention period of 90 days. Server-level auditing covers all databases, and Azure Storage allows you to set retention. This combination meets the security team's requirements efficiently.

Answer

Enable auditing on each database and set the retention period to 90 days.

Answer

Enable Advanced Threat Protection on the Azure SQL server.

Answer

Configure auditing to write to a Log Analytics workspace.

Question 7

You are configuring security for an Azure SQL Database. The security team requires that all connections to the database use TLS 1.2 or higher. You need to enforce this at the server level. What should you do?

Accepted Answer

Configure the 'Minimum TLS version' to 1.2 in the Azure SQL logical server's security settings.. The minimum TLS version for Azure SQL Database is configured in the logical server's security settings. Setting it to TLS 1.2 ensures that all connections use at least TLS 1.2. Other methods like enforcing SSL or firewall rules do not control the TLS version. This setting is the correct way to enforce the requirement.

Answer

Set the 'Minimum TLS version' to 1.2 in the Azure SQL logical server's firewall settings.

Answer

Create a firewall rule that allows only connections from IP addresses that use TLS 1.2.

Answer

Enable 'Enforce SSL connection' on the Azure SQL logical server.

Question 8

You are the DBA for an Azure SQL Database named FinanceDB. The security team requires that members of the 'Auditors' Microsoft Entra ID group can read all tables in the dbo schema but must not be able to see unmasked values in columns classified as 'Confidential'. The 'Auditors' group must not have any other permissions. You need to implement this with the least privilege model. What should you do?

Accepted Answer

Create a database user for the 'Auditors' group, grant SELECT on the dbo schema, and create a dynamic data masking rule on the Confidential columns without granting UNMASK.. The least privilege approach is to grant SELECT only on the dbo schema and rely on dynamic data masking to hide Confidential column values. Because the Auditors group is not granted UNMASK, they will see masked data. Using db_datareader would grant broader access than needed, and denying SELECT or granting UNMASK would either block access or reveal sensitive data.

Answer

Create a database user for the 'Auditors' group, add it to the db_datareader role, and deny SELECT on the Confidential columns.

Answer

Create a database user for the 'Auditors' group, grant SELECT on the dbo schema, and grant UNMASK on the Confidential columns.

Answer

Create a database user for the 'Auditors' group, add it to the db_datareader role, and create a dynamic data masking rule on the Confidential columns with no UNMASK permission granted.

Question 9

You are the security administrator for an Azure SQL Database. You need to allow an application to connect to the database using a managed identity. The application runs on an Azure virtual machine (VM). Which two actions should you perform? (Choose two.)

Accepted Answer

Create a contained database user in Azure SQL Database for the managed identity and grant the necessary permissions.. To allow an application on an Azure VM to connect to Azure SQL Database using a managed identity, you first enable the managed identity on the VM. Then you create a contained database user in the target database for that identity and grant permissions. This enables the application to authenticate using the VM's identity without credentials.

Answer

Store the managed identity's client secret in Azure Key Vault and reference it in the application connection string.

Answer

Configure the Azure virtual machine to use a user-assigned managed identity and assign it to the SQL server.

Answer

Add the managed identity to the db_owner role in the master database.

Question 10

You are the DBA for an Azure SQL Database named FinanceDB. The security team requires that all user access to sensitive tables be audited, and that audit records be retained for 90 days. You need to configure auditing with the least administrative effort and ensure that audit logs are stored in a tamper-evident manner. What should you do?

Accepted Answer

Enable auditing on the database and configure it to write to an Azure Storage account with a retention period of 90 days.. Azure SQL Database auditing can be configured at the database or server level. Writing audit logs to Azure Storage with a 90-day retention policy satisfies both the retention and tamper-evident requirements, as Azure Storage supports immutable policies. This configuration is straightforward and requires minimal administrative effort compared to other options that do not provide tamper-evident storage or comprehensive auditing.

Answer

Enable auditing on the database and configure it to write to a Log Analytics workspace with a retention period of 90 days.

Answer

Enable auditing on the server and configure it to write to Azure Monitor logs with a retention period of 90 days.

Answer

Enable Advanced Threat Protection on the database and set the retention period to 90 days.

Question 11

You are configuring security for an Azure SQL Database. You need to ensure that all connections to the database are encrypted and that the database can be accessed only from a specific Azure virtual network subnet. Which two actions should you perform? (Choose two.)

Accepted Answer

Configure a virtual network rule on the Azure SQL Database server that allows the specific subnet.. To enforce encryption, you enable 'Enforce SSL connection' on the logical server. To restrict access to a specific subnet, you configure a virtual network rule that allows only that subnet. Together, these actions ensure encrypted connections and network-level restriction. The other options either do not enforce encryption or do not limit access to the desired subnet.

Answer

Enable Advanced Threat Protection on the Azure SQL Database server.

Answer

Create a firewall rule that allows only the public IP address of the application server.

Answer

Configure a virtual network service endpoint for the Azure SQL Database server.

Question 12

You manage an Azure SQL Database named HRDB. The security team requires that database administrators never see salary data in plaintext, but they must still be able to run queries that filter on the encrypted Salary column. You need to configure Always Encrypted with the least administrative effort while keeping the encryption keys outside of Azure SQL Database. What should you do?

Accepted Answer

Configure Always Encrypted with secure enclaves and store the column master key in Azure Key Vault.. Always Encrypted with secure enclaves is the only option that keeps keys outside the database while still permitting server-side computations such as filtering on the encrypted Salary column. Deterministic encryption limits operations, and at-rest or masking features leave privileged users able to read plaintext. Azure Key Vault satisfies the requirement to externalize the column master key.

Answer

Enable Transparent Data Encryption and grant the administrators the db_datawriter role.

Answer

Configure Always Encrypted with deterministic encryption and store the column master key in the Windows certificate store of the application server.

Answer

Implement dynamic data masking on the Salary column and assign the administrators the unmask permission.

Question 13

You are the DBA for an Azure SQL Database named OrdersDB. The security team requires that all connections to OrdersDB use Microsoft Entra ID authentication and that the application connects using a managed identity. You need to configure the database to allow the managed identity to authenticate. Which two actions should you perform? (Choose two.)

Accepted Answer

Create a contained database user in OrdersDB for the managed identity using CREATE USER [managed-identity-name] FROM EXTERNAL PROVIDER.. To enable a managed identity to authenticate to Azure SQL Database, you must first set a Microsoft Entra ID admin on the logical server, which enables Microsoft Entra ID authentication. Then, you create a contained database user in the target database for the managed identity using FROM EXTERNAL PROVIDER. Firewall rules, master database roles, and authentication mode changes are not required for this authentication configuration.

Answer

Configure a firewall rule to allow the managed identity's IP address.

Answer

Grant the managed identity the db_owner role in the master database.

Answer

Enable Microsoft Entra ID authentication on the database by setting the AAD authentication mode to 'Microsoft Entra ID only'.

Question 14

You manage an Azure SQL Database named FinanceDB. The compliance team requires that all connections to the database be audited, including successful and failed logins, and that audit logs be retained for 1 year. You need to configure auditing with minimal administrative effort. What should you do?

Accepted Answer

Enable auditing on the logical server and configure it to write to a Log Analytics workspace with a 1-year retention policy.. Server-level auditing with Log Analytics workspace is the most efficient way to audit all connections across databases. It captures successful and failed logins and allows setting a retention period of 1 year. This meets the compliance requirement with minimal administrative effort because it applies to all databases on the server and centralizes log management.

Answer

Configure Azure Monitor diagnostic settings on the database to send logs to a storage account.

Answer

Enable auditing on the database and configure it to write to an Azure Storage account with a 1-year retention policy.

Answer

Enable Advanced Threat Protection on the logical server.

Question 15

You administer an Azure SQL Database that stores regulated financial data. The compliance team requires that all data at rest be encrypted with customer-managed keys and that the keys be rotated automatically. You also need to ensure that if the key becomes unavailable, the database becomes inaccessible rather than falling back to service-managed keys. Which two actions should you perform? (Choose two.)

Accepted Answer

Configure the SQL server's Transparent Data Encryption protector to use the key from Azure Key Vault.. Customer-managed TDE keys require a Key Vault (or Managed HSM) with soft-delete and purge protection to safeguard the key material, and the server's TDE protector must be pointed at that key so Azure SQL uses it to wrap the database encryption key. Together these actions deliver customer control, automatic rotation via Key Vault, and the desired fail-closed behavior if the key is revoked.

Answer

Set the database's encryption protector to SERVICE_MANAGED and rotate the service key annually.

Answer

Create a database-scoped credential that references the Key Vault key.

Answer

Enable Always Encrypted with deterministic encryption on all columns.