Secure, optimize, and deploy database solutions Study Guide — DP-800

Secure, Optimize, and Deploy Database Solutions

This domain accounts for 38% of the DP-800 exam — the single largest domain. It tests your ability to implement end-to-end security (encryption, masking, access control, auditing), optimize query performance using execution plans and observability tools, build and deploy database solutions using SQL Database Projects and CI/CD pipelines, configure Data API Builder (DAB) for REST/GraphQL exposure, and stream change events using CDC and Azure Monitor. Expect scenario-based questions where you must select the correct security control, optimization technique, or deployment configuration given a specific constraint.

Always Encrypted, Column-Level Encryption, and Dynamic Data Masking

Always Encrypted

Always Encrypted ensures that sensitive column data is encrypted at the client driver level — the database engine never sees plaintext. It is the correct choice when you must prevent DBAs, cloud operators, or anyone with database access from reading sensitive values.

Always Encrypted with Secure Enclaves extends Always Encrypted to support rich computations (range queries, pattern matching) inside a trusted execution environment (TEE). Requires ENCLAVE_COMPUTATIONS on the Column Encryption Key (CEK) and an attestation service (Microsoft Azure Attestation).

Two key types:

• Column Master Key (CMK) — stored externally (Key Vault); protects the CEK.
• Column Encryption Key (CEK) — stored in the database metadata; encrypts the actual column data.

Two encryption types:

• Deterministic — same plaintext always produces same ciphertext; supports equality joins, GROUP BY, and indexes.
• Randomized — same plaintext produces different ciphertext each time; more secure, but no searching/grouping without enclaves.

Column-Level Encryption (CLE)

Column-Level Encryption (T-SQL native via ENCRYPTBYKEY) encrypts at the server side. Plaintext is visible to anyone who has the key and database access. Used when application-level transparency is needed but protecting from network sniffing — not suitable for protecting from DBAs.

Dynamic Data Masking (DDM)

Dynamic Data Masking obfuscates data in query results for non-privileged users without altering stored data. It is a presentation-layer control.

Privileged users (db_owner, sysadmin, UNMASK permission) always see plaintext. Grant UNMASK selectively with GRANT UNMASK ON <schema>.<table>.<column>.

Common traps:

• DDM does not prevent users from inferring data via WHERE clause brute-forcing unless combined with Row-Level Security.
• Always Encrypted is the only control that hides data from DBAs — DDM and CLE do not.
• Deterministic encryption supports equality search; randomized does not (without enclaves).
• TDE protects data at rest (files/backups) but not in-query — never substitute it for Always Encrypted in "hide from DBA" scenarios.

Row-Level Security, Object Permissions, Passwordless Access, and Auditing

Row-Level Security (RLS)

Row-Level Security filters rows returned by SELECT, UPDATE, DELETE based on the executing user's context. Implemented via a security policy that references an inline table-valued function (iTVF) as a filter predicate.

CREATE FUNCTION dbo.fn_rls_predicate(@TenantId INT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT 1 AS result WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS INT);

CREATE SECURITY POLICY SalesPolicy
ADD FILTER PREDICATE dbo.fn_rls_predicate(TenantId) ON dbo.Orders
WITH (STATE = ON);

Use SESSION_CONTEXT to pass the tenant ID from the application connection. RLS is transparent to the application.

Block predicates prevent INSERT/UPDATE of rows the user cannot see (add ADD BLOCK PREDICATE).

Object Permissions

Least-privilege model: GRANT, DENY, REVOKE at database, schema, or object level. DENY always overrides GRANT. Use database roles and schema-scoped permissions rather than per-object grants.

Contained database users authenticate at the database level — no server login required. Essential for portability and elastic pools.

Passwordless Access and Managed Identities

Managed Identity (system-assigned or user-assigned) allows Azure services to authenticate to Fabric/SQL without storing credentials.

To grant a managed identity access to a SQL database: create a contained database user mapped to the MI, then grant appropriate roles (db_datareader, db_datawriter).

Auditing

Microsoft Fabric auditing logs workspace and item-level activities in Microsoft Purview. For Azure SQL / Fabric SQL analytics endpoint:

• Audit logs go to Azure Monitor Logs (Log Analytics), Storage Account, or Event Hub.
• The AuditEvent log category captures data access events.
• Use Purview Data Map for data governance scanning alongside auditing.

Common traps:

• RLS filter predicates only filter SELECT/UPDATE/DELETE — you must add block predicates separately for INSERT protection.
• DENY overrides GRANT even through role membership — a common distractor involving inherited permissions.
• System-assigned MI is deleted when the parent resource is deleted; user-assigned MI persists.
• Passwordless means no connection string secrets — using Key Vault references to store connection strings is not passwordless.