Implement a secure environment

You are implementing Always Encrypted (without secure enclaves) on an Azure SQL Database. The Customers table has two sensitive columns: SSN, which must support equality lookups in WHERE clauses and be used as a join key, and MedicalNotes, which is only ever displayed and never filtered. You want the strongest encryption that still satisfies the application's query requirements for each column. Which encryption type should you assign to each column?

A healthcare application stores patient Social Security numbers and salary values in an Azure SQL Database. Compliance requires that these columns remain encrypted at all times, including in memory during query processing, and that the database engine never has access to the plaintext encryption keys. However, analysts must run range queries (for example, WHERE Salary BETWEEN 50000 AND 90000) directly against the encrypted salary column without decrypting the data on the client first. Which configuration meets all of these requirements?

You manage an Azure SQL Database that stores regulated healthcare data. A compliance auditor requires evidence that captures every successful and failed login attempt to the database, including the client IP address and time of each attempt. You have already enabled Azure SQL Auditing to a Log Analytics workspace. Which audit action group must be included in the audit specification to satisfy this requirement?

Your company hosts an Azure SQL Database that must be accessed only by application servers running inside a specific Azure virtual network. A security audit requires that no traffic to the database traverse the public internet and that the database's public endpoint be unreachable. Which combination of actions meets these requirements with the least administrative effort?

You manage an Azure SQL Database that stores regulated healthcare data. A compliance audit requires that all client connections be encrypted in transit and that clients validate the server certificate to prevent man-in-the-middle attacks. A legacy application uses a connection string that currently sets Encrypt=false. What must you configure in the application's connection string to meet the audit requirement?

A .NET application runs on an Azure App Service and connects to an Azure SQL Database. Security policy requires that no passwords or connection-string secrets be stored anywhere, and the application must authenticate to the database using its system-assigned managed identity with only the ability to read and write data in existing tables. As the DBA, which T-SQL statements should you run in the user database to enable this access following least privilege?

You administer an Azure SQL Database that stores customer records. A compliance team requires that columns holding national ID numbers and email addresses be discovered, labeled by sensitivity, and that access to these columns be visible in audit reports so that queries touching sensitive data can be reviewed. You want to accomplish this with minimal custom development. Which feature should you implement?

You manage an Azure SQL Database that must write vulnerability assessment scan results and audit logs to an Azure Storage account. Company policy forbids storing storage account keys or SAS tokens anywhere in the database configuration. You need to configure the database to authenticate to the storage account following least-privilege principles without embedding any secrets. What should you do?

A support team at a retail company queries the Customers table in an Azure SQL Database to help callers. Company policy requires that support agents never see full credit card numbers, but must still be able to view the last four digits to confirm identity. Application service accounts that process payments must continue to see the full value. You want the lowest-effort solution that does not change application queries or physically alter stored data. What should you implement?

A retail company stores customer email addresses in an Azure SQL Database. Requirements: (1) support agents querying the table interactively should see only obfuscated emails like aXXX@XXXX.com, (2) the application service account must continue to read the full plaintext value to send order confirmations, and (3) the solution must require no changes to the application code or client drivers. Which feature should you implement?

You manage an Azure SQL Managed Instance hosting an OLTP database. The compliance team requires a downstream ETL process to incrementally extract all INSERT, UPDATE, and DELETE operations made to a Customers table, including the actual column values before and after each change. The solution must retain the historical change data in system tables that the ETL job can query on a schedule, without modifying the application or the Customers table schema. Which feature should you enable?

A healthcare company must guarantee that a new AuditLog table in an Azure SQL Database can only ever have rows appended — never updated or deleted — and must provide cryptographic proof to external auditors that no historical records have been tampered with. The team wants a native feature that requires no application-layer hashing logic. Which feature should you configure when creating the table?

You manage an Azure SQL Database that stores regulated healthcare data. Compliance auditors require that all successful and failed login attempts, plus schema changes, be captured and retained for at least two years in a queryable store that supports Kusto (KQL) analysis and long-term archival without managing your own storage lifecycle policies. Which auditing configuration best meets these requirements?

You manage an Azure SQL Database that stores regulated healthcare data. A compliance requirement states that the organization must control and rotate the encryption key used to protect data at rest, and Microsoft must not have access to the root key material. Transparent Data Encryption (TDE) is currently enabled using the service-managed key. What should you configure to meet the requirement with the least ongoing administrative effort?

Your organization runs an Azure SQL Database that must comply with a new corporate security policy requiring all authentication to flow through Microsoft Entra ID. The compliance team wants to guarantee that no SQL authentication (username/password) logins can be used to connect, even if such logins already exist. You want to enforce this with the least ongoing administrative effort. What should you do?

A reporting analyst needs to query the Employees table in an Azure SQL Database. Business rules allow the analyst to read all columns except the Salary column, which contains sensitive compensation data. You must enforce this using object-level permissions with the least administrative effort, while keeping the analyst able to run SELECT queries that reference other columns of the table. Which T-SQL approach should you use?

You manage an Azure SQL Database used by a reporting application. A new team of five analysts needs read-only access to all tables in the database. The analysts already belong to an existing Microsoft Entra ID security group named 'sg-report-analysts'. You want to grant access following least-privilege principles while minimizing ongoing user-by-user administration. Which approach should you use?

A developer needs permission to run every stored procedure in the Reporting schema of an Azure SQL Database, but must not be able to read or modify the underlying tables directly. New stored procedures are added to the Reporting schema frequently, and you want to avoid granting permissions procedure-by-procedure. Following least-privilege principles, which T-SQL statement should you execute?

A stored procedure named dbo.uspArchiveOrders performs cross-schema DML that the calling application account (an Entra ID contained user named app_worker) is not permitted to execute directly. You want the procedure to run successfully when called by app_worker without granting that user broad permissions on the underlying tables, and without relying on ownership chaining (the procedure touches objects in a different schema with a different owner). Which approach follows least privilege?

A monitoring engineer needs to query dynamic management views (DMVs) such as sys.dm_exec_requests and sys.dm_os_wait_stats across an Azure SQL Database to diagnose performance issues. Company policy mandates least privilege, and the engineer must not be able to read or modify any application table data. Which approach grants exactly the required access?