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
| Control | Question it answers | What it acts on | What it does not do |
|---|---|---|---|
| Data Discovery & Classification | Which columns hold sensitive data? | Column metadata: sensitivity label and information type | Block, mask, or encrypt anything |
| Auditing | Who did what, and when? | Selected events written to an audit destination | Prevent actions under SQL Server Audit's default ON_FAILURE = CONTINUE |
| Change tracking | Which rows changed since my last sync? | Primary keys and change metadata, recorded synchronously | Keep old values or record who changed a row |
| Change data capture | What values changed, including intermediate ones? | Change tables filled asynchronously from the transaction log | Record who changed a row |
| Dynamic data masking | How much of a value may this reader see? | Query results for users without UNMASK | Change stored data or stop administrators |
| Row-level security | Which rows may this user read or write? | Rows, through filter and block predicates | Obscure columns in rows it returns |
| Ledger | Can I prove the data was not tampered with? | Hashed transactions plus digests stored outside the database | Control who reads data, or prevent file-level tampering |
Decision tree
Cheat sheet
Unlock with Premium — includes all practice exams and the complete study guide.
Also tested in
References
- https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-overview
- https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking
- https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security
- https://learn.microsoft.com/en-us/sql/relational-databases/security/ledger/ledger-overview
- https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
- https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine
- https://learn.microsoft.com/en-us/azure/azure-sql/database/data-discovery-and-classification-overview
- https://learn.microsoft.com/en-us/sql/t-sql/statements/add-sensitivity-classification-transact-sql
- https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-setup
- https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure
- https://learn.microsoft.com/en-us/azure/azure-monitor/platform/activity-log
- https://learn.microsoft.com/en-us/azure/azure-sql/database/connectivity-architecture
- https://learn.microsoft.com/en-us/azure/azure-sql/database/azure-defender-for-sql
- https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-audit-specification-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql
- https://learn.microsoft.com/en-us/sql/t-sql/statements/create-server-audit-transact-sql
- https://learn.microsoft.com/en-us/sql/t-sql/statements/create-credential-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/security/ledger/ledger-digest-management
- https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine
- https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/track-data-changes-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/change-event-streaming/overview
- https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal/manage-retention
- https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-tracking-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/work-with-change-tracking-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/changetable-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-data-capture-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/work-with-change-data-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-table-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-change-job-transact-sql
- https://learn.microsoft.com/en-us/azure/azure-sql/database/change-data-capture-overview
- https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/change-data-capture-and-other-sql-server-features
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replicate-track-change-data-capture-always-on-availability
- https://learn.microsoft.com/en-us/azure/azure-sql/database/dynamic-data-masking-overview
- https://learn.microsoft.com/en-us/sql/relational-databases/triggers/dml-triggers
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-session-context-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/security/ledger/ledger-limits
- https://learn.microsoft.com/en-us/sql/relational-databases/security/ledger/ledger-how-to-updatable-ledger-tables
- https://learn.microsoft.com/en-us/sql/relational-databases/security/ledger/ledger-how-to-append-only-ledger-tables
- https://learn.microsoft.com/en-us/sql/relational-databases/security/ledger/ledger-how-to-configure-ledger-database
- https://learn.microsoft.com/en-us/azure/confidential-ledger/overview