CCNA Implement a secure environment Questions — Page 2 of 2

Multi-Selecteasy

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

Select 2 answers

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

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

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

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

E.Enable 'contained database authentication' on the server

AnswersA, D

Microsoft Entra ID authentication requires a directory administrator designated on the logical server, since that identity governs token issuance and directory-based logins. Without this server-level administrator, Azure SQL Database cannot validate Microsoft Entra ID tokens, so the remaining configuration steps have no authority to authenticate against.

Why this answer

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

Exam trap

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

Practice this question →

MCQhard

You are the database administrator for a financial services company using Azure SQL Database. The security team mandates that all administrative activities on the SQL logical server be performed using just-in-time (JIT) access with approval workflows, and that permanent elevated permissions be eliminated. You need to implement this requirement with the least amount of custom development. What should you use?

A.Microsoft Entra Privileged Identity Management (PIM) for Azure resources, assigning the SQL Server Contributor role as eligible.

B.Custom Azure Functions that grant and revoke database roles based on approval emails.

C.SQL Database contained users with temporary passwords that expire after a short period.

D.Azure SQL Database auditing with Log Analytics alerts for administrative actions.

AnswerA

Microsoft Entra Privileged Identity Management (PIM) provides just-in-time role activation with approval workflows and time-bound assignments. By making the SQL Server Contributor role eligible rather than permanent, administrators must activate it when needed, satisfying the JIT and approval requirements with minimal custom development. This is the native Azure solution for privileged access management.

Why this answer

Microsoft Entra Privileged Identity Management (PIM) is the native Azure service for just-in-time privileged access. By assigning roles such as SQL Server Contributor as eligible, administrators must activate the role with approval, and the assignment is time-bound. This eliminates permanent elevated permissions and meets the JIT and approval requirements with minimal custom development.

Auditing, custom functions, and contained users do not provide the required approval workflows and JIT activation.

Exam trap

The trap here is assuming that auditing or custom code can enforce JIT access, when the native PIM service is designed specifically for this purpose.

Practice this question →

MCQmedium

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

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

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

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

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

AnswerD

A private endpoint assigns a private IP from the virtual network subnet to Azure SQL Database, so traffic from the application VMs stays on the Microsoft backbone and never traverses the public internet, satisfying the encryption and isolation requirement.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

Multi-Selecthard

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

Select 2 answers

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

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

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

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

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

AnswersB, C

This configures the server to use the CMK.

Why this answer

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

Exam trap

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

Practice this question →

MCQeasy

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

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

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

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

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

AnswerC

Setting encrypt=true forces the JDBC driver to negotiate TLS, while trustServerCertificate=false makes it validate the server certificate chain. Together they satisfy the requirement that all connections are genuinely encrypted and authenticated, preventing silent fallback to unencrypted sessions.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

MCQmedium

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

A.The AuditActionGroup specified does not capture SELECT operations.

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

C.The storage endpoint is incorrectly formatted.

D.The storage account access key is invalid.

AnswerA

Auditing captures only the action groups and actions defined in the audit rule. If the configured AuditActionGroup excludes SELECT, statement-level reads are never recorded, so SELECT operations go unaudited even though the rule runs successfully.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

Multi-Selecteasy

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

Select 2 answers

A.Enable Transparent Data Encryption (TDE).

B.Use parameterized queries in the application.

C.Configure Dynamic Data Masking (DDM).

D.Implement stored procedures with input validation.

E.Enable Always Encrypted on sensitive columns.

AnswersB, D

Parameterised queries send user input as bound parameters rather than concatenated SQL text, so the database treats injected payloads as data, never executable code. This directly neutralises the injection vector in the application's SQL-authenticated connection string.

Why this answer

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

Exam trap

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

Practice this question →

MCQeasy

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

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

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

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

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

AnswerA

Transparent Data Encryption with a customer-managed key in Azure Key Vault encrypts backups and data files at rest, and the customer controls the key rather than Microsoft. This satisfies the compliance mandate for customer-managed keys on Azure SQL Managed Instance backups.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

Multi-Selecthard

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

Select 2 answers

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

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

C.Use Azure Private Link with a private endpoint.

D.Configure server-level IP firewall rules.

E.Enable service endpoints on the subnet.

AnswersB, C

Azure SQL Managed Instance is deployed inside a dedicated subnet within a virtual network, so network security groups applied to that subnet filter inbound and outbound traffic. This satisfies the requirement for configuring network security at the instance level.

Why this answer

Option B is correct because Azure SQL Managed Instance is natively deployed inside an Azure virtual network subnet, and you secure it by using Network Security Groups (NSGs) on that subnet to control inbound and outbound traffic on the required ports (for example, 1433, 5022, 11000-11999). Option C is correct because Azure Private Link with a private endpoint lets clients reach the managed instance over a private IP address within the virtual network, providing private, isolated connectivity rather than exposing it publicly. Option A is not valid because virtual network rules are a feature of Azure SQL Database and Azure Database services, not the configuration model for a Managed Instance, which already lives in a delegated subnet.

Option D is not valid because server-level IP firewall rules apply to Azure SQL Database's logical server, whereas Managed Instance networking is governed by the VNet/subnet and NSG configuration. Option E is not valid because service endpoints are used to restrict access to PaaS services from a subnet, but a Managed Instance is deployed into the subnet itself and does not use service endpoints for its own network security.

Exam trap

The most common trap is confusing Azure SQL Database's firewall and VNet rules with Azure SQL Managed Instance's network security model. Managed Instance relies entirely on VNet integration and NSGs; it does not support virtual network rules, IP firewall rules, or service endpoints.

Practice this question →

MCQhard

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

A.minimalTlsVersion 1.2 is not supported in Azure SQL Database

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

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

D.The password does not meet complexity requirements

AnswerC

Enabling azureAdOnlyAuthentication removes SQL authentication entirely, so supplying administratorLogin and administratorLoginPassword conflicts with that setting and fails validation. The template must omit both properties and instead designate a Microsoft Entra ID administrator, satisfying the stem's azureAdOnlyAuthentication constraint.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

MCQhard

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

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

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

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

D.Enable 'autoRotationEnabled' property

AnswerA

Customer-managed keys require the TDE protector to be stored in Azure Key Vault, so the ARM template must supply keyVaultUri, keyName and keyVersion to point at that key. Without these properties, Azure SQL Database defaults to a service-managed key, breaching the policy.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

MCQmedium

You are configuring security for an Azure SQL Database. The security team requires that all administrative actions on the server and databases are logged to an Azure Storage account, and that the logs are retained for 90 days. You need to configure auditing to meet these requirements with minimal effort. What should you do?

A.Enable auditing at the server level and set the retention period to 90 days, targeting an Azure Storage account.

B.Enable auditing at the server level and set the retention period to 0 (unlimited), targeting an Azure Storage account.

C.Enable auditing at the server level and target an Azure Log Analytics workspace with a 90-day retention.

D.Enable auditing at the database level for each database and set the retention period to 90 days, targeting an Azure Storage account.

AnswerA

Server-level auditing in Azure SQL Database automatically audits all databases on the server and can target an Azure Storage account. Setting the retention period to 90 days meets the retention requirement. This configuration applies to all current and future databases, minimizing administrative effort.

Why this answer

Server-level auditing with a 90-day retention targeting an Azure Storage account audits all databases on the server and meets the retention requirement. Database-level auditing would require per-database configuration, and using Log Analytics does not match the storage account requirement. Unlimited retention does not meet the 90-day specification.

Exam trap

The trap here is thinking that database-level auditing is needed for each database, but server-level auditing covers all databases and reduces administrative overhead.

Practice this question →

MCQhard

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

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

B.Row-Level Security

C.Dynamic Data Masking

D.Transparent Data Encryption

AnswerA

Always Encrypted keeps data encrypted in memory and at rest, so administrators querying the database see only ciphertext while backups and index maintenance still function. Storing the column master key in Azure Key Vault separates key custody from the administrators, satisfying the requirement.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

MCQeasy

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

A.Azure SQL Database Vulnerability Assessment

B.Azure SQL Auditing

C.Microsoft Defender for SQL

D.Microsoft Sentinel

AnswerC

Microsoft Defender for SQL provides Advanced Threat Protection, continuously analysing query patterns and audit logs to flag anomalous behaviour indicative of SQL injection. It satisfies the requirement for automatic detection and alerting without manual monitoring, raising alerts through Microsoft Defender for Cloud and integrating with Microsoft Entra ID for investigation.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

Multi-Selecthard

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

Select 3 answers

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

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

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

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

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

AnswersA, B, D

Microsoft Defender for SQL provides threat detection that identifies anomalous and injection-like query patterns against the database, raising alerts for investigation. It adds a detection layer that complements input validation and query parameterisation, forming part of a layered defence.

Why this answer

Option A is correct because Microsoft Defender for SQL provides advanced threat protection that specifically detects anomalous and potentially harmful activities such as SQL injection attempts against Azure SQL Database, generating alerts and recommendations. Option B is correct because parameterized queries and stored procedures ensure that user input is treated as data rather than executable SQL code, which is the fundamental application-level defense against SQL injection. Option D is correct because an Azure Web Application Firewall (WAF), for example on Application Gateway or Front Door, inspects HTTP/HTTPS traffic and blocks common injection patterns before they reach the application and database.

Option C is not correct because dynamic data masking only obscures sensitive data in query results for unauthorized users; it does not prevent or detect SQL injection. Option E is not correct because Transparent Data Encryption protects data at rest by encrypting database files, but it does not stop SQL injection, which exploits the application's query logic rather than the storage layer.

Exam trap

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

Practice this question →

Multi-Selectmedium

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

Select 2 answers

A.Store the column encryption key in the database.

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

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

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

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

AnswersB, E

Randomized encryption provides more security but cannot be searched.

Why this answer

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

Exam trap

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

Practice this question →

MCQmedium

You are the DBA for an Azure SQL Database that stores sensitive customer data. The security team requires that database administrators be able to manage the database but not see the sensitive data in plaintext. You need to implement a solution that meets this requirement with minimal application changes. What should you do?

A.Implement Transparent Data Encryption (TDE) with a customer-managed key.

B.Implement Row-Level Security (RLS) on the sensitive tables.

C.Implement Always Encrypted with column encryption keys stored in Azure Key Vault.

D.Implement Dynamic Data Masking on the sensitive columns.

AnswerC

Always Encrypted ensures that sensitive data is encrypted at rest and in transit, and only clients with access to the column encryption keys can decrypt it. Database administrators cannot see plaintext data because the keys are not available to them. This meets the requirement with minimal application changes, as the encryption/decryption is handled by the client driver.

Why this answer

Always Encrypted is designed to protect sensitive data from high-privileged users like DBAs. By storing column encryption keys in Azure Key Vault and configuring the client driver, data remains encrypted on the server and is only decrypted by authorized applications. This meets the requirement with minimal application changes because the encryption is handled by the client driver without altering the database schema significantly.

Exam trap

The trap here is assuming that TDE or Dynamic Data Masking can prevent administrators from seeing plaintext, when only Always Encrypted provides that separation of duties.

Practice this question →

MCQmedium

You are the Azure SQL Database administrator for a financial services company. The compliance team requires that all data in transit between the application tier and Azure SQL Database be encrypted, and that the server enforce a minimum TLS version of 1.2. The application servers run Windows Server 2019 and use the Microsoft.Data.SqlClient provider. You need to configure the server so that only TLS 1.2 connections are accepted. What should you do?

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

B.Enable Transparent Data Encryption (TDE) on the database with a customer-managed key.

C.Add a firewall rule that allows only the application servers' IP addresses.

D.Set the Minimum TLS version to 1.2 on the Azure SQL logical server's networking settings.

AnswerD

The Azure SQL logical server exposes a Minimum TLS version setting under Networking in the Azure portal and via the minimalTlsVersion property in the Microsoft.Sql/servers resource. Setting it to 1.2 causes the gateway to reject connections negotiated below TLS 1.2, which directly satisfies the compliance requirement for the application tier.

Why this answer

Azure SQL Database encrypts connections by default, but the minimum TLS version accepted by the gateway is controlled by the logical server's Minimum TLS version setting. Raising it to 1.2 causes the gateway to refuse any connection that negotiates a lower version, which is exactly what the compliance requirement demands. Firewall rules, private endpoints, and TDE address different concerns.

Exam trap

The trap here is assuming that enabling TDE or a private endpoint automatically raises the minimum TLS version, when only the server-level Minimum TLS version setting enforces it.

Practice this question →

Multi-Selectmedium

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

Select 2 answers

A.Azure Event Grid

B.Azure Monitor Metrics

C.Azure Storage account

D.Log Analytics workspace

E.On-premises file share

AnswersC, D

Azure Storage account is valid because Azure SQL Database auditing writes audit logs to Azure Blob Storage in a storage account. This satisfies the stem's requirement for a supported audit target, distinct from Log Analytics or Event Hubs.

Why this answer

Azure SQL Database auditing writes audit logs to a target destination, and both an Azure Storage account (option C) and a Log Analytics workspace (option D) are supported audit log destinations — the storage account stores audit records as blobs in a container, while the Log Analytics workspace ingests them for querying with KQL and integration with Azure Monitor. These are the two valid auditing targets offered when configuring auditing at the server or database level. Azure Event Grid (option A) is an event-routing service used for reacting to events, not a SQL Database audit log sink, and Azure Monitor Metrics (option B) holds numeric time-series metrics rather than detailed audit records.

An on-premises file share (option E) is not a supported destination for Azure SQL Database auditing.

Exam trap

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

Practice this question →

MCQeasy

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

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

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

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

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

AnswerB

Dynamic data masking masks sensitive columns in query results automatically, requiring no application code or data model changes, and the UNMASK permission exempts the database administrator. This satisfies all three stem requirements, including Microsoft Entra ID authentication compatibility.

Why this answer

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

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

Exam trap

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

Practice this question →

MCQmedium

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

A.Create DDL triggers that write to a table

B.Configure database-level auditing with blob storage destination

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

D.Create an extended events session to capture DDL events

AnswerC

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

MCQhard

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

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

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

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

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

AnswerA

An Azure Policy initiative bundles the 'Configure Microsoft Defender for SQL to be enabled' policy and, assigned at the root management group, inherits to every subscription, giving scalable automated enforcement. This satisfies the requirement to apply the policy across all subscriptions without manual per-subscription configuration.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

MCQeasy

You need to configure Azure SQL Database to allow connections only from Azure services and from a specific on-premises IP range. Which firewall rule configuration should you apply at the server level?

A.Create a private endpoint for the server.

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

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

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

AnswerC

Enabling 'Allow Azure Services and resources to access this server' permits the Azure-internal 0.0.0.0 virtual gateway address, satisfying the Azure-services constraint, while an explicit server-level firewall rule scoped to the on-premises range restricts external access to that range only. Together they meet both requirements without exposing the server publicly.

Why this answer

Enabling 'Allow Azure Services and resources to access this server' permits connections from all Azure services (including those from other subscriptions) by adding a special firewall rule that allows Azure IP ranges. Adding a separate firewall rule for the specific on-premises IP range then restricts non-Azure external traffic to only that range. This combination meets the requirement to allow only Azure services and the specified on-premises range.

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

MCQhard

You manage an Azure SQL Database that contains a table with sensitive columns. You need to ensure that a specific application can access the data in those columns in plaintext, while other applications see ciphertext. You also need to minimize changes to the application code. What should you implement?

A.Always Encrypted with column encryption keys and a column master key.

B.Dynamic data masking on the sensitive columns.

C.Always Encrypted with secure enclaves.

D.Transparent Data Encryption (TDE) with a customer-managed key.

AnswerA

Always Encrypted allows clients to encrypt sensitive data and decrypt it transparently. By configuring the application with the appropriate column master key and column encryption key, it can access plaintext, while other applications without the keys see ciphertext. The application code requires minimal changes—typically just adding 'Column Encryption Setting=Enabled' to the connection string and using parameters for queries. This meets the requirements.

Why this answer

Always Encrypted is designed to protect sensitive data by allowing clients to encrypt and decrypt data transparently. With Always Encrypted, the application that possesses the column master key can decrypt the data, while others see ciphertext. The application code changes are minimal, often just connection string modifications.

This solution provides the required selective access without significant code changes.

Exam trap

The trap here is thinking that TDE or dynamic data masking can provide selective plaintext access; only Always Encrypted gives different views based on key possession.

Practice this question →

100

Multi-Selectmedium

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

Select 2 answers

A.Assign database roles instead of individual permissions.

B.Enable all database features for maximum functionality.

C.Use contained database users with Azure AD authentication.

D.Use server-level logins for all users.

E.Grant the db_owner role to all application users.

AnswersA, C

Database roles bundle permissions, so members inherit only what the role grants rather than accumulating broad individual grants. This directly satisfies least privilege by keeping each principal's effective permissions minimal and auditable within Azure SQL Database.

Why this answer

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

Exam trap

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

Practice this question →

101

MCQhard

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

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

B.Enable Always Encrypted on the database.

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

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

AnswerD

The encryptionProtector resource binds the server to a specific Key Vault key, switching TDE from service-managed to customer-managed. Without it, the database resource alone cannot reference the key vault key, so service-managed keys remain in effect.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

102

MCQeasy

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

A.Blocks all IP addresses from 10.0.0.0 to 10.0.0.255.

B.Allows all IP addresses except 10.0.0.0 to 10.0.0.255.

C.Allows all IP addresses from 10.0.0.0 to 10.0.0.255.

D.Allows only the IP address 10.0.0.0.

AnswerC

The rule's start and end addresses define an inclusive contiguous range, so every address from 10.0.0.0 through 10.0.0.255 is permitted. Azure SQL Database evaluates the client's source IP against this span, meaning the entire /24 subnet can connect rather than one host.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

103

Multi-Selecteasy

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

Select 2 answers

A.Enable Transparent Data Encryption

B.Enable auditing for all database operations

C.Configure firewall rules to allow only trusted IP addresses

D.Use parameterized queries in application code

E.Use stored procedures with parameters

AnswersD, E

Parameterised queries send SQL text and data values to the engine as separate constructs, so user input is never concatenated into the statement and cannot alter its parse tree. This directly neutralises injection payloads, satisfying the requirement to secure Azure SQL Database against SQL injection attacks.

Why this answer

Option D is correct because parameterized queries cause the database engine to treat user input strictly as data rather than executable SQL, so injected payloads cannot alter the query's structure or logic. Option E is correct because stored procedures that accept parameters likewise bind input values to typed parameters, preventing attackers from concatenating malicious SQL into the statement. Both techniques address the root cause of SQL injection at the application/database interface.

Option A (Transparent Data Encryption) only encrypts data at rest and does nothing to stop injection. Option B (auditing) merely records activity for detection and forensics, not prevention. Option C (firewall rules) restricts network origins but cannot stop injection from an already-trusted client or application.

Exam trap

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

Practice this question →

104

MCQmedium

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

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

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

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

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

AnswerC

A private endpoint gives the Azure SQL Database a private IP inside the VNet, so traffic from the VM never traverses the internet, and disabling public access enforces this. A failover group with a paired private endpoint preserves private connectivity and cross-region failover with minimal overhead.

Why this answer

Creating a private endpoint in the VNet ensures that traffic to the Azure SQL Database uses a private IP address within the VNet, never traversing the internet. Disabling public network access enforces this. A failover group with a private endpoint in the secondary region provides cross-region failover while maintaining private connectivity.

This solution minimizes management overhead and cost by using managed services.

Exam trap

DP-300 often tests the difference between VNet service endpoints and private endpoints. Candidates may confuse the two, but only private endpoints provide a private IP for the database and allow disabling public access. Also, failover groups require private endpoints in both regions for private connectivity.

How to eliminate wrong answers

Option A is wrong because restricting firewall rules to the VM's public IP still allows traffic over the internet, violating the requirement that all traffic must be private. Option B is wrong because a point-to-site VPN from the VM to the database does not provide a private endpoint for the database; the database would still be accessible via public endpoint, and VPN adds management overhead. Option D is wrong because a VNet service endpoint allows access from the VNet but does not make the database private; public access remains enabled, and traffic may still traverse the internet for failover.

Practice this question →

105

MCQeasy

You have an Azure SQL Database server named sqlsrv1. Several application teams connect using SQL logins. The security team mandates that all authentication use Microsoft Entra ID and that multifactor authentication be enforceable. You need to configure the server so that Entra ID authentication is available to database users. What should you do first?

A.Assign a Microsoft Entra ID admin to the Azure SQL logical server.

B.Enable Transparent Data Encryption on each database.

C.Create contained database users for each application in every database.

D.Configure a firewall rule to allow the corporate network range.

AnswerA

Setting a Microsoft Entra ID administrator on the logical server is the prerequisite that enables Entra authentication for the server and its databases. Until this is configured, you cannot create contained database users mapped to directory identities, and multifactor authentication enforcement through Conditional Access has no directory principal to act upon.

Why this answer

Assigning a Microsoft Entra ID administrator to the logical server activates directory authentication for the server. Only after that can you create contained database users mapped to Entra identities and rely on Conditional Access to enforce multifactor authentication. The other choices address encryption, network filtering, or dependent steps that require the directory admin to exist first.

Exam trap

The trap here is treating contained database user creation as the first step rather than a step that depends on a directory administrator already existing.

Practice this question →

106

MCQeasy

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

A.Start IP: 192.168.0.0, End IP: 192.168.2.255

B.Start IP: 192.168.1.0, End IP: 192.168.1.255

C.Start IP: 192.168.1.255, End IP: 192.168.1.0

D.Start IP: 192.168.1.0, End IP: 192.168.1.0

AnswerB

Azure SQL Database firewall rules accept a contiguous start and end IP range, so 192.168.1.0 to 192.168.1.255 covers the entire /24 subnet in a single rule, satisfying the requirement to permit every address in that range.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

107

MCQeasy

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

A.SQL Server authentication

B.Microsoft Entra ID authentication with Conditional Access policy

C.Certificate-based authentication

D.Windows authentication

AnswerB

Microsoft Entra ID authentication delegates sign-in to Entra ID, where Conditional Access enforces MFA at token issuance. This satisfies the stem's MFA mandate for administrators, since SQL logins cannot enforce MFA and Entra-only authentication blocks legacy password access.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

108

Multi-Selecteasy

Which of the following are valid methods to authenticate to Azure SQL Database using Microsoft Entra ID? (Select all that apply.)

Select 2 answers

A.Microsoft Entra ID managed identity

B.Active Directory Federation Services (AD FS) token

C.Microsoft Entra ID integrated authentication

D.Microsoft Entra ID certificate-based authentication

E.Microsoft Entra ID password authentication

AnswersA, C

Microsoft Entra ID managed identity is a valid authentication method for Azure SQL Database because it enables an Azure resource (such as an App Service or VM) to acquire an Entra ID access token using its own system-assigned or user-assigned identity, without any explicit credentials stored in code or configuration. The resource's identity is provisioned as a contained database user or server-level login in SQL, and the token is presented to the database via the connection string, making it ideal for automated and background workloads. This method eliminates the overhead of managing secrets or rotating passwords, as the identity lifecycle is fully managed by Azure.

Why this answer

Microsoft Entra ID offers several authentication methods for Azure SQL Database. Managed identity (A) and integrated authentication (C) are directly supported without additional token acquisition steps. Certificate-based authentication (D) and password authentication (E) require an intermediate application or client library to obtain an Entra ID token, and are not considered direct authentication methods for Azure SQL Database.

AD FS tokens (B) are not directly accepted; Entra ID must issue the token after authentication.

Exam trap

Candidates may think all four listed methods are valid for direct authentication to Azure SQL Database, but only managed identity and integrated authentication are directly supported without additional token acquisition steps.

Practice this question →

109

MCQeasy

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

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

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

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

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

AnswerD

The T-SQL CREATE USER ... FROM EXTERNAL PROVIDER statement provisions a database principal mapped to a Microsoft Entra ID identity, and the subsequent GRANT SELECT ON DATABASE grants read access, producing exactly that user with read permissions.

Why this answer

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

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

Exam trap

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

How to eliminate wrong answers

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

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

Practice this question →

110

Multi-Selecthard

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

Select 2 answers

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

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

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

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

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

AnswersA, D

Client-side protocol negotiation determines the TLS version actually used, so applications must request TLS 1.2 or higher in their connection strings. Server enforcement alone cannot upgrade a client that offers only TLS 1.0 or 1.1.

Why this answer

Option A is correct because client-side enforcement is required: applications must be configured to negotiate TLS 1.2 or higher (for example, via the connection string or the underlying driver/provider settings), otherwise a client may still attempt to negotiate an older protocol version. Option D is correct because Azure SQL Database (and Azure SQL Managed Instance) exposes a server-level 'minimalTlsVersion' property that, when set to '1.2', rejects connections that attempt to negotiate TLS versions lower than 1.2. Together, A and D ensure both the client and the server enforce TLS 1.2 or higher.

Option B is not appropriate because a network security group rule cannot inspect or enforce TLS protocol versions; NSGs filter by IP, port, and protocol (TCP/UDP), not by TLS version. Option C is not relevant because 'DenyPublicNetworkAccess' only controls whether public endpoints are reachable, not the TLS version used. Option E is not applicable because 'ForceEncryption' is a SQL Server on-premises/VM configuration option (set via sp_configure or the protocol properties), not an Azure SQL Database server property.

Exam trap

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

Practice this question →

111

MCQeasy

Your Azure SQL Database contains sensitive financial data. You need to audit all data modifications (INSERT, UPDATE, DELETE) and store the audit logs in a central Azure Storage account for compliance. What should you configure?

A.Enable auditing on the database and set the audit log destination to an Azure Storage account.

B.Configure diagnostic settings to stream query store data to an event hub.

C.Enable Microsoft Defender for SQL and configure security alerts to be sent to a storage account.

D.Enable SQL Vulnerability Assessment and export the results to a storage account.

AnswerA

Azure SQL Database auditing captures INSERT, UPDATE and DELETE activity against the database, and configuring an Azure Storage account as the log destination centralises those records for compliance retention. This directly satisfies the requirement to audit all data modifications and store logs centrally.

Why this answer

Azure SQL Database's built-in auditing feature can be configured to capture all data modifications (INSERT, UPDATE, DELETE) and write audit logs directly to an Azure Storage account. This meets the compliance requirement for centralized, durable storage of audit records without additional services or complex pipelines.

Exam trap

The trap here is confusing security monitoring tools (Defender for SQL, Vulnerability Assessment) or performance diagnostics (query store) with the specific auditing feature required for capturing data modification logs for compliance.

How to eliminate wrong answers

Option B is wrong because diagnostic settings streaming query store data to an event hub captures performance and query metrics, not data modification audit logs; it is designed for real-time monitoring, not compliance auditing. Option C is wrong because Microsoft Defender for SQL provides security alerts and threat detection, not granular audit logs of INSERT/UPDATE/DELETE operations; its alerts are sent to security teams, not stored as a compliance audit trail. Option D is wrong because SQL Vulnerability Assessment scans for security misconfigurations and exports assessment results, not data modification audit logs; it is a security posture tool, not an auditing solution.

Practice this question →

112

MCQmedium

You have an Azure SQL Database that uses a firewall rule allowing access from a specific range of IP addresses. A developer reports that they cannot connect from a new IP address that falls outside the allowed range. You need to temporarily allow the developer's IP address for 24 hours without affecting existing rules. What should you do?

A.Configure a point-to-site VPN connection for the developer.

B.Add a new firewall rule at the server level that allows the developer's IP address.

C.Update the existing firewall rule to include the developer's IP address.

D.Modify the database-level firewall rule to include the developer's IP.

AnswerB

New rule allows the specific IP without affecting existing rules.

Why this answer

Azure SQL Database firewall rules are configured at the server level (the logical server) to control inbound access. Adding a new server-level firewall rule for the developer's specific IP address allows temporary access without modifying or removing the existing range-based rule. This approach is the standard method for granting time-limited access to a single IP while preserving all other firewall configurations.

Exam trap

The trap here is that candidates confuse server-level firewall rules with database-level firewall rules, incorrectly assuming that database-level rules exist in Azure SQL Database (they do not), or they think updating the existing range is acceptable, missing the requirement to leave existing rules unchanged.

How to eliminate wrong answers

Option A is wrong because a point-to-site VPN connection is an over-engineered solution that introduces unnecessary complexity and latency; it is not designed for simple IP-based access control to Azure SQL Database and would require additional networking components (e.g., VPN gateway, certificates). Option C is wrong because updating the existing firewall rule to include the developer's IP would expand the allowed IP range permanently, which contradicts the requirement to temporarily allow access for only 24 hours without affecting existing rules. Option D is wrong because database-level firewall rules are a legacy feature and are not supported for Azure SQL Database; all firewall rules must be configured at the server level or via virtual network rules.

Practice this question →

113

Multi-Selecthard

You are the DBA for an Azure SQL Database named OrdersDB. The security team requires that you implement row-level security (RLS) to ensure that sales representatives can only view orders for their own region. You need to create a security policy that filters rows based on the sales representative's region. Which two actions should you perform? (Choose two.)

Select 2 answers

A.Grant SELECT permission on the Orders table to all sales representatives.

B.Create an inline table-valued function that returns 1 when the sales representative's region matches the row's region.

C.Create a security policy that adds a FILTER PREDICATE on the Orders table using the function.

D.Create a database role for each region and add sales representatives to the appropriate role.

E.Enable Auditing on the Orders table to track access.

AnswersB, C

A predicate function is required for RLS. An inline table-valued function that returns 1 when the user's region matches the row's region is used as the filter predicate in the security policy. This function enforces the row filtering logic based on the current user's region.

Why this answer

Row-level security in Azure SQL Database requires a predicate function that defines the filtering logic and a security policy that applies that function to the table. The inline table-valued function returns 1 when the row should be visible to the user, and the security policy adds a FILTER PREDICATE using that function. Together, they enforce that sales representatives only see orders for their region.

Other actions do not implement RLS.

Exam trap

The trap here is thinking that granting permissions or creating roles alone achieves row-level security, when the essential components are the predicate function and the security policy.

Practice this question →

114

MCQmedium

You administer an Azure SQL Database named HRDB. The security team requires that any connection to HRDB from outside the corporate network be blocked, but on-premises applications must continue to connect over the existing site-to-site VPN. The database currently has a public endpoint and a firewall rule allowing all Azure services. You need to restrict access so that only the VPN subnet can reach HRDB. What should you configure?

A.Enable Microsoft Defender for SQL and set the Advanced Threat Protection alert type to 'Access from unusual location'.

B.Set the database's Public Network Access to Disabled and create a private endpoint in the VPN-connected virtual network.

C.Configure a database-level firewall rule that allows only the on-premises application's service account and deny all other logins.

D.Add a server-level firewall rule for the VPN subnet's public IP address range and keep the public endpoint enabled.

AnswerB

Disabling public network access removes the public endpoint, and a private endpoint places the logical server inside the VPN-connected VNet so on-premises traffic flows over the private IP. This satisfies both the block-outside requirement and continued connectivity from the corporate network without exposing a public listener.

Why this answer

The requirement is network isolation combined with continued VPN access. Disabling public network access closes the internet-facing endpoint, and a private endpoint in the VPN-connected VNet provides a private IP that on-premises systems can reach over the tunnel. Firewall rules and threat detection do not remove the public listener, so only the private-endpoint approach meets the stated condition.

Exam trap

The trap here is assuming that tightening firewall rules or enabling threat detection removes public exposure, when only disabling public network access and using a private endpoint actually eliminates the public listener.

Practice this question →

115

MCQeasy

Refer to the exhibit. You are configuring Azure SQL Database Transparent Data Encryption (TDE) with customer-managed keys (CMK) stored in Azure Key Vault. The deployment uses a user-assigned managed identity. However, after deployment, the TDE status shows 'Inaccessible'. What is the most likely cause?

A.The key specified in the URI does not exist

B.The user-assigned managed identity is not assigned to the SQL Database server

C.The Key Vault firewall is enabled and does not allow Azure services

D.The managed identity lacks 'Get', 'Wrap Key', and 'Unwrap Key' permissions on the Key Vault key

AnswerD

TDE with customer-managed keys requires the user-assigned managed identity to hold Get, Wrap Key and Unwrap Key permissions on the Key Vault key. Without them, Azure SQL Database cannot unwrap the protector, so TDE reports Inaccessible.

Why this answer

When using customer-managed keys (CMK) for TDE in Azure SQL Database, the managed identity assigned to the logical server must have 'Get', 'Wrap Key', and 'Unwrap Key' permissions on the key in Azure Key Vault. Without these specific permissions, the SQL Database service cannot retrieve or use the key to encrypt or decrypt the database encryption key, resulting in an 'Inaccessible' TDE status.

Exam trap

The trap here is that candidates often assume the issue is with the Key Vault firewall or the identity assignment, but the most common post-deployment cause of 'Inaccessible' TDE is missing cryptographic permissions on the managed identity, not network or identity existence issues.

How to eliminate wrong answers

Option A is wrong because if the key specified in the URI did not exist, the deployment would typically fail during configuration, not result in an 'Inaccessible' status after deployment. Option B is wrong because the user-assigned managed identity must be assigned to the logical SQL server, not the SQL Database server (which is a common misconception); the identity is assigned at the server level, and if it were missing, the deployment would likely fail earlier. Option C is wrong because the Key Vault firewall, when enabled, can block access even if 'Allow trusted Microsoft services' is not configured, but the most common and direct cause of 'Inaccessible' status after a successful deployment is missing key permissions on the managed identity.

Practice this question →

116

MCQmedium

You are configuring Azure SQL Database firewall rules for a new application. The application runs on Azure VMs in the same region. To minimize latency and security risk, which approach should you use?

A.Add a firewall rule allowing all Azure IP addresses.

B.Configure a virtual network service endpoint and a virtual network firewall rule.

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

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

AnswerB

A virtual network service endpoint routes traffic to Azure SQL over the Microsoft backbone, keeping it off the public internet. The virtual network firewall rule then permits only that subnet, minimising both latency and exposure for same-region VMs.

Why this answer

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

Exam trap

The trap here is that candidates often confuse 'allowing Azure services' (a broad, insecure setting) with the more secure virtual network service endpoint approach, or they mistakenly think adding individual VM public IPs is sufficient for security and latency.

How to eliminate wrong answers

Option A is wrong because allowing all Azure IP addresses opens the database to any Azure service in any region, vastly increasing the attack surface and violating the principle of least privilege. Option C is wrong because assigning a firewall rule for each VM's public IP address is impractical for dynamic IPs, does not leverage Azure's private network, and still exposes the database to internet-based traffic. Option D is wrong because 'allowing all Azure services' is a legacy setting that permits traffic from any Azure service (e.g., Azure Functions, Logic Apps) without subnet-level control, creating unnecessary exposure.

Practice this question →

117

MCQeasy

You need to ensure that only specific Azure services can access your Azure SQL Database server. You want to allow traffic from Azure services but block all other traffic. What should you configure?

A.Set the firewall rule 'Allow Azure Services and resources to access this server' to ON and remove all other IP rules.

B.Set the firewall rule 'Allow Azure Services and resources to access this server' to OFF and add a rule for 0.0.0.0.

C.Set firewall rules to deny all IP addresses.

D.Set the firewall rule 'Allow Azure Services and resources to access this server' to ON and add a rule for 0.0.0.0.

AnswerA

Enabling that server-level firewall rule permits connections originating from Azure datacentre IP ranges, while removing all other IP rules blocks every other source. This satisfies the requirement to allow Azure services only, though it does not restrict which Azure services connect.

Why this answer

Setting the 'Allow Azure Services and resources to access this server' firewall rule to ON enables a special rule that permits traffic from all Azure datacenter IP ranges, while removing all other IP rules ensures no other external traffic can reach the server. This configuration meets the requirement to allow only Azure services and block all other traffic, as the Azure services rule is a blanket allow for Azure-originated connections without needing specific IP addresses.

Exam trap

The trap here is confusing the 'Allow Azure Services' rule with a generic 0.0.0.0 rule, leading candidates to think they need to add 0.0.0.0 to allow Azure traffic, when in fact the Azure services rule is a distinct mechanism that does not require explicit IP entries.

How to eliminate wrong answers

Option B is wrong because setting the rule to OFF and adding a rule for 0.0.0.0 does not allow Azure services; the 0.0.0.0 rule is typically used to allow all IPs, which contradicts the requirement to block non-Azure traffic. Option C is wrong because denying all IP addresses would block all traffic, including Azure services, failing to meet the requirement to allow Azure services. Option D is wrong because adding a rule for 0.0.0.0 alongside the Azure services rule would allow all IP addresses (including non-Azure traffic), which violates the requirement to block all other traffic.

Practice this question →

118

Multi-Selectmedium

Your company uses Azure SQL Database and needs to comply with GDPR. You must implement data classification and protection. Which TWO actions should you take? (Choose two.)

Select 2 answers

A.Configure sensitivity labels using Microsoft Purview Information Protection.

B.Implement Always Encrypted for all columns containing personal data.

C.Install the Azure Information Protection client on all client machines.

D.Enable Microsoft Defender XDR for the database server.

E.Use SQL Data Discovery & Classification in the Azure portal to classify columns containing personal data.

AnswersA, E

Sensitivity labels can be applied to classified columns and are integrated with Microsoft Purview Information Protection.

Why this answer

Microsoft Purview Information Protection provides sensitivity labels that can be applied to columns in Azure SQL Database to classify and protect personal data, meeting GDPR requirements. These labels enforce encryption, access restrictions, and visual markings, integrating with Azure SQL's data classification capabilities.

Exam trap

The trap here is confusing data classification (labeling and identifying sensitive data) with data encryption (Always Encrypted) or threat detection (Defender XDR), leading candidates to pick security features that do not fulfill the GDPR requirement for classification and labeling.

Practice this question →

119

Multi-Selectmedium

You are designing a secure environment for Azure SQL Database. Which TWO of the following are recommended practices for network security?

Select 2 answers

A.Enable the 'Allow Azure services and resources to access this server' firewall setting.

B.Use VNet service endpoints instead of Private Link to reduce costs.

C.Use Azure Private Link to connect to the database from a virtual network.

D.Disable public network access on the SQL server.

E.Add firewall rules that allow all IP addresses from your organization's IP range.

AnswersC, D

Azure Private Link provisions a private endpoint inside the virtual network, so database traffic traverses the Microsoft backbone rather than the public internet. This removes public exposure, satisfying the network security requirement for private connectivity.

Why this answer

Option C is correct because Azure Private Link (Private Endpoint) provides a private IP address for the Azure SQL logical server inside your VNet, so traffic between the VNet and the database travels over the Microsoft backbone and never exposes the database to the public internet. Option D is correct because disabling public network access on the SQL server ensures the database accepts connections only through approved private paths (such as Private Endpoints) or explicitly permitted exceptions, eliminating the broad public endpoint attack surface. Option A is not recommended because 'Allow Azure services and resources to access this server' creates a firewall exception that permits traffic from any Azure service, which is overly permissive and not a targeted network security control.

Option B is not recommended because VNet service endpoints still route traffic to the database's public endpoint and are generally considered less secure than Private Link, so cost should not drive that choice for a secure design. Option E is not recommended because allowing an entire organization IP range is a broad, IP-based rule that is weaker than private connectivity and can be bypassed if those addresses are compromised or spoofed.

Practice this question →

120

MCQmedium

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. You need to ensure that the database is protected by Microsoft Defender for Cloud (formerly Azure Security Center) with advanced threat protection. What should you enable?

A.Deploy Microsoft Sentinel and connect the SQL Managed Instance

B.Enable Microsoft Defender for Cloud on the subscription or resource

C.Configure Microsoft Purview Data Map

D.Enable Azure SQL Database auditing

AnswerB

Enabling Microsoft Defender for Cloud at the subscription or resource level activates Defender for SQL, which provides advanced threat protection for Azure SQL Managed Instance. This satisfies the stem's requirement for database protection, since Defender for SQL is enabled through the Defender for Cloud plan rather than instance-level configuration.

Why this answer

Microsoft Defender for Cloud provides advanced threat protection for Azure SQL Managed Instance at the subscription or resource level. Enabling it on the subscription or the specific resource activates threat detection capabilities, including alerts for SQL injection, brute-force attacks, and anomalous access patterns, without requiring additional services.

Exam trap

The trap here is that candidates often confuse auditing (which logs events) with threat protection (which actively detects and alerts on suspicious activity), leading them to select auditing as the answer, or they mistakenly think Microsoft Sentinel is required to enable threat detection when it is actually an optional SIEM integration.

How to eliminate wrong answers

Option A is wrong because Microsoft Sentinel is a SIEM (Security Information and Event Management) solution that ingests security logs from various sources, including Defender for Cloud, but it does not directly enable advanced threat protection for SQL Managed Instance; it is an additional layer for centralized security monitoring, not the mechanism to enable threat protection. Option C is wrong because Microsoft Purview Data Map is a data governance and cataloging service for managing data lineage, classification, and discovery, not a security tool for threat detection or protection against database attacks. Option D is wrong because enabling Azure SQL Database auditing captures and logs database events for compliance and forensic analysis, but it does not provide real-time threat detection or advanced protection against malicious activities like SQL injection or anomalous access patterns.

Practice this question →

121

MCQeasy

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

A.Azure SQL Auditing

B.Advanced Threat Protection

C.Transparent Data Encryption (TDE)

D.SQL Vulnerability Assessment

AnswerA

Azure SQL Auditing captures both successful and failed authentication attempts, plus the originating IP and user, writing them to a storage account, Log Analytics workspace, or Event Hubs. This directly satisfies the requirement to audit all login attempts.

Why this answer

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

Exam trap

The trap here is that candidates often confuse Advanced Threat Protection's alerting on suspicious logins with the comprehensive logging of all login attempts provided by Azure SQL Auditing, leading them to select ATP instead.

How to eliminate wrong answers

Option B (Advanced Threat Protection) is wrong because it detects anomalous activities indicating potential threats (e.g., SQL injection, brute force attacks) but does not provide a configurable audit log of all successful and failed login attempts; it alerts on suspicious patterns rather than recording every login event. Option C (Transparent Data Encryption) is wrong because it encrypts the database at rest and in transit but has no capability to log authentication events; it protects data confidentiality, not audit trails. Option D (SQL Vulnerability Assessment) is wrong because it scans for security misconfigurations and vulnerabilities (e.g., missing firewall rules, weak passwords) but does not capture or store login attempt logs; it is a periodic assessment tool, not an ongoing audit mechanism.

Practice this question →

122

Multi-Selectmedium

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

Select 2 answers

A.Enable SQL Server authentication only.

B.Set an Microsoft Entra ID admin for the Azure SQL Server.

C.Create contained database users mapped to Microsoft Entra ID identities.

D.Assign the SQL Server Contributor role to the Entra ID users.

E.Enable Azure AD integration on the SQL server.

AnswersB, C

Setting a Microsoft Entra ID admin at the server level is mandatory before Entra authentication can function, because Azure SQL Database derives its identity provider configuration from the logical server. Without a designated admin, the server cannot validate Entra tokens, so this action directly satisfies the prerequisite for enabling Entra authentication.

Why this answer

Option B is correct because enabling Microsoft Entra ID authentication for Azure SQL Database requires provisioning a Microsoft Entra ID administrator at the Azure SQL logical server level, which establishes the trust relationship between the server and the Entra ID tenant. Option C is correct because after the Entra ID admin is set, you must create contained database users in the target database that are mapped to Entra ID identities (for example, CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER), since Entra principals authenticate at the database level via contained users rather than server-level logins. Option A is incorrect because enabling SQL Server authentication only is the opposite of what is needed and does not enable Entra ID authentication.

Option D is incorrect because assigning the SQL Server Contributor RBAC role grants Azure management-plane permissions, not data-plane authentication rights inside the database. Option E is incorrect because there is no separate 'Azure AD integration' toggle to enable on the SQL server; the Entra ID admin setting itself establishes the integration.

Exam trap

The trap is thinking that enabling Entra ID authentication is a single toggle or that assigning an RBAC role like SQL Server Contributor is sufficient; candidates often miss that contained database users must be created for non-admin Entra ID principals.

Practice this question →

123

MCQhard

You are designing a secure environment for Azure SQL Managed Instance. The company requires that all database backups be encrypted using customer-managed keys stored in Azure Key Vault. Which combination of actions should you take?

A.Configure Always Encrypted with keys stored in Key Vault.

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

C.Use Azure Storage Service Encryption to encrypt the backup files.

D.Enable backup encryption using a certificate stored in the managed instance.

AnswerB

TDE with a customer-managed key in Azure Key Vault encrypts data at rest, including automated backups, using keys the customer controls. This directly satisfies the requirement that all database backups be encrypted with customer-managed keys stored in Key Vault.

Why this answer

Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault allows you to encrypt the database backup files using a key that you control. When TDE is enabled and configured with a customer-managed key (CMK) stored in Azure Key Vault, Azure SQL Managed Instance automatically encrypts backups with the same TDE protector key, meeting the requirement for customer-managed backup encryption.

Exam trap

The trap here is that candidates often confuse Always Encrypted (which protects specific columns) with TDE (which encrypts the entire database and its backups), or they assume that Azure Storage Service Encryption (SSE) can be used to meet customer-managed key requirements, when in fact SSE uses platform-managed keys by default and does not apply to backup files in the same way as TDE with CMK.

How to eliminate wrong answers

Option A is wrong because Always Encrypted is a client-side encryption technology that protects sensitive data in transit and at rest within the database, but it does not encrypt the entire database backup files; backup encryption is handled separately by TDE. Option C is wrong because Azure Storage Service Encryption (SSE) encrypts data at rest in Azure Blob Storage using platform-managed keys, not customer-managed keys, and it applies to the storage layer, not to the backup files themselves in a way that satisfies the requirement for customer-managed key control. Option D is wrong because backup encryption using a certificate stored in the managed instance would use a service-managed certificate, not a customer-managed key from Azure Key Vault, and this approach is deprecated in favor of TDE with CMK.

Practice this question →

124

Multi-Selecthard

You are configuring security for an Azure SQL Managed Instance. The instance will host a critical application that requires always encrypted with secure enclaves. Which TWO actions must you take to support this feature? (Choose two.)

Select 2 answers

A.Select the Intel Software Guard Extensions (Intel SGX) enclave type.

B.Configure the column master key to be stored in Azure Key Vault.

C.Configure a column master key that is enclave-enabled.

D.Enable the enclave attestation policy on the managed instance.

E.Enable Virtualization-Based Security (VBS) enclave type.

AnswersA, C

Intel SGX is the required enclave type for Always Encrypted with secure enclaves on SQL Managed Instance.

Why this answer

Always Encrypted with secure enclaves on Azure SQL Managed Instance requires the Intel Software Guard Extensions (Intel SGX) enclave type. Intel SGX is the only supported enclave technology for this feature on managed instances, providing a trusted execution environment that protects sensitive data in memory during cryptographic operations.

Exam trap

The trap here is that candidates often confuse the requirement for an enclave-enabled column master key (option C) with the need to store the key in Azure Key Vault (option B), but the key location is not a prerequisite for enclave support.

Practice this question →

125

MCQmedium

You are configuring security for an Azure SQL Database. The security policy requires that all connections to the database must be encrypted and that the encryption keys must be managed by your organization. You need to implement Transparent Data Encryption (TDE) with a customer-managed key (CMK) stored in Azure Key Vault. What should you do first?

A.Create an Azure Key Vault and grant the Azure SQL logical server's managed identity the get, wrapKey, and unwrapKey permissions on the key.

B.Configure a firewall rule to allow connections from your organization's IP addresses.

C.Create a database master key (DMK) in the master database of the Azure SQL logical server.

D.Enable TDE on the database using the default service-managed key.

AnswerA

To use a customer-managed key for TDE, you must first create an Azure Key Vault, generate or import a key, and then grant the Azure SQL logical server's managed identity the necessary permissions (get, wrapKey, unwrapKey) to access that key. This allows the server to use the key for TDE operations. This is the foundational step before configuring TDE to use the key.

Why this answer

For TDE with customer-managed keys in Azure SQL Database, the first step is to set up Azure Key Vault and grant the logical server's managed identity the required permissions to access the key. This enables the server to use the key for encryption. Once this is done, you can configure TDE to use the customer-managed key.

The other options either do not meet the key management requirement or are not applicable.

Exam trap

The trap here is thinking that you must first enable TDE with a service-managed key before switching to a customer-managed key, or that on-premises concepts like database master keys apply directly to Azure SQL Database TDE.

Practice this question →

126

MCQhard

You administer an Azure SQL Database that contains a table named dbo.Employees with columns for Social Security Number and salary. Company policy requires that support staff querying the table see only the last four digits of the Social Security Number and a masked salary value, while the payroll application, which connects with a different login, must see the actual values. You need to implement this with the least administrative effort and without changing the application queries. What should you do?

A.Apply Dynamic Data Masking rules to the SSN and salary columns and grant the support staff login SELECT on the table.

B.Encrypt the SSN and salary columns with Always Encrypted and distribute the column master key only to the payroll application.

C.Create a view that returns masked values and grant support staff SELECT on the view instead of the table.

D.Create a row-level security policy that filters rows based on the support staff login.

AnswerA

Dynamic Data Masking applies masking at query time for users without the UNMASK permission, while privileged logins retain full visibility. Because masking is transparent to the query text and enforced in the engine, support staff see masked values and the payroll login sees real values without any application changes.

Why this answer

Dynamic Data Masking is designed for exactly this scenario: it masks column values for users who lack the UNMASK permission while leaving the data intact and visible to privileged logins. Because masking is applied by the engine during query execution, no application changes are needed, and the payroll login with UNMASK sees the real values.

Exam trap

The trap here is choosing Always Encrypted or row-level security to limit visibility, when only Dynamic Data Masking provides partial value masking without changing queries.

Practice this question →

127

MCQeasy

You need to audit all schema changes in an Azure SQL Database and store the audit logs in a storage account for long-term retention. What should you enable?

A.Azure SQL Auditing with storage account destination.

B.Advanced Threat Protection with email alerts.

C.Query Store with 'Data Flush Interval' set to 1 minute.

D.SQL Vulnerability Assessment with recurring scans.

AnswerA

Azure SQL Auditing captures schema changes such as CREATE, ALTER and DROP through the database audit specification, and the storage account destination provides the long-term retention the scenario requires. This satisfies both the auditing and retention constraints.

Why this answer

Azure SQL Auditing with a storage account destination is the correct choice because it tracks database events, including schema changes (DDL operations), and writes audit logs to Azure Blob Storage for long-term retention. This meets the requirement to audit all schema changes and store logs durably, as storage accounts provide configurable retention policies.

Exam trap

The trap here is that candidates confuse Azure SQL Auditing with other security features like Advanced Threat Protection or Vulnerability Assessment, assuming they all capture schema changes, but only Auditing provides granular event logging with a storage destination for long-term retention.

How to eliminate wrong answers

Option B is wrong because Advanced Threat Protection (ATP) detects anomalous activities (e.g., SQL injection, brute-force attacks) and sends email alerts, but it does not log schema changes or provide long-term audit storage. Option C is wrong because Query Store captures query performance data (execution plans, runtime statistics) with a configurable data flush interval, not schema change events or audit logs. Option D is wrong because SQL Vulnerability Assessment performs periodic scans to identify security misconfigurations and vulnerabilities, but it does not audit schema changes or store logs in a storage account.

Practice this question →

128

MCQeasy

You have an Azure SQL Database named SalesDB. You need to grant a user named 'ReportingUser' the ability to read all data in the Sales schema but not modify any data. You want to follow the principle of least privilege. What should you do?

A.Grant SELECT permission on the Sales schema to ReportingUser.

B.Add ReportingUser to the db_datareader role.

C.Grant CONTROL permission on the Sales schema to ReportingUser.

D.Add ReportingUser to the db_owner role.

AnswerA

Granting SELECT on the Sales schema specifically allows ReportingUser to read all data in that schema while denying access to other schemas. This follows the principle of least privilege by limiting permissions to only what is needed. It is the most precise way to meet the requirement.

Why this answer

Granting SELECT on the Sales schema directly to ReportingUser provides read-only access to only that schema, adhering to least privilege. The other options grant broader permissions that either include unnecessary access to other schemas or allow data modification, which is not required.

Exam trap

The trap here is defaulting to built-in roles like db_datareader, which grant access to the entire database rather than a specific schema.

Practice this question →

129

MCQmedium

You are a database administrator for a multinational corporation that uses Azure SQL Managed Instance to host multiple databases for different business units. The security policy requires that all connections to the managed instance must use encrypted connections (TLS 1.2 or higher). Additionally, the company wants to minimize the attack surface by restricting network access. You need to configure the managed instance to enforce encrypted connections and block all public internet traffic. What should you do?

A.Set the 'Minimal TLS Version' property to 1.2 and set 'Public data endpoint' to 'Disabled'

B.Enable a private endpoint and set the 'Minimal TLS Version' property to 1.0

C.Disable the public endpoint and enable a service endpoint for the virtual network

D.Configure a server-level firewall rule to allow only specific IP addresses and set the 'Minimal TLS Version' property to 1.2

AnswerA

Setting Minimal TLS Version to 1.2 enforces TLS 1.2 or higher on every connection, satisfying the encryption policy. Disabling the public data endpoint removes the public internet-facing endpoint, so only private endpoints or internal VNet connections reach the managed instance, directly meeting the attack-surface restriction.

Why this answer

Setting the 'Minimal TLS Version' property to 1.2 enforces that all connections use TLS 1.2 or higher, meeting the encryption requirement. Disabling the 'Public data endpoint' blocks all public internet traffic, ensuring that only traffic from within the virtual network can reach the managed instance. This combination directly satisfies both security policy goals without relying on additional components like private endpoints or firewall rules.

Exam trap

The trap here is that candidates often confuse disabling the public endpoint with using a private endpoint or firewall rules, failing to realize that both the TLS version enforcement and public endpoint disablement are required to fully meet the security policy.

How to eliminate wrong answers

Option B is wrong because setting 'Minimal TLS Version' to 1.0 allows connections using TLS 1.0, which is not compliant with the requirement for TLS 1.2 or higher, and enabling a private endpoint alone does not block public internet traffic unless the public endpoint is also disabled. Option C is wrong because disabling the public endpoint and enabling a service endpoint does not enforce TLS 1.2; service endpoints only secure traffic to Azure services within the virtual network but do not control the TLS version used. Option D is wrong because configuring a server-level firewall rule to allow only specific IP addresses still leaves the public endpoint enabled, which exposes the managed instance to the internet and does not minimize the attack surface as required.

Practice this question →

130

MCQhard

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

A.Assign the SQL Security Manager role.

B.Assign the Reader role.

C.Assign the Contributor role.

D.Assign the SQL DB Contributor role.

AnswerA

Incorrect because the SQL Security Manager role allows updating security policies, which is more than read-only access and violates the requirement to not modify security settings.

Why this answer

The SQL Security Manager role is the minimum built-in Azure RBAC role that grants read access to SQL security-related logs, including failed login audit logs, at the logical server scope. While it can also manage security policies, it is the least-privileged built-in role that satisfies the requirement to view the failed login security logs. The Reader role only provides read access to Azure resource metadata and does not expose SQL security logs.

Exam trap

Candidates may assume the generic Reader role is enough for any read-only task, but SQL security logs require the SQL Security Manager role, which is the minimum role that grants visibility into those logs.

How to eliminate wrong answers

Option B is wrong because the Reader role provides read-only access to all resources but does not include the specific permissions to view security logs like failed logins, which require the SQL Security Manager role. Option C is wrong because the Contributor role grants full management access to all resources, including the ability to modify security settings, which exceeds the requirement of view-only access. Option D is wrong because the SQL DB Contributor role allows management of databases but not the logical server's security logs, and it also includes permissions to modify database configurations, which is more than needed.

Practice this question →

131

MCQhard

You have an Azure SQL Database that needs to be accessed by an application running on an Azure VM. The VM is in a different subscription. You want to minimize administrative overhead and ensure secure connectivity without exposing the database to the public internet. What should you do?

A.Set up a site-to-site VPN between the VM's VNet and the SQL Database's VNet.

B.Use a VNet service endpoint for Azure SQL Database in the VM's VNet.

C.Create a private endpoint for the SQL Database in the VM's VNet.

D.Configure a firewall rule to allow the VM's public IP address.

AnswerC

A private endpoint provisions an Azure NIC with a private IP address from the VM's VNet to the SQL Database logical server, making the database appear as a native resource inside that VNet. Traffic between the VM and the database flows entirely over the Microsoft backbone and never traverses the public internet, even though the SQL Database can reside in a different subscription via Private Link. The private endpoint is deployed in the VM's VNet while the connection to the SQL Database resource is approved, enabling cross-subscription private connectivity with no public exposure.

Why this answer

A private endpoint assigns the Azure SQL Database a private IP address from the VM's VNet, enabling secure connectivity over the Microsoft backbone without exposing the database to the public internet. This minimizes administrative overhead as it does not require VPN gateways or complex routing, and it works across subscriptions by linking the private endpoint to the VM's VNet.

Exam trap

The trap here is that candidates often confuse VNet service endpoints with private endpoints, assuming service endpoints provide the same level of isolation, but service endpoints still rely on the public endpoint of Azure SQL and do not remove public exposure.

How to eliminate wrong answers

Option A is wrong because a site-to-site VPN requires a VPN gateway in both VNets, which adds significant administrative overhead and cost, and is unnecessary when a simpler private endpoint can provide cross-subscription connectivity. Option B is wrong because a VNet service endpoint does not assign a private IP to the SQL Database; it still routes traffic over the public endpoint of Azure SQL, and the database's firewall must allow the VM's VNet, which does not provide the same level of isolation as a private endpoint. Option D is wrong because exposing the VM's public IP address in a firewall rule directly exposes the database to the public internet, violating the requirement for secure connectivity without public exposure.

Practice this question →

132

MCQeasy

You are a database administrator for a retail company that uses Azure SQL Database. The security team wants to prevent SQL injection attacks by ensuring that all application queries use parameterized statements. Which built-in Azure feature should you enable to help detect and alert on potential SQL injection attempts?

A.Enable auditing on the database

B.Enable data discovery and classification

C.Enable Microsoft Defender for SQL

D.Enable SQL vulnerability assessment

AnswerC

Microsoft Defender for SQL detects anomalous query patterns and injection attempts, raising alerts in Microsoft Defender for Cloud. It satisfies the requirement to detect and alert on potential SQL injection, rather than merely preventing it through parameterisation, which is an application-side coding practise outside Azure SQL Database's built-in controls.

Why this answer

Microsoft Defender for SQL includes advanced threat detection capabilities that continuously monitor database activity for anomalous patterns, including SQL injection attempts. When enabled, it analyzes query execution patterns and can alert on suspicious queries that deviate from parameterized statement usage, directly addressing the security team's requirement to detect and alert on potential SQL injection attacks.

Exam trap

The trap here is that candidates confuse passive auditing or assessment features (which log or scan for vulnerabilities) with active threat detection that monitors and alerts on real-time attack patterns like SQL injection.

How to eliminate wrong answers

Option A is wrong because auditing records database events for compliance and forensic analysis but does not actively detect or alert on SQL injection patterns in real time. Option B is wrong because data discovery and classification identifies sensitive columns and recommends classification labels, but it has no mechanism to analyze query patterns or detect injection attempts. Option D is wrong because SQL vulnerability assessment scans for misconfigurations and missing patches, not for active injection attempts or anomalous query behavior.

Practice this question →

133

MCQeasy

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

A.Certificate-based authentication

B.Azure Active Directory authentication

C.SQL authentication with strong passwords

D.Windows authentication

AnswerB

Microsoft Entra ID authentication centralises identity in a directory that enforces multi-factor authentication, conditional access and passwordless methods, satisfying the stem's demand for the strongest security. Unlike SQL logins, credentials are not stored in the database, and tokens are issued per user, enabling auditing and revocation.

Why this answer

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

Exam trap

The trap here is that candidates often assume Windows authentication (Option D) is available in Azure SQL Database because of their on-premises experience, but Azure SQL Database does not support Windows authentication—only Azure AD authentication provides integrated identity management and MFA.

How to eliminate wrong answers

Option A is wrong because certificate-based authentication is not a native authentication method for Azure SQL Database; it can be used only as part of Azure AD authentication or for specific scenarios like service principals, not as a standalone method. Option C is wrong because SQL authentication with strong passwords still relies on a static credential stored in the database, making it vulnerable to brute-force attacks and lacking MFA support. Option D is wrong because Windows authentication is not supported for Azure SQL Database; it is only available for on-premises SQL Server or Azure SQL Managed Instance when integrated with Active Directory.

Practice this question →

134

MCQhard

Your company is migrating on-premises SQL Server databases to Azure SQL Managed Instance. You need to ensure that database backups are encrypted at rest using customer-managed keys stored in Azure Key Vault. You also need to allow the backup service to access the keys. What should you configure?

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

B.Configure Azure Backup for SQL Server in Azure VM and use Backup Center to manage encryption.

C.Enable Transparent Data Encryption (TDE) with customer-managed keys and grant the managed instance's system-assigned managed identity 'get', 'wrapKey', and 'unwrapKey' permissions on the key vault.

D.Configure server-level firewall rules to allow Azure services to access the server.

AnswerC

TDE with customer-managed keys encrypts backups at rest under your Key Vault key, and the system-assigned managed identity must hold get, wrapKey, and unwrapKey so the instance can unwrap the key for backup encryption and decryption operations.

Why this answer

Transparent Data Encryption (TDE) with customer-managed keys (CMK) in Azure SQL Managed Instance encrypts database backups at rest. To allow the Azure backup service to access the key for backup encryption, the managed instance's system-assigned managed identity must be granted 'get', 'wrapKey', and 'unwrapKey' permissions on the Azure Key Vault where the CMK is stored. This ensures that backups are encrypted using the customer-controlled key, meeting the requirement for encryption at rest with customer-managed keys.

Exam trap

The trap here is that candidates confuse Always Encrypted (which protects column data) with TDE (which protects the entire database and backups), leading them to select Option A instead of the correct TDE-based solution.

How to eliminate wrong answers

Option A is wrong because Always Encrypted protects column data in transit and at rest on the client side, not database backups; it does not encrypt backups or involve the backup service. Option B is wrong because Azure Backup for SQL Server in Azure VM is for SQL Server on Azure VMs, not Azure SQL Managed Instance, and Backup Center is a management interface, not a mechanism to encrypt backups with customer-managed keys. Option D is wrong because server-level firewall rules control network access, not encryption of backups; they do not address key management or backup encryption requirements.

Practice this question →

135

MCQeasy

You manage an Azure SQL Database named InventoryDB. The security team requires that all data in the database be encrypted at rest using a key that your organization controls and can revoke. You need to implement this requirement with minimal administrative overhead. What should you do?

A.Configure Always Encrypted with column encryption keys stored in Azure Key Vault.

B.Implement dynamic data masking on all sensitive columns.

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

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

AnswerC

TDE with a customer-managed key in Azure Key Vault allows your organization to control and revoke the encryption key. This satisfies the requirement for data encrypted at rest with organizational control. It is the standard method for achieving customer-managed encryption in Azure SQL Database, and it integrates with Key Vault for key management and revocation.

Why this answer

Transparent Data Encryption with a customer-managed key in Azure Key Vault provides encryption at rest while giving the organization control over the key, including the ability to revoke it. This meets the requirement for organizational control and revocation. Service-managed keys do not offer that control, and Always Encrypted and dynamic data masking address different security concerns.

TDE with customer-managed keys is the appropriate solution for encrypting all data at rest with minimal administrative overhead.

Exam trap

The trap here is confusing encryption at rest with column-level encryption or masking, or assuming that service-managed keys provide organizational control.

Practice this question →

136

MCQeasy

You are the administrator for an Azure SQL Database. The security team requires that all authentication to the database use Microsoft Entra ID (formerly Azure AD) and that multi-factor authentication (MFA) be enforced. You need to configure the database to meet this requirement. What should you do first?

A.Enable Microsoft Entra ID authentication in the Azure SQL Database firewall settings.

B.Configure a Conditional Access policy to require MFA for all users.

C.Set a Microsoft Entra ID admin for the Azure SQL logical server.

D.Create a contained database user for each Microsoft Entra ID user.

AnswerC

To enable Microsoft Entra ID authentication for Azure SQL Database, you must first assign a Microsoft Entra ID admin at the server level. This admin can then create contained database users mapped to Microsoft Entra identities. Once set, clients can authenticate using Microsoft Entra ID, and MFA can be enforced through Conditional Access policies. This is the foundational step for Entra ID authentication.

Why this answer

The first step to enable Microsoft Entra ID authentication for Azure SQL Database is to set a Microsoft Entra ID admin on the logical server. This admin can then manage Entra ID identities and create contained database users. MFA enforcement is achieved through Conditional Access policies in Microsoft Entra ID, but those policies only apply once Entra ID authentication is enabled.

Therefore, setting the Entra ID admin is the prerequisite.

Exam trap

The trap here is jumping to Conditional Access policies or contained users without first establishing the Microsoft Entra ID admin, which is the prerequisite for Entra ID authentication.

Practice this question →

137

MCQeasy

Your organization requires that all changes to sensitive data in an Azure SQL Database be logged for compliance. You need to capture who changed what data and when, and store the logs in a Log Analytics workspace for analysis. What should you configure?

A.Enable change tracking on the database.

B.Enable Microsoft Defender for Cloud on the server.

C.Configure server-level auditing to send logs to a Log Analytics workspace.

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

AnswerC

Server-level auditing in Azure SQL Database captures the identity, statement and timestamp of every data modification and can stream audit records directly to a Log Analytics workspace, satisfying the requirement to record who changed what and when for compliance analysis.

Why this answer

Server-level auditing in Azure SQL Database can be configured to send audit logs directly to a Log Analytics workspace, capturing detailed information about data changes including who made the change, what was changed, and when. This meets the compliance requirement for logging sensitive data changes and enables analysis using Log Analytics queries.

Exam trap

The trap here is that candidates confuse change tracking (which only detects row changes) with auditing (which captures who, what, and when), or they think security tools like Defender for Cloud provide granular data change logging.

How to eliminate wrong answers

Option A is wrong because change tracking only identifies which rows changed and the fact of a change, but does not capture who made the change or the old/new values, and it does not send logs to Log Analytics. Option B is wrong because Microsoft Defender for Cloud provides security alerts and vulnerability assessments, not granular data change auditing with user identity and timestamp logging. Option D is wrong because Transparent Data Encryption (TDE) with customer-managed keys encrypts data at rest but does not log data changes or provide audit trails.

Practice this question →

138

MCQhard

You are reviewing an Azure RBAC role assignment for an Azure SQL Database. The role assignment shown in the exhibit is intended to allow a user to read data from the database. However, the user reports they cannot connect to the database. What is the most likely reason?

A.The RBAC role does not grant data plane access; the user must be mapped to a database user and granted database-level permissions.

B.The principal is incorrectly specified; it should be a security group.

C.The scope is too broad; it should be at the server level.

D.The action 'Microsoft.Sql/servers/databases/read' is not valid; it should be 'Microsoft.Sql/servers/databases/dataReader'.

AnswerA

Azure RBAC roles such as Reader apply to the control plane, governing resource management rather than T-SQL connectivity. Data plane access requires a contained database user mapped to a Microsoft Entra ID login or an external provider, plus database-level permissions such as db_datareader.

Why this answer

Azure RBAC roles control management plane operations (e.g., creating or deleting resources) but do not grant access to the data plane (e.g., reading or writing data in a database). To read data from an Azure SQL Database, the user must be mapped to a database user (via a contained database user or an Azure AD user) and granted database-level permissions such as db_datareader. The RBAC role assignment shown only provides the 'Microsoft.Sql/servers/databases/read' action, which allows reading database metadata (like tags or properties) but not connecting to the database or querying tables.

Exam trap

The trap here is that candidates confuse Azure RBAC roles (management plane) with SQL database-level permissions (data plane), assuming that a role with 'read' in the name allows reading data from tables.

How to eliminate wrong answers

Option B is wrong because the principal type (user, group, or service principal) does not affect data plane access; the core issue is that RBAC does not grant data plane permissions at all. Option C is wrong because expanding the scope to the server level still only grants management plane actions (e.g., listing databases) and does not enable database connectivity or data reading. Option D is wrong because 'Microsoft.Sql/servers/databases/dataReader' is not a valid RBAC action; RBAC actions are management plane operations, and data reader access is granted via SQL-level permissions (e.g., db_datareader role) or Azure AD authentication with contained database users.

Practice this question →

139

Multi-Selectmedium

Your organization uses Azure SQL Managed Instance and needs to implement a defense-in-depth strategy. Which THREE security controls should you implement? (Choose three.)

Select 3 answers

A.Enable advanced threat protection using Microsoft Defender for Cloud.

B.Implement server-level auditing to capture database events.

C.Create columnstore indexes on large tables to improve query performance.

D.Configure network security groups (NSGs) on the subnet to restrict inbound traffic to the managed instance.

E.Create application roles in each database to manage permissions.

AnswersA, B, D

Microsoft Defender for Cloud's advanced threat protection detects anomalous access patterns and brute-force attempts against Azure SQL Managed Instance, raising alerts for suspicious logins and potential SQL injection. This satisfies the defence-in-depth requirement by adding a detective control layer beyond authentication and network isolation, enabling rapid response to compromised credentials or exploitation attempts.

Why this answer

Option A is correct because enabling advanced threat protection via Microsoft Defender for Cloud provides detection and alerting for anomalous activities and potential vulnerabilities on Azure SQL Managed Instance, which is a core detective control in a defense-in-depth strategy. Option B is correct because server-level auditing captures database events and writes them to an audit log, providing the monitoring and traceability required for a layered security approach. Option D is correct because configuring network security groups (NSGs) on the subnet restricts inbound traffic to the managed instance, enforcing network-level segmentation and reducing the attack surface, which is a fundamental preventive control.

Option C is not a security control; columnstore indexes are a performance optimization for analytical workloads and do not contribute to defense-in-depth. Option E is not the best fit because application roles manage permissions within a database but do not address the broader layered security controls expected in a defense-in-depth strategy for Azure SQL Managed Instance.

Exam trap

The trap here is that candidates often confuse performance tuning features (like columnstore indexes) or routine permission management (like application roles) with distinct security controls, failing to recognize that defense-in-depth requires separate, layered protections across network, monitoring, and auditing domains.

Practice this question →

140

MCQmedium

You are the database administrator for a company that uses Azure SQL Database. The company has a policy that database administrators must not have access to sensitive data in a specific table named EmployeeSalaries. You need to implement a solution that allows DBAs to manage the database but prevents them from viewing or modifying data in the EmployeeSalaries table. What should you implement?

A.Dynamic Data Masking (DDM) on the sensitive columns.

B.Always Encrypted with column encryption keys stored in Azure Key Vault, and restrict DBA access to the keys.

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

D.Row-Level Security (RLS) with a filter predicate that excludes DBAs.

AnswerB

Always Encrypted ensures that data is encrypted at the client and never revealed to the database engine. By storing the column encryption keys in Azure Key Vault and not granting DBAs access to the keys, DBAs cannot decrypt the data even if they have full database permissions. This satisfies the requirement to prevent DBAs from viewing or modifying sensitive data.

Why this answer

Always Encrypted is designed to protect sensitive data from high-privileged users like DBAs by ensuring that encryption and decryption occur on the client side. The database engine never sees the plaintext data or the encryption keys. By storing the column encryption keys in Azure Key Vault and not granting DBAs access to the keys, DBAs cannot view or modify the data, even with sysadmin privileges.

This meets the policy requirement.

Exam trap

The trap here is assuming that DDM or RLS can restrict DBAs, but these features are bypassed by users with elevated permissions like sysadmin or db_owner.

Practice this question →

141

MCQeasy

Your organization uses Azure SQL Database and wants to restrict access to only specific on-premises IP addresses. The database has a public endpoint. Which security feature should you configure?

A.Enable 'Allow Azure services and resources to access this server' in the firewall settings.

B.Enable Always Encrypted with secure enclaves.

C.Set firewall rules to allow specific on-premises IP ranges.

D.Create a virtual network service endpoint for SQL.

E.Configure a private endpoint for the database.

AnswerC

Server-level and database-level firewall rules filter inbound connections by source IP address at the Azure SQL gateway, permitting only the listed on-premises ranges while blocking all other public traffic. This directly satisfies the requirement to restrict the public endpoint to specific on-premises addresses.

Why this answer

To restrict access to specific on-premises IP addresses, you should configure firewall rules to allow those IP ranges. Setting a firewall rule ensures that only traffic from allowed IP addresses can reach the database. Option C directly addresses this requirement.

Exam trap

Candidates might consider enabling 'Allow Azure services' or using virtual network endpoints, but those are for Azure service access or private network integration, not for restricting on-premises IPs.

How to eliminate wrong answers

Option B is wrong because Always Encrypted with secure enclaves is a data encryption feature that protects sensitive data at rest and in use, but it does not control network-level access or firewall rules; it addresses data confidentiality, not connectivity restrictions. Option D is wrong because creating a virtual network service endpoint for SQL allows traffic from a specific Azure virtual network to bypass the public endpoint, but it does not restrict access to only specific Azure services and on-premises IPs; it requires additional network rules and does not inherently block all other traffic. Option E is wrong because configuring a private endpoint for the database provides a private IP address within a virtual network, eliminating public endpoint exposure, but it does not allow on-premises IP access unless combined with a VPN or ExpressRoute; it also does not selectively permit specific Azure services without additional configuration.

Practice this question →

142

MCQmedium

Your company uses Azure SQL Database and needs to restrict access to a specific column containing credit card numbers. Only users with the 'CreditCardViewer' role should see the full number; others should see only the last four digits. Which feature should you implement?

A.Always Encrypted

B.Row-Level Security

C.Column-level security with GRANT

D.Dynamic Data Masking

AnswerD

Dynamic Data Masking applies masking rules at query time, so non-privileged users receive only the last four digits of the credit card column while CreditCardViewer role members see full values. This satisfies the requirement without altering stored data or duplicating the column.

Why this answer

Dynamic Data Masking (DDM) is the correct choice because it allows you to obfuscate sensitive data in query results without changing the underlying database. You can define a mask on the credit card column that shows only the last four digits to users without the 'CreditCardViewer' role, while users with that role can be granted the UNMASK permission to see the full value.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with Column-Level Security (GRANT), not realizing that GRANT cannot partially reveal data—it only provides all-or-nothing column access, whereas DDM is designed specifically for partial obfuscation based on permissions.

How to eliminate wrong answers

Option A is wrong because Always Encrypt encrypts data at the client side, preventing the database engine from seeing plaintext values, which would block the ability to selectively show the last four digits based on a database role. Option B is wrong because Row-Level Security controls access to entire rows based on a predicate function, not to individual columns or partial data within a column. Option C is wrong because column-level security with GRANT can restrict access to an entire column, but it cannot partially mask the data—it either allows full visibility or no visibility, not a masked view showing only the last four digits.

Practice this question →

143

MCQeasy

You are a database administrator for a hospital that uses Azure SQL Database to store patient records. The hospital's security policy requires that all database access be authenticated using Microsoft Entra ID (formerly Azure AD). You have already created a Microsoft Entra ID user for yourself and granted you the 'db_owner' role. You now need to create a new Microsoft Entra ID user for a nurse who needs read-only access to the database. What should you do first?

A.In the Azure portal, add the nurse as a server-level Microsoft Entra admin

B.Create a SQL login for the nurse on the logical server and then create a user in the database mapped to that login

C.Connect to the master database using SQL authentication and run 'CREATE USER [nurse@hospital.onmicrosoft.com] FROM EXTERNAL PROVIDER'

D.Connect to the database using your Microsoft Entra account and run 'CREATE USER [nurse@hospital.onmicrosoft.com] FROM EXTERNAL PROVIDER'

AnswerD

Creating a contained database user from an external provider maps the Microsoft Entra identity into the database, which must happen before any role membership or permission grant. The db_owner connection is required because CREATE USER demands ALTER ANY USER permission, which the nurse's account does not yet hold.

Why this answer

The nurse must be created as a contained database user mapped to Microsoft Entra ID. Since the hospital uses Azure SQL Database and requires Microsoft Entra authentication, you must connect to the user database (not master) using your Microsoft Entra account (which has db_owner privileges) and run 'CREATE USER [nurse@hospital.onmicrosoft.com] FROM EXTERNAL PROVIDER'. This creates a database user that authenticates via Microsoft Entra ID without requiring a server-level login, aligning with the security policy.

Exam trap

The trap here is that candidates mistakenly think they need to create a login in the master database first (as in SQL Server or Azure SQL Managed Instance), but Azure SQL Database uses contained database users for Microsoft Entra authentication, so the 'CREATE USER ... FROM EXTERNAL PROVIDER' must be run directly in the user database by a Microsoft Entra-authenticated user.

How to eliminate wrong answers

Option A is wrong because adding the nurse as a server-level Microsoft Entra admin grants full administrative privileges over the logical server, far exceeding the required read-only access and violating the principle of least privilege. Option B is wrong because Azure SQL Database does not support SQL logins for Microsoft Entra users; you cannot create a SQL login mapped to a Microsoft Entra identity, and the approach of creating a SQL login and then a database user is for SQL authentication, not Microsoft Entra authentication. Option C is wrong because connecting to the master database with SQL authentication is not possible if the policy requires Microsoft Entra authentication, and 'CREATE USER ...

FROM EXTERNAL PROVIDER' must be run in the user database, not master, and must be executed by a Microsoft Entra-authenticated principal.

Practice this question →

144

MCQmedium

You are the DBA for a company using Azure SQL Database. The security team requires that all data at rest in the database be encrypted with a customer-managed key (CMK) stored in Azure Key Vault, and that the DBA team be able to rotate the key without any downtime. You have already created an Azure Key Vault and an RSA 2048-bit key. What should you do next to meet these requirements?

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

B.Enable Transparent Data Encryption (TDE) with a service-managed key, then export the key to Azure Key Vault.

C.Configure Azure Disk Encryption on the underlying virtual hard disks of the Azure SQL Database.

D.In the Azure SQL logical server's Transparent Data Encryption settings, select Customer-managed key and choose the Key Vault key.

AnswerD

Azure SQL Database supports TDE with customer-managed keys stored in Azure Key Vault. By configuring the logical server's TDE settings to use a customer-managed key, you enable encryption at rest with your own key. Key rotation is supported by creating a new key version in Key Vault and updating the server configuration, with no downtime. This meets all requirements.

Why this answer

Configuring Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault is the correct approach. TDE encrypts the database, backups, and logs at rest. Using a customer-managed key gives the organization control over the key and allows key rotation without downtime.

The other options either do not provide customer-managed keys or are not applicable to Azure SQL Database.

Exam trap

The trap here is confusing Always Encrypted with TDE; Always Encrypted protects specific columns, not the entire database at rest.

Practice this question →

145

Multi-Selecteasy

You are configuring authentication for Azure SQL Database. Which TWO of the following are supported authentication methods?

Select 2 answers

A.Windows authentication using Kerberos.

B.Microsoft Entra ID authentication with a service principal.

C.OAuth 2.0 token authentication.

D.SQL authentication with a username and password.

E.Certificate-based authentication for SQL logins.

AnswersB, D

Microsoft Entra ID authentication supports service principals, which are non-interactive identities used by applications. This satisfies the scenario's need for a supported authentication method, since the service principal authenticates via token rather than a stored SQL password, integrating with Microsoft Entra ID directory-based identity management.

Why this answer

Option B is correct because Azure SQL Database natively integrates with Microsoft Entra ID (formerly Azure AD), and service principals (app registrations) can be granted access and authenticate via Entra ID tokens, which is a fully supported authentication method. Option D is correct because SQL authentication using a login name and password is a core, supported authentication method for Azure SQL Database (created via CREATE LOGIN or the portal). Option A is not supported because Azure SQL Database does not use Windows/Kerberos authentication; Kerberos-based Windows authentication applies to on-premises SQL Server or Azure SQL Managed Instance with AD integration, not Azure SQL Database.

Option C is not a distinct supported method because OAuth 2.0 tokens are the underlying mechanism used by Entra ID authentication, not a separately configurable authentication method for SQL logins. Option E is not supported because Azure SQL Database does not support certificate-based authentication for SQL logins; certificate authentication applies to SQL Server on-premises or Azure SQL Managed Instance.

Exam trap

The trap here is that candidates often confuse supported authentication methods for Azure SQL Database with those available for on-premises SQL Server, mistakenly selecting Windows authentication or certificate-based SQL logins, which are not supported in Azure SQL Database.

Practice this question →

146

MCQmedium

A company manages an Azure SQL Database that stores sensitive customer data. The security team mandates that all connections to the database use Azure Active Directory (Azure AD) authentication and that no SQL authentication logins exist. You are tasked with implementing this requirement. What should you do first?

A.Set the server's 'Public network access' to 'Disabled'.

B.Remove the server admin login from the master database.

C.Set an Azure Active Directory admin for the Azure SQL Database server.

D.Deny the CONNECT permission to all SQL authentication logins.

AnswerC

Setting a Microsoft Entra ID admin on the logical server is the prerequisite that enables Entra authentication; only after this can you create contained database users and remove SQL logins, satisfying the mandate that no SQL authentication exists.

Why this answer

Before you can enforce Azure AD-only authentication, you must first designate an Azure AD admin for the Azure SQL Database server. This admin is the only identity that can manage Azure AD users and permissions in the database, and once set, you can then remove or disable SQL authentication logins. Without an Azure AD admin, there is no way to authenticate or manage Azure AD principals within the database, making the transition impossible.

Exam trap

The trap here is that candidates often confuse disabling network access or removing permissions with actually changing the authentication model, but the first required step is always to establish an Azure AD admin to enable Azure AD authentication at the server level.

How to eliminate wrong answers

Option A is wrong because disabling public network access restricts network connectivity but does not affect authentication methods; SQL authentication logins would still exist and could be used if network access were re-enabled. Option B is wrong because removing the server admin login from the master database would break all administrative access before an Azure AD admin is established, potentially locking you out of the server entirely. Option D is wrong because denying CONNECT permission to SQL authentication logins does not remove the logins themselves; they remain in the database and could be re-granted permissions, and this action does not enforce Azure AD-only authentication as a policy.

Practice this question →

147

Multi-Selecthard

Which THREE of the following are best practices for managing keys in Azure Key Vault for use with Azure SQL Database TDE?

Select 3 answers

A.Enable soft-delete and purge protection on the Key Vault.

B.Rotate the keys periodically.

C.Grant the server managed identity 'get', 'wrapKey', and 'unwrapKey' permissions.

D.Store the Key Vault in the same resource group as the SQL server.

E.Disable Key Vault auditing to reduce costs.

AnswersA, B, C

Prevents accidental key loss.

Why this answer

Enabling soft-delete and purge protection on the Key Vault is a best practice because soft-delete retains deleted keys for a configurable retention period (default 90 days), allowing recovery if a key is accidentally deleted. Purge protection prevents permanent deletion of keys even after the soft-delete retention period expires, which is critical for TDE because if the key is permanently lost, the encrypted database becomes inaccessible. Together, these features ensure that the TDE protector key is never irrevocably lost, maintaining database recoverability and compliance.

Exam trap

The trap here is that candidates often think placing the Key Vault in the same resource group simplifies management, but Microsoft explicitly recommends a separate resource group to avoid accidental deletion of the vault when the SQL server is deprovisioned.

Practice this question →