Study guide

7.3 Row-Level Security and Ledger in Azure SQL

DP-300: Administering Microsoft Azure SQL Solutions Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources

2 Planning and Deploying Azure SQL Solutions

2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching

3 Configuring Resources for Scale and Performance

3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression

4 Planning and Implementing a Migration Strategy

4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting

5 Configuring Database Authentication and Authorization

5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management

6 Implementing Security for Data at Rest and in Transit

6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints

7 Implementing Compliance Controls for Sensitive Data

7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL

8 Monitoring Resource Activity and Performance

8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events

9 Monitoring and Optimizing Query Performance

9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights

10 Configuring Database Solutions for Optimal Performance

10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing

11 Creating and Managing SQL Server Agent Jobs

11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates

12 Automating Deployments and Database Tasks in Azure

12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks

13 Planning an HA/DR Strategy and Backup and Restore

13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations

14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups

14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups

15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting

15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions

7.3 Row-Level Security and Ledger in Azure SQL

Key Takeaways Explain This Topic AI

Row-Level Security uses a predicate function bound with SCHEMABINDING that returns 1 for allowed rows; filter predicates restrict reads, block predicates restrict writes (BEFORE/AFTER INSERT/UPDATE/DELETE)

The predicate function is invoked per row during scan or seek; keep it cheap, index the filtering column, and prefer a security predicate over a view when the table is queried directly

SESSION_CONTEXT lets an application set tenant context (sp_set_session_context) that the predicate reads via SESSION_CONTEXT(), enabling a single shared connection pool to enforce tenant isolation

Azure SQL Ledger comes in two flavors - append-only ledger tables (INSERT only) and updatable ledger tables (UPDATE/DELETE permitted with a generated history table) - both protected by hash chaining at the row and block level

Ledger integrity is verified by generating a digest (sys.sp_generate_ledger_digest), storing it outside the database (e.g., Azure Blob Storage), and running sys.sp_verify_ledger_digest against the stored digest to prove tamper-evidence

Last updated: August 2026

Why RLS and Ledger Appear Together

Both controls appear in the secure-environment domain because they protect trust : RLS protects trust that a user only sees rows they are entitled to, and Ledger protects trust that historical rows have not been silently modified. RLS is a runtime access control; Ledger is a cryptographic integrity control. Expect scenarios that ask when to choose RLS over a view, when to choose Ledger over a trigger-based audit, and how SESSION_CONTEXT interacts with connection pooling.

Row-Level Security (RLS)

Row-Level Security filters or blocks rows based on the calling user's identity or session context. It is implemented through three pieces: a predicate function , a security predicate that binds the function to a table, and a schema-bound function body that the optimizer can rely on.

Predicate Function

The predicate function is a schema-bound, inline table-valued function that returns 1 (allow) or 0 (deny). It is written against a filter column on the target table, typically joining to a security table or reading session context: CREATE SCHEMA Security;

GO

  • CREATE FUNCTION Security.fn_TenantPredicate (@TenantId int)
  • RETURNS TABLE
  • WITH SCHEMABINDING
  • AS
  • RETURN SELECT 1 AS fn_result
  • WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS int);
  • GO

The WITH SCHEMABINDING clause is required so the function cannot be altered while bound to a security predicate, preventing the predicate from silently changing shape.

Security Predicates: Filter vs Block

The security predicate binds the function to a table and declares the predicate type: CREATE SECURITY POLICY Security.TenantFilterPolicy

  • ADD FILTER PREDICATE Security.fn_TenantPredicate(TenantId) ON dbo.Orders,
  • ADD BLOCK PREDICATE Security.fn_TenantPredicate(TenantId) ON dbo.Orders
  • WITH (STATE = ON);

Two predicate types matter for the exam:

Predicate Applied to Effect

FILTER SELECT, UPDATE, DELETE (read path) Rows that fail the predicate are invisible to the user; the user never sees them in results

BLOCK INSERT, UPDATE, DELETE (write path) Operations that would create or move a row to a state that fails the predicate are rejected

Block predicates have an additional timing modifier: BEFORE (default for INSERT, UPDATE, DELETE) checks the post-operation row state before the change is committed; AFTER (default for UPDATE and DELETE) checks the pre-operation state, preventing a user from deleting or moving a row they no longer should be able to see. The full set is BEFORE INSERT , AFTER INSERT , BEFORE UPDATE , AFTER UPDATE , BEFORE DELETE , AFTER DELETE . A common trap is to add only a FILTER predicate; without a BLOCK predicate, a user can INSERT a row into another tenant's id, so block predicates are required for true isolation.

Group-Based vs Tenant-Based Filtering

The predicate function can read from a security table to support group-based filtering, or read session context for tenant-based filtering. Group-based filtering queries the user's group membership and joins to the table's allowed-group column; tenant-based filtering reads a session variable that the application sets. The exam pattern:

Group-based : predicate reads USER_NAME() or SUSER_SNAME() , joins to a Security.GroupMembership table, returns rows where the user's group matches the row's OwnerGroup . Requires no application change; trusts the database user identity.

Tenant-based : predicate reads SESSION_CONTEXT(N'TenantId') . Requires the application to set the context after each connection open, but lets a connection pool be shared across tenants safely because the predicate is evaluated per session.

SESSION_CONTEXT and Connection Pooling

SESSION_CONTEXT is a key-value bag scoped to the session. The application sets the tenant id immediately after opening a connection: EXEC sp_set_session_context @key = N'TenantId', @value = 42, @read_only = 1;

The @read_only = 1 flag prevents the value from being changed mid-session, closing a bypass where a user resets the tenant id after the predicate is bound. The predicate function reads via SESSION_CONTEXT(N'TenantId') . Because pool returns a reset session, the application must set the context on every checkout - the most common bug the exam tests is forgetting to set context on a pooled connection and getting empty result sets.

Performance Considerations

The predicate function is evaluated for each row the engine considers during a scan or seek. Performance rules:

Keep the predicate cheap. Avoid complex joins inside the function; cache lookups in a security table indexed on the join column.

Index the filter column on the protected table so the predicate can be pushed down into an index seek.

Avoid calling SESSION_CONTEXT multiple times per row; assign to a local variable inside the function.

Use a security table with the user's allowed keys, and join it rather than calling user functions per row.

Predicate functions are not invoked when the caller is a member of the db_owner fixed database role or has the CONTROL permission on the table - RLS is bypassed for these principals, which the exam tests as a trap when a scenario describes a DBA who can see all rows despite RLS being enabled.

RLS Limitations and Traps

RLS does not protect against side-channel inference: a user can SELECT COUNT(*) FROM Orders and observe filtered totals, so leak protection requires additional controls if row counts are sensitive.

Schema-bound functions cannot be altered without dropping the security policy first.

A FILTER predicate alone does not prevent cross-tenant INSERT; you must add a BLOCK predicate.

RLS does not apply to members of sysadmin server role or db_owner .

Index-backed predicates can still scan if the predicate cannot be converted to a seek; monitor execution plans.

Azure SQL Ledger

Azure SQL Ledger is a tamper-evident feature that cryptographically chains database rows so any modification or deletion is detectable. It is available in Azure SQL Database (and SQL MI in the preview/GA track you should verify against current docs at exam time) and provides database-level integrity without trusting the database administrator.

Append-Only vs Updatable Ledger Tables

Two table flavors:

  • Property Append-only ledger table Updatable ledger table
  • Allowed DML INSERT only INSERT, UPDATE, DELETE
  • History table Not required; rows cannot change Generated automatically; stores prior values on UPDATE/DELETE

Generated columns ledger_start_transaction_id , ledger_start_sequence_number Same plus ledger_end_transaction_id , ledger_end_sequence_number

Trust model Once written, the row is immutable Any change is captured in history and provable via hashing

Use case Audit logs, blockchain-style records, immutable compliance events Financial transactions, supply-chain records that evolve but must remain provable

  • Create them with syntax in the CREATE TABLE statement: CREATE TABLE dbo.AuditEvent
  • (
  • EventId bigint IDENTITY PRIMARY KEY,
  • EventType nvarchar(50) NOT NULL,
  • EventTime datetime2 NOT NULL,
  • Payload nvarchar(max) NULL
  • )
  • WITH (LEDGER = ON (APPEND_ONLY = ON));
  • CREATE TABLE dbo.Transfer
  • (
  • TransferId bigint IDENTITY PRIMARY KEY,
  • Account int NOT NULL,
  • Amount decimal(18,2) NOT NULL
  • )
  • WITH (LEDGER = ON (APPEND_ONLY = OFF));
  • Database-Level Ledger and Hash Chaining

Behind every ledger table sits the database-level ledger : the engine computes a row hash from the row contents plus the previous row's hash (forming a per-transaction row chain), then a block hash that combines the row hashes of all rows committed in the same transaction plus the previous block hash. The block hashes form a Merkle-style chain; breaking any row changes its hash, which breaks the block hash, which breaks every subsequent block.

Because the chain is rooted inside the database, an attacker with DBA rights could in principle recompute the chain after tampering. To close that gap, Azure SQL Ledger produces a digest - a snapshot of the latest block hash - which you store outside the database (Azure Blob Storage, an on-prem file, even a printed copy). Storing the digest externally breaks the trust loop: tampering with the database no longer allows recomputing a valid chain because the externally stored digest will not match.

  • Generating and Verifying Digests
  • Generate a digest on a schedule (e.g., daily) and persist it off-box: -- Generate the current digest
  • EXEC sys.sp_generate_ledger_digest;
  • - Verify against an externally stored digest
  • EXEC sys.sp_verify_ledger_digest
  • @ledger_name = 'default',
  • @hash = 0xABC123...; -- the previously stored digest

sp_verify_ledger_digest recomputes the chain from the ledger tables up to the block recorded in the supplied digest and compares. If any row was modified after the digest was generated, the verification fails - the row hash, the block hash, and every subsequent hash no longer match the digest, proving tamper. On success, the procedure confirms the database state is consistent with the digest.

A practical workflow:

Schedule sys.sp_generate_ledger_digest daily; write the result (a JSON document with the database ledger hash and block id) to Azure Blob Storage with immutable WORM policy.

On demand (audit, investigation, or scheduled), retrieve the most recent digest from Blob and call sys.sp_verify_ledger_digest to confirm the chain is intact.

If verification fails, the ledger has been tampered with; investigate which rows differ.

Ledger Tables vs Regular Tables

Ledger tables have additional generated columns (the ledger_* family) and a hidden internal history table (for updatable ledgers). Regular tables have none of these. You cannot convert a regular table to a ledger table with ALTER TABLE in the way you can toggle compression - the table is created as a ledger table from the start, or you migrate data into a new ledger table. The ledger columns appear when you select from the table or its associated history view; applications that select * will see them, so production code should name columns explicitly.

When to Use Ledger

Use Azure SQL Ledger when a scenario demands tamper-evidence - proof that historical records have not been modified by anyone, including privileged administrators. Examples:

Financial transaction records subject to SOX or regulatory audit.

Supply-chain provenance records.

Audit logs themselves (log the auditor's actions into an append-only ledger so the audit trail cannot be rewritten).

Compliance records where a regulator requires cryptographically verifiable integrity.

Do not use ledger when you only need to detect who read or changed data - that is auditing. Do not use ledger when you only need to hide data from unprivileged users - that is RLS or DDM. Ledger is the right answer when the scenario's word is tamper-evidence , immutability , or cryptographic proof of integrity .

Takeaways

RLS = per-row access control through predicate functions; always include both filter and block predicates for true isolation, use SESSION_CONTEXT for tenant-based filtering on pooled connections, and remember db_owner bypasses RLS. Ledger = cryptographic tamper-evidence through append-only or updatable tables with hashes chained in a database-level ledger; generate digests, store them externally, and verify with sys.sp_verify_ledger_digest . Choose RLS for visibility, Ledger for provability, and use both when a scenario demands both tenant isolation and immutable history.