DP-300 Implement a secure environment — 20 Questions

Question 1

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?

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

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 2

Which TWO of the following are best practices for managing firewall rules for Azure SQL Database?

Accepted Answer

Use Azure Private Link to connect from Azure VNets instead of opening firewall rules to IP ranges.. The two best practices for managing firewall rules for Azure SQL Database are: (1) Use Azure Private Link to connect from Azure VNets instead of opening firewall rules to IP ranges (Option C). This ensures traffic stays within the Microsoft backbone network and reduces the attack surface by avoiding public IP exposure. (2) Audit all firewall rule changes using Azure Activity Logs (Option D). Auditing provides visibility into changes, helps detect misconfigurations, and supports compliance requirements. Option A is incorrect because it recommends IP-based rules for all client connections, which increases exposure. Option B is incorrect because broad IP ranges (e.g., 0.0.0.0/0) create security vulnerabilities. Option E is incorrect because enabling 'Allow Azure Services' opens access to all Azure services, which is overly permissive and should be avoided in favor of more specific rules or Private Link.

Answer

Use IP-based firewall rules for all client connections, including Azure services.

Answer

Create firewall rules with broad IP ranges (e.g., 0.0.0.0/0) to simplify management.

Answer

Enable the 'Allow Azure Services' firewall rule to allow connections from Azure services.

Question 3

Your organization has Azure SQL Database with several databases. You need to implement a solution that allows a junior DBA to view the security logs for failed logins but not modify any security settings. What is the minimum role assignment needed on the logical server?

Accepted Answer

Assign the Reader role.. The Reader role at the logical server level provides read-only access to all resources, including security logs for failed logins, without granting any modification permissions. It is the minimum built-in Azure RBAC role that allows viewing these logs while preventing any changes to security settings. The SQL Security Manager role also allows viewing logs but permits updating security policies, which violates the 'not modify any security settings' requirement.

Answer

Assign the SQL Security Manager role.

Answer

Assign the Contributor role.

Answer

Assign the SQL DB Contributor role.

Question 4

Which TWO of the following are valid methods to configure network security for Azure SQL Managed Instance?

Accepted Answer

Deploy the instance in an isolated subnet with network security groups (NSGs).. Azure SQL Managed Instance is deployed into a dedicated subnet within a virtual network. Network security is enforced via network security groups (NSGs) and route tables on that subnet, not through virtual network rules. Therefore, option A is incorrect. Options B and C are correct: B describes the use of NSGs on the isolated subnet, and C describes Azure Private Link, which provides private connectivity. Option D (server-level IP firewall rules) and option E (service endpoints) are not applicable to Managed Instance.

Answer

Configure a virtual network rule to allow traffic from a specific subnet.

Answer

Configure server-level IP firewall rules.

Answer

Enable service endpoints on the subnet.

Question 5

Your organization uses Azure SQL Database with a geo-replication configuration. The primary server is in the East US region and the secondary is in West US. You need to ensure that if a regional outage occurs, failover can be initiated manually and the secondary database will have the same security settings as the primary. What should you configure?

Accepted Answer

Create an auto-failover group with read-write failover policy.. Auto-failover groups replicate server-level security settings (firewall rules, server admin, Azure AD admin) from the primary to the secondary server automatically. They also support manual failover initiation (as well as automatic), allowing you to manually initiate failover during a regional outage while ensuring the secondary database has the same security settings. Option A is incorrect because active geo-replication only replicates database-level security (contained users, roles, permissions); server-level security settings are not replicated and require manual configuration on the secondary server, which does not meet the requirement for having the same security settings automatically. Option C is incorrect because database copy is a one-time operation that does not maintain ongoing replication; changes after the copy are lost, and server-level security settings are not replicated. Option D is incorrect because backup and restore does not provide continuous replication; it requires manual restore steps and server-level security settings are not automatically transferred.

Answer

Configure active geo-replication between the databases and manually initiate failover if needed.

Answer

Use database copy to create a copy in the secondary region.

Answer

Use backup and restore to copy the database to the secondary region.

Question 6

You attempt to create a new Azure SQL Database server with public network access enabled. What will happen?

Accepted Answer

The creation will be denied because the policy prohibits public network access.. In this scenario, it is assumed that an Azure Policy is in place that prohibits public network access for Azure SQL Database servers. Azure Policy enforces rules at the Azure Resource Manager level. Therefore, when you attempt to create a server with public network access enabled, the policy evaluation occurs before provisioning, and the creation request is denied. This prevents the server from being created at all.

Answer

The server will be created, but an audit log will be generated.

Answer

The server will be created successfully with public network access enabled.

Answer

The server will be created, but the databases will be denied creation.

Question 7

What will be the result of this command?

Accepted Answer

The database will log all successful and failed authentication attempts to the storage account for 90 days.. The command enables auditing on the database. When auditing is enabled, by default both successful and failed authentication attempts are logged. The retention period is set to 90 days. Therefore, all authentication attempts are logged and retained for 90 days.

Answer

The database will log only failed authentication attempts to the storage account for 90 days.

Answer

The command will fail because the StorageAccountResourceId parameter is incorrect.

Answer

The command will enable auditing for the server rather than the database.

Question 8

You execute the following query:

SELECT c.client_ip, c.application_name
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
WHERE s.action_id = 'LGIF' AND s.state = 'ABORT';

What does this query return?

Accepted Answer

Successful login events with client IP and application name.. The query does not return failed login attempts because a failed login does not create a session in sys.dm_exec_sessions. The DMVs only contain information for authenticated (successful) sessions. While the WHERE clause filters on action_id='LGIF' and state='ABORT', these columns do not exist in these DMVs; as written, the query would likely return no rows or an error. However, if the columns were present, the DMVs still would not capture failed logins. The only plausible result among the options is successful login events (D), as the DMVs are sources for active session data.

Answer

All security audit events for the database.

Answer

Failed login events at the server level.

Answer

Failed login attempts to the database, including client IP and application name.

Question 9

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 10

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 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 configuring Azure SQL Database firewall rules for a new application. The application runs on Azure VMs in the same region. To minimize latency and security risk, which approach should you use?

Accepted Answer

Configure a virtual network service endpoint and a virtual network firewall rule.. Using a virtual network service endpoint and a virtual network firewall rule allows Azure SQL Database to accept traffic only from the specific subnet hosting the application VMs, without exposing the database to the public internet. This minimizes latency by keeping traffic within the Azure backbone network and reduces the security risk by eliminating broad IP-based rules.

Answer

Add a firewall rule allowing all Azure IP addresses.

Answer

Add a firewall rule for each VM's public IP address.

Answer

Add a firewall rule allowing all Azure services to access the database.

Question 13

You need to audit all successful and failed login attempts to an Azure SQL Database. Which feature should you enable?

Accepted Answer

Azure SQL Auditing. Azure SQL Auditing is the correct feature because it tracks database events, including both successful and failed login attempts, and writes them to an audit log in your Azure Storage account, Log Analytics workspace, or Event Hubs. This allows you to monitor and review authentication activity for compliance and security analysis. Other features like Advanced Threat Protection, TDE, and Vulnerability Assessment do not capture login event logs.

Answer

Advanced Threat Protection

Answer

Transparent Data Encryption (TDE)

Answer

SQL Vulnerability Assessment

Question 14

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?

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 15

You are designing a secure environment for Azure SQL Database. Which authentication method provides the strongest security and supports multi-factor authentication?

Accepted Answer

Azure Active Directory authentication. Azure Active Directory (Azure AD) authentication is the recommended method for Azure SQL Database because it supports multi-factor authentication (MFA), conditional access policies, and identity-driven security. It eliminates the need for password management and leverages Azure AD's built-in security features, providing the strongest security posture for cloud-native environments.

Answer

Certificate-based authentication

Answer

SQL authentication with strong passwords

Answer

Windows authentication

Question 16

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 17

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 Azure AD 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 Azure AD 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 18

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 19

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 20

Which TWO of the following are best practices for securing Azure SQL Database?

Accepted Answer

Enable firewall rules to restrict access to specific IP addresses.. Azure SQL Database firewall rules allow you to restrict access to specific IP address ranges, which is a fundamental network security best practice. By limiting inbound traffic to only trusted IPs, you reduce the attack surface and prevent unauthorized connections from unknown sources. This is a first line of defense in a defense-in-depth strategy for securing Azure SQL Database.

Answer

Enable Auditing to block malicious queries.

Answer

Enable TDE to prevent SQL injection attacks.

Answer

Use SQL authentication with complex passwords.