CCNA Dp300 Secure Environment Questions — Page 2 of 3

MCQmedium

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?

A.Store the key in a different region for disaster recovery.

B.Ensure the previous key version remains enabled in Azure Key Vault.

C.Use a premium tier of Azure Key Vault.

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

AnswerB

TDE uses the key version that was active when the data was written; disabling it causes decryption failures.

Why this answer

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.

Exam trap

The trap here is that candidates often assume key rotation automatically re-encrypts the database or that disabling the old key is safe, but Azure SQL Database requires the previous key version to remain enabled until the DEK is explicitly re-encrypted with the new key.

How to eliminate wrong answers

Option A is wrong because storing the key in a different region is a disaster recovery strategy, not a requirement for maintaining accessibility after key rotation; TDE keys must be available in the same region as the database for decryption. Option C is wrong because the Azure Key Vault tier (Standard vs. Premium) does not affect the ability to access data after key rotation; the critical factor is key version availability, not the vault's performance or feature set.

Option D is wrong because re-encrypting the database with the new key is not required after rotation; TDE automatically uses the new key version for new data, but the old key version must remain enabled to decrypt existing data until the DEK is re-encrypted, which is a separate manual operation.

Practice this question →

MCQeasy

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

A.Set the 'minimal TLS version' to 1.2 in the server's properties in the Azure portal.

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

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

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

AnswerA

This enforces TLS 1.2 for all databases on the server.

Why this answer

Option B is correct because Azure SQL Database has a server-level setting to enforce minimum TLS version. Option A is wrong because there is no database-level TLS setting. Option C is wrong because the firewall does not control encryption protocols.

Option D is wrong because auditing is unrelated.

Practice this question →

MCQmedium

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database. The template configures backup retention. What is the effect of this configuration?

A.Full backups are taken every 12 hours and retained for 7 days.

B.Long-term retention (LTR) is set to 7 days.

C.Point-in-time restore (PITR) backups are retained for 7 days, and differential backups occur every 12 hours.

D.Transaction log backups are taken every 12 hours.

AnswerC

The properties directly set PITR retention and differential backup interval.

Why this answer

The ARM template configures the backup retention settings for Azure SQL Database. By default, Azure SQL Database automatically performs full backups every week, differential backups every 12 hours, and transaction log backups every 5–10 minutes. The configuration shown sets the point-in-time restore (PITR) retention period to 7 days, meaning you can restore the database to any point within the last 7 days.

Differential backups occur every 12 hours to support efficient PITR, but the retention setting directly controls how far back you can perform a point-in-time restore.

Exam trap

The trap here is that candidates confuse the PITR retention period with the frequency of backups, or assume that the retention setting controls the backup schedule (e.g., thinking full backups occur every 12 hours), when in fact it only controls how long backups are kept, not how often they are taken.

How to eliminate wrong answers

Option A is wrong because full backups in Azure SQL Database are taken once per week, not every 12 hours, and the retention setting shown does not change the full backup frequency. Option B is wrong because long-term retention (LTR) is a separate feature that retains full backups for up to 10 years, configured via a different policy, not the 7-day PITR retention setting shown. Option D is wrong because transaction log backups are taken every 5–10 minutes, not every 12 hours, and their frequency is not configurable via this retention setting.

Practice this question →

Drag & Dropmedium

Drag and drop the steps to restore an Azure SQL Database to a point in time in the correct order.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps

Order

Why this order

The restore process starts by selecting the database, then choosing the restore type, specifying the point in time, naming the new database, and finally creating it.

Practice this question →

Multi-Selecthard

You are deploying a new Azure SQL Database that will store Personally Identifiable Information (PII). You need to ensure that the data is encrypted at rest and that access to encryption keys is logged. Which THREE actions should you take? (Choose three.)

Select 3 answers

A.Use Always Encrypted to encrypt all PII columns.

B.Grant the managed identity of the database access to the key vault with 'get', 'wrapKey', and 'unwrapKey' permissions.

C.Configure TDE with service-managed keys.

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

E.Enable auditing on the Azure Key Vault to log key operations.

AnswersB, D, E

This is necessary for TDE with CMK to access the encryption key.

Why this answer

Option B is correct because to use customer-managed TDE keys stored in Azure Key Vault, the Azure SQL Database's managed identity must be granted 'get', 'wrapKey', and 'unwrapKey' permissions. This allows the database to access the key for encryption and decryption operations while maintaining a secure, auditable key management chain.

Exam trap

The trap here is that candidates often confuse Always Encrypted with TDE, selecting Always Encrypted for at-rest encryption when it is actually designed for client-side encryption of sensitive columns, not for full database-level encryption at rest.

Practice this question →

MCQeasy

Your organization has a policy that all Azure SQL Database connections must use Microsoft Entra authentication. You need to ensure that application developers cannot accidentally use SQL authentication. What should you do?

A.Configure server-level firewall rules to block all IP addresses except Azure services.

B.Disable SQL authentication for all contained database users.

C.Create a database-level trigger to reject connections using SQL authentication.

D.Enable 'Azure AD-only authentication' on the logical server.

AnswerD

This setting disables SQL authentication and enforces Microsoft Entra authentication for all connections.

Why this answer

Option D is correct because enabling 'Azure AD-only authentication' on the logical server explicitly blocks all SQL authentication connections, including those from contained database users. This setting enforces that only Microsoft Entra ID (formerly Azure AD) principals can authenticate, directly aligning with the policy to prevent accidental use of SQL authentication.

Exam trap

The trap here is that candidates may think disabling SQL authentication for contained users (Option B) is sufficient, but they miss that the server-level authentication policy must be enforced to block all SQL authentication attempts, including those from server-level logins or newly created contained users.

How to eliminate wrong answers

Option A is wrong because server-level firewall rules control network access, not authentication methods; they cannot distinguish between SQL and Entra ID authentication. Option B is wrong because disabling SQL authentication for contained database users does not prevent SQL authentication at the server level; a contained user could still be created with SQL authentication if the server allows it. Option C is wrong because a database-level trigger cannot intercept or reject connections; triggers fire after a connection is established, so they cannot block the initial authentication attempt.

Practice this question →

MCQhard

Refer to the exhibit. You are configuring an Azure SQL Database security alert policy. What is the most significant misconfiguration?

A.The retentionDays is set to 0, which is not supported

B.Some alerts are disabled, which could leave the database vulnerable

C.Emailing account admins may not be sufficient for notification

D.Multiple email addresses are specified, which may cause duplicate notifications

AnswerA

Retention days must be between 1 and 90.

Why this answer

The most significant misconfiguration is setting `retentionDays` to 0 because Azure SQL Database security alert policies require a retention period between 1 and 365 days. A value of 0 is invalid and will cause the policy to fail validation or be rejected, meaning the alert policy cannot be created or updated. This directly prevents the security alerts from being configured at all, making it a critical blocking issue.

Exam trap

The trap here is that candidates focus on operational concerns like notification methods or alert enablement, but the exam tests knowledge of Azure's specific API validation rules, where an invalid parameter value (retentionDays=0) is a hard failure that blocks the entire configuration.

How to eliminate wrong answers

Option B is wrong because disabling some alerts is a deliberate design choice to reduce noise; it does not inherently leave the database vulnerable if the disabled alerts are not relevant to the threat model. Option C is wrong because emailing account admins is a supported and sufficient notification method for security alerts, and the question asks for the most significant misconfiguration, not a best-practice improvement. Option D is wrong because specifying multiple email addresses is allowed and does not cause duplicate notifications; each address receives the same alert once, which is the intended behavior for broad notification.

Practice this question →

MCQhard

You are responsible for securing an Azure SQL Database. You need to implement data masking for a column that contains credit card numbers, ensuring that users with the db_datareader role see a masked version. However, users with the db_owner role should see the unmasked data. What should you configure?

A.Apply Dynamic Data Masking (DDM) to the credit card column.

B.Implement Row-Level Security (RLS) to filter rows based on user role.

C.Implement Always Encrypted with deterministic encryption.

D.Enable Transparent Data Encryption (TDE).

AnswerA

DDM masks data for non-privileged users; db_owner sees unmasked data by default.

Why this answer

Dynamic Data Masking (DDM) is the correct choice because it allows you to obfuscate sensitive data in query results for non-privileged users (like db_datareader) while permitting users with elevated permissions (like db_owner) to see the unmasked data. DDM is applied at the column level and does not modify the underlying data; it simply masks the output based on the user's permissions. The db_owner role is exempt from masking by default, meeting the requirement exactly.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with Always Encrypted, thinking both provide role-based visibility, but Always Encrypted requires key management and does not support partial masking or role-based exemption without separate keys.

How to eliminate wrong answers

Option B is wrong because Row-Level Security (RLS) controls which rows a user can access based on a predicate function, not which columns are masked; it cannot hide the credit card number within a row. Option C is wrong because Always Encrypted with deterministic encryption encrypts data at rest and in transit, but it does not allow role-based masking—users with the encryption key see plaintext, while others see ciphertext, not a masked format. Option D is wrong because Transparent Data Encryption (TDE) encrypts the entire database at rest but does not provide any per-column or per-user masking; it protects against unauthorized access to the physical files, not against authorized database users.

Practice this question →

MCQhard

Your company uses Azure SQL Database and needs to protect sensitive columns (e.g., credit card numbers) from being accessed by unauthorized users. You implement Always Encrypted. However, some queries that perform pattern matching on the encrypted column are failing because the column cannot be searched. What should you do to allow pattern matching while maintaining security?

A.Enable Always Encrypted with secure enclaves and use a column master key that supports enclave computations.

B.Implement row-level security (RLS) to filter rows based on user identity.

C.Change the encryption type from randomized to deterministic encryption.

D.Use Dynamic Data Masking (DDM) to mask the column for unauthorized users instead of encryption.

AnswerA

Secure enclaves enable rich computations on encrypted data, including pattern matching.

Why this answer

Always Encrypted with secure enclaves allows computations, including pattern matching (LIKE, equality, comparisons), on encrypted columns by using a trusted execution environment (e.g., Intel SGX). The column master key must support enclave computations (enclave-enabled key) to permit the SQL Server engine to offload operations to the enclave. This preserves encryption at rest and in transit while enabling rich query patterns.

Exam trap

The trap here is that candidates often confuse deterministic encryption (which enables equality) with the ability to perform pattern matching, or they mistakenly think Dynamic Data Masking or row-level security can substitute for encrypted search capabilities.

How to eliminate wrong answers

Option B is wrong because row-level security (RLS) controls which rows a user can see based on predicates, but it does not enable pattern matching on encrypted columns; the column remains encrypted and unsearchable. Option C is wrong because changing from randomized to deterministic encryption only enables equality searches (e.g., WHERE column = 'value'), not pattern matching (LIKE '%pattern%'), and deterministic encryption is more vulnerable to frequency analysis attacks. Option D is wrong because Dynamic Data Masking (DDM) only obfuscates data at query results for unauthorized users; it does not encrypt the column, so sensitive data is still stored in plaintext and accessible to privileged users, failing the core security requirement.

Practice this question →

Multi-Selecthard

Which THREE are best practices for securing Azure SQL Database? (Choose three.)

Select 3 answers

A.Use Microsoft Entra ID authentication instead of SQL authentication.

B.Use Azure SQL Database firewall rules to restrict access to known IP addresses.

C.Enable Transparent Data Encryption (TDE) for all databases.

D.Enable public network access to allow flexible connectivity.

E.Grant db_owner role to developers for ease of management.

AnswersA, B, C

Entra ID provides stronger authentication and supports conditional access.

Why this answer

Option A is correct because Microsoft Entra ID (formerly Azure AD) authentication provides centralized identity management, supports conditional access policies, and eliminates the need to manage SQL logins and passwords. It also enables features like multifactor authentication (MFA) and integration with Azure RBAC, which significantly reduces the risk of credential theft compared to SQL authentication.

Exam trap

The trap here is that candidates often confuse 'public network access' with 'flexible connectivity' and overlook that private endpoints or Azure service endpoints are the secure alternatives, while also mistakenly thinking that granting db_owner simplifies management without considering the security implications of over-privileged accounts.

Practice this question →

MCQeasy

You are the database administrator for a company that uses Azure SQL Managed Instance. You need to allow a specific application to connect to the database using a service principal. The application authenticates with Microsoft Entra ID. What should you configure?

A.Create a contained database user mapped to the Microsoft Entra service principal.

B.Enable Always Encrypted and configure column master key with the service principal.

C.Add a server-level firewall rule with the application's IP address.

D.Create a SQL authentication login and user for the application.

AnswerA

This enables the service principal to authenticate to the database.

Why this answer

A contained database user mapped to a Microsoft Entra ID service principal allows the application to authenticate directly to the database using its Microsoft Entra identity, without requiring a SQL Server login. This is the correct approach because Azure SQL Managed Instance supports Microsoft Entra authentication for service principals, enabling token-based authentication from applications that authenticate with Microsoft Entra ID.

Exam trap

The trap here is that candidates often confuse network-level controls (firewall rules) or encryption features (Always Encrypted) with authentication mechanisms, or mistakenly think SQL authentication can be used with Microsoft Entra ID service principals, when in fact a contained user mapped to the service principal is required.

How to eliminate wrong answers

Option B is wrong because Always Encrypted with a column master key protects data at rest and in transit but does not provide authentication; it is a data encryption feature, not an identity or access control mechanism. Option C is wrong because a server-level firewall rule controls network access by IP address, not authentication; the application already needs to authenticate, and firewall rules do not grant database access to a service principal. Option D is wrong because SQL authentication uses a username and password stored in the database, which is not compatible with Microsoft Entra ID service principals; the application authenticates via Microsoft Entra ID, not SQL credentials.

Practice this question →

MCQmedium

Your company uses Azure SQL Database and requires that all connections use Azure Active Directory (now Microsoft Entra ID) authentication. You need to ensure that no SQL authentication logins exist in the database. What should you do?

A.Set the server property 'DisableSqlAuthentication' to true.

B.Set the database property 'contained database authentication' to 0.

C.Use the Azure portal to set the firewall rule to block SQL authentication.

D.Query sys.sql_logins to identify any SQL authentication logins and drop them.

AnswerD

This allows you to remove any existing SQL logins, enforcing Microsoft Entra-only authentication.

Why this answer

Option D is correct because the only way to ensure no SQL authentication logins exist in an Azure SQL Database is to query the `sys.sql_logins` system catalog view to identify any SQL-authenticated logins and then drop them. Azure SQL Database does not support a server-level property like `DisableSqlAuthentication` to block SQL authentication; instead, you must explicitly remove SQL logins to enforce exclusive use of Microsoft Entra ID authentication.

Exam trap

The trap here is that candidates confuse the server-level property 'DisableSqlAuthentication' (which exists only in Azure SQL Managed Instance) with Azure SQL Database, or they mistakenly think firewall rules can filter by authentication type, when in fact firewall rules only control IP-based access.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database does not have a server property named 'DisableSqlAuthentication'; this property exists in Azure SQL Managed Instance but not in Azure SQL Database. Option B is wrong because setting 'contained database authentication' to 0 controls whether contained database users can be created, but it does not affect server-level SQL logins or prevent existing SQL logins from connecting. Option C is wrong because firewall rules control network access by IP address, not authentication method; blocking SQL authentication via the portal is not a supported operation—firewall rules cannot distinguish between SQL and Entra ID authentication.

Practice this question →

MCQmedium

Your team uses Azure SQL Database and wants to use Microsoft Entra ID authentication. You need to create a contained database user mapped to a Microsoft Entra ID application (service principal). Which T-SQL command should you use?

A.CREATE USER [app-name] FROM LOGIN [app-name];

B.CREATE LOGIN [app-name] FROM EXTERNAL PROVIDER;

C.CREATE USER [app-name] WITH PASSWORD = 'password';

D.CREATE USER [app-name] FROM EXTERNAL PROVIDER;

AnswerD

This creates a contained user mapped to an Entra ID identity.

Why this answer

Option D is correct because `CREATE USER [app-name] FROM EXTERNAL PROVIDER` creates a contained database user mapped to a Microsoft Entra ID identity (user, group, or application) directly in the database, without requiring a server-level login. This is the required syntax for authenticating a service principal (application) in Azure SQL Database using Microsoft Entra ID authentication.

Exam trap

The trap here is that candidates confuse `CREATE LOGIN ... FROM EXTERNAL PROVIDER` (which creates a server-level principal) with `CREATE USER ... FROM EXTERNAL PROVIDER` (which creates a contained database user), leading them to select Option B when the requirement is specifically for a contained user.

How to eliminate wrong answers

Option A is wrong because `CREATE USER ... FROM LOGIN` maps a database user to an existing SQL Server login, not to a Microsoft Entra ID identity; it does not support external providers. Option B is wrong because `CREATE LOGIN ...

FROM EXTERNAL PROVIDER` creates a server-level login for an Entra ID identity, but the question asks for a contained database user, not a login; contained users do not require a server login. Option C is wrong because `CREATE USER ... WITH PASSWORD` creates a SQL authentication user with a password, which does not use Microsoft Entra ID authentication at all.

Practice this question →

MCQhard

What will be the result of this command?

A.The database will log all successful and failed authentication attempts to the storage account for 90 days.

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

C.The command will fail because the StorageAccountResourceId parameter is incorrect.

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

AnswerA

AuditActionGroup specifies the events to log, and RetentionInDays sets the retention.

Why this answer

The command configures SQL Database auditing to send audit logs to an Azure Storage account. By default, when auditing is enabled with the AUDIT_SPECIFICATION set to AUDIT_ALL (or the default audit action group), both successful and failed authentication attempts are logged. The RETENTION_DAYS parameter set to 90 ensures logs are retained for 90 days before being automatically deleted.

Option A correctly describes this behavior.

Exam trap

The trap here is that candidates often assume auditing only logs failures by default, but the default audit action group includes both successful and failed logins, making Option A correct and Option B a common distractor.

How to eliminate wrong answers

Option B is wrong because the default audit action group includes both successful and failed logins (DATABASE_LOGOUT_GROUP, DATABASE_LOGIN_GROUP, etc.), not just failed attempts. Option C is wrong because the StorageAccountResourceId parameter is correctly formatted as a full Azure Resource Manager resource ID; the command will not fail due to this parameter unless the storage account does not exist or permissions are missing. Option D is wrong because the command targets the database level (ALTER DATABASE ...

SET AUDIT_SPECIFICATION), not the server level; server-level auditing is configured via different T-SQL commands or Azure portal settings.

Practice this question →

MCQmedium

You are responsible for security compliance of Azure SQL databases. You need to audit all successful and failed login attempts and store the audit logs in a Log Analytics workspace for analysis. You also want to detect potential brute-force attacks. What should you implement?

A.Configure Azure Policy to enforce auditing on all SQL databases in the subscription.

B.Enable SQL Vulnerability Assessment and schedule recurring scans.

C.Enable Azure SQL Auditing for the server, configure the audit log destination to Log Analytics, and enable Microsoft Sentinel for threat detection.

D.Enable Advanced Threat Protection (ATP) for Azure SQL Database.

AnswerC

This combination provides logging and analysis.

Why this answer

Option C is correct because Azure SQL Auditing captures both successful and failed login attempts (audit logs) and can be configured to send them directly to a Log Analytics workspace for centralized analysis. Microsoft Sentinel, when enabled, provides built-in analytics rules to detect brute-force attacks by correlating failed login patterns across time and IP addresses, fulfilling the threat detection requirement.

Exam trap

The trap here is that candidates confuse Advanced Threat Protection (ATP) with the combination of auditing and Sentinel, assuming ATP alone covers login auditing and brute-force detection, but ATP does not capture all login attempts nor store them in Log Analytics for custom analysis.

How to eliminate wrong answers

Option A is wrong because Azure Policy enforces compliance rules (e.g., requiring auditing to be enabled) but does not itself capture login audit logs or detect brute-force attacks; it only ensures the auditing setting is applied. Option B is wrong because SQL Vulnerability Assessment identifies database misconfigurations and missing patches, not login attempts or brute-force patterns; it focuses on security vulnerabilities, not authentication events. Option D is wrong because Advanced Threat Protection (ATP) for Azure SQL Database detects anomalous activities like SQL injection or unusual access patterns, but it does not specifically audit all successful and failed login attempts nor store those logs in Log Analytics; ATP relies on telemetry separate from the audit log stream.

Practice this question →

MCQhard

You are the database administrator for a large e-commerce company. The company has an Azure SQL Database named SalesDB that stores sensitive customer data including credit card numbers and personal details. The security team has mandated the following requirements: 1. All customer credit card numbers must be encrypted at rest and in transit between the application and the database. The encryption keys must be stored in a hardware security module (HSM) managed by the company. 2. All access to the database must be authenticated using Microsoft Entra ID, and multi-factor authentication (MFA) must be enforced for all administrative users. 3. Any attempts to access the database from unusual geographic locations must be detected and automatically blocked for 24 hours. 4. All schema changes must be audited, and the audit logs must be sent to a central Log Analytics workspace for analysis. Currently, the database uses SQL authentication, no encryption, and no auditing. You need to implement the required security controls with minimal downtime. What should you do?

A.Enable Always Encrypted for the credit card columns with keys stored in Azure Key Vault. Keep SQL authentication but require users to use MFA via a VPN. Enable TDE for at-rest encryption.

B.Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault. Configure Azure SQL Auditing to send logs to Log Analytics. Enable Microsoft Defender for SQL.

C.Use Dynamic Data Masking to mask credit card numbers. Enable TDE and use Azure SQL Auditing with a storage account. Enable Microsoft Defender for SQL.

D.Enable Always Encrypted for the credit card columns using Azure Key Vault Managed HSM for column master keys. Switch to Microsoft Entra ID authentication and configure Conditional Access policy to require MFA for administrators. Enable Microsoft Defender for SQL with anomaly detection and automatic blocking. Enable Azure SQL Auditing with Log Analytics destination.

AnswerD

Always Encrypted encrypts data at rest and in transit; Entra ID with MFA meets authentication; Defender for SQL detects and blocks anomalies; Auditing sends to Log Analytics.

Why this answer

Option D is correct because it directly addresses all mandated requirements: Always Encrypted with Azure Key Vault Managed HSM ensures credit card numbers are encrypted at rest and in transit with customer-managed HSM keys; switching to Microsoft Entra ID authentication with a Conditional Access policy enforces MFA for administrators; Microsoft Defender for SQL provides anomaly detection that can automatically block access from unusual geographic locations for 24 hours; and Azure SQL Auditing configured to send logs to Log Analytics meets the central audit requirement. This combination provides the required security controls with minimal downtime as Always Encrypted can be enabled online.

Exam trap

The trap here is that candidates often confuse Transparent Data Encryption (TDE) with Always Encrypted, assuming TDE alone satisfies encryption at rest and in transit, but TDE only protects data at rest and does not encrypt data in transit or provide client-side encryption for sensitive columns like credit card numbers.

How to eliminate wrong answers

Option A is wrong because it keeps SQL authentication, which violates the requirement to use Microsoft Entra ID authentication, and requiring MFA via VPN is not a substitute for native Entra ID MFA enforcement; also, TDE alone does not encrypt data in transit between the application and database. Option B is wrong because TDE encrypts data at rest but does not encrypt data in transit or provide column-level encryption for credit card numbers, and it does not address the requirement for MFA or automatic blocking of unusual geographic locations. Option C is wrong because Dynamic Data Masking only obscures data from non-privileged users, it does not encrypt data at rest or in transit, and it does not meet the requirement for HSM-managed keys or MFA enforcement.

Practice this question →

Multi-Selectmedium

You manage an Azure SQL Database that is accessed by several applications. You need to implement the principle of least privilege for database access. Which three actions should you take? (Choose three.)

Select 3 answers

A.Create contained database users instead of server-level logins.

B.Assign users to custom database roles with specific permissions.

C.Add users to the db_datareader role.

D.Configure firewall rules to restrict IP addresses.

E.Grant permissions at the object level (e.g., SELECT on specific tables) rather than at the schema level.

AnswersA, B, E

Contained users reduce server-level privilege.

Why this answer

Contained database users are authenticated directly within the database, independent of the server-level logins. This aligns with the principle of least privilege by avoiding the need for server-level permissions, which would grant broader access across the server. In Azure SQL Database, contained users are the recommended approach for database-level access control, as they limit the blast radius of a compromised credential to a single database.

Exam trap

The trap here is that candidates may confuse network security controls (firewall rules) with database access controls, or assume that built-in roles like db_datareader are acceptable for least privilege, when in fact they grant excessive permissions.

Practice this question →

MCQmedium

You have an Azure SQL Managed Instance that needs to be accessed from an on-premises application. The connection must be encrypted and the on-premises network uses an ExpressRoute circuit. You need to configure the managed instance to only accept connections from the on-premises network. What should you do?

A.Enable a public endpoint and restrict access using a firewall rule.

B.Configure a server-level firewall rule to allow the on-premises IP range.

C.Connect the on-premises network to the managed instance's virtual network using ExpressRoute and configure network security group (NSG) rules to restrict inbound traffic to the on-premises subnet.

D.Create a private endpoint for the managed instance and configure DNS.

AnswerC

ExpressRoute provides private connectivity, and NSGs control traffic within the VNet.

Why this answer

Option C is correct because Azure SQL Managed Instance is deployed into a virtual network, and the most secure way to allow only on-premises traffic is to extend the on-premises network into that VNet via ExpressRoute (private peering) and then use Network Security Group (NSG) rules to restrict inbound connections to only the on-premises subnet. This ensures the connection remains private, encrypted (via the ExpressRoute circuit), and does not expose a public endpoint.

Exam trap

The trap here is that candidates often confuse SQL Managed Instance with Azure SQL Database, incorrectly assuming that server-level firewall rules or private endpoints apply to Managed Instance, when in fact Managed Instance relies entirely on VNet integration and NSG rules for network security.

How to eliminate wrong answers

Option A is wrong because enabling a public endpoint would expose the managed instance to the internet, contradicting the requirement to accept connections only from the on-premises network, and firewall rules on a public endpoint cannot guarantee private-only traffic. Option B is wrong because server-level firewall rules apply only to public endpoints or Azure SQL Database/Server, not to SQL Managed Instance, which is always deployed in a VNet and does not support server-level IP firewall rules for inbound traffic. Option D is wrong because creating a private endpoint for a managed instance is not supported—SQL Managed Instance already resides in a VNet and uses a native VNet endpoint; a private endpoint is used for Azure SQL Database or SQL Server, not for Managed Instance.

Practice this question →

MCQeasy

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?

A.Configure a vulnerability assessment recurring scan and email the report.

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

C.In the Microsoft Defender for SQL settings, enable 'Email notifications to admins and subscription owners'.

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

AnswerC

This directly sends email alerts for detected threats.

Why this answer

Option C is correct because 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.

Exam trap

The trap here is that candidates often confuse the purpose of vulnerability assessment (periodic scanning) with real-time threat detection, or assume that Azure Monitor metric alerts are the correct way to receive email notifications for Defender for SQL alerts, when in fact the email notification is configured directly within the Defender for SQL settings.

How to eliminate wrong answers

Option A is wrong because vulnerability assessment recurring scans generate periodic reports on database vulnerabilities, not real-time email notifications for suspicious activity detection. Option B is wrong because there is no Azure Monitor metric named 'SQL database threat detection'; threat detection alerts are surfaced through Defender for SQL's own alerting system, not via Azure Monitor metric alerts. Option D is wrong because enabling SQL auditing and streaming logs to Log Analytics enables log collection and analysis, but does not by itself configure email notifications for suspicious activity; that requires an additional alert rule or action group.

Practice this question →

MCQmedium

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?

A.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.

B.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.

C.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'.

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

AnswerC

Meets all requirements.

Why this answer

Option C is correct because database-level auditing with the specified action groups (SCHEMA_OBJECT_CHANGE_GROUP for DDL changes and FAILED_DATABASE_AUTHENTICATION_GROUP for failed logins) meets the requirement to track DDL changes and failed login attempts per database. Configuring the audit log destination to a central Azure Storage account with 180-day retention satisfies the storage and retention requirement. Granting DBAs only the 'Storage Blob Data Reader' role prevents them from viewing or modifying the audit logs, as they cannot delete or overwrite blobs, while still allowing read access if needed.

Exam trap

The trap here is that candidates might confuse server-level auditing with database-level auditing for Azure SQL Managed Instance, or assume that granting DBAs 'Storage Blob Data Reader' is insufficient to prevent access, when in fact it only allows read access and not modification or deletion, meeting the requirement that DBAs cannot view or modify audit logs.

How to eliminate wrong answers

Option A is wrong because using a SQL Server Agent job to capture DDL changes and failed logins into a table does not meet the requirement for a dedicated audit log stored in a central Azure Storage account, and granting DBAs access to the table violates the requirement that DBAs should not be able to view or modify the audit logs. Option B is wrong because server-level auditing cannot be configured to send logs to a central storage account for Azure SQL Managed Instance; server-level auditing is not supported for managed instances, and the requirement specifies a dedicated audit log for each database, not a single server-level audit. Option D is wrong because using Azure Monitor Log Analytics workspace does not meet the requirement for storing audit logs in a central Azure Storage account; Log Analytics is a separate service for log analytics, not a storage account, and granting DBAs 'Log Analytics Reader' role would allow them to view the audit logs, violating the requirement that DBAs should not be able to view or modify them.

Practice this question →

MCQhard

You are reviewing the encryption protector for an Azure SQL Server as shown in the exhibit. The server hosts multiple databases. You need to ensure that the databases are encrypted using the customer-managed key from Azure Key Vault. However, you find that the databases are not using this key. What is the most likely reason?

A.The key version '123456' is incorrect.

B.The key vault is in a different subscription and not accessible.

C.The databases still have the service-managed key as protector and need to be updated.

D.The 'serverKeyName' property is not set correctly.

AnswerC

Existing databases may not automatically switch to CMK.

Why this answer

The exhibit shows that the server's encryption protector is set to a customer-managed key from Azure Key Vault, but this setting only applies to newly created databases. Existing databases retain their original encryption protector, which is typically the service-managed key. To enforce the customer-managed key on existing databases, you must manually update each database's encryption protector using the ALTER DATABASE ENCRYPTION KEY statement or through the Azure portal.

Option C correctly identifies that the databases still have the service-managed key as protector and need to be updated.

Exam trap

The trap here is that candidates assume setting the server-level encryption protector automatically applies to all existing databases, but Azure SQL Database requires explicit per-database updates to change the protector.

How to eliminate wrong answers

Option A is wrong because the key version '123456' is a placeholder in the exhibit; if the key version were incorrect, the server would fail to set the encryption protector or show an error, but the exhibit shows the protector is set successfully. Option B is wrong because the exhibit does not indicate any cross-subscription access issues; if the key vault were in a different subscription and inaccessible, the server would display an error or the protector would not be set. Option D is wrong because the 'serverKeyName' property is correctly set to the customer-managed key name as shown in the exhibit; if it were incorrect, the server would not be able to use the key as the protector.

Practice this question →

MCQhard

An Azure SQL Database contains personally identifiable information (PII). You need to mask the PII columns from non-administrative users while allowing administrators to see the actual data. Which feature should you use?

A.Always Encrypted

B.Transparent Data Encryption (TDE)

C.Dynamic Data Masking

D.Row-Level Security

AnswerC

DDM masks data for non-privileged users and can be bypassed for administrators.

Why this answer

Dynamic Data Masking (DDM) is the correct choice because it selectively obscures sensitive PII columns in query results for non-administrative users, while leaving the data unmasked for users with elevated permissions (e.g., db_owner or the UNMASK permission). This is achieved by defining masking rules on specific columns, such as email or phone number, without altering the underlying stored data.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with Always Encrypted, thinking both hide data from the database engine, but DDM only masks output while Always Encrypted encrypts data at the client and prevents the server from ever seeing plaintext.

How to eliminate wrong answers

Option A is wrong because Always Encrypted encrypts data at the client side, preventing the database engine from seeing plaintext values, which would block administrators from reading the actual data unless they have the column encryption key. Option B is wrong because Transparent Data Encryption (TDE) encrypts the entire database at rest (on disk), but does not control access or mask data for specific users during queries. Option D is wrong because Row-Level Security (RLS) restricts which rows a user can see based on a predicate function, but it does not mask or obfuscate column values within visible rows.

Practice this question →

MCQmedium

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?

A.The diagnostic setting is configured incorrectly.

B.The storage account firewall is blocking the audit logs.

C.The audit is configured to capture only successful operations.

D.The database has Transparent Data Encryption (TDE) enabled.

AnswerC

Correct: Auditing can be set to capture both success and failure; if only success, some operations may not be logged if they fail.

Why this answer

Option C is correct because 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.

Exam trap

The trap here is that candidates assume all data modification operations are always audited by default, overlooking that Azure SQL Database auditing allows filtering by operation outcome (success/failure), which can cause specific operations to be omitted from logs.

How to eliminate wrong answers

Option A is wrong because the diagnostic setting controls the export of audit logs to destinations (e.g., storage, Event Hub, Log Analytics), not which operations are audited; a misconfigured diagnostic setting would cause all logs to be missing, not just some DELETE operations. Option B is wrong because the storage account firewall blocks the writing of audit logs at the network level, which would prevent all audit logs from being written, not selectively omit DELETE operations. Option D is wrong because Transparent Data Encryption (TDE) encrypts data at rest and does not affect auditing behavior; TDE has no interaction with audit log capture.

Practice this question →

MCQhard

Your company uses Azure SQL Database with Microsoft Entra ID (formerly Azure AD) authentication. You need to grant a group of external consultants access to a specific database with read-only permissions. The consultants are from a partner organization that uses their own Microsoft Entra ID tenant. What should you do?

A.Invite the consultants as guest users in your Microsoft Entra ID tenant using B2B collaboration, then create a contained database user for each guest user

B.Create a contained database user mapped to the consultants' Microsoft Entra ID user principal names (UPNs)

C.Configure Azure SQL Database to trust the partner's Microsoft Entra ID tenant

D.Create a SQL Server authentication login and user for the consultants

AnswerA

Guest users can authenticate to Azure SQL Database using their home tenant credentials and are then granted database permissions.

Why this answer

Option A is correct because external consultants from a different Microsoft Entra ID tenant must first be invited as guest users via B2B collaboration to your tenant. Once they are guest users, you can create contained database users in Azure SQL Database mapped to their guest user identities (e.g., their UPN in your tenant) and grant them read-only permissions (e.g., db_datareader role). This approach respects the isolation of the partner's tenant while enabling access through your tenant's identity.

Exam trap

The trap here is that candidates assume you can directly map a contained database user to an external UPN without first establishing cross-tenant identity via B2B collaboration, or they mistakenly think Azure SQL Database can natively trust another Entra ID tenant.

How to eliminate wrong answers

Option B is wrong because you cannot directly create a contained database user mapped to a user principal name (UPN) from an external Microsoft Entra ID tenant; Azure SQL Database only recognizes identities from the tenant it is linked to. Option C is wrong because Azure SQL Database does not support trusting an external Microsoft Entra ID tenant directly; cross-tenant trust must be established via B2B collaboration at the Microsoft Entra ID level. Option D is wrong because SQL Server authentication logins and users bypass Microsoft Entra ID authentication entirely, which violates the requirement to use Microsoft Entra ID authentication and does not leverage the partner's existing identities.

Practice this question →

100

MCQhard

Your company uses Azure SQL Database with Elastic Jobs to run maintenance scripts. You need to ensure that the Elastic Job agent can connect to the target databases securely. The agent runs in an Azure virtual network and uses a managed identity. The target databases are in a different Azure region and have public endpoint disabled. What is the most secure way to connect the Elastic Job agent to the target databases?

A.Use virtual network service endpoints to allow the agent's subnet to connect to the target databases.

B.Configure a private endpoint for each target database and grant the Elastic Job agent's managed identity access to the databases via a contained database user.

C.Add the agent's public IP address to the target database firewall rules.

D.Create a SQL authentication login for the Elastic Job agent and use that in the job credentials.

AnswerB

Private endpoints keep traffic within Microsoft's network and managed identity avoids credential management.

Why this answer

Option B is correct because it combines Azure Private Link (private endpoint) for network-level isolation with managed identity for authentication, eliminating exposure to the public internet and removing the need for passwords. The Elastic Job agent's managed identity is mapped to a contained database user in each target database, enabling secure, passwordless connections even across regions when public endpoints are disabled.

Exam trap

The trap here is that candidates often confuse virtual network service endpoints with private endpoints, assuming service endpoints provide the same private connectivity when public endpoints are disabled, but service endpoints still rely on the public endpoint of the Azure service and cannot bypass it.

How to eliminate wrong answers

Option A is wrong because virtual network service endpoints do not provide private connectivity when the target database's public endpoint is disabled; service endpoints only secure traffic from the VNet to the Azure service's public endpoint, but with public endpoint disabled, no traffic can reach the database. Option C is wrong because the Elastic Job agent runs in a VNet and does not have a public IP address; adding a non-existent public IP to firewall rules would not allow connectivity, and exposing any public IP would reduce security. Option D is wrong because SQL authentication logins are less secure than managed identity (they require password management and are vulnerable to credential theft), and they do not address the network connectivity issue since the target database's public endpoint is disabled.

Practice this question →

101

Multi-Selecthard

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?

Select 3 answers

A.The key encryption key must be stored in Azure Key Vault.

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

C.The Azure Key Vault must have soft-delete and purge protection enabled.

D.The Azure SQL Database server must have an Azure AD identity.

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

AnswersA, C, D

Customer-managed key is stored in AKV.

Why this answer

Option A is correct because Transparent Data Encryption (TDE) with customer-managed keys requires the key encryption key (KEK) to be stored in Azure Key Vault. This KEK is used to protect the database encryption key (DEK), which is stored in the database boot record. Without the KEK in Key Vault, the customer-managed key scenario cannot be implemented, as Azure SQL Database must be able to access the key to perform encryption operations.

Exam trap

The trap here is that candidates often confuse the requirement for the Key Vault to be in the same region as the database (Option B) with the actual requirement that the Key Vault must have soft-delete and purge protection enabled (Option C), and they may also mistakenly think an RBAC role assignment (Option E) is needed instead of the correct access policy permissions for the server's Azure AD identity.

Practice this question →

102

MCQhard

You are a database administrator for a financial services company. You have deployed an Azure SQL Database and configured auditing using the JSON policy shown in the exhibit. After a security incident, you need to review all successful and failed login attempts to the database. However, you notice that login events are not being captured in the audit logs. What is the most likely reason?

A.The audit logs are being sent to Azure Monitor instead of blob storage

B.The retention days are set too high, causing logs to be truncated

C.The audit actions and groups do not include login events

D.Auditing is disabled at the server level

AnswerC

Login events are captured by 'SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP' and 'FAILED_DATABASE_AUTHENTICATION_GROUP', which are not listed in the audit actions and groups.

Why this answer

The JSON policy shown in the exhibit defines audit actions and groups, but it does not include the `SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP` or `FAILED_DATABASE_AUTHENTICATION_GROUP` action groups. These groups are required to capture both successful and failed login attempts (authentication events) in Azure SQL Database. Without them, login events are not recorded in the audit logs, regardless of other settings.

Exam trap

The trap here is that candidates assume auditing automatically captures all security events, but Azure SQL Database requires explicit inclusion of authentication action groups to log login attempts, and the JSON policy in the exhibit likely omits these groups.

How to eliminate wrong answers

Option A is wrong because the destination of audit logs (Azure Monitor vs. blob storage) does not affect which events are captured; it only changes where the logs are stored. Option B is wrong because retention days control how long logs are kept, not which events are captured; setting retention too high would not cause logs to be truncated or missing. Option D is wrong because if auditing were disabled at the server level, no audit logs would be generated at all, but the question states that other events (not login events) are being captured, implying auditing is enabled.

Practice this question →

103

MCQeasy

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?

A.Always Encrypted

B.Dynamic Data Masking

C.Row-Level Security (RLS)

D.Transparent Data Encryption (TDE)

AnswerB

Masking obfuscates columns for unauthorized users.

Why this answer

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.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with Always Encrypted, thinking that encryption is needed for obfuscation, but DDM is specifically designed for on-the-fly data masking without changing the underlying storage or requiring client-side changes.

How to eliminate wrong answers

Option A is wrong because Always Encrypt encrypts data at the client side, ensuring that the database engine never sees plaintext, which prevents even authorized database users (like HR managers) from viewing the data unless they have the column encryption key, making it unsuitable for role-based obfuscation where some users need full access. Option C is wrong because Row-Level Security (RLS) restricts access to rows based on user identity or context, but it does not obfuscate column values; it either shows or hides entire rows, not partial data within a column. Option D is wrong because Transparent Data Encryption (TDE) encrypts the entire database at rest (data files and backups) but does not control or obfuscate data visibility at query time for specific users or columns.

Practice this question →

104

MCQeasy

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?

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

B.The user will be denied the ability to read data from the database.

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

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

AnswerB

The condition denies read data actions because it excludes the data read action from the role.

Why this answer

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.

Exam trap

The trap here is that candidates often assume a condition only restricts certain operations or that metadata reads are separate from data reads, but Azure RBAC deny assignments unconditionally block the specified action, and 'read data' encompasses all data retrieval operations on the database.

How to eliminate wrong answers

Option A is wrong because the condition explicitly denies read access, so the user cannot read all data; a deny assignment takes precedence over any allow. Option C is wrong because the condition denies read data operations entirely, not just metadata; Azure RBAC does not separate metadata read from data read in this context—denying read data blocks both. Option D is wrong because the condition only affects read data permissions, not the ability to assign roles; role assignment permissions are controlled by separate RBAC actions like Microsoft.Authorization/roleAssignments/write.

Practice this question →

105

MCQmedium

You are a database administrator for an Azure SQL Database. You need to ensure that only specific client IP addresses can connect to the database, while all other traffic is blocked. You also need to allow Azure services to access the database. What should you configure?

A.Disable public network access and configure a service endpoint.

B.Configure a private endpoint and disable public network access.

C.Configure network security group (NSG) rules on the subnet where the Azure SQL Database is deployed.

D.Configure server-level firewall rules to allow the specific client IP addresses and enable the 'Allow Azure services and resources to access this server' setting.

AnswerD

This allows specific IPs and Azure services while blocking others.

Why this answer

Option D is correct because Azure SQL Database uses server-level firewall rules to control inbound access. By adding rules for specific client IP addresses and enabling the 'Allow Azure services and resources to access this server' setting, you restrict connections to only those IPs while permitting Azure internal services (e.g., Azure Logic Apps, Azure Functions) to connect. This setting leverages the Azure SQL firewall, which evaluates source IP addresses against the configured rules before allowing a connection.

Exam trap

The trap here is that candidates often confuse network security groups (NSGs) with Azure SQL firewall rules, assuming NSGs can control access to PaaS services like Azure SQL Database, when in fact NSGs only apply to resources within a virtual network and not to the public endpoint of Azure SQL.

How to eliminate wrong answers

Option A is wrong because disabling public network access and configuring a service endpoint would block all public traffic, including the specific client IPs, and service endpoints only secure traffic to Azure SQL from a virtual network, not from arbitrary client IPs. Option B is wrong because configuring a private endpoint and disabling public network access would isolate the database to a virtual network, blocking all public client IPs and requiring clients to be on the same or peered network, which does not meet the requirement to allow specific client IP addresses. Option C is wrong because network security group (NSG) rules apply at the subnet level for resources deployed in a virtual network, but Azure SQL Database is a PaaS service with a public endpoint by default; NSGs cannot control inbound traffic to the Azure SQL Database's public endpoint directly.

Practice this question →

106

MCQhard

Your company uses Azure SQL Managed Instance. You need to configure a server-level firewall rule to allow access from a specific Azure service, but you want to minimize the attack surface. What is the best practice?

A.Set the firewall rule to allow all IP addresses (0.0.0.0/0).

B.Disable public endpoint and use private endpoint.

C.Set the firewall rule to allow Azure services and resources to access this server.

D.Create a firewall rule with the specific IP range of the Azure service.

AnswerC

This option allows only Azure services while blocking all other public IP addresses.

Why this answer

Option C is correct because enabling the 'Allow Azure services and resources to access this server' firewall rule is the best practice for Azure SQL Managed Instance when you need to allow access from a specific Azure service while minimizing the attack surface. This setting uses a special firewall rule that permits connections from any Azure service (including those within the same region) without exposing the server to all public IP addresses, as it relies on Azure's internal network boundaries and does not open the server to the entire internet. It is more secure than allowing all IPs (0.0.0.0/0) because it restricts access to only Azure-originated traffic, and it is simpler than manually specifying IP ranges, which can change dynamically.

Exam trap

The trap here is that candidates often confuse the 'Allow Azure services' rule with the 'Allow all IPs' rule (0.0.0.0/0), but the former is far more restrictive because it only permits traffic from Azure's internal network, not from any public IP address.

How to eliminate wrong answers

Option A is wrong because setting the firewall rule to allow all IP addresses (0.0.0.0/0) opens the managed instance to the entire public internet, vastly increasing the attack surface and violating the principle of least privilege. Option B is wrong because disabling the public endpoint and using a private endpoint is a valid security measure, but it does not directly address the requirement to configure a server-level firewall rule to allow access from a specific Azure service; private endpoint bypasses the firewall entirely by using a private IP within a virtual network, which is a different configuration path. Option D is wrong because creating a firewall rule with the specific IP range of the Azure service is impractical and unreliable, as Azure services often use dynamic IP ranges that can change without notice, and maintaining such rules is error-prone and increases administrative overhead.

Practice this question →

107

Multi-Selecteasy

Which TWO actions are required to enable Microsoft Entra ID authentication for Azure SQL Database?

Select 2 answers

A.Set an Microsoft Entra ID administrator for the logical server

B.Create a login in the master database for each Entra ID user

C.Assign a system-assigned managed identity to the logical server

D.Create a contained database user mapped to an Entra ID identity

E.Enable 'contained database authentication' on the server

AnswersA, D

Required to enable Entra ID authentication at the server level.

Why this answer

Setting a Microsoft Entra ID administrator for the logical server (Option A) is required because it establishes the Entra ID tenant as an identity provider for the Azure SQL Database logical server, enabling token-based authentication. Creating a contained database user mapped to an Entra ID identity (Option D) is required because Azure SQL Database uses contained database users for authentication, where the user is authenticated directly against the database without requiring a login in the master database. These two actions together allow Entra ID users to authenticate to the database using their cloud identities.

Exam trap

The trap here is that candidates confuse the on-premises SQL Server requirement of enabling 'contained database authentication' with Azure SQL Database, which always has this enabled, and they mistakenly think creating logins in master is necessary for Entra ID users when contained database users are the correct approach.

Practice this question →

108

MCQmedium

You manage an Azure SQL Database that is part of a business-critical application. You need to ensure that network traffic between the application hosted on Azure VMs and the database is encrypted and does not traverse the public internet. What should you configure?

A.Use TLS 1.2 for all connections to the database.

B.Configure server-level firewall rules to allow only the application VM IP addresses.

C.Enable forced tunneling on the application VMs to route all traffic through the on-premises network.

D.Create a private endpoint for Azure SQL Database in the same virtual network as the application VMs.

AnswerD

Private endpoints provide a private IP address within the VNet, keeping traffic off the public internet.

Why this answer

Option D is correct because creating a private endpoint for Azure SQL Database places the database service on a private IP address within the same virtual network as the application VMs. This ensures that all traffic between the VMs and the database stays entirely within the Microsoft Azure backbone network, never traversing the public internet, while also providing encryption in transit via TLS by default.

Exam trap

The trap here is that candidates often confuse encryption (TLS) with network isolation, assuming that encrypting traffic alone prevents it from traversing the public internet, or they mistakenly believe that IP-based firewall rules create a private network path.

How to eliminate wrong answers

Option A is wrong because using TLS 1.2 only encrypts the connection but does not prevent traffic from traversing the public internet; the database endpoint remains publicly accessible. Option B is wrong because server-level firewall rules restrict access by IP address but still allow traffic over the public internet; they do not provide a private network path. Option C is wrong because forced tunneling routes all VM traffic through an on-premises network, which adds latency and does not keep traffic within Azure; it also does not create a private connection to Azure SQL Database.

Practice this question →

109

Multi-Selecthard

Which THREE features can help protect Azure SQL Database from data exfiltration?

Select 2 answers

A.Microsoft Defender for SQL

B.Always Encrypted

C.Azure SQL Database Ledger

D.Dynamic Data Masking

E.Azure Policy with deny effect for public network access

AnswersA, E

Defender for SQL can alert on anomalous activities that may indicate exfiltration.

Why this answer

Data exfiltration prevention focuses on preventing unauthorized data extraction. Defender for SQL alerts on suspicious activities, Ledger provides tamper-evidence but not prevention, and Azure Policy can enforce rules like blocking public access. Always Encrypted protects data but does not prevent exfiltration by authorized users.

Dynamic Data Masking obfuscates data but does not prevent copying.

Practice this question →

110

Multi-Selecthard

Which TWO of the following are required steps to configure Azure SQL Database to use a customer-managed key (CMK) for Transparent Data Encryption (TDE) with Azure Key Vault? (Choose two.)

Select 2 answers

A.Create a user-assigned managed identity for the SQL Database server

B.Set the TDE protector to the Key Vault key via the Azure portal or T-SQL

C.Grant the SQL Database server's managed identity Get, Wrap Key, and Unwrap Key permissions on the Key Vault key

D.Store the encryption key in the SQL Database server's hardware security module (HSM)

E.Ensure the SQL Database server is inside the same virtual network as the Key Vault

AnswersB, C

This configures the server to use the CMK.

Why this answer

Option B is correct because setting the TDE protector to the Key Vault key is the step that actually enables customer-managed key (CMK) encryption for Azure SQL Database. This can be done via the Azure portal, PowerShell, Azure CLI, or T-SQL (using ALTER DATABASE SCOPED CONFIGURATION SET TDE_PROTECTOR). Without this step, the database continues to use a service-managed key.

Exam trap

The trap here is that candidates often confuse the required managed identity type (system-assigned vs. user-assigned) or assume network-level constraints (like VNet integration) are mandatory, when in fact only identity and key permissions are strictly required.

Practice this question →

111

Multi-Selecthard

You have an Azure SQL Database that stores personally identifiable information (PII). You need to classify and label the sensitive columns using Microsoft Purview. Additionally, you want to automatically mask these columns for a specific application user. Which two actions should you take? (Choose two.)

Select 2 answers

A.Apply Dynamic Data Masking (DDM) rules to mask the classified columns for the application user.

B.Enable Always Encrypted on the classified columns.

C.Configure Microsoft Sentinel to monitor access to classified columns.

D.Use Microsoft Purview Data Map to scan and classify the database.

E.Enable Transparent Data Encryption (TDE) on the database.

AnswersA, D

DDM masks data for unauthorized users.

Why this answer

Option A is correct because Dynamic Data Masking (DDM) can be applied to classified columns to obfuscate sensitive data for a specific application user without altering the underlying data. DDM rules are defined at the column level and can be configured to mask data for designated users while allowing full access to others, meeting the requirement to automatically mask PII for the application user.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with Always Encrypted or TDE, thinking any encryption feature can mask data for specific users, but DDM is the only option that provides user-specific obfuscation without altering the underlying data or requiring application changes.

Practice this question →

112

MCQmedium

You are implementing row-level security (RLS) in Azure SQL Database to enforce data isolation for multiple tenants. Each tenant has a TenantID. The RLS predicate function uses the SESSION_CONTEXT variable set by the application. You need to ensure that users cannot bypass the RLS by setting arbitrary SESSION_CONTEXT values. What should you do?

A.Create a stored procedure that sets the SESSION_CONTEXT and grant the application only execute permission on that procedure; revoke SET permission from users

B.Use dynamic data masking to hide the TenantID column

C.Encrypt the SESSION_CONTEXT values using Always Encrypted

D.Use a firewall rule to restrict access to the database to only the application IP addresses

AnswerA

This ensures that only the stored procedure can set the SESSION_CONTEXT, preventing users from setting arbitrary values.

Why this answer

Option A is correct because by creating a stored procedure that sets the SESSION_CONTEXT and granting the application only EXECUTE permission on that procedure while revoking SET permission from users, you prevent users from arbitrarily setting SESSION_CONTEXT values. This enforces that only the trusted application code can set the context used by the RLS predicate, ensuring tenants cannot impersonate other tenants.

Exam trap

The trap here is that candidates may confuse data protection features like dynamic data masking or Always Encrypted with access control mechanisms, failing to realize that RLS relies on controlling who can set the session context, not on encrypting or masking the data itself.

How to eliminate wrong answers

Option B is wrong because dynamic data masking only obfuscates data at query results; it does not prevent users from setting arbitrary SESSION_CONTEXT values or bypassing RLS. Option C is wrong because Always Encrypt protects data at rest and in transit but does not control who can set SESSION_CONTEXT values; it does not enforce RLS isolation. Option D is wrong because a firewall rule restricts network access but does not prevent an authenticated user from setting arbitrary SESSION_CONTEXT values within the database; it does not address the authorization of SESSION_CONTEXT manipulation.

Practice this question →

113

MCQeasy

You need to ensure that all connections to an Azure SQL Database use encryption. The application uses the JDBC driver. What should you configure in the connection string?

A.Add 'hostNameInCertificate=*.database.windows.net'

B.Add 'encrypt=false' and 'trustServerCertificate=true'

C.Add 'encrypt=true' and 'trustServerCertificate=false'

D.Add 'useEncryption=true' and 'validateServerCertificate=true'

AnswerC

This forces encryption and validates the server certificate.

Why this answer

Option C is correct because for JDBC connections to Azure SQL Database, setting 'encrypt=true' enforces TLS encryption for data in transit, and 'trustServerCertificate=false' ensures that the server's TLS certificate is validated against the trusted certificate authority (CA) chain, preventing man-in-the-middle attacks. This is the recommended configuration for secure connections to Azure SQL Database.

Exam trap

The trap here is that candidates often confuse the JDBC properties with those of other drivers (like ODBC or .NET SqlClient), where 'TrustServerCertificate' or 'Encrypt' may have different default behaviors, leading them to select Option B or D incorrectly.

How to eliminate wrong answers

Option A is wrong because 'hostNameInCertificate=*.database.windows.net' is used to specify the expected hostname in the server's certificate when the server name in the connection string does not match the certificate's subject, but it does not enable or enforce encryption itself. Option B is wrong because 'encrypt=false' disables encryption, and 'trustServerCertificate=true' bypasses certificate validation, which together create an insecure connection vulnerable to eavesdropping. Option D is wrong because 'useEncryption=true' and 'validateServerCertificate=true' are not valid JDBC connection properties; the correct JDBC properties are 'encrypt' and 'trustServerCertificate'.

Practice this question →

114

MCQhard

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?

A.The service principal used for AKV access has expired.

B.The previous key version was disabled or deleted in AKV.

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

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

AnswerB

TDE requires all previous key versions to be enabled to decrypt existing data.

Why this answer

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.

Exam trap

The trap here is that candidates often assume key rotation is seamless and never causes failures, overlooking that disabling or deleting the previous key version can break in-flight operations that still depend on it.

How to eliminate wrong answers

Option A is wrong because the service principal used for AKV access is a separate authentication entity; its expiration would cause a persistent access failure, not selective failures on long-running queries after a key rotation. Option C is wrong because Azure Key Vault and Azure SQL Database can be in different regions; cross-region access is supported and does not cause encryption errors. Option D is wrong because the scenario explicitly states that TDE with AKV integration is configured, meaning customer-managed keys are already in use; the database is not using service-managed TDE.

Practice this question →

115

MCQmedium

You are reviewing a PowerShell script that configures auditing for an Azure SQL Database. The script sets an audit rule with the specified parameters. After running the script, you notice that SELECT operations are not being audited. What is the most likely cause?

A.The AuditActionGroup specified does not capture SELECT operations.

B.The retention days are set too low, causing logs to be overwritten.

C.The storage endpoint is incorrectly formatted.

D.The storage account access key is invalid.

AnswerA

DATABASE_OBJECT_CHANGE_GROUP captures DDL changes, not SELECT.

Why this answer

The script likely specifies an AuditActionGroup that does not include the group responsible for capturing SELECT operations. In Azure SQL Database auditing, SELECT operations are captured by the SUCCESSFUL_SCHEMA_OBJECT_ACCESS_GROUP or similar action groups. If the configured AuditActionGroup omits this group, SELECT statements will not be logged, even though other operations may be audited correctly.

Exam trap

The trap here is that candidates may assume all DML operations (including SELECT) are captured by default, but Azure SQL Database auditing requires explicit inclusion of the appropriate action group for SELECT operations.

How to eliminate wrong answers

Option B is wrong because retention days affect how long logs are kept, not which operations are captured; low retention may cause logs to be overwritten but does not prevent SELECT operations from being audited. Option C is wrong because an incorrectly formatted storage endpoint would cause all audit logs to fail to write, not selectively miss SELECT operations. Option D is wrong because an invalid storage account access key would prevent any audit logs from being written to the storage account, not specifically exclude SELECT operations.

Practice this question →

116

MCQmedium

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?

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

B.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

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

D.Enable auditing on the database, set the audit log destination to a storage account, and configure retention to 365 days

AnswerD

This captures all database events and retains logs for one year.

Why this answer

Option D is correct because 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.

Exam trap

The trap here is that candidates may confuse server-level auditing with database-level auditing or assume that Log Analytics or event hubs inherently support retention policies, but only storage accounts allow direct retention configuration within the audit settings.

How to eliminate wrong answers

Option A is wrong because there is no standalone 'Audit Logs' feature in the Azure portal; auditing is configured via the 'Auditing' blade under the database or server settings, and retention is set within the audit destination configuration, not a separate feature. Option B is wrong because while Log Analytics can be used as a destination for audit logs, retention is configured at the Log Analytics workspace level (not within the audit settings), and the requirement specifies retaining logs for at least one year, which is possible but not the most direct or default method for database-level auditing. Option C is wrong because enabling auditing on the server and sending logs to an event hub does not provide a built-in retention policy; event hubs are for real-time streaming and require additional services to store logs for a year, making it unsuitable for the retention requirement.

Practice this question →

117

Multi-Selecteasy

You are configuring security for an Azure SQL Database that will be used by a web application. The application uses a connection string with SQL authentication. You need to protect the database from SQL injection attacks. Which two measures should you implement? (Choose two.)

Select 2 answers

A.Enable Transparent Data Encryption (TDE).

B.Use parameterized queries in the application.

C.Configure Dynamic Data Masking (DDM).

D.Implement stored procedures with input validation.

E.Enable Always Encrypted on sensitive columns.

AnswersB, D

Parameterized queries prevent injection.

Why this answer

Parameterized queries ensure that user input is treated as data, not executable code, by separating SQL logic from input values. This prevents attackers from injecting malicious SQL statements into the query string, which is the primary defense against SQL injection attacks.

Exam trap

The trap here is that candidates often confuse data-at-rest or data-masking features (TDE, DDM, Always Encrypted) with injection prevention, when in fact only query-level controls like parameterized queries and validated stored procedures directly mitigate SQL injection.

Practice this question →

118

MCQeasy

You are designing a security strategy for Azure SQL Managed Instance. The compliance team requires that all database backups be encrypted at rest using a customer-managed key. Which feature should you enable?

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

B.Always Encrypted with a column master key in Azure Key Vault.

C.Transparent Data Encryption (TDE) with a service-managed key.

D.Row-Level Security (RLS) with a custom authorization function.

AnswerA

TDE encrypts the underlying database files and backups at rest.

Why this answer

Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault is the correct feature because it encrypts the database at rest, including all backup files, using a key that the customer controls. This satisfies the compliance requirement for customer-managed key encryption of backups, as TDE automatically encrypts backups when the database is encrypted.

Exam trap

The trap here is that candidates confuse Always Encrypted (which encrypts column data but not backups) with TDE (which encrypts the entire database and backups), leading them to select Always Encrypted when the requirement explicitly mentions backup encryption.

How to eliminate wrong answers

Option B is wrong because Always Encrypted protects sensitive data in transit and at rest within the database by encrypting specific columns, but it does not encrypt entire database backups; backups of a database with Always Encrypted columns are not automatically encrypted by this feature. Option C is wrong because TDE with a service-managed key uses a key managed by Azure, not a customer-managed key, so it does not meet the compliance requirement for customer-controlled encryption. Option D is wrong because Row-Level Security (RLS) controls access to rows in a table based on user authorization, but it does not provide any encryption of data at rest or backups.

Practice this question →

119

Multi-Selecthard

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

Select 3 answers

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

B.Deploy the instance in an isolated subnet with network security groups (NSGs).

C.Use Azure Private Link with a private endpoint.

D.Configure server-level IP firewall rules.

E.Enable service endpoints on the subnet.

AnswersA, B, C

Virtual network rules are used for Managed Instance.

Why this answer

Option A is correct because Azure SQL Managed Instance requires deployment into a dedicated subnet within a virtual network. Configuring a virtual network rule allows you to explicitly permit traffic from a specific subnet, ensuring that only resources within that subnet can connect to the instance. This is a fundamental network security mechanism for Managed Instance, as it relies on VNet injection for isolation.

Exam trap

The trap here is that candidates often confuse Azure SQL Database's firewall rules with Azure SQL Managed Instance's network security model, mistakenly thinking IP firewall rules or service endpoints apply to Managed Instance, when in fact it relies entirely on VNet integration and NSGs.

Practice this question →

120

Multi-Selectmedium

You are configuring Azure SQL Database firewall rules. Which THREE actions require the 'Allow Azure services and resources to access this server' setting to be enabled? (Choose three.)

Select 3 answers

A.Azure Data Factory using a self-hosted integration runtime.

B.An on-premises application with a static public IP.

C.A virtual machine using a private endpoint.

D.An Azure Logic App connecting to the database.

E.An Azure App Service connecting to the database.

AnswersA, D, E

If the self-hosted IR is on Azure, it uses Azure IPs.

Why this answer

Option A is correct because the 'Allow Azure services and resources to access this server' firewall setting permits connections from any Azure service that does not have a fixed public IP or a private endpoint. Azure Data Factory using a self-hosted integration runtime (IR) runs on-premises or on a VM, but the IR itself initiates outbound connections to Azure SQL Database from a dynamic Azure IP range. Without this setting enabled, the connection is blocked because the self-hosted IR's source IP is not a static, whitelisted address.

Exam trap

The trap here is that candidates often think the 'Allow Azure services' setting is required for any Azure resource, but it is only needed when the resource does not have a static, whitelistable IP or a private endpoint; for example, a VM with a private endpoint does not need this setting because it uses a private IP within the virtual network.

Practice this question →

121

MCQhard

Refer to the exhibit. You are reviewing an Azure Resource Manager template for deploying an Azure SQL Database server. The template sets publicNetworkAccess to Disabled, minimalTlsVersion to 1.2, and azureAdOnlyAuthentication to true. However, the deployment fails with an error. What is the most likely cause?

A.minimalTlsVersion 1.2 is not supported in Azure SQL Database

B.publicNetworkAccess Disabled requires a private endpoint to be defined in the same template

C.When azureAdOnlyAuthentication is true, the administratorLogin and administratorLoginPassword properties must not be specified

D.The password does not meet complexity requirements

AnswerC

Azure AD-only authentication disables SQL authentication, so providing SQL admin credentials is invalid.

Why this answer

When azureAdOnlyAuthentication is set to true in an Azure SQL Database ARM template, the administratorLogin and administratorLoginPassword properties must be omitted because Azure AD authentication replaces SQL authentication as the sole identity provider. Including these properties causes a validation conflict, as the deployment expects no SQL admin credentials when Azure AD-only authentication is enabled.

Exam trap

The trap here is that candidates assume the deployment fails due to a missing private endpoint or password issue, but the real conflict is the simultaneous presence of SQL admin credentials and Azure AD-only authentication, which the ARM template validation explicitly rejects.

How to eliminate wrong answers

Option A is wrong because minimalTlsVersion 1.2 is fully supported in Azure SQL Database and is actually the recommended minimum TLS version. Option B is wrong because publicNetworkAccess Disabled does not require a private endpoint to be defined in the same template; it only blocks public connectivity, and a private endpoint can be added separately or after deployment. Option D is wrong because the error is not related to password complexity; the deployment fails before password validation due to the conflicting properties when azureAdOnlyAuthentication is true.

Practice this question →

122

MCQhard

Refer to the exhibit. You are deploying an Azure SQL Database with Transparent Data Encryption (TDE) enabled via ARM template. The database will contain highly sensitive data, and your security policy requires that the encryption key be managed by your organization using Azure Key Vault. What additional configuration is needed?

A.Set the 'keyVaultUri', 'keyName', and 'keyVersion' properties to reference the key in Key Vault

B.No additional configuration is needed; the template already enables TDE with customer-managed keys

C.Deploy a separate key rotation policy in the ARM template

D.Enable 'autoRotationEnabled' property

AnswerA

Required to use customer-managed keys for TDE.

Why this answer

Option A is correct because when deploying Azure SQL Database with TDE and customer-managed keys (CMK) via ARM template, you must explicitly specify the 'keyVaultUri', 'keyName', and 'keyVersion' properties under the 'encryptionProtector' resource to link the database to the specific key in Azure Key Vault. Without these properties, the template would only enable TDE with a service-managed key, not the required customer-managed key.

Exam trap

The trap here is that candidates assume enabling TDE in the ARM template automatically uses customer-managed keys, but they overlook the need to explicitly configure the encryption protector with Key Vault properties to switch from service-managed to customer-managed keys.

How to eliminate wrong answers

Option B is wrong because the ARM template shown only enables TDE at the database level (which defaults to service-managed keys), but does not configure the encryption protector to use a customer-managed key from Key Vault; additional properties are required. Option C is wrong because a key rotation policy is not a separate ARM template resource; key rotation is managed via Key Vault's own rotation policy or by updating the key version in the encryption protector, not by a dedicated ARM property. Option D is wrong because 'autoRotationEnabled' is not a valid property for TDE with CMK in Azure SQL Database; automatic key rotation is handled by updating the key version in the encryption protector or by using Key Vault's key rotation, not by an ARM template boolean.

Practice this question →

123

MCQmedium

Your organization uses Azure SQL Database with Azure SQL Managed Instance for a multi-tenant SaaS application. You need to ensure that each tenant's data is isolated and that a compromised tenant cannot access other tenants' data. What is the most secure approach?

A.Deploy a separate database per tenant, configure server-level firewall rules per tenant IP, and use Microsoft Entra authentication with application roles.

B.Use dynamic data masking to obfuscate tenant IDs in query results.

C.Implement row-level security (RLS) with a tenant ID filter on all tables.

D.Use column-level encryption with Azure Key Vault for sensitive columns.

AnswerA

This provides strong isolation at the database level and access control at the network and identity layers, minimizing risk of cross-tenant access.

Why this answer

Option A is correct because deploying a separate database per tenant provides the strongest isolation boundary at the storage and compute layer. Combining server-level firewall rules that restrict access to each tenant's IP range and using Microsoft Entra authentication with application roles ensures that even if one tenant's credentials are compromised, the attacker cannot access another tenant's database. This multi-layered approach (network + authentication + authorization) is the most secure for multi-tenant isolation in Azure SQL Managed Instance.

Exam trap

The trap here is that candidates often choose row-level security (RLS) because it seems like a simple, built-in solution, but they overlook that RLS can be bypassed by a compromised tenant with sufficient privileges (e.g., db_owner) or by using direct table access without the security policy context.

How to eliminate wrong answers

Option B is wrong because dynamic data masking only obfuscates data in query results for unauthorized users; it does not prevent a compromised tenant from reading or modifying other tenants' data if they can issue queries directly. Option C is wrong because row-level security (RLS) applies a tenant ID filter at the query level, but a compromised tenant with direct database access could potentially bypass the filter by using elevated privileges or by manipulating session context (e.g., SET CONTEXT_INFO). Option D is wrong because column-level encryption protects specific sensitive columns but does not isolate entire rows or tables; a compromised tenant could still access other tenants' non-encrypted columns or metadata.

Practice this question →

124

MCQeasy

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?

A.Create a private endpoint for the database.

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

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

D.Add a virtual network rule for the subnet where the service is deployed.

AnswerD

Correct: This restricts access to that subnet.

Why this answer

Option D is correct because 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.

Exam trap

The trap here is that candidates often confuse the 'Allow Azure services' firewall rule (0.0.0.0) with a method to restrict access to specific services, when in reality it permits all Azure services, and they overlook that virtual network rules provide the necessary granularity for service-specific access.

How to eliminate wrong answers

Option A is wrong because a private endpoint assigns a private IP address to the database within a virtual network, which allows connectivity from the virtual network but does not inherently restrict access to specific Azure services; it requires additional configuration like network security groups or service endpoints to filter by service. Option B is wrong because adding firewall rules for the public IP addresses of Azure services is impractical and insecure, as Azure services often use dynamic or shared public IP ranges that can change, and it would expose the database to the public internet. Option C is wrong because adding a firewall rule with start and end IP set to 0.0.0.0 is a special rule that allows Azure services to connect from any Azure IP address, but it does not restrict access to specific services like Azure Data Factory; it permits all Azure services and resources, which is too permissive and not the intended behavior.

Practice this question →

125

MCQhard

Your company has an Azure SQL Database that contains sensitive financial data. You need to ensure that database administrators cannot view the actual data while still being able to perform administrative tasks such as backups and index maintenance. Which feature should you implement?

A.Always Encrypted with column master key stored in Azure Key Vault

B.Row-Level Security

C.Dynamic Data Masking

D.Transparent Data Encryption

AnswerA

Always Encrypted ensures data is encrypted at the client and the database never sees plaintext, even from DBAs.

Why this answer

Always Encrypted with the column master key stored in Azure Key Vault ensures that sensitive data is encrypted at the client side and the encryption keys are never exposed to the database engine. This means database administrators (DBAs) can perform administrative tasks like backups and index maintenance on the encrypted columns without ever being able to view the plaintext data, because the SQL Server instance only sees ciphertext.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with encryption, assuming it provides strong data protection, when in fact it is a lightweight obfuscation that can be easily circumvented by privileged users.

How to eliminate wrong answers

Option B (Row-Level Security) is wrong because it controls which rows a user can see based on a predicate function, but it does not prevent DBAs with elevated permissions (e.g., db_owner) from bypassing the security policy or viewing the data directly. Option C (Dynamic Data Masking) is wrong because it only obfuscates data at the application layer; users with high privileges like db_owner can still query the unmasked data using SELECT or by casting to a different type. Option D (Transparent Data Encryption) is wrong because it encrypts data at rest on disk but does not protect data from being read by authorized users (including DBAs) when the database is online and queries are executed.

Practice this question →

126

MCQeasy

You are configuring a new Azure SQL Database. The company policy requires that all connections use Microsoft Entra authentication and that no SQL authentication accounts exist. What should you do to prevent creation of SQL authenticated logins?

A.Set the 'public_network_access' to 'Disabled'.

B.Set the 'DisallowSqlAuthentication' property to 'True' on the logical server.

C.Remove the SQL admin login after creating the database.

D.Create an Azure Policy to audit SQL authentication usage.

AnswerB

This property explicitly disallows SQL authentication, enforcing only Microsoft Entra authentication.

Why this answer

Disallowing SQL authentication by setting the 'DisallowSqlAuthentication' property to 'True' via Azure CLI or ARM template ensures that only Microsoft Entra authentication is allowed. Option A is correct. Option B is wrong because it only disables the admin login but does not prevent others.

Option C is wrong because Azure Policy can enforce it but requires additional setup; the direct property is simpler. Option D is wrong because it disables all authentication.

Practice this question →

127

MCQhard

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?

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

B.Create an auto-failover group with read-write failover policy.

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

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

AnswerA

Active geo-replication replicates security settings and allows manual failover.

Why this answer

Option A is correct because active geo-replication allows manual failover and security settings like firewall rules are replicated automatically. Option B is wrong because auto-failover groups require automatic failover and may have different security settings if not configured properly. Option C is wrong because a backup and restore does not replicate security settings automatically.

Option D is wrong because copying the database is a one-time operation.

Practice this question →

128

MCQeasy

Your organization uses Azure SQL Database and wants to automatically detect and alert on potential SQL injection attacks. Which Azure service should you enable?

A.Azure SQL Database Vulnerability Assessment

B.Azure SQL Auditing

C.Microsoft Defender for SQL

D.Microsoft Sentinel

AnswerC

Defender for SQL includes Advanced Threat Protection that detects SQL injection.

Why this answer

Microsoft Defender for SQL (option C) is the correct answer because it provides advanced SQL security capabilities, including a dedicated SQL injection detection engine that analyzes database activity patterns and alerts on suspicious queries. Unlike other options, Defender for SQL specifically monitors for SQL injection attempts by evaluating query anomalies and known attack signatures, making it the appropriate service for automatic detection and alerting.

Exam trap

The trap here is that candidates often confuse Vulnerability Assessment (which finds weaknesses) or Auditing (which logs events) with the active threat detection capability of Defender for SQL, not realizing that only Defender for SQL provides automatic, real-time SQL injection detection and alerting without additional configuration.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database Vulnerability Assessment focuses on identifying misconfigurations, missing patches, and security weaknesses in the database schema, not on real-time detection of SQL injection attacks. Option B is wrong because Azure SQL Auditing logs database events for compliance and forensic analysis but does not include built-in threat detection or alerting for SQL injection patterns. Option D is wrong because Microsoft Sentinel is a SIEM (Security Information and Event Management) solution that aggregates logs from multiple sources; while it can ingest SQL audit logs and be configured to detect SQL injection, it is not the native Azure service specifically designed for automatic detection and alerting on SQL Database, and it requires additional setup and custom analytics rules.

Practice this question →

129

Multi-Selecthard

You need to protect Azure SQL Database from SQL injection attacks. Which THREE of the following measures should you implement?

Select 3 answers

A.Enable Microsoft Defender for SQL to detect and alert on SQL injection.

B.Use parameterized queries or stored procedures in the application.

C.Implement dynamic data masking to hide sensitive data from unauthorized users.

D.Use a web application firewall (WAF) in front of the application to filter malicious inputs.

E.Enable Transparent Data Encryption (TDE) to encrypt the database at rest.

AnswersA, B, D

Defender for SQL includes threat detection for SQL injection patterns.

Why this answer

Microsoft Defender for SQL provides advanced security capabilities, including vulnerability assessment and threat detection. It specifically monitors for anomalous activities that indicate a SQL injection attack, such as unusual login attempts or queries that match known injection patterns, and can alert administrators or trigger automated responses.

Exam trap

The trap here is that candidates often confuse data protection features like dynamic data masking or TDE with SQL injection prevention, when in fact they address entirely different threats (data exposure at query time vs. data at rest encryption).

Practice this question →

130

Multi-Selectmedium

Which TWO actions are valid for implementing column-level encryption in Azure SQL Database using Always Encrypted? (Choose two.)

Select 2 answers

A.Store the column encryption key in the database.

B.Use randomized encryption for columns that will not be searched.

C.Use a hash of the column value for encryption.

D.Encrypt an entire row by specifying a row-level encryption key.

E.Use deterministic encryption for columns that will be used in equality searches.

AnswersB, E

Randomized encryption provides more security but cannot be searched.

Why this answer

Always Encrypted supports two encryption types: deterministic and randomized. Randomized encryption is valid for columns that will not be searched because it encrypts the same plaintext into different ciphertexts each time, preventing pattern-based attacks. This makes it suitable for sensitive data like credit card numbers or personal identifiers that only need to be decrypted for use, not queried with equality predicates.

Exam trap

The trap here is that candidates often confuse Always Encrypted with Transparent Data Encryption (TDE) or row-level security, and mistakenly think encryption keys are stored in the database or that hashing is a valid encryption method for Always Encrypted.

Practice this question →

131

Multi-Selecteasy

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?

Select 2 answers

A.SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP

B.FAILED_DATABASE_AUTHENTICATION_GROUP

C.DATABASE_OBJECT_PERMISSION_CHANGE_GROUP

D.DATABASE_OBJECT_CHANGE_GROUP

E.SCHEMA_OBJECT_CHANGE_GROUP

AnswersA, B

Logs successful logins.

Why this answer

Option A is correct because the SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP audit action group captures all successful authentication attempts at the database level. Option B is correct because the FAILED_DATABASE_AUTHENTICATION_GROUP audit action group captures all failed authentication attempts at the database level. Together, these two groups ensure that every database-level authentication event—both successes and failures—is logged, which is required to meet the requirement of logging all database-level authentication failures.

Exam trap

The trap here is that candidates may assume only the FAILED_DATABASE_AUTHENTICATION_GROUP is needed to log failures, but the question requires logging 'all database-level authentication failures'—which implicitly includes both successes and failures to provide complete visibility, or they may confuse authentication groups with object-level change groups that audit DDL or permission changes.

Practice this question →

132

Multi-Selecteasy

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?

Select 3 answers

A.Grant CONTROL permission on the database to the user

B.Add the user to the 'sales' database role

C.Create a database user for the service principal using the CREATE USER statement with the FROM EXTERNAL PROVIDER option

D.Grant SELECT on the 'sales' schema to the user

E.Add the user to the 'db_datareader' fixed database role

AnswersC, D, E

This maps the Microsoft Entra service principal to a database user.

Why this answer

Option C is correct because a service principal (Microsoft Entra ID application) must have a corresponding database user created using the CREATE USER statement with the FROM EXTERNAL PROVIDER clause. This maps the external identity to a database principal, enabling authentication and authorization within Azure SQL Database.

Exam trap

The trap here is that candidates often confuse schemas with database roles, leading them to incorrectly select 'Add the user to the sales database role' (Option B) when no such role exists, or they over-provision by granting CONTROL permission (Option A) instead of using the principle of least privilege.

Practice this question →

133

Multi-Selectmedium

Which TWO are valid methods for auditing Azure SQL Database activity? (Choose two.)

Select 2 answers

A.Azure Event Grid

B.Azure Monitor Metrics

C.Azure Storage account

D.Log Analytics workspace

E.On-premises file share

AnswersC, D

Audit logs can be stored in an Azure Storage account.

Why this answer

Option C is correct because Azure SQL Database auditing can write audit logs directly to an Azure Storage account, which is a supported destination for storing audit records in blob format. Option D is correct because audit logs can also be sent to a Log Analytics workspace, enabling integration with Azure Monitor for advanced querying and alerting on audit data.

Exam trap

The trap here is that candidates often confuse Azure Monitor Metrics (numerical performance data) with Log Analytics (log/event data), or assume that on-premises file shares are supported because SQL Server on-premises supports file-based auditing, but Azure SQL Database is a PaaS service with restricted destination options.

Practice this question →

134

Multi-Selecthard

You have an Azure SQL Database that contains sensitive customer data. You need to classify the data and receive recommendations for protecting it. You also need to detect and alert on suspicious access patterns. Which two Azure services should you enable? (Choose two.)

Select 2 answers

A.Microsoft Sentinel

B.Azure Information Protection

C.Microsoft Defender for SQL

D.Microsoft Purview Data Map

E.Azure Policy

AnswersC, D

Detects and alerts on suspicious database access.

Why this answer

Microsoft Defender for SQL (Option C) provides vulnerability assessment, data discovery and classification, and advanced threat protection for Azure SQL Database. It can classify sensitive columns, recommend protection measures, and detect suspicious access patterns such as SQL injection or brute-force attacks, generating alerts for immediate response.

Exam trap

The trap here is that candidates often confuse Microsoft Sentinel's log aggregation capability with the native data classification and threat detection features that are built directly into Microsoft Defender for SQL, leading them to select Sentinel instead of Defender for SQL.

Practice this question →

135

MCQeasy

You are setting up a new Azure SQL Database for a development team. The database will contain test data that mimics production but with some sensitive fields obfuscated. You need to ensure that developers can query the database without seeing the actual sensitive data. The developers will use Microsoft Entra ID authentication. You have the following requirements: - The sensitive data should be automatically masked in query results for all developers except the database administrator. - The masking should be applied without modifying the application code. - The solution should be easy to manage and not require changes to the data model. What should you implement?

A.Create views that exclude sensitive columns and grant developers access to the views instead of the base tables.

B.Configure dynamic data masking on the sensitive columns, and add the database administrator to the unmask permission.

C.Implement Always Encrypted with column encryption, and grant the developers access to the encryption keys.

D.Create a row-level security policy that denies access to sensitive rows for developers.

AnswerB

Data masking is simple, no code changes, and can be managed via SQL commands.

Why this answer

Option C is correct because dynamic data masking can be configured in the database to mask columns for all users except those with explicit unmask permissions. It does not require code changes. Option A is wrong because Always Encrypted requires application changes.

Option B is wrong because row-level security filters rows, not columns. Option D is wrong because views would require rewriting queries.

Practice this question →

136

MCQmedium

You need to audit schema changes on an Azure SQL Database. Specifically, you must capture details of any DDL statements executed by any user. The audit logs must be stored in a Log Analytics workspace for analysis. What should you configure?

A.Create DDL triggers that write to a table

B.Configure database-level auditing with blob storage destination

C.Configure server-level auditing with a Log Analytics workspace destination

D.Create an extended events session to capture DDL events

AnswerC

Server-level auditing captures all DDL changes and can stream to Log Analytics.

Why this answer

Option C is correct because Azure SQL Database server-level auditing with a Log Analytics workspace destination captures all DDL statements executed by any user and stores them in a Log Analytics workspace for analysis. This meets the requirement to audit schema changes and store logs in Log Analytics, as server-level auditing captures events for all databases on the server, including DDL operations, and supports Log Analytics as a destination.

Exam trap

The trap here is that candidates may confuse database-level auditing with blob storage as sufficient, but the question explicitly requires Log Analytics workspace destination, which is only supported at the server-level auditing configuration in Azure SQL Database.

How to eliminate wrong answers

Option A is wrong because DDL triggers that write to a table are a custom, manual solution that does not integrate with Log Analytics and can be bypassed or disabled, failing to provide a reliable audit trail. Option B is wrong because database-level auditing with blob storage destination stores logs in Azure Blob Storage, not in a Log Analytics workspace, and does not meet the requirement for Log Analytics analysis. Option D is wrong because an extended events session captures DDL events but requires manual configuration to send data to Log Analytics, and it is not a native auditing feature for Azure SQL Database; server-level auditing is the recommended approach for compliance and integration with Log Analytics.

Practice this question →

137

Multi-Selectmedium

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?

Select 2 answers

A.Enable transparent data encryption (TDE) on the database

B.Implement dynamic data masking for columns containing PHI

C.Enable change tracking on the database

D.Enable Microsoft Defender for SQL on the server

E.Enable auditing on the database

AnswersB, D

Dynamic data masking obfuscates sensitive data from non-privileged users, reducing the risk of exposure.

Why this answer

Dynamic data masking (B) limits exposure of PHI by obfuscating sensitive data in query results to non-privileged users, directly preventing unauthorized viewing. Microsoft Defender for SQL (D) provides advanced threat detection and alerts for anomalous activities like SQL injection or data exfiltration attempts, meeting the detection requirement. Together, they address both prevention and detection of unauthorized access and data exfiltration.

Exam trap

The trap here is that candidates often confuse TDE (encryption at rest) with data masking (protection during use), or assume auditing alone satisfies detection requirements, missing that Defender for SQL provides active threat detection beyond passive logging.

Practice this question →

138

MCQhard

Your company has a strict policy that all Azure SQL Databases must have Microsoft Defender for SQL enabled. You need to enforce this policy across all subscriptions using a scalable, automated approach. What should you do?

A.Create an Azure Policy initiative that includes the 'Configure Microsoft Defender for SQL to be enabled' policy and assign it to the root management group.

B.Use Azure Blueprints to deploy a predefined ARM template that enables Defender for SQL.

C.Create a script that runs periodically to check and enable Defender for SQL on all databases.

D.Assign the 'SQL Security Manager' role to a central team to manually enable Defender for SQL.

AnswerA

Azure Policy enforces compliance across all subscriptions automatically.

Why this answer

Option A is correct because Azure Policy provides a scalable, automated, and continuous enforcement mechanism across all subscriptions. By creating an initiative that includes the 'Configure Microsoft Defender for SQL to be enabled' policy and assigning it to the root management group, you ensure that any new or existing subscription inherits the policy, and non-compliant resources are automatically remediated or flagged. This approach aligns with the requirement for a strict, company-wide policy without manual intervention.

Exam trap

The trap here is that candidates often confuse Azure Blueprints (which are for initial deployment and governance) with Azure Policy (which provides continuous enforcement and remediation), leading them to choose Option B instead of A.

How to eliminate wrong answers

Option B is wrong because Azure Blueprints deploy resources at creation time but do not continuously enforce or remediate non-compliant resources after deployment; they are not a real-time policy enforcement tool. Option C is wrong because a periodic script is not scalable, introduces latency between checks, and does not provide continuous compliance monitoring or automatic remediation like Azure Policy does. Option D is wrong because assigning the 'SQL Security Manager' role to a central team relies on manual processes, which are error-prone, not scalable, and violate the requirement for an automated approach.

Practice this question →

139

Multi-Selectmedium

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

Select 2 answers

A.Multi-factor authentication

B.Certificate-based authentication

C.Azure Active Directory authentication

D.SQL authentication

E.Windows authentication

AnswersC, D

Correct: Azure AD authentication is supported.

Why this answer

Azure SQL Database supports Azure Active Directory (Azure AD) authentication, which allows you to centrally manage identities and access. This includes support for modern authentication protocols like OAuth 2.0 and integration with Azure AD, enabling features such as conditional access and managed identities.

Exam trap

The trap here is that candidates often confuse Azure AD authentication with Windows authentication, thinking that because Azure AD is Microsoft's cloud identity service, it supports the same Kerberos-based Windows authentication as on-premises SQL Server, but Azure SQL Database does not support Windows authentication at all.

Practice this question →

140

MCQhard

You are designing a secure environment for Azure SQL Managed Instance. You need to ensure that all connections from client applications use a private endpoint, and no public endpoint is accessible. What should you configure?

A.Set the public endpoint to 'Enabled' and create a firewall rule with a deny-all rule.

B.Enable the public endpoint and use a VNet service tag in a firewall rule.

C.Set the public endpoint to 'Disabled' and configure a private endpoint in the same virtual network with a private DNS zone.

D.Use a VNet service endpoint for Azure SQL Managed Instance and configure a network security group.

AnswerC

This completely removes public access and forces private connections.

Why this answer

Option C is correct because Azure SQL Managed Instance requires the public endpoint to be explicitly disabled to ensure no public traffic can reach it. By configuring a private endpoint within the same virtual network and linking it to a private DNS zone, client applications resolve the managed instance's fully qualified domain name (FQDN) to the private IP address, enforcing all connections through the private endpoint and eliminating public endpoint accessibility.

Exam trap

The trap here is that candidates often confuse Azure SQL Database's firewall rules and service endpoints with Azure SQL Managed Instance's networking model, where only private endpoints can fully disable public access, and service endpoints are not a valid option for Managed Instance.

How to eliminate wrong answers

Option A is wrong because setting the public endpoint to 'Enabled' and adding a deny-all firewall rule does not truly disable the public endpoint; the endpoint remains exposed and could be targeted by attacks, and firewall rules do not prevent DNS resolution to the public IP. Option B is wrong because enabling the public endpoint and using a VNet service tag in a firewall rule still leaves the public endpoint accessible; service tags only help filter traffic but do not block public internet connectivity. Option D is wrong because VNet service endpoints are not supported for Azure SQL Managed Instance; only private endpoints can be used to connect securely, and network security groups (NSGs) cannot restrict traffic to a managed instance's private endpoint.

Practice this question →

141

Multi-Selecthard

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?

Select 3 answers

A.Firewall rules to restrict administrative access to specific IP addresses.

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

C.Dynamic Data Masking to hide sensitive data from non-privileged users.

D.Azure Role-Based Access Control (RBAC) to assign distinct roles.

E.Database-level roles such as db_securityadmin and db_owner.

AnswersC, D, E

Can be used to restrict DBAs from viewing sensitive data.

Why this answer

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.

Exam trap

The trap here is that candidates often confuse network-level controls (firewall rules) or encryption features (TDE) with access control and role separation mechanisms, failing to recognize that separation of duties requires distinct role assignments and permission boundaries, not just data protection or network restrictions.

Practice this question →

142

MCQeasy

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?

A.Create a private endpoint for the server.

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

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

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

AnswerC

Allows Azure services and on-premises traffic.

Why this answer

Option C is correct because 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.

Exam trap

The trap here is that candidates often think 'Allow Azure Services' must be OFF to secure the database, but they miss that the requirement explicitly asks to allow connections from Azure services, making ON necessary, and then they forget to add the on-premises IP rule separately.

How to eliminate wrong answers

Option A is wrong because a private endpoint assigns a private IP from a virtual network to the database, which does not inherently allow connections from all Azure services or from an on-premises IP range; it requires DNS configuration and VPN/ExpressRoute for on-premises access. Option B is wrong because a virtual network service endpoint and VNet firewall rule allow traffic only from a specific VNet/subnet, not from all Azure services, and it does not directly support on-premises IP ranges without additional VPN/ExpressRoute. Option D is wrong because setting 'Allow Azure Services and resources to access this server' to OFF blocks all Azure service connections, including those from other Azure services, which contradicts the requirement to allow connections from Azure services.

Practice this question →

143

Multi-Selectmedium

Your organization has a compliance requirement to automatically classify and protect sensitive data in Azure SQL Database. You need to configure Microsoft Purview to scan and classify the database. Which THREE actions are required?

Select 3 answers

A.Run a scan to discover and classify sensitive data.

B.Create a scan rule set that includes the classification rules for sensitive data types.

C.Register the Azure SQL Database as a data source in Microsoft Purview.

D.Create a Microsoft Entra ID application and grant it access to the database.

E.Enable Microsoft Defender for SQL to automatically classify data.

AnswersA, B, C

The scan performs the classification.

Why this answer

Option A is correct because running a scan is the primary action that discovers and classifies sensitive data in Azure SQL Database using Microsoft Purview. The scan applies the classification rules from the scan rule set to identify patterns like credit card numbers or social security numbers, and then labels the data accordingly for compliance.

Exam trap

The trap here is that candidates often confuse Microsoft Defender for SQL's data classification feature (which is a separate, simpler classification) with Microsoft Purview's full scanning and governance pipeline, leading them to incorrectly select Option E as a required action.

Practice this question →

144

MCQmedium

Your company uses Azure SQL Database with Microsoft Entra ID authentication. You need to ensure that only users with specific Azure AD roles can access the database. What should you configure?

A.Configure an Azure SQL Database firewall rule to allow only Azure AD IP addresses.

B.Assign the SQL Server Contributor role to Azure AD users.

C.Create SQL authenticated users with strong passwords.

D.Create contained database users mapped to Azure AD identities and grant appropriate permissions.

AnswerD

Contained database users allow mapping Azure AD users or groups to SQL permissions.

Why this answer

Option D is correct because Azure SQL Database supports contained database users mapped to Azure AD identities, which allows you to grant granular permissions (e.g., db_datareader, db_owner) to specific Azure AD users or groups. This ensures that only those with the appropriate Azure AD roles (via group membership or direct assignment) can access the database, without relying on SQL Server logins or firewall rules.

Exam trap

The trap here is that candidates often confuse Azure RBAC roles (like SQL Server Contributor) with database-level permissions, mistakenly thinking that assigning a management role grants data access, when in fact it only controls the ability to manage the server resource.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database firewall rules control network access based on IP addresses, not Azure AD roles; they cannot restrict access to specific Azure AD identities. Option B is wrong because the SQL Server Contributor role is an Azure RBAC role that grants management access to the logical server resource, not data-level access to the database; it does not control who can query or authenticate to the database. Option C is wrong because SQL authenticated users bypass Azure AD authentication entirely, which contradicts the requirement to use Azure AD roles for access control.

Practice this question →

145

Multi-Selectmedium

Which TWO actions should you take to implement a secure environment for Azure SQL Database that meets the principle of least privilege?

Select 2 answers

A.Assign database roles instead of individual permissions.

B.Enable all database features for maximum functionality.

C.Use contained database users with Azure AD authentication.

D.Use server-level logins for all users.

E.Grant the db_owner role to all application users.

AnswersA, C

Roles simplify permission management and follow least privilege.

Why this answer

Assigning database roles instead of individual permissions (Option A) aligns with the principle of least privilege by grouping necessary permissions into predefined roles, reducing the risk of over-privileging and simplifying permission management. Contained database users with Azure AD authentication (Option C) eliminate the dependency on server-level logins, allowing authentication at the database level and enabling fine-grained access control without granting server-wide privileges.

Exam trap

The trap here is that candidates often confuse server-level logins with contained database users, assuming that server-level logins are required for all Azure SQL Database scenarios, when in fact contained users with Azure AD authentication provide a more secure, least-privilege-compliant alternative.

Practice this question →

146

MCQhard

You are reviewing an ARM template for Azure SQL Database. The exhibit shows a resource definition for Transparent Data Encryption (TDE). You need to ensure that the database uses customer-managed keys (CMK) stored in Azure Key Vault instead of service-managed keys. What additional configuration is required?

A.Modify the database resource to include a 'keyVaultUri' property.

B.Enable Always Encrypted on the database.

C.Set the TDE state to 'Disabled' and then re-enable with a customer key.

D.Add a resource of type 'Microsoft.Sql/servers/encryptionProtector' and reference the key vault key.

AnswerD

The encryption protector resource specifies the customer-managed key.

Why this answer

Option A is correct because to use CMK, you must configure an Azure Key Vault and set the database's encryption protector to the key. Option B is wrong because TDE is already enabled; CMK requires a specific protector. Option C is wrong because the encryption protector is set at the server level, not database.

Option D is wrong because Always Encrypted is a different feature.

Practice this question →

147

MCQhard

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?

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

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

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

D.The VMs do not have the necessary root CA certificates installed to validate the Azure SQL Database server certificate.

AnswerD

Correct: The certificate chain validation fails if root CAs are missing.

Why this answer

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.

Exam trap

The trap here is that candidates confuse a certificate trust error with network connectivity or TLS version issues, assuming the problem is with the firewall or TLS configuration rather than the client's certificate store.

How to eliminate wrong answers

Option A is wrong because the error is about certificate trust, not TLS version support; the 'Minimum TLS version' is set to 1.2 and modern .NET versions support it, so an older .NET version would cause a different error (e.g., 'could not establish trust relationship'). Option B is wrong because the scenario states the service endpoint for Microsoft.Sql is already enabled on the subnet, and the virtual network rule is added; missing the endpoint would cause a connectivity timeout or access denied, not a certificate trust error. Option C is wrong because 'Allow Azure Services' OFF only affects connections from outside the VNet (e.g., Azure services not in the VNet); the VNet rule explicitly allows traffic from AppSubnet, so the firewall is not blocking it.

Practice this question →

148

MCQhard

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

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

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

C.The creation will be denied because the policy prohibits public network access.

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

AnswerC

The policy explicitly denies servers with public network access enabled.

Why this answer

Azure Policy can enforce rules that restrict or deny the creation of Azure resources that do not meet specific compliance requirements. If a policy is in place that prohibits public network access for Azure SQL Database servers, the creation request will be denied at the Azure Resource Manager level before the server is provisioned. This is a common security control to enforce a private endpoint or deny public connectivity.

Exam trap

The trap here is that candidates may assume the server is always created and then audited or partially blocked, but Azure Policy denies the entire creation request at the Resource Manager level before any resource is provisioned.

How to eliminate wrong answers

Option A is wrong because the server creation is denied by Azure Policy before any resource is created, so no audit log is generated for the server itself (though the denial action may be logged in the Azure Activity Log). Option B is wrong because the policy explicitly prohibits public network access, so the server cannot be created with that setting enabled; the creation request is blocked. Option D is wrong because the server is never created, so no databases are involved in the denial process.

Practice this question →

149

MCQmedium

You are configuring Azure SQL Database for a financial application that must meet PCI DSS compliance. The database contains credit card numbers stored in a column encrypted with Always Encrypted. You need to ensure that database administrators cannot view the plaintext credit card numbers while allowing application users with the proper credentials to access them. What should you implement?

A.Use column-level encryption with a certificate stored in the database

B.Enable Transparent Data Encryption (TDE)

C.Use Always Encrypted with secure enclaves and store the column master key in Azure Key Vault with access granted only to the application

D.Implement Dynamic Data Masking (DDM)

AnswerC

Always Encrypted ensures that only authorized clients can decrypt data, and secure enclaves allow computations without exposing data to DBAs.

Why this answer

Always Encrypted with secure enclaves ensures that credit card numbers are encrypted at all times, including during computation, and that the column master key is stored in Azure Key Vault with access granted only to the application. This prevents database administrators (DBAs) from viewing plaintext data because they never have access to the column master key, while authorized application users can decrypt the data via the client driver. This meets PCI DSS requirements for protecting cardholder data at rest and in use.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking (DDM) with encryption, mistakenly believing that masking prevents administrators from seeing data, when in fact DDM can be bypassed by users with db_owner or CONTROL permissions, whereas Always Encrypted cryptographically enforces separation of duties.

How to eliminate wrong answers

Option A is wrong because column-level encryption with a certificate stored in the database allows DBAs with access to the database to retrieve the certificate and decrypt the data, violating the requirement that administrators cannot view plaintext credit card numbers. Option B is wrong because Transparent Data Encryption (TDE) encrypts data at rest but does not prevent DBAs or users with database access from viewing plaintext data when queried; it protects against physical theft of storage, not unauthorized access by administrators. Option D is wrong because Dynamic Data Masking (DDM) only obfuscates data at the query result level and does not encrypt the underlying data; DBAs with elevated permissions can bypass the mask and view plaintext values.

Practice this question →

150

MCQhard

You are a database administrator for an Azure SQL Database. You need to allow a user to restore a database from a backup to a new database, but the user should not have permission to delete the original database. What is the minimum permission required?

A.db_backupoperator database role

B.db_owner database role

C.CREATE DATABASE permission on the logical server

D.SQL Server Contributor role

AnswerC

CREATE DATABASE permission allows restoring a backup as a new database without delete permissions.

Why this answer

The minimum permission required to restore a database from a backup to a new database in Azure SQL Database is the CREATE DATABASE permission on the logical server. This is because restoring a backup to a new database is essentially creating a new database, and the user does not need any additional permissions on the original database to perform this action. The db_backupoperator and db_owner roles grant excessive permissions on the original database, while the SQL Server Contributor role is an Azure RBAC role that provides broader management capabilities beyond what is needed.

Exam trap

The trap here is that candidates often assume database-level roles like db_backupoperator are sufficient for restore operations, but in Azure SQL Database, restoring to a new database requires server-level CREATE DATABASE permission, not just backup-related roles.

How to eliminate wrong answers

Option A is wrong because the db_backupoperator database role only allows backing up a database, not restoring it to a new database; restoring requires CREATE DATABASE permission at the server level. Option B is wrong because the db_owner database role provides full control over the database, including the ability to delete it, which exceeds the minimum required permission and violates the requirement to prevent deletion. Option D is wrong because the SQL Server Contributor role is an Azure RBAC role that grants permissions to manage the entire logical server, including deleting databases, and is not the minimum permission needed for a single restore operation.

Practice this question →