DP-300 Implement a secure environment — 100 Questions

Question 1

Your company has a strict policy that Azure SQL Database backups must be encrypted with customer-managed keys stored in Azure Key Vault. You configure TDE with AKV integration. After a key rotation, you find that long-running queries start failing with encryption errors. What is the most likely cause?

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

Accepted Answer

The previous key version was disabled or deleted in AKV.. When TDE is configured with Azure Key Vault (AKV) integration, the database uses the current key version from AKV to encrypt and decrypt data. If the previous key version is disabled or deleted during a key rotation, any long-running queries that still rely on that specific key version for decryption will fail with encryption errors. The database cannot automatically fall back to the new key version for in-flight operations that started before the rotation.

Answer

The service principal used for AKV access has expired.

Answer

The new key is in a different Azure region than the database.

Answer

The database is using service-managed TDE and cannot switch to customer-managed keys.

Question 2

Your Azure SQL Database is configured with Advanced Threat Protection (ATP). You receive an alert about a SQL injection attack. After investigation, you confirm the attack was blocked. However, you need to ensure that future similar attacks are automatically prevented without manual intervention. What should you configure?

Accepted Answer

Configure the firewall to automatically block the attacker's IP address.. Configuring the Azure SQL Database firewall to automatically block the attacker's IP address provides a proactive, automated defense against future SQL injection attempts from the same source. Advanced Threat Protection (ATP) can be integrated with Azure Logic Apps or other automation to trigger a firewall rule update that denies traffic from the offending IP, thereby preventing manual intervention. This directly addresses the requirement for automatic prevention of similar attacks.

Answer

Enable Transparent Data Encryption (TDE).

Answer

Run SQL Vulnerability Assessment weekly.

Answer

Enable Azure SQL Auditing to log all queries.

Question 3

A developer reports that they cannot connect to an Azure SQL Database using Azure AD authentication. The developer is a member of an Azure AD group that has been granted db_datareader role in the database. The connection string uses Active Directory Password authentication. What is the most likely issue?

Accepted Answer

The Azure AD group has not been created as a database user.. The most likely issue is that the Microsoft Entra ID group has not been created as a database user in the SQL Database. Even though the group has been granted the db_datareader role at the Microsoft Entra ID level, Azure SQL Database requires that the group be explicitly mapped to a database user via the CREATE USER [group name] FROM EXTERNAL PROVIDER statement. Without this mapping, the group's membership does not translate into database-level permissions, and the developer's authentication will fail.

Answer

The server firewall is blocking the connection.

Answer

The developer's IP address is not allowed.

Answer

Multi-factor authentication is required but not configured.

Question 4

You need to ensure that all connections to an Azure SQL Database are encrypted. Which setting should you enforce?

Accepted Answer

Set 'Force encryption' to 'Yes' on the server.. Setting 'Force encryption' to 'Yes' on the Azure SQL Database server enforces TLS encryption for all client connections. This setting ensures that any client connecting to the database must use an encrypted connection, rejecting any unencrypted attempts. It is the direct mechanism to mandate encryption in transit, as opposed to encryption at rest or auditing.

Answer

Configure firewall rules to allow only specific IP addresses.

Answer

Enable Transparent Data Encryption (TDE).

Answer

Enable SQL Auditing.

Question 5

You are deploying an Azure SQL Database that will store sensitive customer data. Compliance requirements dictate that the data must be encrypted at rest using a customer-managed key that is rotated every 90 days. You configure TDE with Azure Key Vault. What additional step is critical to ensure data remains accessible after key rotation?

Accepted Answer

Ensure the previous key version remains enabled in Azure Key Vault.. When using TDE with Azure Key Vault and customer-managed keys, the database is encrypted using a Data Encryption Key (DEK) that is protected by the key in Key Vault. Key rotation creates a new key version, but the DEK remains encrypted with the previous key version. To decrypt the DEK and access the data after rotation, the previous key version must remain enabled in Key Vault. If it is disabled or deleted, the database becomes inaccessible.

Answer

Store the key in a different region for disaster recovery.

Answer

Use a premium tier of Azure Key Vault.

Answer

Re-encrypt the database with the new key immediately after rotation.

Question 6

Which THREE of the following are required to configure Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault for Azure SQL Database?

Accepted Answer

The key encryption key must be stored in Azure Key Vault.. Option A is correct because TDE with customer-managed keys requires the asymmetric key encryption key (KEK) to be created and stored in Azure Key Vault (or Managed HSM), which wraps the database encryption key (DEK). Option C is correct because Azure SQL Database requires the key vault to have soft-delete and purge protection enabled before it can be used as a TDE protector, preventing accidental or malicious key deletion. Option D is correct because the logical SQL server must have a server-level Microsoft Entra ID identity (a system-assigned or user-assigned managed identity) that is granted access to the key vault so the server can unwrap the KEK. Option B is not required because the key vault does not have to be in the same region as the database. Option E is not required because access is granted through the server's managed identity via Key Vault access policies or Azure RBAC, not by assigning an RBAC role to the SQL server itself as a prerequisite.

Answer

The Azure Key Vault must be in the same region as the database.

Answer

The SQL server must have an RBAC role assignment on the key vault.

Question 7

You are the database administrator for a healthcare company that uses Azure SQL Database to store patient records. The database is named PatientDB. The security team mandates that all database access must be audited, and any suspicious activity must be alerted in real-time. Additionally, compliance requires that all data at rest be encrypted using a customer-managed key stored in Azure Key Vault. You have configured the following:
- TDE with customer-managed key in AKV (key vault name: KV-Health, key name: PatientKey)
- Azure SQL Auditing enabled, writing logs to a storage account (StorageAcctLogs)
- Advanced Threat Protection (ATP) enabled with alerts sent to the security team's email
- Firewall rules allowing only the application server's public IP (203.0.113.50)

A week later, the security team reports that they received an ATP alert about a potential SQL injection attack from IP 198.51.100.25. However, when they check the audit logs, they find no entries from that IP. They also notice that the database remains accessible. The security team wants to know why the audit logs do not contain the suspicious IP even though ATP detected it. What is the most likely reason?

Accepted Answer

The firewall rule blocks connections from the attacker's IP, so no audit log entries are generated because the connection never reaches the database.. Advanced Threat Protection (ATP) can detect suspicious activity at the network level before the connection is fully established, while Azure SQL Auditing only logs events after a connection is accepted and authenticated. Since the firewall rule blocks connections from IP 198.51.100.25, the attacker's IP never reaches the database engine, so no audit log entries are generated for that IP. ATP alerts can be triggered by network-level patterns (e.g., SQL injection signatures) even when the connection is denied by the firewall, explaining the discrepancy.

Answer

The storage account for audit logs is not accessible, so logs are not being written.

Answer

The firewall rule allowing only the application server's IP is misconfigured, allowing the attacker's IP.

Answer

The TDE configuration with customer-managed key is interfering with auditing.

Question 8

You manage an Azure SQL Database named SalesDB that is used by a sales application. The application connects using a SQL login named 'sales_user' with a password. Recently, the security team discovered that 'sales_user' has been compromised. They have reset the password in Azure SQL Database. However, the application continues to connect successfully using the old credentials. You suspect the application might be caching the password. The security team wants to immediately revoke access for the compromised login and ensure that only a new login with a complex password is used. You also want to minimize downtime. What should you do first?

Accepted Answer

Drop the 'sales_user' login using DROP LOGIN sales_user; then create a new login and update the application.. The correct first step because dropping the compromised server login 'sales_user' immediately removes the login object, preventing any new connections using the old credentials. Any existing active sessions using that login are terminated when the login is dropped. After dropping, you can create a new login with a complex password and update the application connection string. Option A is invalid because REVOKE CONNECT operates on database users, not server logins; in Azure SQL Database, you cannot revoke CONNECT from a server-level login. Option B does not guarantee the cached credentials are cleared immediately. Option C does not remediate the security issue.

Answer

Revoke the CONNECT permission from 'sales_user' using REVOKE CONNECT FROM sales_user; then create a new login and update the application connection string.

Answer

Change the password again and ensure the application is restarted to clear the cache.

Answer

Enable auditing to monitor future logins and leave the login as is.

Question 9

You need to configure authentication for Azure SQL Database. Which TWO options are supported?

Accepted Answer

Azure Active Directory authentication. Azure SQL Database natively supports Microsoft Entra ID authentication (option C), which allows centralized identity management, conditional access, and integration with Microsoft Entra ID users, groups, and service principals. It also supports SQL authentication (option D), where logins and passwords are stored in the database itself (for example, the server admin login created at provisioning). Multi-factor authentication (option A) is not a standalone authentication type for Azure SQL Database; it is an enhancement available only through Microsoft Entra ID authentication. Certificate-based authentication (option B) is not a supported native authentication mode for Azure SQL Database. Windows authentication (option E) is not supported directly by Azure SQL Database; it is used by on-premises SQL Server or Azure SQL Managed Instance via Microsoft Entra ID, not by Azure SQL Database.

Answer

Multi-factor authentication

Answer

Certificate-based authentication

Answer

Windows authentication

Question 10

Your organization has an Azure SQL Database server. You need to ensure that only applications running on Azure virtual machines in a specific virtual network can connect to the database. Which THREE actions should you take?

Accepted Answer

Add a virtual network rule for the specific subnet of the VMs.. Option B is correct because a virtual network rule on the Azure SQL logical server allows connections originating from a specific subnet's service endpoint, which is exactly how you scope access to the VMs' subnet. Option C is correct because turning OFF 'Allow Azure Services and resources to access this server' removes the special firewall exception (0.0.0.0) that would otherwise let other Azure-hosted services reach the database regardless of VNet rules. Option D is correct because any existing firewall rules permitting public IP ranges would still allow non-VNet clients to connect, defeating the goal of restricting access to the specified virtual network. Option A is not among the marked correct answers here, and Option E is wrong because setting the Azure services setting to ON would broaden access rather than restrict it to the VNet.

Answer

Add a service endpoint for Azure SQL Database to the virtual network.

Answer

Set the 'Allow Azure Services and resources to access this server' setting to ON.

Question 11

You are troubleshooting a connection issue from Azure SQL Database to Azure Storage using a managed identity. The above credential was created. What is missing from this configuration?

Accepted Answer

The credential is missing the SECRET parameter.. The credential is missing the SECRET parameter because when using a managed identity to access Azure Storage from Azure SQL Database, the credential must specify the identity's client ID or object ID as the SECRET. Without this, the credential cannot authenticate to the storage account, causing the connection to fail. The SECRET parameter is required to map the managed identity to the credential for Microsoft Entra ID authentication.

Answer

The credential should be a server-level credential.

Answer

The credential lacks a valid identity.

Answer

The credential needs to include the storage account name.

Question 12

Your company is planning to migrate on-premises SQL Server databases to Azure SQL Managed Instance. The security team requires that all database connections be encrypted and that the server's identity be verified using a certificate from a trusted public certificate authority (CA). What should you configure?

Accepted Answer

Set the 'Force Encryption' property to True for the managed instance.. Setting 'Force Encryption' to True on the managed instance enforces TLS encryption for all client connections and ensures the server presents a certificate from a trusted public CA to verify its identity. This meets both requirements: encryption of data in transit and server identity verification via a trusted certificate chain.

Answer

Install a custom CA-signed certificate on the managed instance.

Answer

Enable Transparent Data Encryption (TDE) with a server certificate.

Answer

Configure Always Encrypted with column master key in Key Vault.

Question 13

You are setting up Azure SQL Database and need to ensure that only specific Azure services (e.g., Azure Data Factory) can access the database. What should you configure?

Accepted Answer

Add a virtual network rule for the subnet where the service is deployed.. A virtual network rule allows you to restrict access to your Azure SQL Database to traffic originating from a specific virtual network subnet. When Azure services like Azure Data Factory are deployed in a virtual network, you can create a virtual network rule that references the subnet of that service, ensuring only traffic from that subnet can connect. This provides a more secure and granular access control compared to IP-based firewall rules, as it leverages the Azure backbone network and avoids exposing the database to public IP ranges.

Answer

Create a private endpoint for the database.

Answer

Add firewall rules for the public IP addresses of the Azure services.

Answer

Add a firewall rule with start and end IP set to 0.0.0.0.

Question 14

Your company uses Azure SQL Database and needs to audit all data modifications (INSERT, UPDATE, DELETE) for compliance. You enable SQL Database auditing and configure a storage account for logs. However, you notice that some DELETE operations are not being audited. What could be the cause?

Accepted Answer

The audit is configured to capture only successful operations.. Azure SQL Database auditing can be configured to capture only successful operations or only failed operations, or both. If the audit is set to capture only successful operations, DELETE operations that fail (e.g., due to permissions or constraint violations) will not be logged. The question states that some DELETE operations are missing, which aligns with a filter that excludes failed operations.

Answer

The diagnostic setting is configured incorrectly.

Answer

The storage account firewall is blocking the audit logs.

Answer

The database has Transparent Data Encryption (TDE) enabled.

Question 15

You are implementing row-level security (RLS) in Azure SQL Database to restrict access to sales data based on the user's Azure AD identity. Which function should you use in the security policy?

Accepted Answer

USER_NAME(). In Azure SQL Database, row-level security (RLS) predicates must evaluate to a Boolean value based on the current user's identity. USER_NAME() returns the database principal name derived from the Microsoft Entra ID token, which is the correct function to use when filtering rows by the user's Microsoft Entra ID identity. The other functions either return the login name, the session user, or the current context user, which may not reflect the actual Microsoft Entra ID user in an RLS predicate.

Answer

SESSION_USER()

Answer

SUSER_SNAME()

Answer

CURRENT_USER()

Question 16

You are the database administrator for a company that uses Azure SQL Database. The company has a strict security policy requiring that all database connections be encrypted using TLS 1.2 or higher and that the server certificate be validated to prevent man-in-the-middle attacks. Additionally, the company wants to ensure that only applications running on Azure virtual machines (VMs) in a specific virtual network (VNet) can access the database. The VMs use a subnet named 'AppSubnet'. You have configured the following: 
1. The server 'Allow Azure Services' setting is OFF. 
2. A virtual network rule is added for 'AppSubnet' with the service endpoint for Microsoft.Sql enabled. 
3. The server firewall has no other rules. 
4. The 'Minimum TLS version' is set to 1.2. 
5. All client applications are configured to use 'Encrypt=True' and 'TrustServerCertificate=False' in their connection strings.

After deployment, you discover that connections from the VMs are failing with error: 'The certificate chain was issued by an authority that is not trusted'. What is the most likely cause of this issue?

Accepted Answer

The VMs do not have the necessary root CA certificates installed to validate the Azure SQL Database server certificate.. The error 'The certificate chain was issued by an authority that is not trusted' indicates that the client cannot validate the Azure SQL Database server certificate. With 'TrustServerCertificate=False', the client must trust the server's certificate chain, which requires the root CA certificates (e.g., Baltimore CyberTrust Root) to be installed in the VM's trusted root store. Since the VMs are in Azure, they typically have these certificates, but custom images or hardened VMs may lack them, causing the validation failure.

Answer

The client applications are using an older version of .NET that does not support TLS 1.2.

Answer

The virtual network rule is missing the service endpoint for Microsoft.Sql on the subnet.

Answer

The server firewall is blocking traffic from the VNet because 'Allow Azure Services' is OFF.

Question 17

You are designing a secure architecture for Azure SQL Managed Instance. You need to ensure that all connections to the instance are encrypted and that the instance can only be accessed from a specific virtual network. Which TWO configurations should you implement?

Accepted Answer

Configure the managed instance with the 'Force encryption' property set to Enabled.. Option C is correct because setting the 'Force encryption' property to Enabled on Azure SQL Managed Instance enforces TLS encryption for all client connections, ensuring data in transit is encrypted. Option E is correct because Azure SQL Managed Instance must be deployed inside a delegated subnet within an Azure virtual network, which inherently restricts access to that specific VNet and its connected networks. Option A is incorrect because service endpoints are used for PaaS services like Azure SQL Database or Storage, not for SQL Managed Instance, which requires native VNet integration via a delegated subnet. Option B is incorrect because a managed instance cannot be deployed outside a virtual network; VNet deployment is mandatory. Option D is incorrect because private endpoints are used to access PaaS services privately, but SQL Managed Instance already resides in a VNet and does not use private endpoints for its own connectivity.

Answer

Configure a service endpoint on the subnet of the managed instance.

Answer

Deploy the managed instance outside a virtual network.

Answer

Attach a private endpoint to the managed instance.

Question 18

You are the database administrator for a company that uses Azure SQL Database. The company has a strict security policy requiring that all database connections be encrypted and that the server's firewall only allows connections from a list of approved IP addresses. You have configured the server-level firewall rules accordingly and enabled the 'Force encryption' setting on the server. However, after deployment, you notice that an application running on an Azure virtual machine is able to connect to the database even though its public IP address is not in the approved list. The virtual machine is in the same region as the database. What is the most likely cause?

Accepted Answer

The 'Allow Azure services and resources to access this server' setting is enabled, which allows all Azure IPs to connect.. The 'Allow Azure services and resources to access this server' setting, when enabled, adds a firewall rule that permits connections from all Azure IP addresses, including those not in the approved list. This overrides the specific IP-based firewall rules, allowing the Azure VM (even with a public IP not on the approved list) to connect. The setting is designed to allow Azure services like Azure App Service or Azure VMs to bypass IP restrictions, which violates the strict security policy in this scenario.

Answer

The virtual machine is using a private IP address that is automatically allowed.

Answer

The virtual machine is using a service endpoint that bypasses the firewall.

Answer

The 'Force encryption' setting is misconfigured; it should be disabled to allow non-encrypted connections.

Question 19

Refer to the exhibit. The exhibit shows an Azure role assignment with a condition. When user@contoso.com tries to read data from the database 'proddb', what will be the effect of this condition?

Accepted Answer

The user will be denied the ability to read data from the database.. The condition in the Azure role assignment explicitly denies read access to the database 'proddb' for user@contoso.com. In Azure RBAC, a deny assignment overrides any allow assignments, so even if the user has other roles granting read permissions, this condition will block all data read operations on that specific database. Therefore, the user will be denied the ability to read data from the database.

Answer

The user will be allowed to read all data from the database.

Answer

The user will be able to read the database metadata but not the data.

Answer

The user will be able to assign the role to others.

Question 20

Match each Azure SQL Database pricing tier to its key feature.

Accepted Answer

Basic: Low-cost, lightweight workloads with minimal compute and memory resources.. These tiers define the compute and memory resources available for Azure SQL Database.

Answer

Basic: Balanced compute and memory resources for most enterprise workloads with scalable storage.

Answer

Standard: High memory-to-core ratio for memory-intensive workloads like analytics.