Knowledge + Practice

CCNA Implement a secure environment Questions

75 of 147 questions · Page 1/2 · Implement a secure environment · Answers revealed

Practice these questions Domain overview All questions

1

MCQmedium

You are the database administrator for a healthcare organization that uses Azure SQL Database. You need to implement column-level encryption for a column containing patient Social Security numbers (SSNs). The SSNs must be encrypted at rest and in transit, and only authorized client applications should be able to decrypt them. Which technology should you use?

A.Row-level security (RLS) to restrict access based on user role.

B.Dynamic data masking (DDM) to mask SSNs for unauthorized users.

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

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

AnswerD

Always Encrypted with the column master key in Azure Key Vault encrypts SSNs at rest and in transit, and only client applications with key access can decrypt them. Azure SQL Database never sees plaintext, satisfying the authorised-client constraint.

Why this answer

Always Encrypted is the correct choice because it ensures that sensitive data, such as SSNs, is encrypted both at rest and in transit, and the encryption keys are stored client-side (e.g., in Azure Key Vault). This design ensures that only authorized client applications with access to the column master key can decrypt the data, preventing even database administrators or cloud operators from viewing the plaintext values.

Exam trap

The trap here is that candidates often confuse Transparent Data Encryption (TDE) with column-level encryption, mistakenly thinking TDE protects data from all unauthorized access, when in fact TDE only encrypts data at rest and does not prevent authorized database users from reading sensitive columns in plaintext.

How to eliminate wrong answers

Option A is wrong because Row-Level Security (RLS) controls which rows a user can access based on predicates, but it does not encrypt data or protect it in transit; it only filters rows at query time. Option B is wrong because Dynamic Data Masking (DDM) obfuscates data for unauthorized users at the application layer but does not encrypt the underlying data, leaving it vulnerable to unauthorized decryption or exposure in backups and logs. Option C is wrong because Transparent Data Encryption (TDE) encrypts the entire database at rest but does not protect data in transit or prevent authorized database users (e.g., DBAs) from reading the plaintext SSNs; it also does not support client-side key control for granular column-level encryption.

Practice this question →

2

MCQhard

Your company uses Azure SQL Database with a server-level Microsoft Entra ID admin. You need to implement a solution where database-level roles are automatically assigned based on the user's group membership in Microsoft Entra ID. What should you use?

A.Use Azure RBAC to assign roles to the Entra ID groups.

B.Configure a SQL Server Agent job to update database roles based on group membership.

C.Create database users from Microsoft Entra ID groups and grant roles to those users.

D.Create a DDL trigger that assigns roles when users log in.

AnswerC

Creating contained database users mapped to Microsoft Entra ID groups lets role membership flow from group assignment, so permissions are granted automatically as users join or leave groups. This satisfies the requirement for automatic role assignment without per-user manual grants.

Why this answer

Azure SQL Database supports creating database users mapped to Microsoft Entra ID (formerly Azure AD) groups. By creating a user for the Entra ID group and then granting database roles to that group user, all members of the group automatically inherit the assigned permissions. This directly satisfies the requirement for role assignment based on group membership without custom scripting or triggers.

Exam trap

The trap here is that candidates confuse Azure RBAC (management-plane access) with database-level permissions (data-plane access), or assume that SQL Server Agent or DDL triggers are available in Azure SQL Database, leading them to choose options that are either not applicable or unsupported in the PaaS environment.

How to eliminate wrong answers

Option A is wrong because Azure RBAC controls access to Azure resources (e.g., the logical server or database) at the management plane, not database-level permissions within the SQL engine; it cannot assign database roles like db_datareader. Option B is wrong because SQL Server Agent is not available in Azure SQL Database (it is a PaaS service with no Agent support), and even if it were, polling group membership would be inefficient and not real-time. Option D is wrong because DDL triggers fire on schema changes (e.g., CREATE TABLE), not on login events; logon triggers are not supported in Azure SQL Database, and they cannot dynamically assign database roles based on group membership.

Practice this question →

3

Multi-Selecthard

Which THREE actions are required to configure Microsoft Entra ID authentication for an Azure SQL Database? (Choose three.)

Select 3 answers

A.Configure a firewall rule to allow connections from the Microsoft Entra ID service.

B.Set a Microsoft Entra ID administrator for the Azure SQL Database server.

C.Create contained database users in the database mapped to Microsoft Entra identities.

D.Ensure that the Microsoft Entra identity used to connect is a member of the same Azure AD tenant as the server.

E.Ensure that SQL authentication is enabled as a fallback.

AnswersB, C, D

Microsoft Entra ID authentication requires a Microsoft Entra administrator assigned at the server level, since that identity governs directory-based logins for every database on the server. Without this administrator, contained database users cannot be created or authenticated.

Why this answer

Option B is correct because every Azure SQL logical server must have a Microsoft Entra ID administrator provisioned (via the server's Microsoft Entra ID admin setting) before Entra authentication can be used; this admin acts as the security principal authorized to manage Entra logins and users on the server. Option C is correct because, after the Entra admin is set, you must create contained database users (for example, CREATE USER [name] FROM EXTERNAL PROVIDER) in each target database and grant them permissions, since Entra principals are not automatically mapped to database-level access. Option D is correct because the Entra identity used to authenticate must belong to the same tenant as the Azure SQL server (or be a supported guest/B2B identity), as cross-tenant authentication is not supported for Azure SQL Database.

Option A is incorrect because no special firewall rule for the 'Microsoft Entra ID service' is required; Entra authentication uses the existing SQL firewall rules for client connectivity, not a service-specific rule. Option E is incorrect because SQL authentication does not need to be enabled as a fallback — Entra-only authentication is fully supported and SQL auth can even be disabled.

Exam trap

The trap here is that candidates often confuse network-level firewall rules with authentication configuration, incorrectly assuming that a special firewall rule is needed for Entra ID traffic, when in fact only IP-based rules are required for network access.

Practice this question →

4

MCQmedium

You are configuring Azure SQL Database for a multi-tenant application. Each tenant's data is stored in a separate database. You need to ensure that a tenant admin can only manage their own database and not other databases on the same logical server. What is the best approach?

A.Use a server-level firewall rule to restrict access to the tenant's IP.

B.Create a contained database user with db_owner role in each tenant's database and use Microsoft Entra authentication.

C.Create a server-level login and assign it as db_owner on all databases.

D.Create a database-level firewall rule for each tenant database.

AnswerB

Contained database users live inside each database, so a tenant admin granted db_owner there cannot reach other databases on the same logical server. Microsoft Entra authentication supplies the identity, and no server-level login is created, preserving tenant isolation.

Why this answer

Creating a contained database user with the db_owner role in each tenant's database, using Microsoft Entra authentication, ensures that the tenant admin can only manage their own database. Contained database users are scoped to the individual database, not the logical server, so they cannot access other databases on the same server. This aligns with the principle of least privilege for multi-tenant isolation.

Exam trap

The trap here is that candidates often confuse server-level logins with database-level contained users, assuming that assigning db_owner via a server login is sufficient for isolation, but it actually grants cross-database access.

How to eliminate wrong answers

Option A is wrong because a server-level firewall rule restricts access based on IP address, not database-level permissions; it would allow or block network access to the entire server, not isolate tenant admins to their own database. Option C is wrong because creating a server-level login and assigning it as db_owner on all databases would grant the tenant admin full control over every database on the server, violating multi-tenant isolation. Option D is wrong because a database-level firewall rule controls network access at the database level but does not manage authentication or authorization; it cannot prevent a user from connecting to other databases if they have server-level credentials.

Practice this question →

5

MCQhard

Your Azure SQL Database is accessed by three separate applications. You must ensure that each application can connect only from its own set of IP addresses, that the addresses are managed centrally without editing each database, and that no application can reach the database over the public internet from any other address. What should you implement?

A.Enable the Allow Azure services and resources to access this server rule and rely on database permissions per application

B.Create a database-level firewall rule in each database for the corresponding application's addresses

C.Configure a virtual network service endpoint and a network security group that permits the three application subnets

D.Create server-level firewall rules, one per application, containing that application's IP ranges

AnswerD

Server-level firewall rules apply to the logical server and therefore to every database it hosts, so the addresses are managed in one place rather than per database. Defining a separate rule per application restricts each to its own ranges. Because the firewall denies traffic that does not match a rule, other addresses cannot reach the database.

Why this answer

Server-level firewall rules are evaluated for the logical server and apply to all its databases, so they centralize address management while letting you define a distinct rule per application. Database-level rules scatter the configuration, the Azure services toggle admits too much, and service endpoints constrain to virtual network subnets rather than per-application address ranges.

Exam trap

The trap here is confusing which firewall scope applies where, leading to database-level rules when the requirement is centralized management across all databases on the server.

Practice this question →

6

MCQeasy

You are the database administrator for an Azure SQL Database that contains a column storing national ID numbers. A new regulation requires that this column be hidden from users who run ad hoc queries in the Azure portal Query Editor, while still being available to the payroll application. The payroll application connects with a login that has SELECT permission on the table. What should you implement?

A.Create a view that excludes the national ID column and grant the ad hoc users SELECT on the view only.

B.Apply dynamic data masking to the national ID column and grant UNMASK to the payroll application's user.

C.Enable row-level security on the table with a predicate that filters rows where the national ID is present.

D.Encrypt the national ID column with Always Encrypted and share the column master key with the payroll application.

AnswerB

Dynamic data masking hides the column values from users without the UNMASK permission while leaving the data intact. Granting UNMASK to the payroll user lets that application see real values, and ad hoc users without UNMASK see masked output, satisfying both requirements without altering stored data.

Why this answer

Dynamic data masking is designed to limit exposure of sensitive columns to users without the UNMASK permission while preserving the actual stored values. Granting UNMASK to the payroll application's user allows that workload to read real data, and portal users without UNMASK see masked results, matching the regulation and application needs.

Exam trap

The trap here is reaching for encryption or views when the requirement is permission-based display masking, which dynamic data masking provides without changing the stored data or application code.

Practice this question →

7

MCQmedium

Your company has an Azure SQL Database that is accessed by multiple applications. You need to implement a security solution that meets the following requirements: - Each application must have its own database user with specific permissions. - All authentication must use Microsoft Entra ID. - You need to be able to rotate credentials for each application without impacting other applications. - The solution must support automatic credential rotation for service principals. What should you do?

A.Use managed identities for each Azure resource and assign permissions to the database.

B.Create a single Microsoft Entra ID service principal for all applications and assign different database roles.

C.Create SQL logins and users for each application with strong passwords, and configure password rotation policies.

D.Create a Microsoft Entra ID service principal for each application, store the client secret in Azure Key Vault, and create a contained database user mapped to each service principal.

AnswerD

Contained database users mapped to Microsoft Entra ID service principals let each application authenticate independently, with no shared server-level login. Storing each client secret in Azure Key Vault enables per-application rotation without affecting others, and Key Vault's rotation policies satisfy the automatic credential rotation requirement.

Why this answer

Creating a separate Microsoft Entra ID service principal per application, storing each client secret in Key Vault, and mapping each to a contained database user gives per-application identity, Entra-only authentication, independent credential rotation, and Key Vault's automatic rotation support. Contained users live in the database, so no server-level login is needed and permissions are scoped per app.

Exam trap

The trap is choosing managed identities for applications (they only work for Azure-hosted resources) or a shared service principal (which breaks per-app rotation isolation).

How to eliminate wrong answers

Option A is wrong because managed identities are tied to Azure resources, not arbitrary applications, and they do not provide the per-application credential rotation described. Option B is wrong because a single shared service principal means rotating its secret affects every application, violating the isolation requirement. Option C is wrong because SQL logins with passwords do not use Microsoft Entra ID authentication, which is a hard requirement.

Practice this question →

8

Multi-Selecteasy

Which TWO are valid methods to connect to an Azure SQL Database without exposing a public endpoint?

Select 2 answers

A.Use Always Encrypted

B.Use a public endpoint with a firewall rule

C.Use a service endpoint

D.Use a site-to-site VPN gateway and connect to public endpoint

E.Use a private endpoint

AnswersC, E

Service endpoint secures traffic to Azure SQL from your VNet without a public IP.

Why this answer

A service endpoint extends your virtual network private address space and the identity of your VNet to Azure SQL Database over a direct connection on the Azure backbone network. This allows you to secure your logical SQL server to accept traffic only from a specific subnet, eliminating the need for a public endpoint while still using the public endpoint's DNS name internally.

Exam trap

The trap here is that candidates confuse network-level access controls (firewall rules, VPNs) with endpoint exposure, mistakenly thinking that encrypting traffic or routing through a VPN eliminates the public endpoint's existence, when in fact the public DNS name and IP remain reachable from the internet.

Practice this question →

9

Multi-Selectmedium

You manage an Azure SQL Database that contains a table with sensitive columns. You need to implement Dynamic Data Masking so that users in the 'Reporting' database role see masked values, while users in the 'DataEntry' role see unmasked values. You have created the masking rules. Which two actions should you perform to meet the requirement? (Choose two.)

Select 2 answers

A.Alter the masking rules to use the 'default()' function for the sensitive columns.

B.Ensure the 'Reporting' role does not have the UNMASK permission.

C.Grant the UNMASK permission to the 'DataEntry' role.

D.Create a separate database user for each member of the 'Reporting' role.

E.Enable auditing on the database to track who views masked data.

AnswersB, C

By default, users without UNMASK see masked data. To ensure the Reporting role sees masked values, you must confirm that the role does not possess UNMASK, either directly or through role membership. If the role or its members had UNMASK, they would see the unmasked data, defeating the purpose. Thus, verifying and, if necessary, revoking UNMASK from the Reporting role is required.

Why this answer

Dynamic Data Masking is enforced through the UNMASK permission. Users without UNMASK see masked data; users with UNMASK see the original values. To allow the DataEntry role to see unmasked data, you grant UNMASK to that role.

To ensure the Reporting role sees masked data, you must verify that the role (and its members) does not have UNMASK. These two actions together satisfy the requirement without altering the masking rules.

Exam trap

The trap here is assuming that masking rules themselves can be targeted to specific roles, when in fact masking is controlled by the UNMASK permission and applies to all users who lack it.

Practice this question →

10

MCQmedium

You administer an Azure SQL Database named HRDB. The security team requires that all data written to the database be encrypted with a customer-managed key that is stored in Azure Key Vault, and that the key be automatically rotated every 90 days. You need to configure Transparent Data Encryption (TDE) with Bring Your Own Key (BYOK). What should you do first?

A.Enable Always Encrypted on all columns in HRDB to use the customer-managed key.

B.Export the existing TDE protector from the master database and upload it to Azure Key Vault.

C.Create an Azure Key Vault with purge protection enabled and grant the logical server's managed identity the Key Vault Crypto Service Encryption User role.

D.Create a database-scoped credential that references the Azure Key Vault key and assign it to the database.

AnswerC

TDE with BYOK requires the logical server to access the key. The server's system-assigned managed identity must be granted the Key Vault Crypto Service Encryption User role, and the vault must have purge protection enabled to prevent accidental key deletion. This is the prerequisite before configuring the key as the TDE protector.

Why this answer

To configure TDE with BYOK, you first need an Azure Key Vault with purge protection enabled and the logical server's managed identity granted the appropriate Key Vault Crypto Service Encryption User role. This allows the server to access and use the key as the TDE protector. Without this prerequisite, the key cannot be used for encryption.

Exam trap

The trap here is confusing Always Encrypted with TDE BYOK, or assuming the existing TDE protector can be exported.

Practice this question →

11

MCQmedium

You need to audit all schema changes (DDL) on an Azure SQL Database for compliance. The audit logs must be retained for 7 years. What should you do?

A.Enable auditing on the database, log to a storage account, and set the retention policy to 7 years.

B.Create an extended events session to capture DDL events and save to a file.

C.Enable change tracking on the database and query the change tracking tables.

D.Enable SQL Server Audit at the server level and specify a file destination.

AnswerA

Logging to an Azure Storage account supports retention policies of arbitrary length, unlike Log Analytics, which caps retention at two years. Setting the retention policy to 2,555 days (7 years) on the storage account therefore satisfies the compliance requirement while capturing all DDL schema changes through database-level auditing.

Why this answer

Azure SQL Database auditing can be configured at the database level to log all database events, including DDL changes, to a storage account. The retention policy can be set to 7 years directly in the audit settings, ensuring compliance with long-term retention requirements. This is the native, supported method for auditing schema changes on Azure SQL Database.

Exam trap

The trap here is that candidates confuse SQL Server Audit (which supports file destinations on-premises) with Azure SQL Database auditing, which does not support file destinations and requires a storage account, Log Analytics, or Event Hub; they also mistakenly think change tracking or extended events can serve as a compliance audit solution.

How to eliminate wrong answers

Option B is wrong because extended events sessions are primarily for performance monitoring and troubleshooting, not for long-term compliance auditing; they lack built-in retention policies and are not designed for 7-year archival. Option C is wrong because change tracking is designed to track DML changes (INSERT, UPDATE, DELETE) for synchronization scenarios, not DDL schema changes, and it does not provide audit logs with retention. Option D is wrong because SQL Server Audit at the server level with a file destination is not supported on Azure SQL Database; Azure SQL Database only supports database-level auditing, and the file destination is not available—only storage account, Log Analytics, or Event Hub destinations are supported.

Practice this question →

12

MCQmedium

You manage an Azure SQL Database named HRDB. The security team requires that all data in transit between the application and HRDB be encrypted, and that the database reject any connections using TLS versions below 1.2. You need to enforce this requirement with the least administrative effort. What should you do?

A.Set the 'Minimum TLS version' to 1.2 on the Azure SQL logical server.

B.Configure a firewall rule to allow only the application's IP address.

C.Enable Always Encrypted on sensitive columns in HRDB.

D.Enable Transparent Data Encryption (TDE) on HRDB.

AnswerA

Azure SQL Database enforces TLS 1.2 by default, but the logical server setting lets you explicitly require a minimum TLS version. Configuring this at the server level applies to all databases on that server and requires no application changes or client certificate management, satisfying the requirement with minimal effort.

Why this answer

The logical server's minimum TLS version setting enforces the required protocol for all connections to databases on that server. This is a server-level configuration that requires no application code changes and ensures clients using TLS 1.0 or 1.1 are rejected. TDE, firewall rules, and Always Encrypted address different security concerns and do not control the TLS version negotiated.

Exam trap

The trap here is confusing data-at-rest encryption features like TDE or Always Encrypted with transport security controls that govern the TLS version used during connection negotiation.

Practice this question →

13

MCQmedium

Your company uses Azure SQL Database. You need to ensure that all connections to the database use TLS 1.2 or higher. Currently, some client applications are connecting using TLS 1.0. What should you do?

A.Configure the server firewall to block non-TLS traffic.

B.Set the 'Minimum TLS version' property of the logical server to 1.2.

C.Set the 'tls_version' database parameter to 1.2 in the master database.

D.Update the client applications to only use TLS 1.2.

AnswerB

Configuring the logical server's Minimum TLS version to 1.2 rejects any client handshake negotiating TLS 1.0 or 1.1, directly satisfying the requirement that all connections use TLS 1.2 or higher. This server-level setting enforces the constraint across every database on that server.

Why this answer

Setting the 'Minimum TLS version' property of the Azure SQL logical server to 1.2 enforces that all incoming connections must use TLS 1.2 or higher. This server-level setting overrides any client-side configuration, blocking connections that attempt to use TLS 1.0 or 1.1. It is the simplest and most effective way to enforce the minimum TLS version across all client applications without requiring changes to each client.

Exam trap

The trap here is that candidates may think updating client applications (Option D) is sufficient, but the exam tests the understanding that server-side enforcement is required to guarantee compliance across all clients, especially when you cannot control or update every client application.

How to eliminate wrong answers

Option A is wrong because the server firewall controls IP-based access, not TLS protocol version enforcement; blocking non-TLS traffic does not prevent clients from connecting with TLS 1.0. Option C is wrong because Azure SQL Database does not expose a 'tls_version' database parameter in the master database; TLS version is controlled at the logical server level, not through database-scoped configuration. Option D is wrong because while updating client applications to use TLS 1.2 is a valid approach, it is not a server-side enforcement mechanism and does not guarantee that all clients will comply; the question asks what you should do to ensure all connections use TLS 1.2 or higher, which requires server-side enforcement.

Practice this question →

14

MCQmedium

You are the database administrator for an Azure SQL Database that hosts a multi-tenant SaaS application. Each tenant has its own database user mapped to a Microsoft Entra ID group. The security team requires that every tenant user can see only rows belonging to their own tenant, and that no tenant can infer the existence of other tenants' data through error messages or row counts. You need to implement row-level filtering that enforces this requirement with the least administrative effort. What should you do?

A.Create a view for each tenant that filters rows by tenant ID, and grant tenants access only to their view.

B.Configure a database-scoped credential and use EXECUTE AS USER in every stored procedure that queries tenant data.

C.Create a security policy that uses a predicate function comparing the tenant ID column to the result of DATABASE_PRINCIPAL_ID().

D.Enable Dynamic Data Masking on the tenant ID column so tenants cannot see values belonging to other tenants.

AnswerC

A security policy with a predicate function that filters on the tenant ID column against the current database principal ID enforces row-level security directly in the database engine. Because the filter is applied to every query automatically, tenants cannot see or infer other tenants' rows, and no application changes are required. This matches the least-effort requirement while meeting the strict isolation goal.

Why this answer

Row-level security in Azure SQL Database uses a security policy with an inline table-valued predicate function. When the predicate compares the tenant column to the current database principal, the engine transparently filters rows for every query, including aggregates, preventing cross-tenant visibility without application changes. Views, masking, and impersonation each leave gaps or add maintenance overhead that the scenario rules out.

Exam trap

The trap here is assuming that Dynamic Data Masking or per-tenant views provide row isolation, when masking only hides values and views are easily bypassed or unmanageable at scale.

Practice this question →

15

MCQhard

You are the database administrator for an Azure SQL Database that uses Microsoft Entra ID authentication. A new application must connect to the database using a managed identity. The application runs on an Azure virtual machine. You have assigned the managed identity to the VM. What should you do next to allow the application to authenticate to the database?

A.Add the managed identity as a server-level Microsoft Entra ID administrator.

B.Store the managed identity's client secret in Azure Key Vault and configure the application to retrieve it.

C.Create a contained database user in the target database that maps to the managed identity and grant the required permissions.

D.Enable Microsoft Entra ID authentication on the server and set the managed identity as the server admin.

AnswerC

For a managed identity to access Azure SQL Database, you must create a contained database user that represents the identity and then assign permissions. The user is created with the FROM EXTERNAL PROVIDER clause, which maps the identity to a database principal. Without this step, the managed identity can obtain a token but will not have a database user, so authentication will fail at the database level.

Why this answer

When an application uses a managed identity to connect to Azure SQL Database, the identity must be represented as a contained database user in the target database. The user is created with CREATE USER [identity-name] FROM EXTERNAL PROVIDER, and then permissions are granted. This allows the identity to authenticate and access only the objects it needs, following least privilege.

Without this user, the token-based login will not map to a database principal.

Exam trap

The trap here is thinking that assigning a managed identity to a VM is sufficient for database access, when in fact a contained database user must be created inside the database to map the identity to a principal.

Practice this question →

16

MCQhard

Refer to the exhibit. You are deploying an Azure SQL Database audit policy using an ARM template. What is the MOST significant security concern with the configuration shown?

A.Enabling Azure Monitor target could allow unauthorized access to logs

B.The storage account access key is exposed in the template

C.Retention of 90 days may be too short for compliance

D.Including successful authentication events may expose sensitive login activity

AnswerB

Embedding the storage account access key in the ARM template exposes a full-privilege credential in plaintext, readable by anyone with access to the template, deployment history or source control. This grants unrestricted access to the audit storage account, undermining the audit trail's integrity.

Why this answer

The ARM template exposes the storage account access key in plaintext as a parameter value. This is a critical security concern because anyone with access to the template (e.g., in source control or deployment logs) can retrieve the key and gain unrestricted access to the storage account, including reading, modifying, or deleting audit logs. Azure SQL Database audit policies should use managed identities or Azure AD authentication to avoid embedding secrets.

Exam trap

The trap here is that candidates may focus on audit event types or retention periods as security concerns, but the real risk is the plaintext storage account key in the template, which is a classic secret exposure vulnerability.

How to eliminate wrong answers

Option A is wrong because enabling Azure Monitor target does not inherently allow unauthorized access; access is controlled by Azure RBAC and the Log Analytics workspace permissions, not by the audit policy configuration itself. Option C is wrong because retention of 90 days is a common compliance requirement (e.g., HIPAA, PCI DSS) and is not inherently a security concern; the question asks for the most significant security concern, not a compliance or operational one. Option D is wrong because including successful authentication events is a standard audit practice for security monitoring and does not expose sensitive login activity in a way that violates security; the concern is about the storage key exposure, not the event types.

Practice this question →

17

MCQeasy

You are a database administrator for an Azure SQL Managed Instance. You need to ensure that all connections to the instance use encrypted connections. What should you configure?

A.Set the 'Force Encryption' option to Yes on the server properties.

B.Enable Transparent Data Encryption (TDE).

C.Enable Always Encrypted for sensitive columns.

D.Configure a firewall rule to allow only specific IP addresses.

AnswerA

This enforces encrypted connections.

Why this answer

Setting 'Force Encryption' to Yes on the Azure SQL Managed Instance server properties enforces the use of TLS (Transport Layer Security) for all client connections. This configuration ensures that any client attempting to connect without encryption will be rejected, thereby meeting the requirement that all connections use encrypted connections. The setting is applied at the instance level and overrides client-side encryption preferences.

Exam trap

The trap here is that candidates often confuse encryption in transit (Force Encryption) with encryption at rest (TDE) or column-level encryption (Always Encrypted), leading them to select a security feature that does not address the specific requirement of encrypting all connections.

How to eliminate wrong answers

Option B is wrong because Transparent Data Encryption (TDE) encrypts data at rest (the database files on disk), not data in transit between the client and the server; it does not enforce encrypted connections. Option C is wrong because Always Encrypted protects sensitive columns by encrypting data at the client side and keeping the encryption keys from the database engine, but it does not enforce encryption for the entire connection or for all data transmitted. Option D is wrong because configuring a firewall rule to allow only specific IP addresses controls network access based on source IP, but it does not enforce encryption on the connections that are allowed through.

Practice this question →

18

MCQhard

You administer an Azure SQL Database named FinanceDB. Auditors require that all SELECT statements against a table named Ledger be recorded with the identity of the caller, and that the audit records be retained for seven years in immutable storage. You need to configure auditing to meet these requirements. What should you do?

A.Enable auditing with the target set to Log Analytics and set the workspace retention to seven years.

B.Enable auditing with the target set to a storage account configured with an immutability policy and a retention period of seven years.

C.Enable auditing with the target set to Event Hubs and forward events to a consumer application.

D.Enable server auditing with the target set to a storage account and set retention to 0.

AnswerB

Writing audit logs to a storage account that has a time-based immutability policy enforces write-once, read-many retention for the specified period, satisfying the seven-year requirement. Auditing captures the caller identity for SELECT statements against Ledger, and the immutability policy prevents deletion or alteration during the retention window.

Why this answer

Auditing to a storage account with a time-based immutability policy both records caller identity for the monitored statements and enforces immutable retention for the required period. Retention of 0 is not immutable, Log Analytics retention is mutable, and Event Hubs is a transient streaming target. Only the immutability policy on the storage account meets the auditors' requirement.

Exam trap

The trap here is treating a long retention period as equivalent to immutable retention.

Practice this question →

19

Multi-Selectmedium

Your company wants to implement transparent data encryption (TDE) for an Azure SQL Database using a customer-managed key stored in Azure Key Vault. Which TWO prerequisites must be met? (Choose two.)

Select 2 answers

A.The Azure SQL Server must have a system-assigned managed identity.

B.The Key Vault must have an access policy granting necessary permissions to the SQL Server identity.

C.The database must contain a column master key.

D.The Key Vault must be in a different region than the SQL Server.

E.The database must be taken offline during key configuration.

AnswersA, B

A system-assigned managed identity gives the Azure SQL logical server an identity in Microsoft Entra ID, which Key Vault access policies and key wrapping require. Without it, the server cannot authenticate to the vault to unwrap the customer-managed TDE protector.

Why this answer

Option A is correct because Azure SQL TDE with a customer-managed key (BYOK) requires the logical Azure SQL Server to have a managed identity (system-assigned or user-assigned) that can authenticate to Azure Key Vault; the system-assigned managed identity is the standard prerequisite for granting the server access to the key. Option B is correct because the Key Vault must grant that SQL Server identity the required key permissions — typically get, wrapKey, and unwrapKey — via an access policy (or Azure RBAC role assignment) so the server can wrap and unwrap the TDE protector key. Option C is not required: a column master key belongs to Always Encrypted, not TDE, which uses a database encryption key (DEK) protected by a server-level TDE protector.

Option D is incorrect because the Key Vault and SQL Server can be in the same region; there is no requirement that they be in different regions. Option E is incorrect because TDE key configuration does not require taking the database offline; the operation is performed online without database downtime.

Exam trap

The trap here is that candidates often confuse TDE prerequisites with Always Encrypted prerequisites, mistakenly thinking a column master key (Option C) is needed, or they assume the database must be offline (Option E) for key configuration, which is not the case for TDE.

Practice this question →

20

MCQmedium

You execute the following query: SELECT c.client_ip, c.application_name FROM sys.dm_exec_sessions s JOIN sys.dm_exec_connections c ON s.session_id = c.session_id WHERE s.action_id = 'LGIF' AND s.state = 'ABORT'; What does this query return?

A.All security audit events for the database.

B.Failed login events at the server level.

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

D.Successful login events with client IP and application name.

AnswerD

Correct. The DMVs only contain data for successful logins, so the query (if it were valid) would return information about active sessions, which are successful logins.

Why this answer

The query as written is invalid. sys.dm_exec_sessions does not contain action_id or state columns; it has a status column with values such as sleeping, running, dormant, and preconnect. Therefore the query would raise an error or return no rows, not successful login events. None of the listed options accurately describes the result. sys.dm_exec_sessions and sys.dm_exec_connections only contain authenticated sessions, so they cannot return failed login data either.

Exam trap

Candidates may try to map 'LGIF' and 'ABORT' to login events, but these are not valid filters for these DMVs. The query fails before returning any login data, so the correct outcome is an error/no result, not a list of successful or failed logins.

How to eliminate wrong answers

Option A is wrong because the query specifically filters for login failures (`action_id` = 'LGIF' and `state` = 'ABORT'), not all security audit events, which would include a broader set of actions like DDL changes, permission changes, or successful logins. Option B is wrong because the query uses `sys.dm_exec_sessions` and `sys.dm_exec_connections`, which are database-level DMVs that capture session and connection information for the current database context, not server-level login events (which would require server-scoped views like `sys.dm_exec_sessions` at the server level or the `sys.server_principals` catalog view). Option D is wrong because the query filters for `state` = 'ABORT', which indicates a failed login, not a successful one; successful logins would have a different `state` value (e.g., 'CONNECTED' or no abort state).

Practice this question →

21

MCQeasy

You are configuring security for an Azure SQL Database. You need to ensure that only members of a specific Microsoft Entra ID group can connect to the database as contained database users with db_owner permissions. What should you do?

A.Create a SQL login for each member of the Microsoft Entra ID group and grant them db_owner permissions.

B.Configure the Microsoft Entra ID group as the server admin for the logical server.

C.Enable Microsoft Entra ID authentication on the server and assign the group to the db_owner role using Azure RBAC.

D.Create a contained database user for the Microsoft Entra ID group and add it to the db_owner role.

AnswerD

In Azure SQL Database, you can create contained database users that map to Microsoft Entra ID identities, including groups. By creating a user for the Entra ID group and adding it to the db_owner role, all members of that group inherit db_owner permissions. This is the recommended approach for managing permissions for Entra ID groups without server-level logins.

Why this answer

Contained database users in Azure SQL Database can be mapped to Microsoft Entra ID groups, allowing group members to authenticate and inherit permissions. Adding the group to the db_owner role grants the necessary database-level permissions. This method centralizes management in Entra ID and follows least privilege by scoping permissions to the database.

Exam trap

The trap here is confusing server-level admin or Azure RBAC with database-level role membership; only contained database users grant database permissions for Entra ID groups.

Practice this question →

22

MCQeasy

You are the DBA for an Azure SQL Database that stores sensitive financial data. The security team requires that all user activity on the database be audited, and audit logs must be retained for 90 days. You need to configure auditing with minimal effort. What should you do?

A.Use SQL Server Extended Events to capture all statements and store the files in Azure Blob Storage.

B.Enable Azure SQL Database auditing and configure it to write logs to an Azure Storage account with a retention period of 90 days.

C.Enable Azure Monitor diagnostic settings on the database and send logs to a Log Analytics workspace with 90-day retention.

D.Create a SQL Server Audit specification on the database and write to the Windows Application log.

AnswerB

Azure SQL Database auditing can be enabled at the server or database level and configured to write to Azure Storage, Log Analytics, or Event Hubs. Setting retention to 90 days in the storage account meets the requirement. This is the simplest way to audit all user activity and retain logs for the specified period.

Why this answer

Enabling Azure SQL Database auditing and directing logs to Azure Storage with a 90-day retention period is the correct approach because it is a built-in feature that captures all user activity and meets the retention requirement with minimal configuration. The other options either are not supported in Azure SQL Database, require more manual effort, or do not provide the required audit scope.

Exam trap

The trap here is confusing Azure Monitor diagnostic settings with SQL auditing; while both can send logs, only SQL auditing is designed to capture all database activity for compliance with configurable retention.

Practice this question →

23

MCQhard

You manage an Azure SQL Database that contains a table with a column named CreditCardNumber. The security team requires that this column be encrypted so that even database administrators cannot view the plaintext values. The application that inserts and queries data must continue to work with minimal changes, and the encryption keys must be stored in Azure Key Vault. What should you implement?

A.Dynamic data masking on the CreditCardNumber column with a masking rule that shows only the last four digits.

B.Row-level security (RLS) with a security policy that filters rows based on user identity.

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

D.Always Encrypted with column master key in Azure Key Vault and column encryption key for the CreditCardNumber column.

AnswerD

Always Encrypted is designed to protect sensitive data from high-privileged users like DBAs. The client driver encrypts and decrypts data, so the database engine never sees plaintext. Storing the column master key in Azure Key Vault meets the key storage requirement. The application requires minimal changes: it needs to use a supported client driver with Always Encrypted enabled and connection string adjustments.

Why this answer

Always Encrypted ensures that sensitive data is never revealed to the database engine, protecting it from DBAs. The column master key stored in Azure Key Vault satisfies the key management requirement. Applications need only enable Always Encrypted in the client driver and use parameterized queries, which is a minimal change compared to other encryption methods.

Exam trap

The trap here is assuming that TDE or dynamic data masking protects data from DBAs; only Always Encrypted keeps plaintext away from the database engine.

Practice this question →

24

MCQeasy

You are configuring Azure SQL Database firewall rules. You need to allow a team of developers to connect from their office IP range (192.168.1.0/24) to a specific database. The developers should not be able to access other databases on the same logical server. What should you do?

A.Create a private endpoint for the database.

B.Add a server-level firewall rule for the IP range 192.168.1.0/24.

C.Add a database-level firewall rule for the IP range 192.168.1.0/24.

D.Configure a virtual network service endpoint for the server.

AnswerC

Database-level firewall rules apply only to the specified database, not the whole logical server. A server-level rule would grant the developers access to every database on that server, breaching the isolation requirement. Scoping the rule to 192.168.1.0/24 at database level satisfies the constraint that other databases remain unreachable.

Why this answer

Database-level firewall rules in Azure SQL Database allow you to restrict access to a specific database on a logical server, rather than the entire server. By adding a rule for the IP range 192.168.1.0/24 at the database level, the developers can connect only to that database, and they will be blocked from accessing other databases on the same server. This is the correct approach because server-level rules would grant access to all databases, which violates the requirement.

Exam trap

The trap here is that candidates often assume server-level firewall rules are sufficient for all scenarios, but the DP-300 exam tests the distinction that database-level rules are required when you need to restrict access to a specific database on a logical server.

How to eliminate wrong answers

Option A is wrong because a private endpoint connects the database to a virtual network privately, but it does not restrict access to a specific database; it still requires firewall rules to control which clients can connect. Option B is wrong because a server-level firewall rule for the IP range would allow the developers to access all databases on the logical server, not just the specific one. Option D is wrong because a virtual network service endpoint integrates the server with a VNet but does not provide per-database access control; it still relies on server-level firewall rules and would allow access to all databases.

Practice this question →

25

MCQhard

Your Azure SQL Database contains sensitive customer data. You need to implement column-level encryption so that only authorized users can read specific columns. The encryption must be managed by the application, not the database. What should you use?

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

B.Use dynamic data masking to obfuscate the sensitive columns for unauthorized users.

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

D.Create a row-level security policy to restrict access to the sensitive rows.

AnswerA

Always Encrypted with the column master key in Azure Key Vault keeps keys outside the database engine, so encryption and decryption occur in the application driver. This satisfies the requirement that the application, not the database, manages encryption.

Why this answer

Always Encrypted is the correct choice because it ensures that sensitive data is encrypted at the column level and that the encryption keys are never revealed to the database engine. By storing the column master key in Azure Key Vault and using client-side encryption, the application manages the encryption and decryption process, so only authorized users with access to the key can read the plaintext data. This meets the requirement that encryption be managed by the application, not the database.

Exam trap

The trap here is that candidates often confuse dynamic data masking with encryption, or assume TDE provides column-level control, but the key differentiator is that Always Encrypted keeps encryption keys client-side, fulfilling the 'managed by the application' requirement.

How to eliminate wrong answers

Option B is wrong because dynamic data masking only obfuscates data at query time for unauthorized users but does not encrypt the data at rest or in transit, and the database still holds the plaintext values, so it does not meet the requirement for application-managed encryption. Option C is wrong because Transparent Data Encryption (TDE) encrypts the entire database at rest but does not provide column-level granularity, and the encryption is managed by the database engine, not the application. Option D is wrong because row-level security restricts access to rows based on predicates but does not encrypt the data, and it is managed by the database, not the application.

Practice this question →

26

MCQeasy

You need to ensure that all users accessing Azure SQL Database from outside the corporate network are required to use multi-factor authentication (MFA). What should you configure?

A.Enable Azure RBAC for the SQL server.

B.Configure a Conditional Access policy in Microsoft Entra ID.

C.Create an Azure Policy to require MFA.

D.Turn on Transparent Data Encryption (TDE).

AnswerB

Conditional Access evaluates sign-in conditions and enforces authentication strength, so a policy targeting users outside the corporate network can require MFA. This satisfies the constraint of enforcing MFA specifically for external access, which SQL firewall rules alone cannot do.

Why this answer

Conditional Access policies in Microsoft Entra ID (formerly Azure AD) allow you to enforce MFA based on network location, device state, or risk level. By configuring a policy that targets the Azure SQL Database application and requires MFA for all access from outside the corporate network, you meet the requirement without altering the database or server configuration.

Exam trap

The trap here is confusing Azure Policy (which governs resource configuration compliance) with Conditional Access (which governs user authentication and access conditions), leading candidates to choose Azure Policy when only Conditional Access can enforce MFA at the sign-in level.

How to eliminate wrong answers

Option A is wrong because Azure RBAC controls management-plane permissions (who can create, delete, or modify the SQL server), not data-plane authentication or MFA enforcement for user connections. Option C is wrong because Azure Policy enforces compliance rules on Azure resource configurations (e.g., requiring TDE or auditing), but it cannot enforce MFA at the authentication layer for database users. Option D is wrong because Transparent Data Encryption (TDE) encrypts data at rest, not in transit or during authentication, and has no effect on MFA requirements.

Practice this question →

27

MCQeasy

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

A.Microsoft Defender for SQL threat detection.

B.SQL Vulnerability Assessment.

C.Enable Microsoft Defender for Cloud's regulatory compliance dashboard.

D.SQL Server Audit with a server-level audit specification that includes SUCCESSFUL_LOGIN_GROUP and FAILED_LOGIN_GROUP.

AnswerD

SQL Server Audit with a server-level specification capturing SUCCESSFUL_LOGIN_GROUP and FAILED_LOGIN_GROUP records both successful and failed login attempts. Server-level scope is required because login events occur at the server, not database, level in Azure SQL Database.

Why this answer

SQL Server Audit is the correct feature because it allows you to capture both successful and failed login attempts at the server level by defining a server audit specification that includes the SUCCESSFUL_LOGIN_GROUP and FAILED_LOGIN_GROUP audit action groups. These groups specifically log authentication events, which is exactly what is needed to audit all login attempts. Microsoft Defender for SQL, Vulnerability Assessment, and regulatory compliance dashboards do not provide granular login event auditing.

Exam trap

The trap here is that candidates often confuse Microsoft Defender for SQL's threat detection (which does log some security events) with the dedicated, configurable SQL Server Audit feature that is required for explicit login auditing.

How to eliminate wrong answers

Option A is wrong because Microsoft Defender for SQL threat detection focuses on identifying anomalous database activities and potential threats, not on auditing individual login success or failure events. Option B is wrong because SQL Vulnerability Assessment is a tool for discovering, tracking, and remediating potential database vulnerabilities, not for capturing login audit logs. Option C is wrong because the regulatory compliance dashboard in Microsoft Defender for Cloud provides a view of compliance posture against standards like CIS or SOC 2, but does not itself generate or store login audit records.

Practice this question →

28

MCQeasy

You need to prevent users from accidentally deleting an Azure SQL Database. What should you configure?

A.Apply a 'CanNotDelete' Azure Resource Lock on the resource group.

B.Revoke the db_ddladmin role from users.

C.Create an Azure Policy to deny SQL Database creation.

D.Set a deny rule in the SQL Database firewall.

AnswerA

A CanNotDelete resource lock prevents deletion of the database and its parent resources regardless of RBAC permissions, directly satisfying the requirement to stop accidental deletion. It blocks delete operations while still permitting reads and writes.

Why this answer

A 'CanNotDelete' Azure Resource Lock on the resource group prevents any user, including those with high-level permissions like Owner, from deleting the Azure SQL Database. This lock overrides all role-based access control (RBAC) permissions at the resource or resource group level, ensuring accidental deletion is blocked even if a user has delete permissions.

Exam trap

The trap here is that candidates confuse database-level permissions (like db_ddladmin) with Azure Resource Manager-level operations, mistakenly thinking that revoking schema modification rights will prevent database deletion, when in fact deletion is an ARM operation controlled by locks or RBAC at the subscription/resource group scope.

How to eliminate wrong answers

Option B is wrong because revoking the db_ddladmin role prevents users from modifying the database schema (e.g., creating or altering tables), but it does not prevent deletion of the database itself, which is an Azure Resource Manager (ARM) operation, not a SQL Server-level operation. Option C is wrong because creating an Azure Policy to deny SQL Database creation prevents new databases from being provisioned, but it does not protect an existing database from being deleted. Option D is wrong because setting a deny rule in the SQL Database firewall controls network access to the database (blocking IP addresses), but it has no effect on the ability to delete the database resource via ARM.

Practice this question →

29

MCQhard

Refer to the exhibit. You are troubleshooting an Azure SQL Database auditing configuration. The exhibit shows the blob auditing policy. The storage account access key is null, and the subscription ID is all zeros. What is the most likely issue?

A.Auditing will fall back to Log Analytics workspace.

B.Auditing will work because managed identity is used.

C.Auditing will fail because the storage account access key is null.

D.Auditing will write to the storage account using the system-assigned managed identity.

AnswerC

Blob auditing writes audit logs to the storage account using its access key; a null key means the database cannot authenticate to that account. Auditing therefore fails to record events, regardless of the subscription ID placeholder shown in the exhibit.

Why this answer

The exhibit shows that the storage account access key is null, which means Azure SQL Database cannot authenticate to the storage account using the access key. Without a valid access key or a configured managed identity, blob auditing will fail because the database cannot write audit logs to the specified storage container. Option C is correct because a null access key directly prevents auditing from functioning when no alternative authentication method is configured.

Exam trap

The trap here is that candidates assume managed identity is automatically used when the access key is null, but in reality, managed identity must be explicitly configured and granted permissions, and the exhibit shows no such configuration.

How to eliminate wrong answers

Option A is wrong because auditing does not automatically fall back to Log Analytics workspace; the audit destination is explicitly set to storage, and if storage fails, auditing fails entirely unless a different destination is configured in the policy. Option B is wrong because managed identity is not automatically used; it must be explicitly enabled and assigned to the SQL Database, and the exhibit shows no indication of a managed identity being configured. Option D is wrong because writing to the storage account using a system-assigned managed identity requires that the managed identity be enabled and that the storage account grants appropriate RBAC permissions (e.g., Storage Blob Data Contributor) to that identity, which is not shown in the exhibit.

Practice this question →

30

MCQhard

You administer an Azure SQL Managed Instance that hosts a database containing regulated data. The security team requires that all data be encrypted at rest with a customer-managed key stored in Azure Key Vault, and that the key be rotated annually. You configure a key in Key Vault and set the instance's Transparent Data Encryption protector to that key. Six months later, the key approaches its expiration date. What should you do to rotate the key while keeping the instance online and encrypted?

A.Enable the Key Vault 'soft delete' and 'purge protection' features, and the key will rotate automatically each year.

B.Create a new key version in the same Key Vault key and set the instance's TDE protector to the new version.

C.Disable Transparent Data Encryption on the instance, create a new key, then re-enable TDE with the new key.

D.Delete the old key from Key Vault and create a new key with the same name; the instance will automatically pick it up.

AnswerB

Setting the TDE protector to a new key version re-wraps the database encryption key with the new key material while the instance stays online. This is the supported rotation path for customer-managed keys and maintains continuous encryption, satisfying the annual rotation requirement.

Why this answer

Customer-managed TDE keys are rotated by pointing the instance's TDE protector at a new key version in Key Vault. The database encryption key is re-wrapped with the new material while the instance remains online and encrypted. Retention features such as soft delete protect against loss but do not rotate keys, and disabling TDE would create an unprotected window.

Exam trap

The trap here is believing that Key Vault retention features or key deletion drive rotation, when rotation actually requires updating the TDE protector to a new key version while keeping protection active.

Practice this question →

31

MCQeasy

You have an Azure SQL Database that stores sensitive customer data. You need to ensure that the data is encrypted at rest using a customer-managed key stored in Azure Key Vault. What should you configure?

A.Configure dynamic data masking (DDM).

B.Implement row-level security (RLS) to restrict access.

C.Enable Transparent Data Encryption (TDE) with a customer-managed key from Azure Key Vault.

D.Enable Always Encrypted for the sensitive columns.

AnswerC

Transparent Data Encryption with a customer-managed key satisfies the encryption-at-rest requirement by wrapping the database encryption key with an asymmetric key held in Azure Key Vault. This gives you control over the key lifecycle, unlike service-managed keys, and TDE operates beneath the application layer, so no schema or query changes are needed.

Why this answer

Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault encrypts the database at rest, using a key that you control and rotate independently. This meets the requirement for encryption at rest with a customer-managed key, as TDE performs real-time I/O encryption and decryption of the data and log files without requiring application changes.

Exam trap

The trap here is that candidates often confuse Always Encrypted (which encrypts specific columns at the client side) with TDE (which encrypts the entire database at rest), and they may choose Always Encrypted because it also uses Azure Key Vault, but it does not meet the 'encryption at rest for the entire database' requirement.

How to eliminate wrong answers

Option A is wrong because Dynamic Data Masking (DDM) obfuscates data in query results to unauthorized users but does not encrypt data at rest; it is a presentation-layer control. Option B is wrong because Row-Level Security (RLS) restricts row access based on user context or predicates, but it does not provide encryption at rest. Option D is wrong because Always Encrypted encrypts sensitive columns at the client-side, protecting data in transit and at rest, but it requires application changes and does not encrypt the entire database at rest; the question specifies encryption at rest for the entire database, not just specific columns.

Practice this question →

32

MCQeasy

Your company 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 Auditing

B.SQL Vulnerability Assessment

C.Microsoft Defender for Cloud (formerly Azure Security Center) with Advanced Threat Protection for Azure SQL Database

D.Azure Policy

AnswerC

Microsoft Defender for Cloud's Advanced Threat Protection for Azure SQL Database analyses audit logs to detect anomalous queries indicative of SQL injection, raising alerts automatically. This directly satisfies the requirement to detect and alert on potential injection attacks without manual monitoring.

Why this answer

Microsoft Defender for Cloud with Advanced Threat Protection for Azure SQL Database is the correct choice because it specifically detects anomalous activities indicating SQL injection attempts, such as unusual SQL queries or patterns, and triggers security alerts. Azure SQL Auditing only logs database events for compliance and forensic analysis, not real-time threat detection. SQL Vulnerability Assessment identifies configuration weaknesses but does not monitor for active attacks.

Azure Policy enforces compliance rules but lacks intrusion detection capabilities.

Exam trap

The trap here is that candidates confuse Azure SQL Auditing (logging) with threat detection, assuming that because auditing records events, it can also alert on attacks, but it lacks the real-time analysis and machine learning required for SQL injection detection.

How to eliminate wrong answers

Option A is wrong because Azure SQL Auditing captures and stores database event logs for auditing and compliance, but it does not analyze logs in real-time to detect or alert on SQL injection attacks. Option B is wrong because SQL Vulnerability Assessment scans for misconfigurations and missing patches, not for active malicious activity like SQL injection. Option D is wrong because Azure Policy enforces resource compliance rules (e.g., requiring TDE or firewall rules) but does not provide threat detection or alerting for database attacks.

Practice this question →

33

Drag & Dropmedium

Drag and drop the steps to configure a failover group for an Azure SQL Database in the correct order.

Drag or tap steps into the slots.

Steps

Order

1Step 1

2Step 2

3Step 3

4Step 4

Why this order

Failover groups require a secondary server, then creating the group, adding databases, configuring policy, and testing.

Practice this question →

34

MCQmedium

You are deploying Azure SQL Database for a multi-tenant application. Each tenant's data must be isolated. You need to ensure that tenants cannot access each other's data even if there is a SQL injection vulnerability. Which security feature should you implement?

A.Use Always Encrypted to encrypt sensitive columns.

B.Configure Azure SQL Database auditing to monitor cross-tenant access.

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

D.Implement row-level security (RLS) with a security policy that filters rows by tenant ID.

AnswerD

Row-level security with a security policy filtering by tenant ID enforces isolation inside the database engine itself, so a SQL injection cannot return another tenant's rows regardless of query construction. This satisfies the requirement that tenants cannot access each other's data.

Why this answer

Row-level security (RLS) is the correct choice because it enforces data isolation at the database engine level by filtering rows based on a tenant ID predicate. Even if a SQL injection vulnerability allows an attacker to execute arbitrary queries, RLS ensures that only rows belonging to the attacker's tenant are returned, preventing cross-tenant data access. This is a defense-in-depth measure that works regardless of application-layer flaws.

Exam trap

The trap here is that candidates often confuse data-at-rest encryption (TDE or Always Encrypted) with access control, mistakenly believing encryption alone can prevent unauthorized row access during a SQL injection attack.

How to eliminate wrong answers

Option A is wrong because Always Encrypted protects data at rest and in transit by encrypting specific columns, but it does not control which rows a query can return; an attacker with a SQL injection could still retrieve all encrypted rows (though they would be ciphertext) or bypass the encryption if the injection occurs before decryption. Option B is wrong because auditing only logs database activity for compliance and monitoring; it does not prevent unauthorized access or block cross-tenant data retrieval in real time. Option C is wrong because Transparent Data Encryption (TDE) encrypts the entire database at rest but provides no row-level filtering or access control; an attacker exploiting SQL injection could still read all decrypted data once the database is in use.

Practice this question →

35

MCQmedium

A developer at your company needs to run ad hoc queries against an Azure SQL Database from a workstation on the corporate network. Security policy forbids storing credentials in the application and forbids any inbound public network access to the database. The workstation already has a Microsoft Entra ID-joined identity. What should you configure to meet these requirements?

A.Enable the Allow Azure services and resources to access this server firewall rule and connect over the public endpoint

B.Create a SQL login with a strong password and store the password in the connection string on the workstation

C.Create a contained database user mapped to the Microsoft Entra identity and connect through a private endpoint using Microsoft Entra authentication

D.Issue the developer a shared SQL login and restrict it with an IP-based firewall rule for the corporate egress address

AnswerC

A contained database user mapped to a Microsoft Entra identity lets the developer authenticate with their existing directory credentials, so no password is stored anywhere. Pairing that with a private endpoint keeps all traffic on the private network and removes public exposure. This combination satisfies both the no-stored-credential and no-public-access constraints without extra secrets.

Why this answer

Meeting both constraints requires an authentication method that needs no stored secret and a network path that avoids the public endpoint. A contained database user mapped to a Microsoft Entra identity lets the developer sign in with directory credentials, and a private endpoint routes traffic over the private network. Password-based logins and firewall rules that keep the public endpoint reachable fail the policy.

Exam trap

The trap here is treating a firewall rule as equivalent to removing public access, when an IP rule still leaves the public endpoint reachable.

Practice this question →

36

MCQeasy

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

A.Azure SQL Advanced Threat Protection

B.Azure SQL Database Vulnerability Assessment

C.Azure SQL Auditing

D.Dynamic Data Masking

AnswerC

Azure SQL Auditing writes login events, including failed attempts, to a storage account, Log Analytics workspace, or Event Hub. This captures every failed authentication against the database, satisfying the requirement to audit all failed login attempts.

Why this answer

Azure SQL Auditing is the correct feature because it tracks database events, including failed login attempts, and writes them to an audit log in an Azure Storage account, Log Analytics workspace, or Event Hubs. This allows you to review and analyze authentication failures for security and compliance purposes. The audit logs capture the exact timestamp, source IP address, and the specific error message for each failed login, such as 'Login failed for user'.

Exam trap

The trap here is that candidates often confuse Azure SQL Auditing with Advanced Threat Protection, assuming that threat detection automatically logs all failed logins, but in reality, ATP only alerts on suspicious patterns and does not provide a comprehensive audit trail of every failed login attempt.

How to eliminate wrong answers

Option A is wrong because Azure SQL Advanced Threat Protection is a security intelligence service that detects anomalous activities like SQL injection or brute-force attacks, but it does not provide a configurable audit trail of all failed login attempts; it only alerts on suspicious patterns. Option B is wrong because Azure SQL Database Vulnerability Assessment is a scanning and reporting service that identifies potential database vulnerabilities and misconfigurations, but it does not log or audit individual login events. Option D is wrong because Dynamic Data Masking is a data protection feature that obfuscates sensitive data in query results for unauthorized users, and it has no capability to log or audit authentication failures.

Practice this question →

37

MCQmedium

Your company is using Azure SQL Database with Microsoft Entra ID authentication. A developer needs to connect to the database using a service principal. What should you provide to the developer?

A.The connection string with 'Authentication=Active Directory Service Principal' and the service principal's object ID.

B.The service principal's client ID and client secret, and the connection string with 'Authentication=Active Directory Service Principal'.

C.The service principal's username and password.

D.The service principal's managed identity endpoint.

AnswerB

Service principal authentication requires the client ID and client secret as credentials, paired with the Active Directory Service Principal authentication keyword in the connection string, enabling the developer to authenticate non-interactively against Microsoft Entra ID.

Why this answer

To connect to Azure SQL Database using a service principal with Microsoft Entra ID authentication, the developer needs the service principal's client ID and client secret (or certificate) for authentication, and the connection string must include 'Authentication=Active Directory Service Principal' to specify the authentication method. This combination allows the application to obtain an access token from Microsoft Entra ID via the OAuth 2.0 client credentials grant flow, which is then used to authenticate to the database.

Exam trap

The trap here is that candidates often confuse the service principal's object ID (directory object identifier) with the client ID (application identifier), or mistakenly think a service principal uses a username/password like a regular user, when in fact it relies on OAuth 2.0 client credentials with a client ID and secret.

How to eliminate wrong answers

Option A is wrong because the connection string requires 'Authentication=Active Directory Service Principal', but the service principal's object ID is not used in the connection string; instead, the client ID (application ID) is used as the User ID. Option C is wrong because service principals do not have traditional username/password credentials; they authenticate using a client ID and client secret (or certificate) via OAuth 2.0, not a username and password. Option D is wrong because the managed identity endpoint is used for Azure resources with a managed identity (e.g., VM, App Service), not for a service principal; a service principal is a separate application identity that requires explicit client credentials.

Practice this question →

38

Multi-Selectmedium

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

Select 2 answers

A.Enable Auditing to block malicious queries.

B.Enable TDE to prevent SQL injection attacks.

C.Use SQL authentication with complex passwords.

D.Enable firewall rules to restrict access to specific IP addresses.

E.Use Azure Active Directory authentication instead of SQL authentication.

AnswersD, E

Restricting access to specific IP addresses via firewall rules satisfies the requirement to limit network exposure of Azure SQL Database. Server-level and database-level firewall rules block connections originating outside approved ranges, ensuring only known clients reach the logical server, which directly reduces the attack surface from arbitrary internet traffic.

Why this answer

Option D is correct because Azure SQL Database firewall rules (server-level and database-level) restrict inbound connections to approved IP address ranges, reducing the exposed attack surface by denying traffic from unknown sources. Option E is correct because Azure Active Directory (now Microsoft Entra ID) authentication centralizes identity management, supports MFA and conditional access, and eliminates the risk of weak or shared SQL logins and passwords. Option A is incorrect because Auditing records and logs activity for compliance and forensics; it does not block or prevent malicious queries.

Option B is incorrect because Transparent Data Encryption encrypts data at rest (and backups) and does nothing to stop SQL injection, which is an application-layer input validation issue. Option C is incorrect because SQL authentication with complex passwords is a weaker, legacy approach compared to Entra ID authentication, and complex passwords alone do not constitute a best practice for securing Azure SQL Database.

Exam trap

The trap here is that candidates often confuse Auditing (logging) with blocking, or TDE (encryption at rest) with SQL injection prevention, leading them to select options that sound security-related but do not perform the stated function.

Practice this question →

39

Multi-Selectmedium

Which THREE of the following are required to configure Microsoft Entra authentication for an Azure SQL Managed Instance?

Select 3 answers

A.Enable the managed instance system-assigned managed identity.

B.Set a Microsoft Entra admin for the managed instance.

C.Ensure the managed instance is deployed in a different virtual network from the clients.

D.Grant the managed instance identity the 'Directory Readers' role in Microsoft Entra ID.

E.Configure the managed instance to allow public network access.

AnswersA, B, D

The managed identity is used to authenticate to Entra ID.

Why this answer

Enabling the system-assigned managed identity for Azure SQL Managed Instance is required because Microsoft Entra authentication relies on this identity to authenticate the instance itself against Microsoft Entra ID. Without a managed identity, the instance cannot securely obtain tokens or perform directory lookups needed for authentication and authorization.

Exam trap

The trap here is that candidates often confuse the 'Directory Readers' role assignment (which is required) with optional network settings like VNet isolation or public access, leading them to incorrectly select C or E as necessary steps.

Practice this question →

40

MCQmedium

You are troubleshooting a connectivity issue: an application running on an Azure virtual machine (VM) cannot connect to an Azure SQL Database. The VM is in the same region as the SQL Database. The VM can ping other resources, but the SQL connection fails. The SQL Database has a firewall rule allowing the VM's private IP address. What is the most likely cause?

A.The SQL Database has the public endpoint disabled

B.The firewall rule uses the VM's private IP address, but Azure SQL Database sees the VM's public IP address

C.The SQL Database firewall is configured at the database level, not the server level

D.The VM does not have an outbound security rule allowing traffic to Azure SQL Database

AnswerB

Azure SQL Database's firewall evaluates the source IP as seen by the gateway, which is the VM's public IP after SNAT, not its private address. The allowlisted private IP therefore never matches, so the connection is refused despite the rule existing.

Why this answer

When an Azure VM connects to Azure SQL Database, the source IP address seen by the SQL firewall is the VM's public outbound IP address due to Source Network Address Translation (SNAT) performed by Azure. Even if the VM is in the same region, traffic to Azure SQL Database egresses through the VM's public IP, not its private IP. Therefore, a firewall rule allowing the private IP will not match, causing the connection to fail.

Exam trap

The trap here is that candidates assume Azure SQL Database sees the VM's private IP because they are in the same region, overlooking Azure's mandatory SNAT for public endpoint connections.

How to eliminate wrong answers

Option A is wrong because disabling the public endpoint would prevent all external connections, but the VM could still connect via a private endpoint or service endpoint if configured; the question states the VM cannot connect, but the issue is specifically about the firewall rule. Option C is wrong because firewall rules at the database level inherit server-level rules, and a database-level rule allowing the private IP would still fail for the same SNAT reason; the level does not change the source IP seen. Option D is wrong because outbound security rules in Azure NSGs control traffic flow but do not alter the source IP seen by the SQL firewall; the VM can ping other resources, indicating outbound connectivity is functional.

Practice this question →

41

Multi-Selecthard

You are a database administrator for a manufacturing company that uses Azure SQL Database. The company has a requirement to encrypt sensitive data in transit between the application and the database. Additionally, the company wants to ensure that database administrators (DBAs) cannot view the sensitive data. Which TWO features should you implement?

Select 2 answers

A.Implement row-level security (RLS) to filter rows

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

C.Configure the server to enforce TLS 1.2 by setting the 'Minimal TLS Version' property

D.Implement dynamic data masking on sensitive columns

E.Use Always Encrypted with a column master key stored in Azure Key Vault

AnswersC, E

Enforcing TLS 1.2 via the Minimal TLS Version property encrypts data in transit between application and server, satisfying the in-transit encryption requirement. It does not hide data from DBAs, so it must pair with Always Encrypted to meet the second constraint.

Why this answer

Option C is correct because setting the server's 'Minimal TLS Version' property to 1.2 forces all client connections to Azure SQL Database to negotiate TLS 1.2, which encrypts data in transit between the application and the database. Option E is correct because Always Encrypted encrypts sensitive column data on the client side using a column master key stored in Azure Key Vault, so the data is never decrypted on the SQL server and DBAs cannot view the plaintext. Option A is incorrect because row-level security only filters which rows a user can access; it does not encrypt data in transit or hide column values from DBAs.

Option B is incorrect because TDE encrypts data at rest (database, log, and backup files) and does not protect data in transit, nor does it prevent DBAs from viewing data since they can still query decrypted values. Option D is incorrect because dynamic data masking only obscures data in query results for non-privileged users and does not encrypt data in transit or prevent DBAs, who can be granted UNMASK permission, from viewing the actual data.

Exam trap

The trap here is that candidates often confuse encryption at rest (TDE) or access control (RLS, masking) with encryption in transit and client-side encryption, leading them to select TDE or dynamic data masking instead of the correct combination of TLS enforcement and Always Encrypted.

Practice this question →

42

MCQmedium

You are the database administrator for a company that uses Azure SQL Database. You need to implement a security solution that automatically detects and alerts on suspicious activities, such as SQL injection attempts. Which feature should you enable?

A.Azure SQL Auditing

B.SQL Vulnerability Assessment

C.Microsoft Defender for SQL

D.Transparent Data Encryption (TDE)

AnswerC

Microsoft Defender for SQL continuously monitors Azure SQL Database activity, detecting anomalous behaviour such as SQL injection attempts and generating alerts. It provides the automatic threat detection the scenario requires, unlike auditing or static firewall rules, which log or block but do not alert on suspicious patterns.

Why this answer

Microsoft Defender for SQL (formerly Azure Security Center's Advanced Threat Protection) is the correct feature because it continuously monitors database traffic for anomalous activities, including SQL injection, brute-force attacks, and privilege escalation. When suspicious behavior is detected, it generates a security alert that can be viewed in the Azure portal or integrated with Azure Sentinel for automated response. This is the only option among the choices that provides proactive threat detection and alerting for suspicious activities.

Exam trap

The trap here is that candidates often confuse Azure SQL Auditing (which logs events) with Microsoft Defender for SQL (which actively detects threats), leading them to choose Auditing because it sounds like it would 'detect' suspicious activity, but it only records data for manual review, not automatic alerting.

How to eliminate wrong answers

Option A is wrong because Azure SQL Auditing tracks and logs database events (e.g., successful and failed logins, schema changes) for compliance and forensic analysis, but it does not automatically detect or alert on suspicious activities like SQL injection — it only records the raw data for later review. Option B is wrong because SQL Vulnerability Assessment scans for misconfigurations, missing patches, and security best-practice violations (e.g., weak firewall rules or excessive permissions), but it is a static assessment tool that does not monitor real-time traffic or detect active attacks. Option D is wrong because Transparent Data Encryption (TDE) performs real-time encryption and decryption of data at rest (the database files and backups) using a symmetric key, but it has no capability to detect or alert on suspicious database activities.

Practice this question →

43

MCQmedium

You are the Azure SQL Database administrator for a healthcare company. A new compliance requirement mandates that all data at rest in Azure SQL Database be encrypted with a customer-managed key (CMK) stored in Azure Key Vault, and that you can revoke access to the key at any time. The database is currently encrypted with the default service-managed key. What should you do first to meet this requirement?

A.Enable dynamic data masking on all columns containing sensitive data.

B.Enable Always Encrypted on all sensitive columns using a column master key stored in Azure Key Vault.

C.Configure an Azure SQL Database auditing policy to send logs to a storage account encrypted with a customer-managed key.

D.Create an Azure Key Vault, generate a key, and configure the SQL server's Transparent Data Encryption (TDE) protector to use that key.

AnswerD

This is the correct first step because Azure SQL Database TDE with customer-managed keys requires an Azure Key Vault (or Managed HSM) to store the asymmetric key, and then the logical server's TDE protector must be pointed to that key. Once configured, the database's data encryption key is wrapped by the customer key, allowing revocation via Key Vault permissions.

Why this answer

The requirement is for encryption at rest with a customer-managed key that can be revoked. Azure SQL Database TDE supports customer-managed keys stored in Azure Key Vault. The first step is to create the Key Vault and key, then configure the server's TDE protector to use it.

This ensures the database encryption key is protected by the customer key, and revoking access in Key Vault effectively blocks decryption.

Exam trap

The trap here is confusing Always Encrypted with TDE; Always Encrypted protects individual columns and does not provide database-level encryption at rest with a revocable customer-managed key.

Practice this question →

44

Multi-Selecteasy

Which TWO of the following are valid methods to connect to Azure SQL Database securely?

Select 2 answers

A.Connect using Azure AD authentication with multi-factor authentication.

B.Connect using a shared access key from Azure Storage.

C.Connect using a private endpoint within a virtual network.

D.Connect directly using the server's public IP address without encryption.

E.Connect using SQL authentication with a simple password.

AnswersA, C

Microsoft Entra ID authentication with multi-factor authentication verifies identity through Entra ID and adds a second factor, defeating stolen-password attacks. Traffic remains encrypted via TLS, satisfying the stem's secure connection requirement without embedding credentials in connection strings.

Why this answer

Option A is correct because Azure SQL Database natively supports Azure Active Directory (Azure AD, now Microsoft Entra ID) authentication, and enforcing multi-factor authentication adds a strong identity-verification layer that eliminates static password-only credential risks. Option C is correct because a private endpoint assigns a private IP address from your virtual network to the Azure SQL logical server, so traffic traverses the Microsoft backbone via Azure Private Link instead of the public internet, removing public exposure. Option B is wrong because shared access keys are an Azure Storage construct (used for blobs, queues, tables, files), not an authentication mechanism for Azure SQL Database.

Option D is wrong because connecting over the public IP without encryption (no TLS) exposes credentials and data in transit and is not a secure method. Option E is wrong because SQL authentication with a simple password is weak, lacks MFA, and is vulnerable to brute-force and credential-stuffing attacks.

Exam trap

The trap here is that candidates may confuse shared access keys (a Storage concept) with SQL Database connection methods, or assume that a simple password is acceptable for security, when the exam emphasizes Azure AD and network isolation as the secure standards.

Practice this question →

45

MCQeasy

You are a database administrator for a company that stores sensitive customer data in Azure SQL Database. The security team requires that all access to the database be authenticated using Microsoft Entra ID and that no SQL authentication logins exist. You need to verify that SQL authentication is disabled. What should you do?

A.Set the 'Deny public network access' property to 'Yes'

B.Configure a server-level firewall rule to block all IP addresses

C.In the Azure portal, navigate to the SQL server's 'Microsoft Entra ID' blade and enable 'Azure AD-only authentication'

D.Query sys.sql_logins to check for any SQL authenticated logins

AnswerC

Enabling Microsoft Entra ID-only authentication on the logical server rejects all SQL logins, directly satisfying the requirement that no SQL authentication logins exist. This server-level setting disables the SQL authentication endpoint entirely, so existing SQL logins can no longer connect.

Why this answer

Enabling 'Azure AD-only authentication' in the SQL server's Microsoft Entra ID blade disables SQL authentication entirely, ensuring that only Microsoft Entra ID principals can authenticate. This directly meets the security requirement to eliminate SQL authentication logins, as it prevents any connection attempt using SQL login credentials, even if they exist in the system catalog.

Exam trap

The trap here is that candidates often think querying sys.sql_logins (Option D) is sufficient to verify the requirement, but the question asks to disable SQL authentication, not just check for its existence, and only the Azure AD-only authentication setting enforces the block.

How to eliminate wrong answers

Option A is wrong because setting 'Deny public network access' to 'Yes' only blocks connections from public endpoints, but does not disable SQL authentication; private endpoint connections could still use SQL logins. Option B is wrong because configuring a server-level firewall rule to block all IP addresses restricts network access but does not disable SQL authentication; authenticated users from allowed IPs could still use SQL logins. Option D is wrong because querying sys.sql_logins only checks for existing SQL authenticated logins, but does not disable them or prevent their use; the requirement is to disable SQL authentication, not just verify its existence.

Practice this question →

46

MCQmedium

Your company plans to use Azure SQL Managed Instance for a mission-critical application. You need to ensure that all connections to the database are encrypted and that the server's identity is verified. Which configuration should you enforce?

A.Set 'Force Encryption' = OFF and 'Trust Server Certificate' = OFF

B.Set 'Force Encryption' = ON and 'Trust Server Certificate' = OFF

C.Set 'Force Encryption' = ON and 'Trust Server Certificate' = ON

D.Set 'Force Encryption' = OFF and 'Trust Server Certificate' = ON

AnswerB

Forcing encryption guarantees the connection is TLS-protected, while disabling Trust Server Certificate compels the client to validate the server certificate chain against a trusted authority, verifying the instance's identity. This satisfies both the encryption and identity-verification requirements without bypassing certificate validation, unlike trusting self-signed certificates.

Why this answer

Setting 'Force Encryption' = ON ensures that all connections to Azure SQL Managed Instance use TLS encryption, while setting 'Trust Server Certificate' = OFF forces the client to validate the server's certificate against a trusted certificate authority (CA). This combination guarantees both data-in-transit encryption and server identity verification, meeting the requirement for a mission-critical application.

Exam trap

The trap here is that candidates often confuse 'Trust Server Certificate' = ON as a convenience setting that simplifies connections, not realizing it disables certificate validation and undermines security for mission-critical workloads.

How to eliminate wrong answers

Option A is wrong because setting 'Force Encryption' = OFF allows unencrypted connections, violating the encryption requirement. Option C is wrong because setting 'Trust Server Certificate' = ON instructs the client to trust the server certificate without validation, bypassing identity verification and potentially allowing man-in-the-middle attacks. Option D is wrong because setting 'Force Encryption' = OFF permits unencrypted connections, and 'Trust Server Certificate' = ON disables certificate validation, failing both encryption and identity verification requirements.

Practice this question →

47

MCQhard

You are deploying an Azure SQL Database that will contain highly sensitive personal data. The security policy requires that the data be encrypted at rest, in transit, and in use. Additionally, the encryption keys must be stored in a hardware security module (HSM) and be customer-managed. Which combination of features should you implement?

A.TDE with a service-managed key, enforce TLS 1.2, and Always Encrypted with a column master key in Key Vault.

B.TDE with a customer-managed key in Key Vault, enforce TLS 1.2, and use Dynamic Data Masking.

C.TDE with a customer-managed key in Key Vault, enforce TLS 1.2, and Always Encrypted with a column master key stored in Windows Certificate Store.

D.TDE with a customer-managed key in Azure Key Vault (HSM-backed), enforce TLS 1.2, and Always Encrypted with a column master key in Azure Key Vault (HSM-backed).

AnswerD

TDE with a customer-managed HSM-backed key in Azure Key Vault covers encryption at rest, enforced TLS 1.2 secures data in transit, and Always Encrypted with an HSM-backed column master key protects data in use. This satisfies all three encryption states plus customer-managed HSM keys.

Why this answer

It satisfies all requirements: encryption at rest via TDE with a customer-managed key stored in an HSM-backed Key Vault, encryption in transit by enforcing TLS 1.2, and encryption in use via Always Encrypted with the column master key also stored in an HSM-backed Key Vault. This ensures that all three states of data are encrypted and that keys are both customer-managed and hardware-protected.

Exam trap

The trap here is that candidates may confuse Dynamic Data Masking with encryption in use, or overlook that storing keys in Key Vault does not automatically imply HSM protection unless the vault is specifically HSM-backed.

How to eliminate wrong answers

Option A is wrong because TDE with a service-managed key does not meet the customer-managed key requirement, and storing the column master key in Key Vault alone does not guarantee HSM protection unless the vault is HSM-backed. Option B is wrong because Dynamic Data Masking does not encrypt data in use; it only obfuscates data at query time, failing the encryption-in-use requirement. Option C is wrong because storing the column master key in the Windows Certificate Store does not use an HSM, violating the requirement that keys be stored in a hardware security module.

Practice this question →

48

MCQmedium

You are the database administrator for a company that uses Azure SQL Database. The security team requires that all data in transit between the application and the database be encrypted, and they want to enforce a minimum TLS version of 1.2 at the server level. The application connects using the server's fully qualified domain name. What should you configure to meet this requirement with the least administrative effort?

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

B.Create a server-level firewall rule that allows only the application's IP address and set 'Allow Azure services' to OFF.

C.Enable Transparent Data Encryption (TDE) on the database and use a customer-managed key.

D.Set the 'Minimum TLS version' to 1.2 in the Azure SQL server's firewall and virtual networks settings.

AnswerD

The Azure SQL logical server has a 'Minimum TLS version' setting under Security > Firewall and virtual networks (or Networking) in the Azure portal. Setting it to 1.2 enforces TLS 1.2 or higher for all connections to the server, satisfying the requirement without application changes or additional infrastructure.

Why this answer

The requirement is to enforce a minimum TLS version for all connections to the Azure SQL server. The logical server's 'Minimum TLS version' setting directly controls the minimum TLS version accepted. Configuring this setting to 1.2 ensures that any client attempting to connect with an older TLS version is rejected, and it requires no changes to the application or additional networking components.

Exam trap

The trap here is confusing encryption in transit with encryption at rest or network isolation features, and assuming that a private endpoint or TDE automatically enforces a minimum TLS version.

Practice this question →

49

MCQeasy

You are the database administrator for an Azure SQL Database that contains several tables with columns that store personally identifiable information (PII). The security team requires that these columns be identified and labeled as 'Confidential' in the database. You need to implement a solution that automatically classifies these columns based on their names and data patterns. What should you use?

A.SQL Data Discovery and Classification.

B.Always Encrypted.

C.Transparent Data Encryption (TDE).

D.Dynamic Data Masking (DDM).

AnswerA

SQL Data Discovery and Classification is a feature in Azure SQL Database that scans your database, identifies columns that may contain sensitive data based on names and data patterns, and allows you to apply classification labels such as 'Confidential'. It provides recommendations and can automatically classify columns, meeting the requirement to identify and label PII columns.

Why this answer

SQL Data Discovery and Classification is the built-in feature in Azure SQL Database that scans for potentially sensitive columns, such as those containing PII, and allows you to apply classification labels like 'Confidential'. It uses pattern matching and column names to provide recommendations, which can be reviewed and applied. This directly meets the requirement to identify and label PII columns.

Exam trap

The trap here is confusing data protection features like DDM or Always Encrypted with data discovery and classification, which is a separate capability for identifying and labeling sensitive data.

Practice this question →

50

Multi-Selecthard

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

Select 2 answers

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

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

C.Use Azure Private Link to connect from Azure VNets instead of opening firewall rules to IP ranges.

D.Audit all firewall rule changes using Azure Activity Logs.

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

AnswersC, D

Azure Private Link provides a private IP endpoint within the VNet, eliminating public IP firewall rules entirely. This satisfies the best-practise requirement to avoid exposing the database to internet ranges, reducing attack surface while maintaining connectivity from Azure VNets.

Why this answer

Option C is correct because Azure Private Link (Private Endpoint) gives Azure SQL Database a private IP inside your VNet, so clients connect over the Microsoft backbone without any public IP firewall rules, which is the recommended way to restrict access from Azure VNets. Option D is correct because Azure Activity Logs record control-plane operations such as creating, updating, or deleting firewall rules, providing the audit trail needed to detect unauthorized or accidental rule changes. Options A and B are not best practices: IP-based rules for all clients, especially Azure services, expose the logical server to the public internet, and broad ranges like 0.0.0.0/0 defeat the purpose of a firewall.

Option E is not recommended because the 'Allow Azure Services' rule (0.0.0.0) permits connections from any Azure tenant, not just your own resources, so it should be avoided in favor of Private Link or specific IP rules.

Exam trap

The trap here is that candidates often think only one correct answer exists, but the question explicitly asks for TWO. Candidates may select Option E ('Allow Azure Services') thinking it is a best practice, but it is actually too broad and should be avoided. Auditing (Option D) is often overlooked as a management best practice, but it is essential for security governance.

Practice this question →

51

MCQmedium

Your company has an Azure SQL Database that stores sensitive customer data. You need to ensure that data is encrypted at rest and in transit. The database is currently using Transparent Data Encryption (TDE) with service-managed keys. Compliance requirements now mandate that you use customer-managed keys stored in Azure Key Vault. Additionally, all connections must use encrypted connections. What should you do?

A.Create a new Azure SQL Database with TDE enabled using a customer-managed key from Key Vault. Migrate data using SQL Server Management Studio (SSMS) with 'Encrypt connection' enabled.

B.Configure the Azure SQL Server to use a customer-managed key from Azure Key Vault for TDE and set 'Encrypted connection' to 'Required' on the server.

C.Enable Transparent Data Encryption (TDE) with service-managed keys and set 'Minimum TLS version' to 1.2.

D.Implement Always Encrypted with keys stored in Azure Key Vault and set 'Encrypted connection' to 'Required' on the server.

AnswerB

Configuring a customer-managed key in Azure Key Vault for TDE satisfies the compliance mandate for customer-controlled encryption at rest, while setting Encrypted connection to Required enforces encryption in transit for all connections to the Azure SQL server.

Why this answer

It directly addresses both requirements: using a customer-managed key from Azure Key Vault for TDE (which replaces the service-managed key) and enforcing encrypted connections by setting 'Encrypted connection' to 'Required' on the Azure SQL Server. This configuration ensures data at rest is encrypted with a key you control, and all client connections must use TLS encryption, meeting compliance mandates without requiring a new database or data migration.

Exam trap

The trap here is that candidates often confuse Always Encrypted with TDE, thinking column-level encryption satisfies the 'at rest' requirement for the entire database, or they assume that setting 'Minimum TLS version' alone ensures all connections are encrypted, when in fact 'Encrypted connection' must be explicitly set to 'Required' to reject unencrypted connections.

How to eliminate wrong answers

Option A is wrong because creating a new database and migrating data is unnecessary; you can change the TDE key type on the existing database by configuring the server to use a customer-managed key from Key Vault, and 'Encrypt connection' in SSMS only affects that specific migration session, not all future connections. Option C is wrong because it keeps service-managed keys for TDE, which does not satisfy the compliance requirement for customer-managed keys, and setting 'Minimum TLS version' to 1.2 only enforces a minimum protocol version but does not require encrypted connections for all clients. Option D is wrong because Always Encrypted protects data in use and in transit at the column level, but it does not encrypt the entire database at rest (TDE is needed for that), and it does not address the requirement to use customer-managed keys for TDE.

Practice this question →

52

MCQeasy

You need to encrypt sensitive columns in an Azure SQL Database table so that data is encrypted at rest and in transit between the application and database. Which feature should you use?

A.Row-Level Security

B.Transparent Data Encryption (TDE)

C.Dynamic Data Masking

D.Always Encrypted

AnswerD

Always Encrypted keeps data encrypted at rest and in transit, with keys held outside Azure SQL Database. The client driver encrypts and decrypts values, so ciphertext never leaves the database engine unencrypted. This satisfies the stem's requirement for protection both at rest and between the application and database, unlike Transparent Data Encryption, which only covers data at rest.

Why this answer

Always Encrypted is the correct choice because it encrypts sensitive data both at rest in the database and in transit between the application and the database. It ensures that encryption keys are never revealed to the database engine, so data remains encrypted throughout the entire data path, including during query execution. This meets the requirement for encryption at rest and in transit.

Exam trap

The trap here is that candidates often confuse Transparent Data Encryption (TDE) as covering both at-rest and in-transit encryption, but TDE only encrypts data at rest on disk, not during network transmission or while in memory.

How to eliminate wrong answers

Option A is wrong because Row-Level Security (RLS) controls access to rows based on user identity or context, but it does not encrypt data at rest or in transit. Option B is wrong because Transparent Data Encryption (TDE) encrypts the database files at rest but does not protect data in transit between the application and the database; it also does not prevent the database engine from seeing plaintext data during query processing. Option C is wrong because Dynamic Data Masking obfuscates data in query results for unauthorized users but does not encrypt the underlying data at rest or in transit, and the database engine still processes plaintext data.

Practice this question →

53

MCQeasy

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

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

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

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

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

AnswerA

Configuring the server-level minimal TLS version to 1.2 rejects any client handshake negotiating TLS 1.0 or 1.1, enforcing the requirement across every database on that logical server. This is a server property, so it applies to all connections without per-database changes.

Why this answer

To enforce TLS 1.2 or higher for all connections to an Azure SQL Database, you must configure the 'minimal TLS version' setting at the server level in the Azure portal. This setting applies to all databases hosted on that logical server, ensuring that any client attempting to connect with a TLS version lower than 1.2 is rejected. The server-level property directly controls the TLS protocol version accepted during the SSL/TLS handshake, overriding any client-side or database-level settings.

Exam trap

The trap here is that candidates often confuse the 'minimal TLS version' setting with a database-level property or think that firewall rules or connection string options can enforce TLS version restrictions, but Azure SQL Database only exposes this control at the server level.

How to eliminate wrong answers

Option B is wrong because the 'minimal TLS version' setting is a server-level property, not a database-level property; Azure SQL Database does not expose a per-database TLS version configuration. Option C is wrong because firewall rules in Azure SQL Database control IP-based access, not TLS protocol versions; they cannot inspect or deny connections based on the TLS version used. Option D is wrong because 'Force encryption' in the connection string ensures encryption is used but does not enforce a specific TLS version; the client and server may negotiate a lower TLS version (e.g., 1.0 or 1.1) even with encryption enabled.

Practice this question →

54

MCQmedium

You are the database administrator for a company that uses Azure SQL Database. The security team requires that all data at rest be encrypted with a customer-managed key (CMK) stored in Azure Key Vault, rather than the default service-managed key. You need to implement this requirement with the least administrative overhead. What should you do?

A.Create a database master key (DMK) in each user database and encrypt it with a password.

B.Enable Transparent Data Encryption (TDE) with a customer-managed key by configuring the Azure SQL logical server to use a key from Azure Key Vault.

C.Enable Always Encrypted with a column master key stored in Azure Key Vault for all columns in every table.

D.Configure Azure Storage Service Encryption (SSE) with a customer-managed key on the storage account that hosts the database files.

AnswerB

Configuring TDE with a customer-managed key at the logical server level enables Bring Your Own Key (BYOK) for all databases on that server. The server's managed identity accesses the key in Azure Key Vault, so no per-database key management is needed, meeting the requirement with minimal overhead.

Why this answer

TDE with a customer-managed key at the logical server level is the correct approach because it applies to all databases on the server and uses Azure Key Vault for key storage, satisfying the security team's requirement for customer-managed keys. The other options either do not provide full database encryption, require per-column configuration, or apply to services outside Azure SQL Database.

Exam trap

The trap here is assuming that Always Encrypted or a database master key provides data-at-rest encryption for the entire database, when only TDE with a customer-managed key does so at the server level.

Practice this question →

55

MCQmedium

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

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

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

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

D.Transaction log backups are taken every 12 hours.

AnswerC

Configuring the retention period to 7 days sets the PITR window, so full backups are kept for seven days and differential backups run every 12 hours. This satisfies the template's backup retention constraint rather than long-term retention, which requires a separate policy.

Why this answer

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

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

56

Drag & Dropmedium

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

Drag or tap steps into the slots.

Steps

Order

1Step 1

2Step 2

3Step 3

4Step 4

Why this order

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

Practice this question →

57

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 →

58

MCQeasy

You manage an Azure SQL Database server that hosts multiple databases. The security policy requires that all connections to the server use a minimum TLS version of 1.2 and that the setting applies to all databases on the server. What should you configure?

A.Set the Minimum TLS version to 1.2 in the server's Transact-SQL firewall settings.

B.Set the Minimum TLS version to 1.2 in the server's networking properties in the Azure portal.

C.Enable 'Enforce SSL connection' on the server and set the client driver to TLS 1.2.

D.Configure the database's connection policy to 'Proxy' and enable TLS 1.2 in the connection string.

AnswerB

The minimum TLS version is a server-level networking property in Azure SQL Database. Setting it to 1.2 in the Azure portal (or via PowerShell/CLI) enforces that all connections to every database on that server use at least TLS 1.2, satisfying the security policy with a single configuration.

Why this answer

The minimum TLS version is a server-level networking setting in Azure SQL Database. Configuring it to 1.2 in the server's networking properties ensures that all connections to all databases on that server must use TLS 1.2 or higher. Firewall rules, connection policies, and client-side settings do not enforce the minimum TLS version server-wide.

Exam trap

The trap here is confusing the 'Enforce SSL connection' setting with the 'Minimum TLS version' setting; the former only requires encryption, while the latter actually enforces a specific TLS version.

Practice this question →

59

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

Dynamic Data Masking applies masking at query time based on the caller's permissions, so db_datareader users receive masked credit card values while db_owner users, who are excluded from masking, still see the full unmasked data. No data is altered at rest.

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 →

60

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

Deterministic encryption only permits equality comparisons, so pattern matching fails. Secure enclaves extend Always Encrypted by performing computations inside a hardware-protected enclave, enabling `LIKE`, range and sorting operations on encrypted columns. Configuring an enclave-enabled column master key satisfies the stem's requirement to search encrypted data while keeping it protected from unauthorised users.

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 →

61

MCQmedium

You administer an Azure SQL Database named HRDB. The security team requires that all data at rest be encrypted with a customer-managed key stored in Azure Key Vault, and that the key be automatically rotated every 90 days. You create the Key Vault and grant the logical server's managed identity the necessary permissions. What should you do next to meet the requirement?

A.Configure TDE on the logical server to use a customer-managed key from Azure Key Vault and enable auto-rotation.

B.Enable Transparent Data Encryption (TDE) with a service-managed key on the logical server.

C.Enable Dynamic Data Masking on all sensitive columns.

D.Enable Always Encrypted on all columns containing sensitive data.

AnswerA

Configuring TDE with a customer-managed key (BYOK) stored in Azure Key Vault meets the requirement for customer control. Azure SQL supports automatic key rotation when the key is set to auto-rotate in Key Vault, so the 90-day rotation requirement is satisfied without manual intervention.

Why this answer

The requirement is for encryption at rest using a customer-managed key in Azure Key Vault with automatic rotation. TDE with a customer-managed key (BYOK) is the Azure SQL feature that provides this. Configuring TDE to use a Key Vault key and enabling auto-rotation ensures the key is rotated every 90 days without manual intervention, satisfying the security team's policy.

Exam trap

The trap here is confusing Always Encrypted with TDE, assuming any encryption feature satisfies the customer-managed key requirement.

Practice this question →

62

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

Microsoft Entra ID authentication eliminates stored SQL logins and passwords, satisfying the stem's requirement to secure Azure SQL Database. It enforces centralised identity governance, conditional access and multifactor authentication, while supporting managed identities for application connections. This removes credential sprawl and weak password reuse, which SQL authentication cannot prevent.

Why this answer

Option A is correct because Microsoft Entra ID authentication centralizes identity management, supports MFA and conditional access, and eliminates the risk of weak or shared SQL logins and passwords. Option B is correct because Azure SQL Database firewall rules (server-level and database-level) limit inbound connections to specific, known IP address ranges, reducing the exposed attack surface from the public internet. Option C is correct because Transparent Data Encryption (TDE) encrypts data at rest, including backups and log files, protecting against unauthorized access to physical media or backup theft.

Option D is not a best practice because enabling public network access broadens exposure; private endpoints or restricted firewall rules are preferred. Option E is not a best practice because granting db_owner to developers violates least privilege and gives excessive control over the database.

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 →

63

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

A contained database user mapped to the Microsoft Entra service principal lets the application authenticate via Microsoft Entra ID without a SQL login, satisfying the requirement to connect using a service principal on Azure SQL Managed Instance.

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 →

64

MCQmedium

You are the database administrator for an Azure SQL Database named HRDB. The security team mandates that the database must be protected against SQL injection attacks and that any suspicious activity must be automatically detected and reported. You need to enable a feature that provides this protection with minimal administrative effort. What should you enable?

A.Transparent Data Encryption (TDE)

B.Auditing

C.Advanced Threat Protection

D.Dynamic Data Masking

AnswerC

Advanced Threat Protection for Azure SQL Database detects anomalous activities such as SQL injection attempts, unusual access patterns, and potential brute-force attacks. It sends alerts to the configured recipients and integrates with Microsoft Defender for Cloud. Enabling it requires minimal configuration and directly addresses the requirement for automatic detection and reporting of suspicious activity.

Why this answer

Advanced Threat Protection continuously monitors database activities and uses machine learning to identify potential vulnerabilities and anomalous access patterns, including SQL injection. It automatically raises alerts, satisfying the need for detection and reporting. The other features provide encryption, masking, or logging but lack the automated threat detection capability.

Exam trap

The trap here is confusing auditing or encryption with threat detection, assuming that logging or encrypting data automatically protects against SQL injection and alerts on suspicious behavior.

Practice this question →

65

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

Server-level Azure SQL Auditing captures successful and failed logins, and routing those records to a Log Analytics workspace enables querying. Microsoft Sentinel then correlates the ingested data to detect brute-force patterns, satisfying both the auditing and threat-detection requirements.

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 →

66

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 →

67

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

Enabling email notifications to admins and subscription owners within the Microsoft Defender for SQL settings routes alerts for suspicious activity to the specified recipients. This satisfies the requirement to be notified when anomalous database access is detected.

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 →

68

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

Dynamic Data Masking applies masking rules at query time, returning masked values to non-administrative users while privileged accounts with UNMASK permission see actual data, satisfying the requirement to hide PII from non-administrators without altering stored values.

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 →

69

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

B2B collaboration brings the partner tenant's identities into your tenant as guests, which Microsoft Entra authentication can then resolve. Contained database users created from those guest identities grant read-only access scoped to the database without server-level logins.

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 →

70

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

The audit policy's action list omits the relevant login groups, so authentication events are never written to the audit log. Azure SQL Database auditing captures only the actions and action groups explicitly specified in the policy; without SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP or FAILED_DATABASE_AUTHENTICATION_GROUP, both successful and failed logins remain uncaptured.

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 →

71

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

Dynamic Data Masking applies masking rules at query time, returning obfuscated values to unauthorised users while privileged roles such as HR managers see the underlying salary data. It satisfies the requirement to hide sensitive columns without altering stored data or application code.

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 →

72

MCQhard

You manage an Azure SQL Database that contains a table with a column named 'CreditCardNumber' that stores sensitive data. You need to ensure that the data in this column is encrypted at rest and in use, and that only specific application users can decrypt it. You also need to minimize performance impact on queries that do not access this column. What should you implement?

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

B.Dynamic Data Masking on the 'CreditCardNumber' column.

C.Row-Level Security (RLS) with a security policy that filters rows based on user identity.

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

AnswerA

Always Encrypted with secure enclaves protects data at rest and in use, and allows rich computations on encrypted data within the enclave. The column master key in Azure Key Vault controls access, so only clients with permission to the key can decrypt. Queries not accessing the encrypted column are unaffected, minimizing performance impact. This meets all requirements: encryption at rest and in use, granular access control, and minimal impact.

Why this answer

Always Encrypted with secure enclaves is designed to protect sensitive data both at rest and in use. It uses column encryption keys and a column master key stored in a key store such as Azure Key Vault. Only clients with access to the column master key can decrypt data.

Secure enclaves allow computations on encrypted data without exposing it to the database engine. This approach meets the need for granular access control and minimal performance impact on unrelated queries.

Exam trap

The trap here is assuming that Transparent Data Encryption or Dynamic Data Masking provides in-use encryption and granular decryption control, when only Always Encrypted with secure enclaves does.

Practice this question →

73

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

Server-level firewall rules permit the named client IP ranges, while the 'Allow Azure services' toggle opens the special 0.0.0.0 rule that lets platform-originated traffic through. Together they satisfy both constraints: specific client IPs only, plus Azure services access.

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 →

74

MCQeasy

A junior developer at your company connects to an Azure SQL Database using the SQL login 'appuser'. You need to grant 'appuser' the ability to read from a table named dbo.Orders in the Sales schema, but nothing else in the database. You also want to follow the principle of least privilege. What should you do?

A.Grant SELECT on the dbo.Orders table to 'appuser'.

B.Add 'appuser' to the db_datareader role.

C.Add 'appuser' to the db_owner role.

D.Grant CONTROL on the Sales schema to 'appuser'.

AnswerA

A table-level GRANT SELECT gives exactly the read permission required on dbo.Orders and nothing more. It follows least privilege because the user receives no access to other tables, views, or schemas, and it is the most granular standard permission available for this scenario.

Why this answer

The most granular permission that satisfies the requirement is a table-level GRANT SELECT on dbo.Orders. It gives the developer exactly the read access needed while leaving all other objects inaccessible. Role memberships such as db_datareader and db_owner, and schema-level CONTROL, all grant broader rights than the scenario allows.

Exam trap

The trap here is reaching for a built-in role such as db_datareader because it is convenient, when it grants read access to every table rather than the one table required.

Practice this question →

75

MCQmedium

You manage an Azure SQL Database. A security review finds that an application service principal is connecting with a SQL login that has db_owner membership, and that the login's password has not changed in two years. You must reduce the standing privilege and eliminate the long-lived password while keeping the application working. What should you do?

A.Rotate the SQL login password and move it into Azure Key Vault, leaving db_owner membership unchanged

B.Enable Microsoft Entra authentication on the server and keep the SQL login as a fallback for the application

C.Create a contained database user mapped to the service principal's Microsoft Entra identity, grant it only the required permissions, and remove the SQL login

D.Add the service principal to a custom database role with db_owner and enforce a password policy on the login

AnswerC

A contained database user mapped to the service principal authenticates through Microsoft Entra ID, so no password exists to rotate or leak. Granting only the permissions the application needs removes the db_owner standing privilege. Removing the old SQL login closes the credential path entirely, satisfying both the least-privilege and no-long-lived-password requirements.

Why this answer

Replacing the password-based SQL login with a contained database user mapped to the service principal removes the long-lived secret and lets you grant only the permissions the application actually needs. Rotating a password or renaming the role leaves both problems intact, and merely enabling directory authentication without removing the login does not close the insecure path.

Exam trap

The trap here is believing that moving a password into Key Vault or rotating it satisfies a no-long-lived-password requirement, when the password itself still exists and the excessive role membership is untouched.

Practice this question →

Page 1 of 2 · 147 questions totalNext →

Ready to test yourself?

Try a timed practice session using only Implement a secure environment questions.

Start 20-question session