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

Question 1

You are the DBA for a company that uses Azure SQL Database. You need to ensure that only authorized users can view sensitive columns (e.g., salary) in the Employees table. You want to obfuscate the data for certain users but allow full access to HR managers. Which feature should you use?

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

Accepted Answer

Dynamic Data Masking. Dynamic Data Masking (DDM) is the correct choice because it obfuscates sensitive columns (e.g., salary) in query results for unauthorized users while allowing full visibility for authorized users like HR managers. DDM applies masking rules at the database level without modifying the underlying data, making it ideal for scenarios where you need to limit exposure of sensitive data to certain roles without changing the application code.

Answer

Always Encrypted

Answer

Row-Level Security (RLS)

Answer

Transparent Data Encryption (TDE)

Question 2

You are configuring Azure SQL Database for a multi-tenant application. Each tenant's data is stored in a separate database. You need to ensure that a tenant admin can only manage their own database and not other databases on the same logical server. What is the best approach?

Accepted Answer

Create a contained database user with db_owner role in each tenant's database and use Microsoft Entra authentication.. Creating a contained database user with the db_owner role in each tenant's database, using Microsoft Entra authentication, ensures that the tenant admin can only manage their own database. Contained database users are scoped to the individual database, not the logical server, so they cannot access other databases on the same server. This aligns with the principle of least privilege for multi-tenant isolation.

Answer

Use a server-level firewall rule to restrict access to the tenant's IP.

Answer

Create a server-level login and assign it as db_owner on all databases.

Answer

Create a database-level firewall rule for each tenant database.

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

You need to configure Azure SQL Database to allow connections only from Azure services and from a specific on-premises IP range. Which firewall rule configuration should you apply at the server level?

Accepted Answer

Set 'Allow Azure Services and resources to access this server' to ON and add a firewall rule for the on-premises IP range.. Enabling 'Allow Azure Services and resources to access this server' permits connections from all Azure services (including those from other subscriptions) by adding a special firewall rule that allows Azure IP ranges. Adding a separate firewall rule for the specific on-premises IP range then restricts non-Azure external traffic to only that range. This combination meets the requirement to allow only Azure services and the specified on-premises range.

Answer

Create a private endpoint for the server.

Answer

Create a virtual network service endpoint and add a VNet firewall rule.

Answer

Set 'Allow Azure Services and resources to access this server' to OFF and add a firewall rule for the on-premises IP range.

Question 5

You are responsible for securing Azure SQL Managed Instance. You need to ensure that only encrypted connections are allowed and that all client connections use the latest TLS protocol. Which TWO configurations should you implement?

Accepted Answer

Set the 'Minimal TLS Version' to 1.2.. Options A and D are correct. Setting 'Minimal TLS Version' to 1.2 ensures that only encrypted connections using TLS 1.2 or higher are accepted, enforcing the latest protocol. Enabling 'Force SSL' ensures that all client connections are encrypted. Option B is incorrect because firewall rules control network access, not encryption. Option C is incorrect because Microsoft Defender for SQL provides security threat detection, not encryption enforcement. Option E is incorrect because Transparent Data Encryption (TDE) encrypts data at rest, not in transit.

Answer

Configure a firewall rule to allow only specific IP addresses.

Answer

Enable Microsoft Defender for SQL.

Answer

Enable Transparent Data Encryption (TDE).

Question 6

You are deploying Azure SQL Database and need to comply with regulatory requirements that mandate separation of duties for database administration. Specifically, the security team should manage access policies, and the database administrators should manage the data. Which THREE features should you use to implement this?

Accepted Answer

Dynamic Data Masking to hide sensitive data from non-privileged users.. Dynamic Data Masking (C) is correct because it allows database administrators to manage data while hiding sensitive information from non-privileged users, supporting separation of duties by preventing security team members from viewing actual data. Azure RBAC (D) is correct because it enables distinct role assignments (e.g., SQL Security Manager vs. SQL DB Contributor) to separate who manages access policies from who manages data. Database-level roles (E) are correct because they provide granular permissions within the database, such as db_securityadmin for managing security policies and db_owner for full data management, enforcing separation at the database level.

Answer

Firewall rules to restrict administrative access to specific IP addresses.

Answer

Transparent Data Encryption (TDE) with customer-managed keys.

Question 7

You are a database administrator for a financial services company that uses Azure SQL Database for a critical trading application. The application connects using a service principal (Microsoft Entra application) and executes stored procedures. You need to implement the following security requirements:

1. All connections must use Microsoft Entra authentication with MFA enforced for the service principal.
2. The application should only be able to execute specific stored procedures (usp_Trade, usp_GetQuote) and no other operations.
3. All data at rest must be encrypted using customer-managed keys stored in Azure Key Vault.
4. Auditing must capture all failed login attempts and all changes to the database schema.
5. The database must be protected against SQL injection attacks from the application layer.

You have already configured Microsoft Entra authentication and enabled TDE with customer-managed key in Azure Key Vault. Which additional steps should you take to meet all remaining requirements?

Accepted Answer

Create a database role with EXECUTE permission on the required stored procedures and assign it to the service principal. Configure a Conditional Access policy that requires MFA for the service principal. Enable audit for FAILED_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP. Ensure the application uses parameterized queries.. It addresses all remaining requirements: (1) A database role with EXECUTE permission on the required stored procedures restricts the service principal to only executing those procedures. (2) A Conditional Access policy enforcing MFA for the service principal meets the MFA requirement. (3) Audit enabled for FAILED_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP captures failed logins and schema changes. (4) Using parameterized queries prevents SQL injection. Option B is incorrect because dynamic data masking does not prevent SQL injection and does not enforce MFA. Option C is incorrect because a contained database user with EXECUTE permission does not enforce MFA, and Always Encrypted does not prevent SQL injection. Option D is incorrect because row-level security restricts data access but does not prevent SQL injection, and Microsoft Defender for SQL only detects but does not prevent SQL injection; also, no MFA enforcement is mentioned.

Answer

Create a database role that only has EXECUTE permission on the required stored procedures. Configure dynamic data masking on sensitive columns. Enable audit for FAILED_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP.

Answer

Create a contained database user for the service principal with EXECUTE permission. Enable Always Encrypted for sensitive columns. Configure audit for FAILED_DATABASE_AUTHENTICATION_GROUP only.

Answer

Create a database role with EXECUTE on the stored procedures. Implement row-level security to restrict data access. Use Microsoft Defender for SQL to detect SQL injection attempts.

Question 8

You are a junior DBA at a startup that uses Azure SQL Database for its SaaS application. The application uses a single database and the development team frequently makes schema changes. You need to implement security measures to:

1. Ensure that all connections from the application use TLS encryption.
2. Prevent unauthorized access from the internet.
3. Allow your office IP range (203.0.113.0/24) to connect for management.
4. Enable auditing of all schema changes.
5. Encrypt the database at rest using Azure-managed keys.

You have already enabled Transparent Data Encryption (TDE) with service-managed keys. What should you do next to meet the remaining requirements?

Accepted Answer

Set the minimal TLS version to 1.2. Add a firewall rule for the office IP range and remove the default 'Allow Azure services' rule. Enable auditing with SCHEMA_OBJECT_CHANGE_GROUP.. It addresses all remaining requirements: setting the minimal TLS version to 1.2 ensures encrypted connections (requirement 1), adding a firewall rule for the office IP range and removing the default 'Allow Azure services' rule prevents unauthorized internet access while allowing management traffic (requirements 2 and 3), and enabling auditing with SCHEMA_OBJECT_CHANGE_GROUP captures schema changes (requirement 4). TDE with service-managed keys already satisfies requirement 5, so no further action is needed for encryption at rest.

Answer

Set the minimal TLS version to 1.2. Enable auditing with SCHEMA_OBJECT_CHANGE_GROUP.

Answer

Add a firewall rule for the office IP range. Enable auditing with SCHEMA_OBJECT_CHANGE_GROUP.

Answer

Add a firewall rule for the office IP range. Remove the default 'Allow Azure services' rule.

Question 9

Your company uses Azure SQL Database for a customer-facing application. You need to ensure that only the application can access the database, and that access is restricted to specific IP ranges. The application runs on Azure App Service with a dynamic outbound IP address. What should you do?

Accepted Answer

Add a firewall rule for the App Service's outbound IP address range.. The application runs on Azure App Service with a dynamic outbound IP address, and the requirement is to restrict access to specific IP ranges. By adding a firewall rule for the App Service's outbound IP address range (which can be found in the App Service properties or via the `WEBSITE_OUTBOUND_IP_ADDRESSES` app setting), you allow the application to connect to Azure SQL Database while blocking other traffic. This approach directly addresses the dynamic IP challenge without requiring VNet integration or private endpoints.

Answer

Configure a VNet integration for the App Service and set the SQL firewall to allow the VNet subnet.

Answer

Migrate the database to Azure SQL Managed Instance and use a service endpoint.

Answer

Create a private endpoint for the SQL Database and connect App Service to it via VNet peering.

Question 10

You are configuring Microsoft Defender for SQL for an Azure SQL Database. You want to receive email notifications when a suspicious activity is detected. What should you configure?

Accepted Answer

In the Microsoft Defender for SQL settings, enable 'Email notifications to admins and subscription owners'.. Microsoft Defender for SQL includes a dedicated 'Email notifications to admins and subscription owners' setting under its threat detection policy. When enabled, this sends email alerts to Azure subscription owners and administrators whenever Defender detects suspicious activities such as SQL injection, brute-force attacks, or anomalous access patterns. This is the direct, built-in mechanism for email-based alerting on threat detections.

Answer

Configure a vulnerability assessment recurring scan and email the report.

Answer

Create an Azure Monitor alert rule for the 'SQL database threat detection' metric.

Answer

Enable SQL auditing and stream logs to a Log Analytics workspace.

Question 11

Your Azure SQL Database is configured with a failover group across two regions. You need to ensure that client connections automatically redirect to the secondary region during a regional outage, without changing the connection string. What should you implement?

Accepted Answer

Configure the failover group with a listener endpoint and use that in the connection string.. A failover group in Azure SQL Database provides a listener endpoint (e.g., `server.database.windows.net`) that automatically resolves to the primary or secondary region based on the current role. By using this listener in the connection string, client connections are redirected transparently during a regional outage without any manual changes to the connection string, as the DNS record is updated by the failover group to point to the secondary region.

Answer

Enable read scale-out and configure application retry logic.

Answer

Deploy Azure Traffic Manager in front of the SQL Database and configure failover routing.

Answer

Use private endpoints in both regions and configure DNS with a CNAME record.

Question 12

You need to audit all schema changes (DDL) on an Azure SQL Database for compliance. The audit logs must be retained for 7 years. What should you do?

Accepted Answer

Enable auditing on the database, log to a storage account, and set the retention policy to 7 years.. Azure SQL Database auditing can be configured at the database level to log all database events, including DDL changes, to a storage account. The retention policy can be set to 7 years directly in the audit settings, ensuring compliance with long-term retention requirements. This is the native, supported method for auditing schema changes on Azure SQL Database.

Answer

Create an extended events session to capture DDL events and save to a file.

Answer

Enable change tracking on the database and query the change tracking tables.

Answer

Enable SQL Server Audit at the server level and specify a file destination.

Question 13

Your Azure SQL Database contains sensitive customer data. You need to implement column-level encryption so that only authorized users can read specific columns. The encryption must be managed by the application, not the database. What should you use?

Accepted Answer

Implement Always Encrypted with column master key stored in Azure Key Vault.. Always Encrypted is the correct choice because it ensures that sensitive data is encrypted at the column level and that the encryption keys are never revealed to the database engine. By storing the column master key in Azure Key Vault and using client-side encryption, the application manages the encryption and decryption process, so only authorized users with access to the key can read the plaintext data. This meets the requirement that encryption be managed by the application, not the database.

Answer

Use dynamic data masking to obfuscate the sensitive columns for unauthorized users.

Answer

Use Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault.

Answer

Create a row-level security policy to restrict access to the sensitive rows.

Question 14

You have a new Azure SQL Database. You need to ensure that all connections use TLS 1.2 or higher. What should you configure?

Accepted Answer

Set the 'minimal TLS version' to 1.2 in the server's properties in the Azure portal.. To enforce TLS 1.2 or higher for all connections to an Azure SQL Database, you must configure the 'minimal TLS version' setting at the server level in the Azure portal. This setting applies to all databases hosted on that logical server, ensuring that any client attempting to connect with a TLS version lower than 1.2 is rejected. The server-level property directly controls the TLS protocol version accepted during the SSL/TLS handshake, overriding any client-side or database-level settings.

Answer

Set the 'minimal TLS version' to 1.2 in the database's properties.

Answer

Add a firewall rule to deny connections using TLS 1.0 or 1.1.

Answer

Enable the 'Force encryption' option in the connection string and require TLS 1.2.

Question 15

Your company is using Azure SQL Database with Microsoft Entra ID authentication. A developer needs to connect to the database using a service principal. What should you provide to the developer?

Accepted Answer

The service principal's client ID and client secret, and the connection string with 'Authentication=Active Directory Service Principal'.. To connect to Azure SQL Database using a service principal with Microsoft Entra ID authentication, the developer needs the service principal's client ID and client secret (or certificate) for authentication, and the connection string must include 'Authentication=Active Directory Service Principal' to specify the authentication method. This combination allows the application to obtain an access token from Microsoft Entra ID via the OAuth 2.0 client credentials grant flow, which is then used to authenticate to the database.

Answer

The connection string with 'Authentication=Active Directory Service Principal' and the service principal's object ID.

Answer

The service principal's username and password.

Answer

The service principal's managed identity endpoint.