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

Question 1

You are configuring security for an Azure SQL Database that will be used by a web application. The application uses a connection string with SQL authentication. You need to protect the database from SQL injection attacks. Which two measures should you implement? (Choose two.)

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

Accepted Answer

Use parameterized queries in the application.. Parameterized queries ensure that user input is treated as data, not executable code, by separating SQL logic from input values. This prevents attackers from injecting malicious SQL statements into the query string, which is the primary defense against SQL injection attacks.

Answer

Enable Transparent Data Encryption (TDE).

Answer

Configure Dynamic Data Masking (DDM).

Answer

Enable Always Encrypted on sensitive columns.

Question 2

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 3

You are the database administrator for a SaaS company that uses Azure SQL Database. The company has a new requirement to audit all SELECT operations on a specific table containing sensitive customer data. You enable auditing on the server and configure a storage account for audit logs. However, after 24 hours, you notice that no SELECT operations are captured in the audit logs. You verify that the table is being accessed frequently. What is the most likely cause?

Accepted Answer

The audit policy is not configured to capture SELECT operations; only UPDATE, INSERT, and DELETE are captured. The default server-level audit policy in Azure SQL Database captures only data manipulation language (DML) operations like UPDATE, INSERT, and DELETE, not SELECT queries. To audit SELECT operations, you must explicitly configure a database-level audit action group such as DATABASE_OBJECT_ACCESS_GROUP or SCHEMA_OBJECT_ACCESS_GROUP, or use a custom audit action like SELECT on the specific table. Without this configuration, SELECT operations are not recorded in the audit logs, even if server auditing is enabled and the storage account is properly configured.

Answer

The storage account key was rotated and the audit configuration is using an expired key

Answer

The client application is using a connection string that bypasses the server firewall

Answer

The storage account is in a different region than the SQL Database server

Question 4

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 5

Your company uses Azure SQL Database with Microsoft Entra ID (formerly Azure AD) authentication. You need to grant a group of external consultants access to a specific database with read-only permissions. The consultants are from a partner organization that uses their own Microsoft Entra ID tenant. What should you do?

Accepted Answer

Invite the consultants as guest users in your Microsoft Entra ID tenant using B2B collaboration, then create a contained database user for each guest user. External consultants from a different Microsoft Entra ID tenant must first be invited as guest users via B2B collaboration to your tenant. Once they are guest users, you can create contained database users in Azure SQL Database mapped to their guest user identities (e.g., their UPN in your tenant) and grant them read-only permissions (e.g., db_datareader role). This approach respects the isolation of the partner's tenant while enabling access through your tenant's identity.

Answer

Create a contained database user mapped to the consultants' Microsoft Entra ID user principal names (UPNs)

Answer

Configure Azure SQL Database to trust the partner's Microsoft Entra ID tenant

Answer

Create a SQL Server authentication login and user for the consultants

Question 6

You are troubleshooting a connectivity issue: an application running on an Azure virtual machine (VM) cannot connect to an Azure SQL Database. The VM is in the same region as the SQL Database. The VM can ping other resources, but the SQL connection fails. The SQL Database has a firewall rule allowing the VM's private IP address. What is the most likely cause?

Accepted Answer

The firewall rule uses the VM's private IP address, but Azure SQL Database sees the VM's public IP address. When an Azure VM connects to Azure SQL Database, the source IP address seen by the SQL firewall is the VM's public outbound IP address due to Source Network Address Translation (SNAT) performed by Azure. Even if the VM is in the same region, traffic to Azure SQL Database egresses through the VM's public IP, not its private IP. Therefore, a firewall rule allowing the private IP will not match, causing the connection to fail.

Answer

The SQL Database has the public endpoint disabled

Answer

The SQL Database firewall is configured at the database level, not the server level

Answer

The VM does not have an outbound security rule allowing traffic to Azure SQL Database

Question 7

You need to ensure that all connections to an Azure SQL Database use encryption. The application uses the JDBC driver. What should you configure in the connection string?

Accepted Answer

Add 'encrypt=true' and 'trustServerCertificate=false'. For JDBC connections to Azure SQL Database, setting 'encrypt=true' enforces TLS encryption for data in transit, and 'trustServerCertificate=false' ensures that the server's TLS certificate is validated against the trusted certificate authority (CA) chain, preventing man-in-the-middle attacks. This is the recommended configuration for secure connections to Azure SQL Database.

Answer

Add 'hostNameInCertificate=*.database.windows.net'

Answer

Add 'encrypt=false' and 'trustServerCertificate=true'

Answer

Add 'useEncryption=true' and 'validateServerCertificate=true'

Question 8

Your company uses Azure SQL Database with Microsoft Defender for Cloud. You receive an alert indicating a potential SQL injection attack. The alert shows that the attack originated from the IP address of your company's VPN gateway. You have verified that no legitimate users are using the VPN at that time. What should you do to immediately stop the attack while preserving legitimate access?

Accepted Answer

Remove the firewall rule that allows the VPN gateway IP address. The attack is originating from the VPN gateway's IP address, and the firewall rule allowing that IP is the only entry point for the attacker. By removing that specific firewall rule, you immediately block the malicious traffic while preserving legitimate access through other firewall rules (e.g., for on-premises or other VPN ranges). This directly stops the SQL injection attack at the network layer without affecting other users or services.

Answer

Disable the public endpoint on the SQL Database

Answer

Change the SQL Database server admin password

Answer

Add the attacker's IP address to a block list in Microsoft Defender for Cloud

Question 9

You are implementing row-level security (RLS) in Azure SQL Database to enforce data isolation for multiple tenants. Each tenant has a TenantID. The RLS predicate function uses the SESSION_CONTEXT variable set by the application. You need to ensure that users cannot bypass the RLS by setting arbitrary SESSION_CONTEXT values. What should you do?

Accepted Answer

Create a stored procedure that sets the SESSION_CONTEXT and grant the application only execute permission on that procedure; revoke SET permission from users. By creating a stored procedure that sets the SESSION_CONTEXT and granting the application only EXECUTE permission on that procedure while revoking SET permission from users, you prevent users from arbitrarily setting SESSION_CONTEXT values. This enforces that only the trusted application code can set the context used by the RLS predicate, ensuring tenants cannot impersonate other tenants.

Answer

Use dynamic data masking to hide the TenantID column

Answer

Encrypt the SESSION_CONTEXT values using Always Encrypted

Answer

Use a firewall rule to restrict access to the database to only the application IP addresses

Question 10

You need to audit schema changes on an Azure SQL Database. Specifically, you must capture details of any DDL statements executed by any user. The audit logs must be stored in a Log Analytics workspace for analysis. What should you configure?

Accepted Answer

Configure server-level auditing with a Log Analytics workspace destination. Azure SQL Database server-level auditing with a Log Analytics workspace destination captures all DDL statements executed by any user and stores them in a Log Analytics workspace for analysis. This meets the requirement to audit schema changes and store logs in Log Analytics, as server-level auditing captures events for all databases on the server, including DDL operations, and supports Log Analytics as a destination.

Answer

Create DDL triggers that write to a table

Answer

Configure database-level auditing with blob storage destination

Answer

Create an extended events session to capture DDL events

Question 11

Refer to the exhibit. You are reviewing an Azure Resource Manager template for deploying an Azure SQL Database server. The template sets publicNetworkAccess to Disabled, minimalTlsVersion to 1.2, and azureAdOnlyAuthentication to true. However, the deployment fails with an error. What is the most likely cause?

Accepted Answer

When azureAdOnlyAuthentication is true, the administratorLogin and administratorLoginPassword properties must not be specified. When azureAdOnlyAuthentication is set to true in an Azure SQL Database ARM template, the administratorLogin and administratorLoginPassword properties must be omitted because Azure AD authentication replaces SQL authentication as the sole identity provider. Including these properties causes a validation conflict, as the deployment expects no SQL admin credentials when Azure AD-only authentication is enabled.

Answer

minimalTlsVersion 1.2 is not supported in Azure SQL Database

Answer

publicNetworkAccess Disabled requires a private endpoint to be defined in the same template

Answer

The password does not meet complexity requirements

Question 12

Refer to the exhibit. You are configuring Azure SQL Database Transparent Data Encryption (TDE) with customer-managed keys (CMK) stored in Azure Key Vault. The deployment uses a user-assigned managed identity. However, after deployment, the TDE status shows 'Inaccessible'. What is the most likely cause?

Accepted Answer

The managed identity lacks 'Get', 'Wrap Key', and 'Unwrap Key' permissions on the Key Vault key. When using customer-managed keys (CMK) for TDE in Azure SQL Database, the managed identity assigned to the logical server must have 'Get', 'Wrap Key', and 'Unwrap Key' permissions on the key in Azure Key Vault. Without these specific permissions, the SQL Database service cannot retrieve or use the key to encrypt or decrypt the database encryption key, resulting in an 'Inaccessible' TDE status.

Answer

The key specified in the URI does not exist

Answer

The user-assigned managed identity is not assigned to the SQL Database server

Answer

The Key Vault firewall is enabled and does not allow Azure services

Question 13

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 14

Which THREE of the following are features that help protect against data exfiltration in Azure SQL Database? (Choose three.)

Accepted Answer

Always Encrypted. Always Encrypted is correct because it encrypts sensitive data at the client-side, ensuring that the encryption keys are never revealed to the database engine. This prevents even database administrators or attackers with access to the server from reading the plaintext data, directly protecting against data exfiltration.

Answer

Dynamic Data Masking (DDM)

Answer

Transparent Data Encryption (TDE)

Question 15

Which TWO of the following are required steps to configure Azure SQL Database to use a customer-managed key (CMK) for Transparent Data Encryption (TDE) with Azure Key Vault? (Choose two.)

Accepted Answer

Set the TDE protector to the Key Vault key via the Azure portal or T-SQL. Setting the TDE protector to the Key Vault key is the step that actually enables customer-managed key (CMK) encryption for Azure SQL Database. This can be done via the Azure portal, PowerShell, Azure CLI, or T-SQL (using ALTER DATABASE SCOPED CONFIGURATION SET TDE_PROTECTOR). Without this step, the database continues to use a service-managed key.

Answer

Create a user-assigned managed identity for the SQL Database server

Answer

Store the encryption key in the SQL Database server's hardware security module (HSM)

Answer

Ensure the SQL Database server is inside the same virtual network as the Key Vault