Domain 2 of 5 · Chapter 3 of 3

Implement compliance controls for sensitive data

Unlock the complete study guide + 25 practice exams totaling 1,500 questions.

Bundled into the existing Administering Microsoft Azure SQL Solutions premium course — no separate purchase.

14-day refund guarantee on one-time purchases and your first subscription charge.

Included in this chapter:

  • Compliance controls for sensitive data
  • Classifying columns: recommendations and T-SQL
  • How classification feeds audit records
  • Azure SQL Database auditing: server and database policies
  • Choosing an audit destination and retention
  • What audit delivery depends on
  • Reading Azure SQL audit evidence
  • Microsoft Defender for SQL is not an audit trail
  • SQL Server Audit: the audit and its specifications
  • Audit targets and the Managed Instance URL
  • Audit failure policy: ON_FAILURE
  • Audit permissions and the trust boundary
  • Choosing a change mechanism
  • Change tracking: enable, retain, and validate
  • Consuming change tracking with CHANGETABLE
  • Change data capture: capture instances and change tables
  • Reading CDC changes over a valid LSN range
  • Tuning the CDC capture and cleanup jobs
  • CDC in Azure SQL Database
  • Change data through restores and failover
  • Permissions for change tracking and CDC readers
  • Temporal history retention
  • Dynamic data masking: masking functions
  • UNMASK and who sees original values
  • Where a mask can be defined
  • Row-level security: filter and block predicates
  • Security policies and predicate functions
  • Tenant context under RLS: SESSION_CONTEXT
  • Administrators under RLS
  • What RLS does not filter
  • Hardening RLS predicates
  • Ledger tables: updatable or append-only
  • Ledger digests and verification
  • Ledger is a one-way configuration
  • Requirements and the compliance controls that meet them

Seven controls and the question each one answers

ControlQuestion it answersWhat it acts onWhat it does not do
Data Discovery & ClassificationWhich columns hold sensitive data?Column metadata: sensitivity label and information typeBlock, mask, or encrypt anything
AuditingWho did what, and when?Selected events written to an audit destinationPrevent actions under SQL Server Audit's default ON_FAILURE = CONTINUE
Change trackingWhich rows changed since my last sync?Primary keys and change metadata, recorded synchronouslyKeep old values or record who changed a row
Change data captureWhat values changed, including intermediate ones?Change tables filled asynchronously from the transaction logRecord who changed a row
Dynamic data maskingHow much of a value may this reader see?Query results for users without UNMASKChange stored data or stop administrators
Row-level securityWhich rows may this user read or write?Rows, through filter and block predicatesObscure columns in rows it returns
LedgerCan I prove the data was not tampered with?Hashed transactions plus digests stored outside the databaseControl who reads data, or prevent file-level tampering

Decision tree

Find and labelsensitive columns?Data Discovery& ClassificationYesNoRecord who did what,and when?AuditingYesNoLimit which rows auser reads or writes?Row-levelsecurityYesNoObscure values inquery results?Dynamic datamaskingYesNoProve data wasnot tampered with?LedgerYesNoSync changes: needsold and new values?Change datacaptureYesChange trackingNo

Cheat sheet

  • Sensitive-column discovery proposes classifications rather than automatically enforcing protection
  • T-SQL classification supports repeatable labeling with narrowly delegated permissions
  • Column classifications enrich audit interpretation only when database activity is also audited
  • Server audit policies cover existing and future databases and are not overridden by database policies
  • Audit storage, analysis, and streaming requirements can justify different destinations
  • An enabled audit policy still needs a working destination delivery configuration
  • Audit identity evidence depends on the authentication stage and effective database principal
  • A healthy Azure SQL audit destination does not establish lossless full-text capture
  • Defender findings and threat alerts complement rather than replace SQL audit evidence
  • A server audit supplies the destination while audit specifications select the events
  • Managed Instance auditing to a storage URL needs a credential for that container
  • Native SQL Server audit targets require destination-specific operating-system authorization
  • An audit configured with ON_FAILURE = SHUTDOWN can make SQL Server unavailable
  • Delegated audit management remains inside the audit's trust boundary
  • Change tracking is appropriate for current-state synchronization rather than full value history
  • Change tracking requires database enablement before individual table enablement
  • A change-tracking consumer must reinitialize when its synchronization version is too old
  • Temporal history cleanup requires both table retention and database-level enablement
  • Change-tracking consumption must preserve complete, retryable version batches
  • Snapshot isolation gives change tracking a consistent multi-step synchronization view
  • CDC exposes log-derived row images through a capture instance rather than synchronous change-tracking metadata
  • Change tracking and CDC require feature-specific permissions beyond ordinary table reads
  • A CDC extraction interval must fit within the retained and already captured LSN range
  • CDC capture and cleanup jobs must operate on the availability-group primary after failover
  • Live change-tracking enumeration belongs on the availability-group primary
  • CDC enumeration can preserve individual changes or collapse an interval to its net effect
  • A cross-server SQL Server restore needs KEEP_CDC to preserve capture metadata
  • CDC administration must match Azure SQL Database's managed-service capabilities
  • Dynamic data masking reduces result exposure while leaving stored values unchanged
  • The masking function determines which parts of a value remain recognizable
  • UNMASK must cover the source data before a reader copies plaintext values
  • Dynamic masking is defined on eligible base columns rather than derived expressions
  • Updatable and append-only ledger tables support different application write models
  • Ledger verification relies on digests protected outside the database
  • Ledger configuration cannot be treated as a reversible switch on existing tables
  • RLS filters control visible rows while block predicates reject invalid writes
  • An RLS security policy binds predicate functions to the protected table
  • SESSION_CONTEXT can carry tenant identity only when the application establishes it safely
  • RLS applies to administrators unless the policy explicitly allows their access
  • RLS protection must be reviewed separately for history and change-data access
  • Reliable RLS isolation requires hardening beyond a normally filtered SELECT

Unlock with Premium — includes all practice exams and the complete study guide.

Also tested in

References

  1. https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-overview
  2. https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking
  3. https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security
  4. https://learn.microsoft.com/en-us/sql/relational-databases/security/ledger/ledger-overview
  5. https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
  6. https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine
  7. https://learn.microsoft.com/en-us/azure/azure-sql/database/data-discovery-and-classification-overview
  8. https://learn.microsoft.com/en-us/sql/t-sql/statements/add-sensitivity-classification-transact-sql
  9. https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-setup
  10. https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions
  11. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure
  12. https://learn.microsoft.com/en-us/azure/azure-monitor/platform/activity-log
  13. https://learn.microsoft.com/en-us/azure/azure-sql/database/connectivity-architecture
  14. https://learn.microsoft.com/en-us/azure/azure-sql/database/azure-defender-for-sql
  15. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-audit-specification-transact-sql
  16. https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql
  17. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-server-audit-transact-sql
  18. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-credential-transact-sql
  19. https://learn.microsoft.com/en-us/sql/relational-databases/security/ledger/ledger-digest-management
  20. https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine
  21. https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/track-data-changes-sql-server
  22. https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/change-event-streaming/overview
  23. https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal/manage-retention
  24. https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-tracking-sql-server
  25. https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server
  26. https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/work-with-change-tracking-sql-server
  27. https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/changetable-transact-sql
  28. https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server
  29. https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-data-capture-sql-server
  30. https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/work-with-change-data-sql-server
  31. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-table-transact-sql
  32. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-change-job-transact-sql
  33. https://learn.microsoft.com/en-us/azure/azure-sql/database/change-data-capture-overview
  34. https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/change-data-capture-and-other-sql-server-features
  35. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replicate-track-change-data-capture-always-on-availability
  36. https://learn.microsoft.com/en-us/azure/azure-sql/database/dynamic-data-masking-overview
  37. https://learn.microsoft.com/en-us/sql/relational-databases/triggers/dml-triggers
  38. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-session-context-transact-sql
  39. https://learn.microsoft.com/en-us/sql/relational-databases/security/ledger/ledger-limits
  40. https://learn.microsoft.com/en-us/sql/relational-databases/security/ledger/ledger-how-to-updatable-ledger-tables
  41. https://learn.microsoft.com/en-us/sql/relational-databases/security/ledger/ledger-how-to-append-only-ledger-tables
  42. https://learn.microsoft.com/en-us/sql/relational-databases/security/ledger/ledger-how-to-configure-ledger-database
  43. https://learn.microsoft.com/en-us/azure/confidential-ledger/overview