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

Question 1

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?

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

Accepted Answer

Dynamic Data Masking to hide sensitive data from non-privileged users.. Option D is correct because Azure RBAC lets you assign distinct built-in or custom roles (for example, Security Reader/Contributor versus SQL DB Contributor) so the security team controls access policies while DBAs manage data, directly implementing separation of duties. Option E is correct because database-level roles such as db_securityadmin (manages permissions and role membership) and db_owner (full control of data and objects) can be granted to different principals, separating security administration from data administration within the database. Option C is correct because Dynamic Data Masking limits exposure of sensitive columns to non-privileged users, so DBAs can work with data while masked values are hidden from roles that should not see them, supporting least privilege and separation of duties. Option A is not correct because firewall rules only restrict network origin of connections and do not separate administrative duties between teams. Option B is not correct because TDE with customer-managed keys protects data at rest and key custody, but it does not by itself divide security-policy management from data management responsibilities.

Answer

Firewall rules to restrict administrative access to specific IP addresses.

Answer

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

Question 2

You are configuring Azure SQL Database auditing. You need to ensure that all database-level authentication failures are logged. Which TWO audit action groups should you include?

Accepted Answer

FAILED_DATABASE_AUTHENTICATION_GROUP. To log only authentication failures, only the FAILED_DATABASE_AUTHENTICATION_GROUP is required. SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP logs successes, which is not needed per the requirement. The other groups audit permission changes or object changes, not authentication failures.

Answer

SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP

Answer

DATABASE_OBJECT_PERMISSION_CHANGE_GROUP

Answer

DATABASE_OBJECT_CHANGE_GROUP

Answer

SCHEMA_OBJECT_CHANGE_GROUP

Question 3

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 every remaining requirement with the right Azure SQL Database mechanism: a database role with EXECUTE permission on only usp_Trade and usp_GetQuote enforces least privilege at the object level; a Conditional Access policy is the only way to enforce MFA for a service principal (Microsoft Entra authentication alone does not enforce MFA); auditing FAILED_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP captures failed logins and schema changes respectively; and parameterized queries are the primary application-layer defense against SQL injection. TDE with customer-managed key in Key Vault was already configured, so encryption at rest is satisfied.

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 4

You are the DBA for a company that uses Azure SQL Managed Instance to host multiple databases for different departments. The security team has mandated that:

- All connections to the managed instance must be encrypted using TLS 1.2 or higher.
- SQL Server authentication must be disabled; only Microsoft Entra authentication is allowed.
- A dedicated audit log must be created for each database to track all DDL changes and all failed login attempts.
- The audit logs must be stored in a central Azure Storage account with 180-day retention.
- Database administrators (DBAs) should not be able to view or modify the audit logs.

You have already set the minimal TLS version to 1.2 and disabled SQL Server authentication. What should you do next to meet the remaining requirements?

Accepted Answer

Enable server-level auditing with the same action groups and send logs to a central storage account. Use Azure RBAC to deny DBAs access to the storage account.. Server-level auditing on Azure SQL Managed Instance can capture DDL changes and failed login attempts using the same action groups (SCHEMA_OBJECT_CHANGE_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP) even though it's server-level; the audit logs are still stored per database in the audit files. By sending logs to a central storage account and using Azure RBAC to explicitly deny DBAs access to the storage account, they cannot view or modify the audit logs, meeting the security requirement. Database-level auditing (Option C) would grant Storage Blob Data Reader role, which allows viewing logs, violating the 'no view' requirement. Option B's RBAC denial prevents both viewing and modification.

Answer

Create a SQL Server Agent job on the managed instance that captures DDL changes and failed logins into a table. Grant DBAs access to the table.

Answer

Enable database-level auditing on each database with audit action groups SCHEMA_OBJECT_CHANGE_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP. Configure the audit log destination to be a central Azure Storage account. Set retention to 180 days. Grant the DBAs 'Storage Blob Data Reader' role on the storage account but not 'Contributor' or 'Owner'.

Answer

Use Azure SQL Auditing with Azure Monitor Log Analytics workspace. Configure the workspace with 180-day retention. Grant DBAs 'Log Analytics Reader' role.

Question 5

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 6

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 7

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 8

Your organization is migrating a legacy on-premises SQL Server database to Azure SQL Managed Instance. The database contains sensitive financial data. You need to implement column-level encryption so that even database administrators cannot view the plaintext data. The encryption keys must be stored in Azure Key Vault, and the application must be able to encrypt and decrypt data transparently. The application currently uses Entity Framework Core and runs on Azure App Service. You have the following requirements:
- Use a solution that provides the strongest security by ensuring the database never has access to the plaintext.
- Minimize changes to the application code.
- The application must be able to perform equality searches on encrypted columns.

What should you implement?

Accepted Answer

Use Always Encrypted with deterministic encryption and store column master key in Azure Key Vault.. Always Encrypted with deterministic encryption and storing the column master key in Azure Key Vault is correct because it ensures the database never has access to plaintext (the keys are held in Key Vault and only the client driver can decrypt), minimizes application changes (Entity Framework Core supports Always Encrypted with minimal code changes), and supports equality searches on encrypted columns (deterministic encryption produces the same ciphertext for the same plaintext, enabling equality comparisons).

Answer

Use Always Encrypted with secure enclaves and randomized encryption.

Answer

Use dynamic data masking to hide the data from unauthorized users.

Answer

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

Question 9

You are a database administrator for a healthcare organization that uses Azure SQL Database to store protected health information (PHI). The compliance team requires that you implement controls to prevent unauthorized access and detect potential data exfiltration. Which TWO actions should you take?

Accepted Answer

Implement dynamic data masking for columns containing PHI. Option B is correct because dynamic data masking (DDM) limits exposure of sensitive data such as PHI by masking column values to non-privileged users, directly helping prevent unauthorized access to protected health information. Option D is correct because Microsoft Defender for SQL provides advanced threat protection, including alerts for anomalous access patterns, potential SQL injection, and suspicious data exfiltration attempts, which satisfies the detection requirement. Option A is not correct here because transparent data encryption (TDE) protects data at rest by encrypting the database files, but it does not prevent unauthorized logical access or detect exfiltration. Option C is not correct because change tracking only records which rows changed for synchronization purposes and does not provide access control or security threat detection. Option E is not correct because auditing records database events for compliance and forensic review, but by itself it does not prevent unauthorized access or actively detect exfiltration like Defender for SQL does.

Answer

Enable transparent data encryption (TDE) on the database

Answer

Enable change tracking on the database

Answer

Enable auditing on the database

Question 10

You are a database administrator for a startup that uses Azure SQL Database to run an e-commerce application. The application uses a service principal (Microsoft Entra ID application) to authenticate to the database. You need to grant the service principal the ability to read data from all tables in the 'sales' schema. Which THREE actions should you take?

Accepted Answer

Create a database user for the service principal using the CREATE USER statement with the FROM EXTERNAL PROVIDER option. Option C is correct because an Microsoft Entra ID service principal must first be mapped into the Azure SQL Database as a contained database user via CREATE USER [app-name] FROM EXTERNAL PROVIDER before any permissions can be assigned to it. Option D is correct because granting SELECT on the 'sales' schema (GRANT SELECT ON SCHEMA::sales TO [user]) provides exactly the required read access to all tables in that schema, following least privilege. Option E is correct because adding the user to the db_datareader fixed database role also grants read (SELECT) access to all user tables, satisfying the requirement to read data from the tables. Option A is incorrect because CONTROL grants full ownership-level permissions on the database, far exceeding the read-only requirement and violating least privilege. Option B is incorrect because 'sales' is a schema, not a database role, so there is no such role to add the user to.

Answer

Grant CONTROL permission on the database to the user

Answer

Add the user to the 'sales' database role

Question 11

You are a database administrator for a government agency that uses Azure SQL Database to store classified data. The database contains highly sensitive columns (e.g., Social Security Numbers) that must be masked for most users, but fully visible to a small group of compliance officers. The compliance officers authenticate using Microsoft Entra ID. You need to implement a solution that automatically masks the sensitive columns for all users except the compliance officers, without requiring application code changes. The solution must also ensure that the compliance officers are identified by their Microsoft Entra ID user principal name (UPN). What should you do?

Accepted Answer

Configure dynamic data masking (DDM) on the sensitive columns and define a masking policy that excludes the compliance officers based on their UPN. Dynamic Data Masking (DDM) is the correct choice because it automatically masks sensitive columns in query results without requiring application code changes. By defining a masking policy that excludes compliance officers based on their Microsoft Entra ID UPN, DDM ensures that only those users see the unmasked data, while all other users see the masked values. This meets the requirement of no code changes and automatic masking based on UPN.

Answer

Grant SELECT permission on the sensitive columns only to the compliance officers and deny SELECT to all other users

Answer

Use Always Encrypted to encrypt the sensitive columns and configure the column master key to allow access only to compliance officers

Answer

Implement row-level security (RLS) to filter rows based on user's UPN

Question 12

You are a database administrator for a bank that uses Azure SQL Database to store transaction data. The compliance team requires that you implement a solution to encrypt data at rest and in transit. Additionally, the solution must allow the database to be restored to any point in time within the last 35 days. Which TWO actions should you take?

Accepted Answer

Enable transparent data encryption (TDE) on the database. Option D is correct because Transparent Data Encryption (TDE) is the Azure SQL Database feature that provides encryption of data at rest, encrypting the database, backups, and log files at the page level using a symmetric key protected by a certificate or asymmetric key, which directly satisfies the compliance requirement to encrypt data at rest. Option E is correct because Azure SQL Database's point-in-time restore (PITR) retention is configurable from 1 to 35 days, so setting the retention period to 35 days is exactly what enables restoring the database to any point in time within the last 35 days. Option A is incorrect because Microsoft Defender for SQL is a threat detection and vulnerability assessment service, not an encryption or backup-retention mechanism. Option B is incorrect because dynamic data masking only obfuscates sensitive data in query results for non-privileged users; it does not encrypt data at rest or in transit and does not affect restore capabilities. Option C is incorrect because Always Encrypted protects data in use and in transit between client and server by keeping keys client-side, but it is not the feature that provides at-rest encryption of the whole database, and it is not required to meet the stated PITR requirement.

Answer

Enable Microsoft Defender for SQL

Answer

Implement dynamic data masking on sensitive columns

Answer

Enable Always Encrypted for all columns containing sensitive data

Question 13

You are a database administrator for a technology company that uses Azure SQL Database to support a multi-tenant SaaS application. Each tenant has its own database. The security team requires that users from one tenant should never be able to access data from another tenant, even if a user's credentials are compromised. You need to implement a solution that enforces tenant isolation at the database level. The solution must be transparent to the application and must not require application code changes. What should you do?

Accepted Answer

Create a contained database user for each tenant in their respective database and ensure that cross-database queries are not allowed by configuring server-level firewall rules to block access between databases. Each tenant has its own database, and by creating contained database users specific to each tenant and preventing cross-database access through firewall rules or private endpoints, you enforce tenant isolation. Even if credentials are compromised, the attacker can only access the single tenant's database. Option A is incorrect because Always Encrypted protects data at rest and in transit but does not restrict which data a user can query; it does not prevent cross-tenant access. Option C is incorrect because elastic queries are designed to query across databases, which would allow cross-tenant access if not carefully restricted. Option D is incorrect because a linked server enables cross-database queries, increasing the risk of cross-tenant access.

Answer

Use Always Encrypted to encrypt each tenant's data with a separate column encryption key

Answer

Implement elastic queries to query across all tenant databases and use a view to filter by tenant ID

Answer

Create a linked server between all tenant databases and use a security policy to restrict access

Question 14

You are a database administrator for an e-commerce company that uses Azure SQL Database to store order data. The company is implementing a new policy that requires all database access to be audited, including both successful and failed attempts. Additionally, the audit logs must be retained for at least one year for compliance purposes. You need to configure auditing for the database. What should you do?

Accepted Answer

Enable auditing on the database, set the audit log destination to a storage account, and configure retention to 365 days. Azure SQL Database auditing can be configured at the database level to send audit logs to a storage account, which allows you to set a retention policy of 365 days directly on the audit configuration. This meets the requirement to audit both successful and failed attempts and retain logs for at least one year for compliance.

Answer

Use the 'Audit Logs' feature in the Azure portal to enable auditing and set retention to 365 days

Answer

Enable auditing on the database and configure it to send logs to a Log Analytics workspace, then set the retention in Log Analytics to 365 days

Answer

Enable auditing on the server and configure it to send logs to an event hub

Question 15

You are the database administrator for an Azure SQL Database named HRDB that contains employee salary data. The security team requires that any user who queries the Salary and Bonus columns must be authenticated with a valid Microsoft Entra ID identity, and that the data must be encrypted in memory so that DBAs cannot view it. You need to implement this requirement with the least administrative overhead. What should you do?

Accepted Answer

Configure Always Encrypted with secure enclaves and set the column encryption keys to be stored in Azure Key Vault.. The requirement is to protect data in use from DBAs and to require Microsoft Entra ID authentication for key access. Always Encrypted with secure enclaves encrypts data in memory and supports rich computations, while Azure Key Vault provides centralized key management and integration with Microsoft Entra ID. TDE, dynamic data masking, and auditing do not encrypt data in memory or prevent privileged users from viewing plaintext.

Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault.

Answer

Create a database audit specification that logs all SELECT statements against the Salary and Bonus columns.

Answer

Implement dynamic data masking on the Salary and Bonus columns and grant users the UNMASK permission.