Implement a secure environment practice questions

The previous key version was disabled or deleted in AKV.

TDE with AKV integration requires every key version protecting the database encryption key to remain accessible. Disabling or deleting the previous version breaks unwrap operations for data still encrypted under it, so long-running queries touching those pages fail with encryption errors.

Configure the firewall to automatically block the attacker's IP address.

The firewall can be set to block IPs after a detected attack.

The Microsoft Entra ID group has not been created as a database user.

Microsoft Entra ID group logins require a corresponding database principal; granting db_datareader to the group in Microsoft Entra ID alone leaves no database user, so authentication succeeds but authorisation fails. The group must be created as a contained database user first.

Set 'Force encryption' to 'Yes' on the server.

Setting 'Force encryption' to 'Yes' on the logical server makes Azure SQL reject any connection that does not request encryption, so all client traffic uses TLS. This enforces encryption for every connection rather than relying on client-side configuration.

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

TDE wraps the database encryption key with the asymmetric key version. After rotation, existing data remains encrypted under the earlier version, so that version must stay enabled in Azure Key Vault or the database cannot unwrap the DEK and becomes inaccessible.

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

Customer-managed TDE requires the asymmetric key encryption key (KEK) to reside in Azure Key Vault, since the database's data encryption key is wrapped by that KEK. Storing the KEK in the vault satisfies the stem's customer-managed key constraint, enabling Microsoft Entra ID-backed access control and key rotation independent of Microsoft.

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

Soft-delete and purge protection are mandatory prerequisites for customer-managed TDE keys, ensuring deleted key material remains recoverable and cannot be permanently purged during the retention window. Without both enabled, Azure SQL Database rejects the key configuration, since an unrecoverable key would render the encrypted database permanently inaccessible.

The Azure SQL Database server must have an Microsoft Entra ID identity.

A server-level managed identity in Microsoft Entra ID is mandatory: TDE with customer-managed keys requires the logical server to authenticate to Azure Key Vault, and only a Microsoft Entra identity can be granted the wrapKey, unwrapKey and get permissions on the key. Without it, the server cannot access the vault.

The firewall rule blocks connections from the attacker's IP, so no audit log entries are generated because the connection never reaches the database.

Azure SQL Auditing only records sessions that reach the database engine. The server-level firewall drops the attacker's connection at the gateway, so no audit entry is written, while Advanced Threat Protection still raises an alert from gateway-level telemetry.

Drop the 'sales_user' login using DROP LOGIN sales_user; then create a new login and update the application.

Correct. Dropping the login immediately removes the compromised credential, terminates existing connections, and prevents new connections. It allows creation of a new login and minimizes downtime.

Microsoft Entra ID authentication

Microsoft Entra ID authentication lets Azure SQL Database accept identities managed in the directory, supporting users, groups and service principals with token-based access. It is one of the two authentication methods the platform natively supports, alongside SQL authentication.

SQL authentication

SQL authentication uses database-scoped logins and passwords stored within the server, requiring no directory dependency. Azure SQL Database supports it as one of its two native authentication methods, making it valid alongside Microsoft Entra ID authentication.

Add a virtual network rule for the specific subnet of the VMs.

A virtual network rule permits connections originating from the specified subnet's private address range, letting those VMs reach the logical server. This satisfies the requirement that only applications on VMs in that specific virtual network can connect.

Set the 'Allow Azure Services and resources to access this server' setting to OFF.

Disabling that setting blocks the broad Azure services exception, which otherwise allows any Azure-hosted resource regardless of virtual network. Turning it off ensures only the explicitly permitted subnet traffic reaches the database, matching the restriction required.

Remove all existing firewall rules that allow public IP ranges.

Public IP firewall rules admit traffic from arbitrary internet addresses, contradicting the requirement to restrict access to one virtual network. Removing them leaves only the virtual network rule governing connectivity, so no external public range can reach the server.

The credential is missing the SECRET parameter.

Correct: For managed identity, SECRET must be specified, even if empty string.

Set the 'Force Encryption' property to True for the managed instance.

Force Encryption set to True makes the managed instance require encrypted connections, but server identity verification via a trusted public CA certificate is provided by the default server certificate chain, not by this setting alone. It satisfies the encryption half of the requirement.

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

A virtual network rule permits connections from a specific subnet, so services deployed there such as Azure Data Factory can reach the database while other Azure services are blocked. This satisfies the constraint of allowing only specific services rather than all Azure traffic.

The audit is configured to capture only successful operations.

Auditing captures both successful and failed operations only when both are selected; if configured for successful operations alone, DELETE statements that fail or are rolled back produce no audit record, so gaps appear despite auditing being enabled.

USER_NAME()

USER_NAME() returns the database principal name mapped from the Microsoft Entra ID identity, letting the predicate filter rows per signed-in user. This satisfies the requirement to restrict sales data by identity within the security policy.

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

TrustServerCertificate=False forces full chain validation, so the client must trust the CA that issued the Azure SQL Database gateway certificate. VMs lacking the required root CA in their trust store fail with exactly this error, despite the TLS 1.2 minimum and AppSubnet virtual network rule being correctly configured.

Configure the managed instance with the 'Force encryption' property set to Enabled.

Setting 'Force encryption' to Enabled makes the managed instance reject unencrypted TDS connections, satisfying the stem's requirement that all connections be encrypted. This is the only option addressing transport encryption; the virtual network restriction is handled separately by a subnet delegation and network security group.

Deploy the managed instance inside the desired virtual network.

Deploying the managed instance inside the target virtual network gives it a native VNet address and private endpoint, so access is restricted to that network. This satisfies the stem's isolation requirement, working alongside forced encryption.

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

The 'Allow Azure services and resources to access this server' setting creates a virtual firewall exception permitting any Azure-hosted resource, including the VM, to connect regardless of public IP rules. This satisfies the stem's scenario, explaining why the unlisted VM still connects.

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

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

Basic: Low-cost, lightweight workloads with minimal compute and memory resources.

Correct. Basic tier is indeed low-cost and lightweight.

Standard: Balanced compute and memory resources for most enterprise workloads with scalable storage.

Correct. Standard tier offers balanced compute and memory with scalable storage.

Premium: High memory-to-core ratio for memory-intensive workloads like analytics and reporting.

Correct. Premium tier is optimized for memory-intensive workloads.