Secure, Optimize, and Deploy Database Solutions

Data security on DP-800 spans several independent, layered controls — the exam expects you to know which layer stops which threat, since a database can pass one control and still be wide open through another.

Always Encrypted and column-level encryption

• Always Encrypted protects sensitive columns *in use*, not just at rest or in transit: encryption and decryption happen client-side inside a driver that holds the column encryption key, so plaintext values never reach the SQL Server or Azure SQL engine itself — even a DBA with full `sysadmin` rights only ever sees ciphertext when querying the column directly.
• *Deterministic* encryption always produces the same ciphertext for the same plaintext value, which allows equality comparisons, joins, and grouping on the encrypted column — at the cost of being vulnerable to frequency analysis on low-cardinality data (an attacker who can see ciphertext patterns can infer which encrypted values repeat most often).
• *Randomized* encryption produces different ciphertext every time, which is more secure but disallows searching, joining, or grouping on that column entirely — the classic trade-off is deterministic for columns you must query on (a national ID used as a lookup key) and randomized for columns you only ever retrieve (a stored secret answer).
• Always Encrypted *with secure enclaves* relaxes that trade-off by performing richer operations (pattern matching, range comparisons, in-place cryptographic re-keying) inside a hardware- or software-based trusted execution environment, without plaintext ever being visible to the database engine outside the enclave.

Dynamic Data Masking

• `ALTER TABLE ... ALTER COLUMN col ADD MASKED WITH (FUNCTION = 'default()')` (or `email()`, `partial(prefix,[padding],suffix)`, `random(low,high)`) obscures a column's *displayed* value for users without the `UNMASK` permission, while the real value is stored unmasked and unchanged in the table.
• Because masking is a display-time transformation rather than actual encryption, it's meant to reduce accidental over-exposure to a broad set of ordinary users (support staff running ad hoc queries) — it is explicitly not a substitute for encryption or access control against a determined or privileged attacker, since the underlying data is fully readable to anyone granted `UNMASK` or with sufficient permission to query it another way.

Row-Level Security

• Row-Level Security is implemented as an inline table-valued function (a *security predicate*) bound to a target table through `CREATE SECURITY POLICY ... ADD FILTER PREDICATE fn(column) ON schema.table`; a `FILTER` predicate silently hides non-matching rows from `SELECT`/`UPDATE`/`DELETE`, while a `BLOCK` predicate actively rejects a write (`AFTER INSERT`, `AFTER UPDATE`, `BEFORE UPDATE`, or `BEFORE DELETE`) that would violate it.
• Because the predicate function is just T-SQL, it commonly compares a row's tenant/owner column against `USER_NAME()`, `SESSION_CONTEXT()` (a value the application sets after connecting with a shared, lower-privileged login), or a role membership check — the policy is what wires that comparison to a specific table and enforces it automatically on every query, so application code never has to remember to add the filter itself.
• A security policy created with the default `SCHEMABINDING = ON` locks the predicate function and any tables/functions it references, and bypasses extra permission checks on those dependencies when a user queries the protected table — turning it `OFF` requires granting those permissions separately.

Object-level permissions and passwordless access

• Standard `GRANT`/`DENY`/`REVOKE` on schemas, tables, views, and procedures remains the first line of access control underneath any of the row- or column-level features above — RLS and masking narrow what's visible *within* an object a principal can already access, they don't substitute for object-level grants.
• Passwordless, Microsoft Entra ID–based authentication (a managed identity or a federated user) removes a stored credential from the connection string entirely, closing off the credential-leak risk that comes with SQL authentication — the same managed-identity pattern used for Key Vault and Storage access elsewhere in Azure applies directly to connecting to the database itself.

Auditing

• Azure SQL / SQL Server auditing writes a durable log of database events (logins, schema changes, specific statement types) to a storage target, giving you the "who did what, when" record that none of the preventive controls above produce on their own — prevention (RLS, encryption, permissions) and detection (auditing) are complementary, not overlapping, controls.

Common confusion

• Dynamic Data Masking and Always Encrypted are frequently confused because both "hide" a column's value, but they defend against completely different threats: masking changes what an *unprivileged application user* sees in query results while the true value sits in plaintext in storage, whereas Always Encrypted keeps the value as ciphertext everywhere outside the client driver — including from a privileged database administrator — which is why masking alone is never an acceptable substitute for encrypting a genuinely sensitive column.

Once a database exposes model endpoints, GraphQL/REST APIs, or MCP tools, the attack surface extends well past traditional login-and-query access — this section is where DP-800 checks that you extend the same security discipline to that newer surface.

Securing model endpoints with Managed Identity

• When T-SQL calls out to an external model (via `sp_invoke_external_rest_endpoint` or an `AI_GENERATE_EMBEDDINGS`-backed external model), authenticating that outbound call with the database's system- or user-assigned managed identity avoids embedding an API key as a stored credential that could leak.
• The managed identity still needs an explicit role assignment on the target resource (for example, Cognitive Services OpenAI User on the Azure OpenAI resource) before the call succeeds — creating a `DATABASE SCOPED CREDENTIAL` with `IDENTITY = 'Managed Identity'` wires the database up to *use* the identity, but doesn't by itself grant that identity any permissions on the far end.

Securing GraphQL, REST, and MCP endpoints

• An endpoint exposed through Data API builder (REST or GraphQL) or through a SQL/Fabric MCP server is a new perimeter around the database — each entity's configured permissions determine which roles can read, write, or execute through that endpoint, independent of the underlying table's own object-level `GRANT`s.
• MCP-specific risk is less about network exposure and more about *scope creep*: a tool that's supposed to run read-only reporting queries but is connected with a broadly-privileged account can be induced (by a malicious prompt, a compromised client, or an over-eager agent) into running writes it was never intended to perform — scoping the credential behind an MCP or API endpoint to the minimum it needs is the primary defense.
• Rate limiting, authentication requirements, and audit logging apply to these endpoints the same way they would to any other externally reachable API — treating a GraphQL or MCP endpoint as "just internal tooling" and skipping those controls is a common, exam-relevant mistake.

Common confusion

• Enabling Microsoft Entra–based (passwordless) authentication for direct database connections and securing a GraphQL/REST/MCP endpoint in front of that same database are two different perimeters — locking down the direct SQL connection doesn't automatically constrain what an API or MCP layer sitting in front of it is configured to allow, since that layer typically connects to the database with its own service identity and enforces (or fails to enforce) its own separate permission model.

Performance troubleshooting on DP-800 follows a consistent pattern: understand the concurrency model that's causing contention, then use the engine's built-in diagnostic surface to find exactly which query or plan is responsible.

Transaction isolation levels and concurrency

• `READ UNCOMMITTED` allows dirty reads (seeing another transaction's uncommitted changes) in exchange for the least blocking; `READ COMMITTED` (the default) never reads uncommitted data but can still see different results for the same query re-run within one transaction; `REPEATABLE READ` and `SERIALIZABLE` progressively lock more to guarantee re-reads stay stable, at the cost of more blocking.
• `READ COMMITTED SNAPSHOT` and `SNAPSHOT` isolation use row versioning instead of locks for read consistency — readers don't block writers and writers don't block readers, which is why they're often reached for specifically to reduce blocking without loosening isolation guarantees the way `READ UNCOMMITTED` does.
• Choosing an isolation level is a genuine trade-off between data consistency and concurrency throughput, not a purely technical default to leave alone — a reporting workload might deliberately accept `READ UNCOMMITTED`'s looser guarantees for less blocking against an OLTP workload running concurrently.

Diagnosing blocking and deadlocks

• Blocking is one session waiting for a lock another session holds — normal and often transient, but a problem when a long-running transaction holds locks far longer than necessary. `sys.dm_exec_requests` and `sys.dm_os_waiting_tasks` surface who's blocked and who's holding the lock they're waiting on.
• A deadlock is a cycle of blocking (session A waits on B, B waits on A) with no possible resolution, which SQL Server detects automatically and resolves by killing one session as the "deadlock victim" — the exam expects you to know this is automatic, not something an operator manually resolves in real time, though the victim's application still needs retry logic to recover gracefully.

Execution plans and DMVs

• An execution plan shows the operators (scans, seeks, joins, sorts) the optimizer chose and their relative cost — a table scan where a seek was expected, or a sort/hash operation consuming disproportionate resources, is usually the first thing to investigate in a slow query.
• Dynamic management views (DMVs) like `sys.dm_exec_query_stats`, `sys.dm_db_index_usage_stats`, and `sys.dm_os_wait_stats` expose the engine's own internal counters — aggregate wait statistics in particular are often the fastest way to identify *what kind* of bottleneck a workload is hitting (locking, I/O, CPU, memory) before drilling into any one query.

Query Store and Query Performance Insight

• Query Store persists the history of query plans and their runtime statistics over time, which is what makes it possible to answer "did this query get slower after last Tuesday's deployment" — a live DMV snapshot alone can't answer that, since it only reflects the current moment.
• Query Store's plan-forcing capability pins a query to a specific historical plan, directly addressing plan regression (the optimizer picking a new, worse plan after a statistics update or schema change) without changing the query text itself.
• Query Performance Insight (Azure SQL Database) is a portal-level view built on the same underlying data, surfacing top resource-consuming queries over a chosen window without writing a single KQL or T-SQL diagnostic query by hand — the friendlier, less flexible sibling of querying Query Store's catalog views directly.

Common confusion

• Blocking and deadlocking are often used interchangeably but aren't the same failure mode: blocking is a normal, usually self-resolving wait for a lock to be released, while a deadlock is a genuine cycle with no resolution path that the engine must break by killing a session — a long blocking chain that never deadlocks can still be a serious performance problem worth investigating, even though nothing ever gets automatically killed the way it does in a true deadlock.

Treating a database schema as versioned, buildable code — rather than a set of manual changes applied directly to production — is the practice this section tests, using SQL Database Projects as the concrete tool.

Projects, DACPACs, and SDK-style projects

• A SQL Database Project (a `.sqlproj`) is a source-controlled representation of a database's schema as individual object files, built into a `.dacpac` (data-tier application package) the same way application code compiles into a binary — the dacpac, not the loose SQL files, is what actually gets deployed.
• The newer SDK-style project format (built on `Microsoft.Build.Sql`) is leaner and more MSBuild-native than the classic SSDT project format, supporting a graphical table designer and richer tooling in Visual Studio while still producing an equivalent dacpac — classic and SDK-style projects both remain supported, with tool support varying slightly across Visual Studio, VS Code, and SSMS.
• `sqlpackage`, the cross-platform CLI, is what actually performs the build-and-deploy actions in automation: `sqlpackage /Action:Extract` reverse-engineers an existing database into project-like files, `/Action:Publish` deploys a dacpac to a target database, and `/Action:Script` generates the deployment T-SQL without running it — the last option being how a pipeline produces a reviewable script instead of auto-applying changes blind.

Schema-drift detection with schema comparison

• Schema compare evaluates the difference between any two of: a connected database, a project, or a dacpac — surfacing exactly which objects would be added, changed, or dropped to make the target match the source, which is the direct tool for catching drift (a change some developer applied straight to a database, bypassing source control).
• Running compare with the project as the *source* and a live database as the *target* is how you validate a database hasn't silently diverged from what's checked in, before that drift causes a confusing failure during the next real deployment.

Reference/static data and pre/post-deployment scripts

• Reference or seed data (lookup tables, configuration rows) that needs to exist identically across every environment is checked into the project as a pre- or post-deployment script — a plain `.sql` file that runs before or after the schema deployment itself, commonly using `MERGE` so it's safe to re-run without duplicating rows.
• SQLCMD variables parameterize environment-specific values (a schema name, a feature flag) inside these scripts and the project's build, so the same project artifact deploys correctly to dev, test, and production without hand-editing anything per environment.

Testing, secrets, and branching

• Unit tests validate individual database objects' logic (a stored procedure's output for known inputs); integration tests validate that a set of objects work correctly together — both run as part of CI before a dacpac is trusted enough to publish anywhere.
• Trunk-based development with short-lived feature branches, pull-request review, and required passing CI checks before merge is the same discipline used for application code, applied to a database project — branching policies, required reviewers ("code owners"), and approval gates on the deployment pipeline itself are what prevent an unreviewed schema change from reaching production.
• Secrets (connection strings, credentials used by the pipeline to reach a target database) are stored in the pipeline's secret store, never hard-coded into the project or a checked-in publish profile — the same principle as keeping API keys out of application source.

Common confusion

• `sqlpackage /Action:Publish` and `/Action:Script` both compute the identical set of schema differences under the hood, but only `Publish` actually applies them — `Script` produces the T-SQL that *would* run, which is why a cautious deployment pipeline generates the script first for human review or an approval gate, and only invokes `Publish` (or runs the reviewed script) once that approval is granted, rather than letting every merge auto-publish straight to a shared database.

A database rarely stands alone in production — this section covers turning it into an application-facing API surface with Data API builder, and wiring it into the broader event and observability ecosystem around it.

Data API builder configuration

• Data API builder (DAB) generates both REST and GraphQL endpoints from a single `dab-config.json` describing a data source (connection string, database type) and one or more *entities*, each mapped to a table, view, or stored procedure.
• `dab init --database-type mssql --connection-string "..."` scaffolds the base configuration, and `dab add <EntityName> --source dbo.TableName --permissions "anonymous:*"` registers an entity and its access rules — the resulting `dab-config.json` can also be hand-authored or hand-edited directly rather than only through the CLI.
• Connection strings belong in configuration via the `@env('VAR_NAME')` function rather than as literal text in `dab-config.json` — resolved from an environment variable at runtime, which keeps credentials out of a file that's typically checked into source control alongside the rest of the configuration.
• Per-entity settings control caching (`--cache.enabled`, `--cache.ttl-seconds`, cache level `L1` in-process or `L1L2` with a distributed second tier), field-level inclusion/exclusion, and — for stored-procedure entities — parameter defaults and requiredness, giving fine control over exactly what each entity exposes without writing any API code by hand.
• Exposing a stored procedure or a view (including one that models a GraphQL relationship across multiple tables) through DAB works the same as exposing a table entity, just with a different `--source.type`.

Deploying DAB

• DAB ships as a container image, so it deploys wherever containers run: Azure Container Apps, Azure App Service, Azure Container Instances, or a self-hosted Docker environment — the deployment target is a hosting decision independent of the configuration file itself, which stays identical across targets.
• A production deployment mounts or bakes in `dab-config.json` (with its connection string resolved via `@env()`) rather than hard-coding it into the container image, keeping the same image usable across environments by only changing environment variables.

Change-handling patterns

• Change Data Capture (CDC) records inserts/updates/deletes from the transaction log into change tables that downstream consumers can poll — built for capturing a full stream of row-level changes for ETL or auditing without adding triggers to the source tables.
• Change Tracking is lighter-weight than CDC: it tracks *which* rows changed (and a version number) but not the historical values or every intermediate change, suited to sync scenarios that only need "what's changed since I last checked," not a full change history.
• Change Event Streaming (CES) publishes row-level changes as events to a streaming destination (like Event Hubs), letting other services react to a change in near-real time rather than polling change tables on an interval — the event-driven counterpart to CDC's log-based capture.
• Azure Functions SQL trigger binding invokes a function automatically when a watched table changes, and Azure Logic Apps connectors similarly react to database changes to drive a low-code workflow — both are consumer-side ways to act on a change, layered on top of one of the change-capture mechanisms above.

Observability: Azure Monitor, Application Insights, and Log Analytics

• Azure Monitor collects platform-level metrics and diagnostic logs from the database and any surrounding Azure resources; Application Insights adds distributed tracing and request telemetry when an application (including a DAB-hosted API) is instrumented; Log Analytics is the query layer (KQL) over both, used for everything from an ad hoc investigation to a saved alert rule.
• Wiring diagnostic settings on the database (and on the DAB hosting resource) to send data to a Log Analytics workspace is the setup step that has to happen *before* any of this telemetry exists to query — a common gap that leaves an incident with no historical data to investigate.

Common confusion

• CDC and Change Tracking are both change-capture features and are easy to reach for interchangeably, but they answer different questions: Change Tracking tells you *that* a row changed (and to what version), fitting a sync scenario that only needs the current state, while CDC tells you the actual historical sequence of values a row went through, which is what a downstream system needs if it has to replay or audit every individual change rather than just catch up to the latest state.