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

Question 1

You are the Azure SQL Database administrator for a healthcare organization. A new compliance rule requires that any connection from an application server to your Azure SQL Database must be encrypted with TLS 1.2 or higher, and that the client certificate must be validated by the server. You need to enforce this at the database server level with the least administrative effort. What should you configure?

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

Accepted Answer

Set the 'Minimum TLS version' to 1.2 in the Azure SQL Database server's firewall and virtual networks settings.. The 'Minimum TLS version' setting on the Azure SQL logical server is the correct control to enforce a minimum TLS version for all connections. It is a server-level configuration that applies to all databases on that server and requires no application changes. Firewall rules control network access, auditing provides visibility, and 'Enforce SSL connection' is not a real setting. Thus, setting the minimum TLS version to 1.2 meets the compliance requirement with least effort.

Answer

Configure a server-level firewall rule to allow only the application server's IP address.

Answer

Enable 'Enforce SSL connection' in the Azure SQL Database connection strings settings.

Answer

Create a database-level audit policy that logs connections using TLS versions lower than 1.2.

Question 2

You are configuring security for an Azure SQL Database that will be accessed by an application hosted in a different Azure region. The security team requires that the application connect to the database without traversing the public internet. You need to implement a private connection. What should you do?

Accepted Answer

Create a private endpoint for the Azure SQL Database server in a virtual network in the application's region and configure a private DNS zone for the server's name.. A private endpoint is the correct solution because it provisions a private IP address for the Azure SQL Database server within a virtual network, enabling private connectivity. For cross-region access, the private endpoint must be in the application's region, and a private DNS zone ensures name resolution to the private IP. The other options do not provide a private connection or are not supported for Azure SQL Database.

Answer

Enable the 'Deny public network access' setting on the Azure SQL Database server and allow Azure services to connect.

Answer

Set up an Azure VPN Gateway between the application's virtual network and the Azure SQL Database server.

Answer

Configure a virtual network service endpoint for the Azure SQL Database server from the application's virtual network.

Question 3

You administer an Azure SQL Database that stores customer records. The security team requires that any attempt to access the database from a public IP address outside the corporate network be logged to Azure Monitor, but legitimate application traffic from the corporate network must still be allowed without interruption. You configure a server firewall rule for the corporate IP range. What should you implement to satisfy the logging requirement?

Accepted Answer

Enable auditing at the server level and configure a Log Analytics workspace as the audit log destination.. Auditing in Azure SQL Database can record connection events and query activity to a Log Analytics workspace, Azure Storage, or Event Hubs. By enabling server-level auditing with a Log Analytics destination, all access attempts, including those from public IPs not covered by the firewall rule, are captured and available for review. This meets the security team's requirement while the existing firewall rule continues to allow corporate traffic.

Answer

Create an alert rule in Azure Monitor that triggers when the database CPU exceeds 80 percent.

Answer

Configure a private endpoint for the Azure SQL Database and disable public network access.

Answer

Enable Microsoft Defender for SQL and configure the Advanced Threat Protection email recipients.

Question 4

You manage an Azure SQL Database named HRDB that contains a column named NationalID encrypted with Always Encrypted using a column encryption key. The column master key is stored in Azure Key Vault. A nightly ETL process running as an Azure Automation runbook must decrypt the NationalID values to load them into a separate reporting database. The runbook authenticates to Azure using a system-assigned managed identity. You need the ETL process to be able to decrypt the column. What should you configure?

Accepted Answer

Grant the managed identity the Key Vault Crypto Service Encryption User role on the key vault and add the managed identity to the Column Encryption Key ACL in the database.. Always Encrypted performs encryption and decryption in the client driver, which needs two things: access to the column master key in Azure Key Vault and permission to use the column encryption key stored in the database. The Key Vault Crypto Service Encryption User role covers key unwrap, and the column encryption key ACL controls which principals may use that key. Both must be granted for the ETL runbook to decrypt data.

Answer

Grant the managed identity the db_owner role in HRDB and enable Always Encrypted in the connection string.

Answer

Add the managed identity as a Microsoft Entra admin on the logical server and set Column Encryption Setting=Enabled in the connection string.

Answer

Configure the database to use a service-managed Transparent Data Encryption key and enable Column Encryption Setting=Enabled in the connection string.

Question 5

You manage an Azure SQL Database named HRDB that contains employee records. The security team wants to audit all successful and failed logins to HRDB and retain the audit logs for 180 days. They also want to minimize administrative overhead. What should you configure?

Accepted Answer

Enable auditing on HRDB and send the audit logs to an Azure Storage account with a retention of 180 days.. Azure SQL Database auditing writes records of successful and failed logins to a chosen target. Using an Azure Storage account as the target allows a retention period in days to be set directly in the audit policy, so 180 days is achieved with a single setting. Log Analytics, Event Hubs, and Advanced Threat Protection either require extra configuration or do not provide the required retention and coverage.

Answer

Enable Advanced Threat Protection on HRDB and configure email notifications to the security team.

Answer

Enable auditing on HRDB and send the audit logs to Event Hubs for the security team to consume.

Answer

Enable auditing on HRDB and send the audit logs to a Log Analytics workspace with a 180-day retention.

Question 6

You are configuring security for an Azure SQL Database named OrderDB. The security team requires that all connections to the database be encrypted and that the database be accessible only from a specific Azure virtual network subnet. You need to implement these requirements. (Choose two.)

Accepted Answer

Enforce TLS 1.2 or higher by setting the minimum TLS version on the logical server.. To restrict access to a specific subnet, a private endpoint with public access disabled is required, as it provides private IP connectivity. To enforce encrypted connections, setting the minimum TLS version to 1.2 or higher on the logical server ensures all connections use strong encryption. Together, these two configurations meet the security team's requirements for network isolation and encrypted connections.

Answer

Enable Transparent Data Encryption (TDE) on the database.

Answer

Configure the Azure SQL Database firewall to allow only the specific subnet's IP range.

Answer

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

Question 7

You are the DBA for an Azure SQL Database named SalesDB. The application connects using a contained database user named 'AppUser'. You need to ensure that AppUser can read data from a table named Sales but cannot modify it. You also need to allow AppUser to execute a stored procedure named GetSalesReport that reads from the Sales table. What should you do?

Accepted Answer

Grant SELECT on the Sales table to AppUser and grant EXECUTE on GetSalesReport to AppUser.. Granting SELECT on the Sales table and EXECUTE on the stored procedure provides exactly the required permissions: read access to the table and the ability to run the procedure. This adheres to the principle of least privilege and meets both requirements.

Answer

Grant EXECUTE on GetSalesReport to AppUser and rely on ownership chaining to access the Sales table.

Answer

Grant CONTROL on the Sales table to AppUser and grant EXECUTE on GetSalesReport to AppUser.

Answer

Add AppUser to the db_datareader role and grant EXECUTE on GetSalesReport to AppUser.

Question 8

You are the database administrator for a company that uses Azure SQL Database. The security team requires that you implement a solution to audit all database activities, including successful and failed logins, and retain the audit logs for 90 days. You also need to ensure that the audit logs are stored in a tamper-proof manner. Which two actions should you perform? (Choose two.)

Accepted Answer

Configure the audit retention period to 90 days in the auditing settings.. To meet the requirements, you need to enable auditing and direct logs to Azure Monitor logs, which supports immutable storage and long retention. Setting the retention period to 90 days ensures logs are kept as required. Storing in Azure Storage is possible but not tamper-proof by default. TDE and firewall rules are unrelated to auditing. Thus, the correct actions are sending logs to Azure Monitor logs and setting retention to 90 days.

Answer

Enable Azure SQL Database auditing and configure it to store logs in an Azure Storage account.

Answer

Enable Transparent Data Encryption (TDE) on the database.

Answer

Create a server-level firewall rule to restrict access to the database.

Question 9

You are configuring Transparent Data Encryption (TDE) for an Azure SQL Database using a customer-managed key (CMK) stored in Azure Key Vault. The security policy requires that the key be automatically rotated every 90 days and that the database remain available if the key vault is temporarily unavailable. What should you configure?

Accepted Answer

Enable the 'soft delete' and 'purge protection' options on the key vault, and set up automatic key rotation in Key Vault with a 90-day rotation policy.. To meet the requirements, you need automatic key rotation and protection against key vault unavailability. Key Vault's automatic rotation feature, configured with a 90-day policy, rotates the key on schedule. Soft delete and purge protection ensure that the key cannot be permanently deleted, so the database remains accessible even if the key vault is temporarily unavailable. Azure SQL Database automatically picks up the new key version when rotation occurs.

Answer

Store the TDE protector in Azure Key Vault and manually rotate the key every 90 days using PowerShell scripts.

Answer

Use a service-managed key for TDE, which is automatically rotated by Microsoft every 90 days.

Answer

Enable TDE with a customer-managed key and configure the database to use the 'Allow access to Azure services' setting in the key vault firewall.

Question 10

You are the DBA for an Azure SQL Database that uses Microsoft Entra ID authentication. You need to allow a group of users to connect to the database and have read-only access to all tables in the dbo schema. The users are members of an Entra ID group named 'SalesReaders'. You want to minimize administrative effort and ensure that group membership changes are automatically reflected. What should you do?

Accepted Answer

Create a contained database user for the 'SalesReaders' group and add it to the db_datareader role.. Creating a contained database user for the Entra ID group and adding it to the db_datareader role is the best approach. It grants read-only access to all tables and automatically reflects group membership changes. Other options either require manual user management, do not use Entra ID groups, or are not suitable for user access.

Answer

Create a SQL login for each user and add them to the db_datareader role.

Answer

Create a contained database user for the 'SalesReaders' group and grant SELECT on the dbo schema.

Answer

Create an application role, assign permissions, and have users connect using the application role.

Question 11

You are the DBA for an Azure SQL Managed Instance that hosts a database containing personally identifiable information (PII). The security team wants to classify and label columns that contain PII, such as names, addresses, and social security numbers. They also want to track who accesses this sensitive data. You need to implement a solution that automatically identifies and classifies these columns and provides audit reports on data access. What should you use?

Accepted Answer

SQL Data Discovery and Classification. SQL Data Discovery and Classification is designed to automatically discover, classify, and label sensitive columns such as PII. It provides a built-in scanning mechanism and allows you to apply sensitivity labels and information types. It also integrates with auditing to track access to sensitive data, meeting the requirement to monitor who accesses PII. Other options like Vulnerability Assessment, Microsoft Purview, and Dynamic Data Masking do not provide the combined classification and auditing capabilities needed.

Answer

Microsoft Purview

Answer

SQL Vulnerability Assessment

Answer

Dynamic Data Masking

Question 12

You are the DBA for an Azure SQL Managed Instance. The security team wants to ensure that all connections to the instance are encrypted and that the instance only accepts connections from a specific subnet in a virtual network. What should you configure?

Accepted Answer

Configure a network security group (NSG) on the subnet that allows traffic only from the specific subnet, and enable 'Force Encryption' on the managed instance.. Azure SQL Managed Instance resides in a virtual network subnet, so network security groups are used to control inbound and outbound traffic. By creating an NSG rule that permits traffic only from the specified subnet, you restrict connections to that subnet. Additionally, enabling the 'Force Encryption' property on the managed instance ensures that all connections are encrypted. These two configurations together satisfy the security team's requirements.

Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key and configure a firewall rule for the specific subnet.

Answer

Configure a private endpoint for the managed instance and disable public endpoint access.

Answer

Configure the managed instance to use a service endpoint and enable 'Secure transfer required' on the storage account.

Question 13

You are configuring security for an Azure SQL Database. You need to implement row-level security (RLS) to restrict access to rows based on the user's department. You also need to ensure that users cannot bypass the security policy. Which two actions should you perform? (Choose two.)

Accepted Answer

Enable the security policy with the SCHEMABINDING option on the predicate function.. To implement row-level security, you must create a security policy with a predicate function and apply it to the table. Additionally, the predicate function should be schema-bound to prevent schema changes that could bypass the policy. These two actions together enforce RLS and prevent bypass. Other options do not contribute to RLS enforcement.

Answer

Create a database role and add users to it, then grant the role SELECT permission.

Answer

Grant users direct SELECT permission on the table.

Answer

Set the database to read-only to prevent data modifications.

Question 14

You are configuring security for an Azure SQL Database that will store credit card data. The compliance requirement states that the database must use a customer-managed key for Transparent Data Encryption (TDE), and the key must be automatically rotated every 90 days. You need to configure the key rotation. What should you do?

Accepted Answer

Create a new key version in Azure Key Vault every 90 days and update the TDE protector to use the new version.. Azure SQL Database does not automatically rotate the TDE protector when a new key version is created in Key Vault. You must create a new key version and then update the TDE protector to use it. This can be automated using Azure Automation or other tools. The other options either describe non-existent features or do not meet the customer-managed key requirement.

Answer

Enable Transparent Data Encryption with a service-managed key and rotate the key automatically.

Answer

Set the key expiration date in Azure Key Vault to 90 days and enable autorotation.

Answer

Use the Azure portal to set the TDE protector to auto-rotate every 90 days.

Question 15

You are the database administrator for a company that uses Azure SQL Database. The security team requires that all connections to the database be encrypted and that the database server enforce a minimum TLS version of 1.2. Additionally, they want to ensure that the database can be accessed only from specific Azure virtual networks. You need to implement these requirements. Which two actions should you perform? (Choose two.)

Accepted Answer

Set the minimal TLS version to 1.2 on the logical server.. To meet the requirements, you must enforce a minimum TLS version of 1.2 on the logical server and restrict access to specific Azure virtual networks. The minimal TLS version setting ensures encrypted connections with TLS 1.2 or higher. Virtual network service endpoints for Microsoft.Sql allow you to limit access to specific subnets. Together, these actions satisfy the security team's requirements. Firewall rules, TDE, and private endpoints do not fully address both requirements.

Answer

Configure a private endpoint for the Azure SQL Database.

Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key.

Answer

Configure a firewall rule to allow only specific IP addresses.