ExamFinch.com My Study

Study guide

6.1 Transparent Data Encryption and Object-Level Encryption

Practice this exam

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

6.1 Transparent Data Encryption and Object-Level Encryption

Key Takeaways Explain This Topic AI

Transparent Data Encryption (TDE) encrypts Azure SQL Database, SQL Managed Instance, and SQL Server data and log files at the page level with a database encryption key (DEK); on Azure SQL Database it is enabled by default on every new database

Service-managed TDE uses a Microsoft-held certificate; customer-managed TDE (BYOK) stores the TDE Protector in Azure Key Vault, giving you control of key rotation and revocation

Geo-restore and geo-replication require the TDE Protector to be accessible in the target region; with customer-managed TDE you must replicate the key vault to the secondary region or the secondary database cannot come online

Object-level (cell-level) encryption via EncryptByCert/EncryptByPassPhrase is application-driven and distinct from TDE; Always Encrypted moves key management entirely to the client driver, so the server never sees plaintext or keys

Last updated: August 2026

Why TDE Matters for DP-300

Transparent Data Encryption (TDE) encrypts data and log files at rest at the page level : every 8 KB page is encrypted before it is written to disk and decrypted when it is read into memory, transparently to the application. TDE protects against the physical media or storage snapshot being read outside the database engine, which is the primary data-at-rest threat model for cloud databases. For DP-300, expect scenario questions on enabling TDE, choosing key models, and understanding the boundary between TDE, object-level encryption, and Always Encrypted.

On Azure SQL Database , TDE is enabled by default on every newly created database (this default changed in recent years, and many legacy exam answers still describe TDE as opt-in - the current behavior is on-by-default). You cannot turn TDE off on a database once it is in a geo-replication relationship.

DEK, Server Certificate, and the TDE Protector

TDE uses a two-tier key hierarchy. At the leaf is the database encryption key (DEK) , a symmetric key stored inside the database (in sys.symmetric_keys ). The DEK actually encrypts the data pages. The DEK itself is protected by the TDE Protector , which is either a certificate in the master database or, for customer-managed TDE, an asymmetric key in Azure Key Vault . The protector is the trust root; whoever holds the protector can decrypt the DEK and therefore the database.

Two key management models apply on Azure SQL Database:

Model TDE Protector location Rotation control Revocation Use case

Service-managed Internal Microsoft certificate (auto-rotated ~90 days) Microsoft Not supported Default, lowest overhead

Customer-managed (BYOK) Azure Key Vault asymmetric key Customer, on demand Yes - key vault can disable the key Compliance, separation of duties

With customer-managed TDE (BYOK) , the database reaches into Azure Key Vault to unwrap the DEK at startup and periodically rewraps it. The Key Vault can be Microsoft-hosted or managed HSM , and the key can be RSA 2048 or RSA 3072 (and RSA 4096 on supported SKUs). Disabling or deleting the TDE Protector in the vault makes the database inaccessible - this is the revocation lever, and the exam may frame it as a data-breach containment step.

Key Rotation and Geo-Replication Implications

Two rotations exist and the exam tests the distinction:

DEK rotation regenerates the symmetric DEK and re-encrypts every page in the database - a size-of-data operation. It is rarely necessary and is not done automatically.

TDE Protector rotation rewraps the DEK with a new protector (a new Key Vault key or certificate). It is a metadata-only operation that takes seconds, regardless of database size. With customer-managed TDE you can rotate the protector on demand through the portal, CLI, or REST.

The geo-replication gotcha is heavily tested. A geo-secondary or geo-restored copy of a database needs the same TDE Protector. With service-managed TDE , the protector is regional and the geo-secondary is reachable automatically. With customer-managed TDE , the key vault that holds the TDE Protector must be available in the secondary region - either a vault replicated there or a separate vault holding the same key material. If you revoke the key in the primary region and the secondary cannot reach a vault with the key, the secondary stops serving. For geo-restore of a customer-managed TDE backup, the target server must have access to the same Key Vault key.

TDE on SQL Managed Instance and SQL Server on VMs

On SQL Managed Instance , TDE is available (and on by default for new databases, mirroring Azure SQL Database) and uses the same BYOK-through-Key-Vault flow. On SQL Server on Azure VMs and on-premises SQL Server, TDE is off by default and you enable it manually using the on-box workflow: create a master key in the master database, create or import a server certificate protected by the master key, then enable TDE: USE master;

  • CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPwd!';
  • CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';
  • USE SalesDB;
  • CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECert;
  • ALTER DATABASE SalesDB SET ENCRYPTION ON;

For EKM (Extensible Key Management) or Azure Arc-enabled SQL Server, the protector can be a Key Vault key instead of a local certificate. Azure Arc-enabled SQL Server extends cloud-based BYOK to on-premises and edge SQL instances: the server registers with Arc, a Key Vault-backed protector is wired through the EKM provider, and rotation/revocation behavior mirrors Azure SQL Database.

Disabling TDE means the engine decrypts every page as a size-of-data operation - plan for I/O load. A common exam trap is to assume ALTER DATABASE ... SET ENCRYPTION OFF is instant; it is not. Also, tempdb is automatically encrypted when any user database has TDE on, which can affect capacity planning for VMs.

Object-Level (Cell-Level) Encryption

Where TDE encrypts the whole database file, object-level encryption (sometimes called cell-level encryption) encrypts specific columns within a row using functions the application calls explicitly:

EncryptByCert(cert_id, plaintext) - encrypts with a certificate's public key; decryption needs the private key.

EncryptByPassPhrase('passphrase', plaintext) - derives a symmetric key from a passphrase.

EncryptByKey(key_guid, plaintext) - uses a symmetric key opened in the session.

These functions require certificate and symmetric key management inside the database (CREATE CERTIFICATE, BACKUP CERTIFICATE, CREATE SYMMETRIC KEY), and the application must call the encrypt/decrypt functions on every read and write. Plaintext never persists unless you write it. The drawbacks: queries cannot search encrypted columns without a deterministic pattern, indexes on encrypted columns are not range-searchable, and application logic changes are invasive.

TDE vs Object-Level vs Always Encrypted

The exam frequently contrasts the three models:

  • Property TDE Object-level encryption Always Encrypted
  • Scope Whole database (pages) Specific cells Specific columns
  • Where encryption happens Database engine Database engine (function call) Client driver, before sending
  • Server sees plaintext? Yes Yes (when functions are bypassed) No

Application change required None Yes - call encrypt/decrypt functions Yes - column master key configuration, parameterized queries

Protects in-memory data? No Yes, the persisted ciphertext only Yes, ciphertext only at server

The crucial boundary: with TDE and object-level encryption , the encryption happens inside the engine, so a privileged SQL login or anyone with access to the buffer pool or a memory dump can read plaintext. Always Encrypted pushes encryption to the client driver : the column master key never lives on the server, and the server only ever receives and stores ciphertext. That is the defining security property the exam tests - choose Always Encrypted when the threat model assumes a compromised or untrusted database administrator.