DP-800 Module 5 Study Guide

Implement Data Security and Compliance with SQL

*Complete concept explanations + exam-style practice questions*

Security questions on DP-800 are almost always "which feature solves this requirement?" puzzles. The
features overlap deliberately — encryption vs masking vs RLS vs permissions all "protect data" — so the skill is
matching the *threat model in the scenario* to the *right layer*. Keep this master question in mind
throughout: who are we protecting the data from?

Protecting from… Feature

Someone who steals the database files/backups TDE (encryption at rest)

Someone sniffing the network TLS (encryption in transit)

DBAs/admins of the database itself Always Encrypted

App users who shouldn't see full values (casually) Dynamic Data Masking

Users who should see only *their* rows Row-Level Security

Users doing things they shouldn't Permissions (GRANT/DENY)

Stolen/leaked passwords Entra ID + managed identities (passwordless)

"We need to know who did what" Auditing

Unit 2 — Protect Data with Encryption

2.1 The encryption layers (orientation)

Encryption at rest — TDE (Transparent Data Encryption): encrypts the physical data files, log files, and
backups. "Transparent" = no application or query changes; data is decrypted automatically in memory.
Protects against stolen disks/backup files. On by default for new Azure SQL databases. It does not protect
data from anyone who can log in — a DBA querying the table sees plaintext.

Encryption in transit — TLS: encrypts the connection between client and server (Encrypt=True in connection
strings). Protects against network eavesdropping.

Encryption in use — Always Encrypted: the star of this unit, below.

2.2 Always Encrypted — protecting data from the database itself

The defining property: encryption and decryption happen in the client driver, never on the server. The
database stores only ciphertext and — crucially — never has the keys. Consequence: DBAs, cloud operators,
even sysadmins querying the table see encrypted bytes. This is the answer whenever a scenario says "data
must be protected even from database administrators / from Microsoft / from the hosting team."

The two-key hierarchy (memorize):

- Column Encryption Key (CEK) — actually encrypts the column data. Stored *in the database*, but only in
encrypted form.

dp800-module5-study-guide 1 Page 1
 - Column Master Key (CMK) — encrypts the CEKs. Stored outside the database: Azure Key Vault, Windows
certificate store, or an HSM. The database holds only *metadata pointing to* the CMK's location.

The client driver fetches the encrypted CEK, uses its access to the CMK to decrypt it, then encrypts/decrypts
column values locally. No CMK access no plaintext, no matter your database role.

The two encryption types — the classic exam decision:

Deterministic Randomized

Same plaintext Same ciphertext always Different ciphertext each time

Supports Equality comparisons: WHERE =, joins, No operations server-side (only retrieve +

GROUP BY, DISTINCT on the column decrypt client-side)

Security Weaker — patterns/frequency can leak Stronger

(bad for low-cardinality columns like
Gender)

Use for Lookup keys you must filter by: SSN, Maximum-secrecy values you never filter
national ID, account number by: salary, medical notes

CREATE TABLE [Link] (

PatientID INT IDENTITY PRIMARY KEY,
NationalID CHAR(11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK1,
ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),
Diagnosis NVARCHAR(400)
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK1,
ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
);

Practical facts that get tested: the app's connection string needs Column Encryption Setting = Enabled;
deterministic columns require a BIN2 collation; range queries (>, <, LIKE) don't work on either type in classic
Always Encrypted — that's what Always Encrypted with secure enclaves adds (a protected server-side memory
region that can do richer operations, including pattern matching and in-place encryption, without exposing
keys to the host).

2.3 Column-level encryption (the T-SQL built-in kind)

Older, server-side alternative: encrypt specific values with keys that live in the database's key hierarchy
(Service Master Key Database Master Key certificates symmetric keys):

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Str0ng!Passw0rd';

CREATE CERTIFICATE SalaryCert WITH SUBJECT = 'Salary protection';
CREATE SYMMETRIC KEY SalaryKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE SalaryCert;

OPEN SYMMETRIC KEY SalaryKey DECRYPTION BY CERTIFICATE SalaryCert;

UPDATE [Link]
SET SalaryEncrypted = ENCRYPTBYKEY(KEY_GUID('SalaryKey'), CAST(Salary AS NVARCHAR(20)));

SELECT CONVERT(NVARCHAR(20), DECRYPTBYKEY(SalaryEncrypted)) AS Salary FROM [Link];

CLOSE SYMMETRIC KEY SalaryKey;

Always Encrypted vs column-level encryption — the distinction the exam draws: column-level encryption's
keys are on the server, so admins with key access can decrypt (protects data at rest granularly, requires
app/T-SQL changes, VARBINARY columns); Always Encrypted keeps keys client-side (protects from the server's
own admins). "Protect from DBAs" Always Encrypted, every time.

dp800-module5-study-guide 1 Page 2
 Unit 3 — Configure Dynamic Data Masking (DDM)

3.1 What it is — and honestly, what it is not

DDM obfuscates values in query results for non-privileged users. The stored data is untouched and
unencrypted; masking is applied on the way out. It's a presentation-layer, casual-exposure control — perfect
for "support staff see XXX-XX-6789 instead of full SSNs" — and explicitly not a substitute for encryption. Users
with enough query freedom can make inferences (WHERE Salary > 100000 still filters on real values even if
the displayed salary is masked). The exam expects you to know both its use and its limits.

3.2 The four mask functions

CREATE TABLE [Link] (
CustomerID INT IDENTITY PRIMARY KEY,
FullName NVARCHAR(100) MASKED WITH (FUNCTION = 'default()'), -- 1
Email NVARCHAR(100) MASKED WITH (FUNCTION = 'email()'), -- 2
CreditCard CHAR(16) MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)'), -- 3
Discount INT MASKED WITH (FUNCTION = 'random(1,10)') -- 4
);

1. default() — full mask by type: strings 'xxxx', numbers 0, dates 1900-01-01.

2. email() — first letter + XXX@[Link]: aXXX@[Link].
3. partial(prefix, padding, suffix) — expose first *n* and last *m* characters with custom padding between.
The credit card above shows only the last 4.

4. random(low, high) — numeric columns get a random value in the range.

Add to an existing column: ALTER TABLE t ALTER COLUMN c ADD MASKED WITH (FUNCTION = '...');
— no data rewrite, instant.

3.3 Who sees through the mask

The UNMASK permission controls it — grantable at database, schema, table, or column granularity:

GRANT UNMASK ON [Link](CreditCard) TO FraudInvestigators; -- column-level

GRANT UNMASK TO ComplianceRole; -- everything

Members of db_owner (and admins) see unmasked data inherently. Everyone else gets masked results with
zero application changes — the feature's main selling point.

Unit 4 — Implement Row-Level Security (RLS)

4.1 The problem it solves

"Salespeople may query the Orders table, but each sees only their own customers' rows." Without RLS you'd
bake WHERE clauses into every app query (fragile, bypassable). RLS enforces the filter in the database engine
— transparent and unavoidable regardless of tool: SSMS, Power BI, the app, everything.

4.2 The two-piece architecture (memorize the anatomy)

dp800-module5-study-guide 1 Page 3
 Piece 1 — the predicate function: an inline table-valued function WITH SCHEMABINDING returning a row
when access is allowed:

CREATE SCHEMA Security;

GO
CREATE FUNCTION Security.fn_TenantFilter (@TenantID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_result
WHERE @TenantID = CAST(SESSION_CONTEXT(N'TenantID') AS INT)
OR IS_MEMBER('db_owner') = 1; -- an escape hatch for admins

Piece 2 — the security policy binding the function to tables:

CREATE SECURITY POLICY [Link]

ADD FILTER PREDICATE Security.fn_TenantFilter(TenantID) ON [Link],
ADD BLOCK PREDICATE Security.fn_TenantFilter(TenantID) ON [Link] AFTER INSERT
WITH (STATE = ON);

FILTER vs BLOCK predicates — the tested distinction:

- FILTER — silently removes rows from reads (SELECT, and from the row-visibility of UPDATE/DELETE: you
can't modify what you can't see).

- BLOCK — explicitly rejects writes violating the predicate (AFTER INSERT / AFTER UPDATE / BEFORE UPDATE /
BEFORE DELETE variants). Without a BLOCK AFTER INSERT, a tenant could *insert* rows for another tenant
(which they'd then be unable to see — data leaks *in*). Complete multitenant protection = FILTER +
BLOCK.

4.3 Identifying the user inside the predicate

Three common mechanisms:

- USER_NAME() / SUSER_SNAME() — when each human has their own database user (WHERE SalesRep =
USER_NAME()).
- SESSION_CONTEXT(N'key') — when the app connects with one shared identity (connection pooling) and sets
per-user context after connecting: EXEC sp_set_session_context @key = N'TenantID', @value =
42;. The standard answer for middle-tier/multitenant apps.

- IS_MEMBER('role') — role-based carve-outs (managers see all).

Design cautions worth points: grant users no direct UPDATE rights to the column driving the predicate (or
they reassign themselves); the predicate function should be simple and fast (it's appended to every query);
and side-channel leakage exists in edge cases (e.g., divide-by-zero error probing) — RLS is robust access
control, not an information-theoretic guarantee against a determined attacker with query access.

Unit 5 — Manage Permissions and Secure Access

5.1 The permission model in five ideas

1. Principals receive permissions: logins (server level), database users, and roles (bundles of users). Best
practice: grant to roles, add users to roles — never sprinkle grants on individuals.

2. The three verbs:

dp800-module5-study-guide 1 Page 4
 GRANT SELECT ON [Link] TO SalesRole; -- allow
DENY DELETE ON [Link] TO SalesRole; -- explicitly forbid
REVOKE SELECT ON [Link] FROM SalesRole; -- remove a previous GRANT or DENY (back to neutral)

DENY beats GRANT — if any role you belong to is denied, you're denied, regardless of grants elsewhere. And
REVOKE DENY: revoke removes an entry (you might still have access via another role); deny actively blocks.
Guaranteed exam material.

3. Granularity ladder: database-wide roles (db_datareader = read everything — usually too broad) schema
level (GRANT SELECT ON SCHEMA::Sales TO Analysts; — the sweet spot: new tables in the schema are
covered automatically) object level (one table/proc/view) column level (GRANT SELECT ON
[Link](FullName, Dept) TO ... — everything except excluded columns like Salary).

4. Least privilege + the module-2 pattern: users get EXECUTE on procs and SELECT on views; zero direct table
permissions; ownership chaining does the rest. Applications get purpose-built roles with exactly the needed
rights.

5. EXECUTE AS — objects can run under a defined identity (WITH EXECUTE AS OWNER) when ownership
chains break (dynamic SQL, cross-database) — a controlled-elevation tool to recognize.

5.2 Passwordless access with Microsoft Entra ID

The compliance driver: connection strings with passwords are a breach waiting to happen (leaked in source
control, config files, memory dumps). Modern requirement: eliminate stored credentials.

Entra ID authentication: Azure SQL and Fabric SQL authenticate against Microsoft Entra ID (formerly Azure
AD) instead of SQL logins. Humans sign in interactively with MFA; you create database users mapped to Entra
identities:

CREATE USER [maya@[Link]] FROM EXTERNAL PROVIDER;

CREATE USER [DataAnalystsGroup] FROM EXTERNAL PROVIDER; -- an Entra GROUP as one user
ALTER ROLE db_datareader ADD MEMBER [DataAnalystsGroup];

Mapping groups rather than individuals = access managed in Entra, not per-database.

Managed identities — passwordless for applications: an Azure resource (App Service, Function, VM) gets an
identity managed by Azure itself — no password exists to steal, rotate, or leak; Azure handles token issuance:

- System-assigned — born with, and dies with, one specific resource (1:1).
- User-assigned — a standalone identity attachable to many resources (shared identity for a fleet).

In the database, the managed identity becomes a user the same way: CREATE USER [my-app-service]
FROM EXTERNAL PROVIDER; and the app's connection string says Authentication=Active Directory
Managed Identity — no secret anywhere. Scenario tell: "eliminate credentials from configuration" / "no
passwords to rotate" managed identity.

An admin note: the Entra admin must be configured on the server, and Azure SQL supports enforcing
Entra-only authentication (disabling SQL logins entirely) for maximum posture.

Unit 6 — Implement Auditing

6.1 What auditing answers

dp800-module5-study-guide 1 Page 5
 Not "prevent" — prove. Who read the salary table? Who deleted those rows? When did the failed logins spike?
Compliance regimes (GDPR, HIPAA, SOX…) require this trail.

6.2 SQL Server Audit — the three-object architecture

-- 1. The SERVER AUDIT: the destination (file, or Windows event logs)
CREATE SERVER AUDIT ComplianceAudit
TO FILE (FILEPATH = 'D:\Audits\')
WITH (ON_FAILURE = SHUTDOWN); -- strictest: no audit → no server (compliance stance)
ALTER SERVER AUDIT ComplianceAudit WITH (STATE = ON);

-- 2. SERVER AUDIT SPECIFICATION: instance-level actions to capture

CREATE SERVER AUDIT SPECIFICATION LoginAuditSpec
FOR SERVER AUDIT ComplianceAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON);

-- 3. DATABASE AUDIT SPECIFICATION: database-level actions

USE SchoolDB;
CREATE DATABASE AUDIT SPECIFICATION SensitiveAccessSpec
FOR SERVER AUDIT ComplianceAudit
ADD (SELECT, UPDATE ON [Link] BY public), -- who touches this table
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP) -- role membership changes
WITH (STATE = ON);

The hierarchy — audit (destination) specifications (what to capture) — and the split (server spec =
logins/instance events; database spec = object access/DDL/role changes within a database) are the testable
structure. Read file-based audit logs with sys.fn_get_audit_file(...). ON_FAILURE options (CONTINUE /
SHUTDOWN / FAIL_OPERATION) encode how much you value the trail vs availability.

6.3 Auditing in Azure SQL and Fabric

Azure SQL auditing is a portal/policy-level feature (server- or database-scoped; server policy applies to all
databases) writing to three possible sinks — a storage account, Log Analytics, or Event Hubs — with Log
Analytics enabling queryable dashboards and alerts. Fabric SQL databases likewise surface audit logging of
database events for compliance monitoring. Conceptual mapping is identical: destinations + captured action
groups.

Auditing vs the lookalikes: temporal tables capture data *values* over time (not who read them); ledger
proves data wasn't tampered with; auditing records *activity* — including reads, which neither of the others
sees. "Track who SELECTs from the table" only auditing.

Unit 7 — Configure Secure Access to AI Services (Model

Endpoints)

7.1 The scenario this unit exists for

AI-enabled databases call external model endpoints — Azure OpenAI for embeddings/completions — from
inside the database (e.g., via sp_invoke_external_rest_endpoint, or the AI functions that generate
embeddings during queries). That call must authenticate to the AI service. The insecure way: an API key
stored in the database or app config — a static secret to leak. This unit is about doing it without keys.

7.2 The pattern: managed identity + database-scoped credential

dp800-module5-study-guide 1 Page 6
 1. Give the database's server a managed identity (Azure SQL: enable the server's system-assigned identity, or
attach a user-assigned one).

2. Grant that identity a role on the AI resource — for Azure OpenAI, the RBAC role Cognitive Services OpenAI
User on the OpenAI resource. Access is now governed by Azure RBAC: revocable, auditable, no secret.

3. In the database, create a DATABASE SCOPED CREDENTIAL that says "authenticate as my managed
identity":

CREATE DATABASE SCOPED CREDENTIAL [[Link]

WITH IDENTITY = 'Managed Identity',
SECRET = '{"resourceid":"[Link]

4. Database code invoking the endpoint references the credential; Azure exchanges the identity for a token
automatically. No API key exists anywhere.

Contrast for the exam: WITH IDENTITY = 'HTTPEndpointHeaders', SECRET = '{"api-key":"..."}'

is the key-based form — it works, but the *recommended, compliance-friendly* answer is 'Managed
Identity'. Scenario keywords: "without storing keys," "rotate-free," "use Azure RBAC to control model
access."

Defense-in-depth extras to recognize: restricting outbound networking so the database can only call
approved endpoints; Private Link/private endpoints so traffic to the AI service never crosses the public
internet; and granting the identity only the narrowest RBAC role needed.

Unit 8 — Secure Data API Endpoints (REST, GraphQL, MCP)

8.1 Data API builder (DAB) in one paragraph

Data API builder turns database tables/views/procs into REST and GraphQL endpoints (and MCP endpoints for
AI agents) via configuration — no API code. Because it exposes your database to the network, its security
configuration is the whole game: who may call it, and what may each caller do.

8.2 The three security layers in DAB's config ([Link])

Layer 1 — Authentication (who are you?): DAB validates callers' JWT tokens from a configured provider —
Microsoft Entra ID for enterprise APIs (also supports Static Web Apps' EasyAuth; a Simulator mode exists for
local development only — never production). Unauthenticated callers are the anonymous role; authenticated
ones are authenticated, plus any roles carried in token claims.

Layer 2 — Authorization (what may you do?): per entity, per role, per action (create/read/update/delete):

"entities": {
"Student": {
"source": "[Link]",
"permissions": [
{ "role": "anonymous", "actions": [] },
{ "role": "authenticated", "actions": ["read"] },
{ "role": "registrar", "actions": ["create", "read", "update"] }
]
}
}

Only entities in the config are exposed at all — everything else is invisible. Field-level restrictions
(include/exclude columns per role) and item-level policies (predicate expressions comparing token claims to
row data — RLS-flavored filtering at the API layer) refine it further.

dp800-module5-study-guide 1 Page 7
 Layer 3 — Database identity: DAB connects to the database with its own identity — which should be a
managed identity (Unit 7's lesson again) holding least-privilege rights: if the API only reads three tables, its
database user can only read those three tables. Defense in depth: even a misconfigured endpoint can't
exceed what the database allows its user. You can also flow the *end user's* context to the database (DAB can
set session context from token claims) so database-side RLS applies per caller — the layers compose.

MCP endpoints (exposing database operations as tools for AI agents) inherit the same model: authenticated
callers, role-scoped permissions, least-privileged database identity — plus the Module 4 wisdom that
agent-facing tools deserve the tightest scoping of all, since an agent will happily use every permission it's
given.

DP-800-Style Practice Questions — Module 5

Attempt all 20 before checking the key.

Q1. Compliance requires that database administrators — who have full access to the database — must NEVER
be able to view customers' national ID numbers in plaintext. Which feature satisfies this?

A. Transparent Data Encryption B. Dynamic Data Masking C. Always Encrypted D. Column-level encryption
with a database certificate

Q2. Why does Always Encrypted protect data even from sysadmins?

A. The server decrypts only for approved logins B. Encryption/decryption happens in the client driver, and the
column master key lives outside the database (e.g., Azure Key Vault) — the server never has the keys C. Data is
stored in a hidden system schema D. Sysadmins are denied SELECT automatically

Q3. An Always Encrypted column must support equality lookups (WHERE NationalID = @p) and joins. Which
encryption type is required, and what's the trade-off?

A. Randomized; no trade-off B. Deterministic; identical plaintexts produce identical ciphertexts, which can leak
patterns on low-cardinality data C. Randomized; requires a BIN2 collation D. Deterministic; the column
becomes read-only

Q4. A salary column is encrypted with Always Encrypted (randomized). The business now needs range queries
(Salary > 100000) evaluated server-side without exposing keys to the host. Which technology enables this?

A. Dynamic Data Masking B. Always Encrypted with secure enclaves C. TDE D. A persisted computed column

Q5. Which threat does TDE protect against?

A. A DBA querying sensitive columns B. Theft of the physical database files or backup files C. SQL injection D.
Excessive permissions

Q6. Support agents should see credit card numbers as XXXX-XXXX-XXXX-1234 in the CRM, while the fraud team
sees full values. Data need not be encrypted; no app changes are allowed. Which combination?

dp800-module5-study-guide 1 Page 8
 A. Always Encrypted + a key for the fraud team B. Dynamic Data Masking with partial(0,"XXXX-XXXX-XXXX-",4) +
GRANT UNMASK to the fraud team's role C. RLS with a filter predicate D. A view with SUBSTRING + separate
table for fraud

Q7. Which statement about Dynamic Data Masking is TRUE?

A. It encrypts data at rest B. Masked users can still filter on real underlying values (e.g., WHERE Salary > x),
enabling inference — DDM is not a substitute for encryption C. It requires application code changes D.
UNMASK can only be granted database-wide

Q8. Which DDM function displays 'kXXX@[Link]' for 'kiran@[Link]'?

A. default() B. partial(1,"XXX",4) C. email() D. random(1,100)

Q9. A multitenant SaaS app uses ONE shared SQL login via connection pooling. Each tenant must see only its
own rows. Which mechanism lets the RLS predicate identify the tenant?

A. USER_NAME() in the predicate B. sp_set_session_context after connecting + SESSION_CONTEXT() in the

predicate function C. A separate database per query D. IS_MEMBER('tenant')

Q10. With only a FILTER predicate on [Link] (no BLOCK predicates), which action can a tenant still
perform against another tenant's data?

A. SELECT the other tenant's rows B. UPDATE the other tenant's rows C. INSERT new rows carrying the other
tenant's TenantID D. DELETE the other tenant's rows

Q11. What are the two required components of a Row-Level Security implementation?

A. A DDL trigger and a certificate B. An inline table-valued predicate function (WITH SCHEMABINDING) and a
SECURITY POLICY binding it to tables C. A masked column and UNMASK grants D. A symmetric key and an
audit specification

Q12. A user belongs to RoleA (GRANT SELECT ON [Link]) and RoleB (DENY SELECT ON [Link]). What
happens when they query [Link], and why?

A. Allowed — GRANT is evaluated first B. Denied — DENY always overrides GRANT C. Allowed for 30 days, then
denied D. Depends on which role was created first

Q13. Analysts need SELECT on every current AND future table in the Sales schema, and nothing else. The
lowest-maintenance correct grant?

A. GRANT SELECT ON DATABASE B. Add them to db_datareader C. GRANT SELECT ON SCHEMA::Sales TO

AnalystsRole D. Grant SELECT on each table individually

Q14. An Azure App Service must connect to Azure SQL with NO credentials stored anywhere — nothing to leak
or rotate. Which approach?

dp800-module5-study-guide 1 Page 9
 A. SQL authentication with the password in Key Vault B. Enable a managed identity on the App Service;
CREATE USER [app-name] FROM EXTERNAL PROVIDER in the database; connect with Authentication=Active
Directory Managed Identity C. Windows authentication D. Store the connection string in an environment
variable

Q15. One identity must be shared by TWELVE Azure Functions that all need the same database access. Which
identity type fits best?

A. Twelve system-assigned managed identities B. One user-assigned managed identity attached to all twelve
C. One SQL login D. A certificate per function

Q16. Compliance asks: "Produce a record of every SELECT against [Link], including who and when."
Which feature is the ONLY one on this list that can capture reads?

A. Temporal tables B. Ledger tables C. SQL Server Audit with a database audit specification on SELECT for that
table D. Change Data Capture

Q17. In SQL Server Audit architecture, what is the role of the SERVER AUDIT object itself?

A. It lists the database-level actions to record B. It defines the destination (file/event log) and failure behavior
for audit records C. It encrypts the audit trail D. It replaces the transaction log

Q18. Azure SQL code calls Azure OpenAI to generate embeddings. Security mandates NO API keys stored in
the database. Which configuration is correct?

A. A DATABASE SCOPED CREDENTIAL with IDENTITY='HTTPEndpointHeaders' containing the api-key B. Enable

the SQL server's managed identity, grant it the Cognitive Services OpenAI User role on the OpenAI resource,
and create a DATABASE SCOPED CREDENTIAL WITH IDENTITY = 'Managed Identity' C. Hardcode the key in the
stored procedure D. Store the key in a table with DDM

Q19. In Data API builder, entity permissions are defined per role and per action. A "Product" entity grants
anonymous read and manager create/read/update. What can an unauthenticated caller do to Product,
and what happens with entities absent from the config?

A. Nothing at all; absent entities are read-only B. Read only; absent entities are not exposed through the API
at all C. Full CRUD; absent entities inherit anonymous read D. Read and update; absent entities throw 500
errors

Q20. (Scenario) An architect designs a DAB-based REST API: Entra ID JWT authentication, role permissions in
[Link], and DAB connecting to the database as a managed identity whose database user has only
SELECT on the three exposed views. What security principle does the LAST element demonstrate, and why
does it matter?

A. Ownership chaining — it speeds up queries B. Defense in depth via least privilege — even if API-layer
authorization is misconfigured, the database identity physically cannot exceed read access on those three
views C. Security through obscurity — attackers can't find the tables D. Key rotation — the identity changes
daily

dp800-module5-study-guide 1 Page 10
 Answer Key with Explanations
Q1 C. "Protected even from DBAs" is Always Encrypted's defining scenario — keys live client-side, server sees
only ciphertext. TDE (A) is transparent to anyone who can log in; DDM (B) is bypassed by UNMASK/admin
rights and doesn't encrypt; certificate-based column encryption (D) keeps keys on the server where admins
can reach them.

Q2 B. The architecture is the answer: client-driver crypto + CMK outside the database (Key Vault/cert store).
The server stores an *encrypted* CEK and metadata about where the CMK lives — nothing decryptable
server-side.

Q3 B. Only deterministic encryption preserves equality (same plaintext same ciphertext server can
match). The cost: ciphertext equality reveals value repetition — dangerous on low-cardinality columns
(Gender, State). Randomized supports no server-side operations.

Q4 B. Secure enclaves add a protected server-side execution region that can compute on plaintext inside the
enclave without exposing it to the host — enabling range/pattern operations and in-place encryption that
classic Always Encrypted can't do.

Q5 B. TDE encrypts files/backups at rest — the stolen-disk threat. It's transparent to queries, so it does
nothing about who reads data through the front door.

Q6 B. Casual visual protection + role exceptions + zero app changes = DDM's exact niche. partial() shows the
last 4; GRANT UNMASK (grantable per column/table) opens it for fraud. Always Encrypted (A) is heavier and
changes app connection behavior; the requirement said "need not be encrypted."

Q7 B. The documented limitation: masking alters *display*, not the values predicates run against —
inference through filtering is possible. Hence "complementary to, not a replacement for, encryption." A/C are
false by design; UNMASK supports column granularity (D false).

Q8 C. email() = first character + XXX@[Link]. partial() could approximate it but the built-in email() is the
designed answer.

Q9 B. Connection pooling destroys per-user logins — everyone is the same principal. The pattern: the app
calls sp_set_session_context (tenant from *its* auth layer), and the predicate reads
SESSION_CONTEXT(N'TenantID'). USER_NAME() (A) would return the shared login for everyone.

Q10 C. FILTER governs what you can *see* (blocking reads, and reach of UPDATE/DELETE). It does NOT
inspect *new* rows — INSERTs with a foreign TenantID succeed (and vanish from the inserter's view). BLOCK
AFTER INSERT closes the hole. This asymmetry is the most-tested RLS fact.

Q11 B. RLS = predicate function (inline TVF, SCHEMABINDING) + security policy (FILTER/BLOCK predicates
bound to tables, STATE = ON). Two objects, always.

Q12 B. DENY trumps GRANT across all memberships — the fundamental precedence rule. (And remember
REVOKE just removes an entry; it doesn't block.)

Q13 C. Schema-scoped grant: covers all current and future objects in Sales, nothing outside it.
db_datareader (B) reads *every* schema — violates least privilege; per-table grants (D) miss future tables.

Q14 B. Managed identity is the no-secret answer: Azure issues tokens for the App Service's identity; the
database maps it via FROM EXTERNAL PROVIDER. Key Vault (A) still *stores a password* — better hygiene, but
a secret exists; the requirement said none.

dp800-module5-study-guide 1 Page 11
 Q15 B. User-assigned = standalone identity attachable to many resources — one database user, one grant
set, twelve consumers. System-assigned (A) is 1:1 with each resource twelve users and twelve grant sets to
manage.

Q16 C. Reads leave no trace in data-change technologies: temporal (A) and CDC (D) capture modifications;
ledger (B) proves integrity. Only auditing records SELECT activity with principal + timestamp.

Q17 B. The server audit = the *destination and delivery* contract (file path / event log, ON_FAILURE =
CONTINUE / SHUTDOWN / FAIL_OPERATION). The *what to capture* lives in the server/database audit
specifications attached to it.

Q18 B. The managed-identity chain: server identity RBAC role on the OpenAI resource (Cognitive Services
OpenAI User) DATABASE SCOPED CREDENTIAL WITH IDENTITY='Managed Identity'. Option A works
mechanically but embeds the API key — precisely what was forbidden.

Q19 B. Anonymous role has ["read"] read only. And DAB's exposure model is allow-list: entities not present
in the configuration simply don't exist to the API — the safe default that makes the config the security
boundary.

Q20 B. Least-privileged database identity = defense in depth: each layer (authentication, API authorization,
database permissions) independently limits damage, so a failure in one doesn't cascade. The database
physically can't serve a write even if the API layer mistakenly permits one.

Score guide

- 17–20: Solid. The 30-minute exercise unit is worth doing anyway — RLS and DDM only truly click when you
test them as different users (EXECUTE AS USER = '...' makes that easy).

- 13–16: Re-study the "protecting from whom?" table and the RLS FILTER/BLOCK asymmetry — those two
areas cause most misses.

- 12: Rebuild the threat-model table from memory first; every feature in this module is an answer to a
specific attacker.

The one-page mental model to walk in with

- Threat feature: stolen files TDE; network TLS; DBAs Always Encrypted; casual over-exposure DDM;
"only my rows" RLS; wrong actions permissions; stolen passwords Entra/managed identity; "prove
who did what" auditing.

- Always Encrypted: client-side crypto; CMK outside DB, CEK inside (encrypted); deterministic =
equality-capable but pattern-leaky, randomized = stronger but no server ops; enclaves add rich server-side
ops; BIN2 collation for deterministic.

- DDM: display-only; default/email/partial/random; UNMASK down to column level; inference via WHERE
remains — not encryption.

- RLS: schemabound inline TVF + security policy; FILTER hides reads, BLOCK rejects writes (INSERT hole
without it); SESSION_CONTEXT for pooled/shared-login apps.

- Permissions: roles not users; DENY > GRANT; REVOKE = neutral; schema-level grants cover future objects;
column-level GRANT exists; procs/views as the security API.

- Passwordless: CREATE USER … FROM EXTERNAL PROVIDER; system-assigned MI = 1:1 with a resource,
user-assigned = shared across many; Entra groups as database users.

dp800-module5-study-guide 1 Page 12
 - Auditing: server audit (destination, ON_FAILURE) + server spec (logins) + database spec (object access incl.
SELECT); the only feature that sees reads; Azure sinks: storage / Log Analytics / Event Hubs.

- Model endpoints: managed identity + RBAC (Cognitive Services OpenAI User) + DATABASE SCOPED
CREDENTIAL WITH IDENTITY='Managed Identity' — no API keys.

- DAB endpoints: JWT auth (Entra) per-entity/role/action permissions (allow-list exposure, field/item
policies) least-privileged managed-identity database user. Layers compose = defense in depth.

dp800-module5-study-guide 1 Page 13