CCNA Implement a secure environment Questions — Page 2 of 3

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

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

Enabling 'Azure AD-only authentication' on the logical server disables all SQL authentication methods, including the server-level admin login. This directly enforces the policy that all connections must use Microsoft Entra ID, as any attempt to connect with a SQL username and password is rejected at the server level. This satisfies the constraint of preventing accidental SQL authentication by developers.

Why this answer

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

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

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

`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 →

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

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

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

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

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 →

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

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

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 →

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

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 →

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

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 →

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 →

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 →

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 →

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

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 →

100

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

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 →

101

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 →

102

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

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 →

103

Multi-Selecthard

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

Select 3 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, B, E

Correct: Provides threat detection and alerts for suspicious data extraction activities.

Why this answer

Microsoft Defender for SQL detects anomalous activities that may indicate data exfiltration attempts. Azure Policy with deny effect for public network access blocks external connectivity, preventing unauthorized data transfer. Always Encrypted ensures that sensitive data remains encrypted even when accessed by unauthorized users, so if data is exfiltrated, it is unusable without the encryption keys.

Together, these three features protect Azure SQL Database from data exfiltration.

Exam trap

Candidates often overlook Always Encrypted as a data exfiltration prevention feature because it primarily protects data in use, but it also prevents exfiltration of readable data.

Practice this question →

104

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

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 →

105

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

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 →

106

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

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 →

107

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

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 →

108

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 →

109

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 →

110

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

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 →

111

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 →

112

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 →

113

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

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 →

114

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 →

115

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

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 →

116

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

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 →

117

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

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 →

118

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 →

119

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

Setting the 'DisallowSqlAuthentication' property to 'True' on the logical server enforces that only Microsoft Entra authentication can be used to connect to the Azure SQL Database. This property prevents the creation of any SQL authenticated logins, including the SQL admin account, and ensures compliance with the policy that no SQL authentication accounts exist.

Exam trap

The trap here is that candidates often confuse network-level controls (like disabling public network access) with authentication-level controls, or assume that auditing or post-creation removal of the SQL admin is sufficient to enforce a no-SQL-authentication policy.

How to eliminate wrong answers

Option A is wrong because disabling public network access only restricts network connectivity to the database, not authentication methods; it does not prevent the creation of SQL authenticated logins. Option C is wrong because removing the SQL admin login after creating the database does not prevent the initial creation of SQL authenticated logins, and the SQL admin account can be re-enabled or recreated unless the property is set. Option D is wrong because creating an Azure Policy to audit SQL authentication usage only monitors and reports on authentication activity, but does not actively prevent the creation of SQL authenticated logins.

Practice this question →

120

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 →

121

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 →

122

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 →

123

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

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 →

124

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

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 →

125

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 →

126

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

Dynamic Data Masking can be applied directly to sensitive columns, automatically masking data for all users except those with unmask permission, meeting all requirements.

Why this answer

Dynamic Data Masking (DDM) can be configured on sensitive columns to automatically mask data in query results without modifying application code or the data model. The database administrator can be added to the unmask permission to see the actual data. Option A is incorrect because creating views would require changes to the data model and application queries.

Option C is incorrect because Always Encrypted requires application code changes to handle encryption/decryption. Option D is incorrect because Row-Level Security filters rows based on predicates, not columns, and does not mask data.

Exam trap

Candidates often confuse Dynamic Data Masking with other security features like Always Encrypted or Row-Level Security. DDM masks data in query results at the database level without altering the underlying data or requiring application changes.

Practice this question →

127

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

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 →

128

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 →

129

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

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 →

130

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 →

131

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

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 →

132

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 →

133

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

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 →

134

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

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 →

135

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

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 →

136

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 →

137

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

To use customer-managed keys (CMK) for Transparent Data Encryption (TDE) in Azure SQL Database, you must configure an encryption protector that points to the key in Azure Key Vault. This is done by adding a resource of type 'Microsoft.Sql/servers/encryptionProtector' to the ARM template, which sets the server-level encryption protector to use the specified key vault key. Option D correctly identifies this required resource, as TDE with CMK is managed at the server level, not directly on the database resource.

Exam trap

The trap here is that candidates mistakenly think TDE key configuration is a property of the database resource (like a 'keyVaultUri' property) or that toggling TDE state is required, when in fact the encryption protector is a separate server-level resource that must be explicitly defined in the ARM template.

How to eliminate wrong answers

Option A is wrong because the 'keyVaultUri' property is not a valid property on the database resource; the key vault reference is configured at the server level via the encryption protector resource. Option B is wrong because Always Encrypted is a separate feature for column-level encryption and does not affect TDE key management; it uses its own keys and is unrelated to switching TDE from service-managed to customer-managed keys. Option C is wrong because you cannot disable and re-enable TDE to switch to a customer key; the transition is done by setting the encryption protector to the key vault key without toggling the TDE state, and disabling TDE would expose data at rest.

Practice this question →

138

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 →

139

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 →

140

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 →

141

MCQeasy

You are reviewing a JSON representation of an Azure SQL Database firewall rule. What is the effect of this rule?

A.Blocks all IP addresses from 10.0.0.0 to 10.0.0.255.

B.Allows all IP addresses except 10.0.0.0 to 10.0.0.255.

C.Allows all IP addresses from 10.0.0.0 to 10.0.0.255.

D.Allows only the IP address 10.0.0.0.

AnswerC

Correct: This is the range defined.

Why this answer

The JSON representation of the Azure SQL Database firewall rule with startIpAddress '10.0.0.0' and endIpAddress '10.0.0.255' defines a range that allows all IP addresses from 10.0.0.0 to 10.0.0.255 inclusive. Azure SQL Database firewall rules use inclusive IP range matching, so any client with an IP in that range is permitted to connect, provided the rule is enabled.

Exam trap

The trap here is that candidates often confuse the inclusive range behavior with a single IP or assume that a range implies blocking, when in fact Azure SQL Database firewall only supports allow rules and the range is inclusive of both endpoints.

How to eliminate wrong answers

Option A is wrong because the rule allows, not blocks, the specified IP range; blocking would require a deny rule, which Azure SQL Database firewall does not support—only allow rules exist. Option B is wrong because the rule explicitly allows the range 10.0.0.0 to 10.0.0.255, not all IPs except that range; that behavior would require a default allow with a separate deny, which is not how Azure SQL firewall works. Option D is wrong because the rule specifies a range (start and end IP), not a single IP; a single IP rule would have identical start and end values (e.g., '10.0.0.0' for both).

Practice this question →

142

Multi-Selecteasy

Which TWO actions should you take to secure Azure SQL Database against SQL injection attacks?

Select 2 answers

A.Enable Transparent Data Encryption

B.Enable auditing for all database operations

C.Configure firewall rules to allow only trusted IP addresses

D.Use parameterized queries in application code

E.Use stored procedures with parameters

AnswersD, E

Prevents SQL injection by separating code from data.

Why this answer

Parameterized queries (Option D) and stored procedures with parameters (Option E) are both effective defenses against SQL injection because they separate SQL code from user input. When using parameterized queries, the database engine treats input as data only, never as executable code, which prevents attackers from injecting malicious SQL statements. This is a fundamental secure coding practice recommended by Microsoft for Azure SQL Database.

Exam trap

The trap here is that candidates often confuse security features like encryption (TDE) or network controls (firewall rules) with application-layer defenses, mistakenly thinking they prevent SQL injection when they only address different threat vectors.

Practice this question →

143

MCQmedium

You are a database administrator for a healthcare company. You have an Azure SQL Database that stores patient records. The database is currently accessible from the public internet via firewall rules. You need to implement a secure environment that meets the following requirements: - All traffic to the database must be private and not traverse the internet. - The database must be accessible from an Azure Virtual Machine in a specific VNet. - The solution must minimize management overhead and cost. - You need to ensure that the database can be failed over to a secondary region in case of an outage. What should you do?

A.Restrict firewall rules to only the VM's public IP and enable active geo-replication.

B.Configure a point-to-site VPN from the VM to the database and set up geo-replication.

C.Create a private endpoint in the VNet, disable public network access, and configure a failover group with a private endpoint in the secondary region.

D.Create a VNet service endpoint and a failover group. Keep public access enabled for failover.

AnswerC

This ensures private connectivity and disaster recovery.

Why this answer

It meets all requirements: a private endpoint ensures all traffic to the database stays within the Microsoft backbone network and never traverses the internet. Disabling public network access enforces that no public traffic can reach the database, fulfilling the private traffic requirement. Configuring a failover group with a private endpoint in the secondary region provides geo-replication and automatic failover while maintaining private connectivity, with minimal management overhead and no additional cost for VPNs.

Option A is incorrect because restricting the firewall to the VM's public IP still allows traffic over the public internet, violating the private traffic requirement. Option B is incorrect because a point-to-site VPN adds complexity and cost, and still uses a VPN connection that may traverse the internet, and does not provide the same seamless private connectivity as a private endpoint. Option D is incorrect because a VNet service endpoint does not make traffic private; it still uses the public endpoint of the database, and keeping public access enabled fails the requirement to disable public internet access.

Practice this question →

144

MCQhard

You are the database administrator for a company that uses Azure SQL Managed Instance. The security team has detected unusual query activity from a specific client IP address. You need to immediately block all connections from that IP address while maintaining connectivity for all other users. Which solution should you implement?

A.Create a private endpoint for the managed instance and disable public network access.

B.Add a server-level firewall rule at the logical server level to deny the IP address.

C.Add a firewall rule at the managed instance level that denies the specific IP address.

D.Modify the network security group (NSG) associated with the managed instance's subnet to deny the IP address.

AnswerC

Instance-level firewall rules can allow or deny specific IP addresses.

Why this answer

Azure SQL Managed Instance supports instance-level firewall rules that can explicitly deny a specific client IP address while allowing all other traffic. Unlike Azure SQL Database, which uses server-level firewall rules, Managed Instance firewall rules are configured directly on the instance and can include both allow and deny entries, enabling granular access control without affecting other users.

Exam trap

The trap here is that candidates confuse Azure SQL Database's server-level firewall rules with Managed Instance's instance-level firewall rules, or incorrectly assume that NSG modifications are the correct way to block a single client IP without understanding that NSGs affect the entire subnet.

How to eliminate wrong answers

Option A is wrong because creating a private endpoint and disabling public network access would block all external connections, not just the specific IP, and is a permanent architectural change rather than an immediate targeted block. Option B is wrong because Azure SQL Managed Instance does not have a logical server-level firewall; server-level firewall rules apply only to Azure SQL Database and Azure Synapse, not to Managed Instance. Option D is wrong because modifying the NSG associated with the Managed Instance's subnet would block traffic at the network layer for all instances in that subnet, potentially disrupting other services, and Managed Instance does not expose direct NSG control for individual IP filtering without impacting the entire subnet.

Practice this question →

145

MCQmedium

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

A.The Azure AD group has not been created as a database user.

B.The server firewall is blocking the connection.

C.The developer's IP address is not allowed.

D.Multi-factor authentication is required but not configured.

AnswerA

Azure AD groups must be mapped to a database user using CREATE USER [group] FROM EXTERNAL PROVIDER.

Why this answer

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

Exam trap

The trap here is that candidates often confuse Azure AD group membership with database user creation, assuming that granting a role to an Azure AD group automatically grants database access, when in fact a separate database user must be created for the group.

How to eliminate wrong answers

Option B is wrong because the server firewall blocking the connection would result in a network-level error (e.g., cannot connect to server), not an authentication failure. Option C is wrong because the developer's IP address not being allowed would also cause a network-level error, not an Azure AD authentication issue. Option D is wrong because Multi-Factor Authentication (MFA) is not required for Active Directory Password authentication; MFA is only enforced when using Active Directory Interactive authentication or when conditional access policies are applied, and the connection string here uses Password authentication, which does not support MFA.

Practice this question →

146

MCQeasy

You are configuring Azure SQL Database firewall rules. You need to allow a range of IP addresses (192.168.1.0 to 192.168.1.255) to connect to the database. Which firewall rule should you create?

A.Start IP: 192.168.0.0, End IP: 192.168.2.255

B.Start IP: 192.168.1.0, End IP: 192.168.1.255

C.Start IP: 192.168.1.255, End IP: 192.168.1.0

D.Start IP: 192.168.1.0, End IP: 192.168.1.0

AnswerB

Correct: This covers the entire /24 subnet.

Why this answer

Azure SQL Database firewall rules require a contiguous range of IP addresses defined by a start and end IP. The range 192.168.1.0 to 192.168.1.255 exactly covers the specified /24 subnet, allowing all hosts in that block to connect. This is the standard method for permitting a subnet in Azure SQL firewall configuration.

Exam trap

The trap here is that candidates may confuse Azure SQL firewall rules with on-premises firewall or network ACLs, where reversed ranges or single-IP entries might be accepted, but Azure SQL strictly requires a valid start ≤ end IP and does not support CIDR notation, leading to errors if you try to use a subnet mask or reversed order.

How to eliminate wrong answers

Option A is wrong because it defines a range from 192.168.0.0 to 192.168.2.255, which is a /22 subnet (192.168.0.0/22) and includes addresses outside the required range (e.g., 192.168.0.1 and 192.168.2.1), granting excessive access. Option C is wrong because it reverses the start and end IPs (start 192.168.1.255, end 192.168.1.0), which is invalid; Azure SQL firewall rules require the start IP to be less than or equal to the end IP, and such a rule would be rejected or behave incorrectly. Option D is wrong because it sets both start and end IP to 192.168.1.0, which only allows a single host (192.168.1.0) rather than the full /24 range, thus blocking all other addresses in the subnet.

Practice this question →

147

MCQeasy

Your organization requires that all Azure SQL Database administrators use multi-factor authentication (MFA) when connecting. Which authentication method must be used?

A.SQL Server authentication

B.Microsoft Entra ID authentication with Conditional Access policy

C.Certificate-based authentication

D.Windows authentication

AnswerB

Entra ID can require MFA via Conditional Access.

Why this answer

Microsoft Entra ID authentication combined with a Conditional Access policy is required to enforce multi-factor authentication (MFA) for Azure SQL Database administrators. Conditional Access policies can mandate MFA as a condition for authentication, which is not possible with SQL Server authentication, certificate-based authentication, or Windows authentication alone. This method integrates with Microsoft Entra ID (formerly Azure AD) to provide the necessary security controls.

Exam trap

The trap here is that candidates often assume certificate-based authentication (Option C) can enforce MFA, but certificates alone do not require a second factor; MFA must be explicitly enforced via a Conditional Access policy with Microsoft Entra ID authentication.

How to eliminate wrong answers

Option A is wrong because SQL Server authentication uses a username and password stored in the database and does not support MFA or integration with Microsoft Entra ID. Option C is wrong because certificate-based authentication relies on client certificates for identity verification and does not inherently enforce MFA; it can be used with Entra ID but requires additional configuration like Conditional Access to require MFA. Option D is wrong because Windows authentication is used for on-premises SQL Server and is not supported for Azure SQL Database; it cannot enforce MFA through Conditional Access policies.

Practice this question →

148

MCQeasy

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

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

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

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

D.The 'Allow Azure services and resources to access this server' setting is enabled, which allows all Azure IPs to connect.

AnswerD

This setting bypasses firewall rules for Azure services.

Why this answer

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

Exam trap

The trap here is that candidates often assume private IPs or service endpoints automatically bypass firewall rules, but the actual cause is the default 'Allow Azure services' setting, which is frequently overlooked as a blanket permission that overrides specific IP restrictions.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database does not automatically allow private IP addresses; firewall rules are based on public IP addresses, and private IPs are not routable over the internet. Option B is wrong because a service endpoint does not bypass the firewall; it routes traffic over the Azure backbone but still requires the source public IP to be allowed in the firewall rules. Option C is wrong because disabling 'Force encryption' would allow non-encrypted connections, which violates the encryption policy, and does not address the firewall bypass issue.

Practice this question →

149

MCQeasy

Refer to the exhibit. You run these commands in an Azure SQL Database. What is the result?

A.The user is created but not granted any permissions.

B.The commands fail because Entra ID users cannot be created in Azure SQL Database.

C.A SQL Server authentication user is created and granted read access.

D.A Microsoft Entra ID user is created and granted read access to the database.

AnswerD

The user is from Entra ID and granted db_datareader.

Why this answer

The commands create a user in an Azure SQL Database mapped to a Microsoft Entra ID (formerly Azure AD) identity. The CREATE USER statement with FROM EXTERNAL PROVIDER creates a user that corresponds to an Entra ID user or group. The ALTER ROLE statement then adds this user to the db_datareader database role, granting read access to all tables and views.

Therefore, option D correctly describes the outcome: a Microsoft Entra ID user is created and granted read access.

Exam trap

The trap here is that candidates often confuse the FROM EXTERNAL PROVIDER syntax with creating a contained database user for SQL authentication, leading them to incorrectly choose option C or A, when in fact the command explicitly maps to an Entra ID identity.

How to eliminate wrong answers

Option A is wrong because the user is not only created but also explicitly granted read permissions via the ALTER ROLE statement. Option B is wrong because Entra ID users can be created in Azure SQL Database using the FROM EXTERNAL PROVIDER syntax; this is a supported feature. Option C is wrong because the CREATE USER ...

FROM EXTERNAL PROVIDER syntax creates a user mapped to an Entra ID identity, not a SQL Server authentication user; SQL authentication users are created with CREATE USER ... WITH PASSWORD or CREATE LOGIN.

Practice this question →

150

Multi-Selecthard

Your Azure SQL Database is accessed by multiple applications. You need to ensure that all connections use Transport Layer Security (TLS) 1.2 or higher. Which TWO configurations should you verify or enable?

Select 2 answers

A.Configure client applications to use TLS 1.2 in their connection strings.

B.Create a network security group rule to block non-TLS traffic.

C.Set 'DenyPublicNetworkAccess' to 'Yes' on the server.

D.Set the server's 'minimalTlsVersion' property to '1.2'.

E.Enable 'ForceEncryption' on the SQL Server instance.

AnswersA, D

Clients must also support TLS 1.2.

Why this answer

Client applications must explicitly request TLS 1.2 in their connection strings (e.g., by adding 'Encrypt=True;TrustServerCertificate=False;' and specifying the minimum TLS version in the underlying driver configuration). Without this, the client may negotiate a lower TLS version, even if the server supports higher versions. Option D is correct because setting the server's 'minimalTlsVersion' property to '1.2' enforces that the Azure SQL Database server rejects any connection attempt using TLS 1.0 or 1.1, ensuring only TLS 1.2+ connections are accepted at the server level.

Exam trap

The trap here is that candidates often confuse network-level controls (like NSGs) with TLS-level enforcement, or mistakenly apply on-premises SQL Server settings (like 'ForceEncryption') to Azure SQL Database, which has different configuration mechanisms and defaults.

Practice this question →