This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Implement data security and compliance
      --> Design and implement data encryption, including Always Encrypted and column-level encryption

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Data encryption is one of the most important security capabilities available in Microsoft SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Microsoft Fabric SQL Database. Encryption helps protect sensitive information such as personally identifiable information (PII), financial records, healthcare data, passwords, and confidential business information from unauthorized access.

For the DP-800: Developing AI-Enabled Database Solutions exam, you should understand not only how to implement encryption, but also when to use different encryption technologies, their limitations, performance implications, and how they interact with applications.

Why Data Encryption Matters

Modern organizations must comply with regulations such as:

• GDPR
• HIPAA
• PCI-DSS
• SOC 2
• ISO 27001

Encryption protects data against:

• Database theft
• Unauthorized administrators
• Lost backups
• Insider threats
• Network interception

SQL Server provides multiple encryption technologies, each solving a different security problem.

SQL Server Encryption Technologies

Understanding which technology solves which problem is critical for the exam.

Data at Rest vs Data in Transit vs Data in Use

A common exam objective is understanding these three states.

Data at Rest

Data stored on:

• MDF files
• LDF files
• Backups
• Storage disks

Protected using:

• TDE
• Column encryption
• Always Encrypted

Data in Transit

Data traveling:

• Client → SQL Server
• SQL Server → Application

Protected using:

• TLS (SSL)

Data in Use

Data currently being processed inside memory.

Only Always Encrypted protects sensitive data while SQL Server is processing queries.

Transparent Data Encryption (TDE)

Although the objective focuses on Always Encrypted and column-level encryption, you should understand how TDE differs.

TDE encrypts:

• Database files
• Log files
• Backups

Advantages:

• No application changes
• Easy to enable
• Minimal performance overhead

Limitations:

• SQL Server decrypts data automatically.
• Database administrators can still read data.

TDE protects storage—not the data itself from privileged users.

Column-Level Encryption

Column-level encryption encrypts specific columns inside a table.

Example:

CreditCardNumber
SocialSecurityNumber
Salary

Instead of encrypting the whole database, only selected columns are encrypted.

How Column-Level Encryption Works

SQL Server uses encryption functions such as:

• ENCRYPTBYKEY
• DECRYPTBYKEY
• ENCRYPTBYPASSPHRASE
• DECRYPTBYPASSPHRASE

Example:

OPEN SYMMETRIC KEY CustomerKey
DECRYPTION BY CERTIFICATE CustomerCert;
UPDATE Customers
SET SSN =
ENCRYPTBYKEY(KEY_GUID('CustomerKey'), '123-45-6789');

Reading data:

SELECT
CONVERT(varchar,
DECRYPTBYKEY(SSN))
FROM Customers;

Encryption Hierarchy

SQL Server uses multiple encryption layers.

Service Master Key
        ↓
Database Master Key
        ↓
Certificate
        ↓
Symmetric Key
        ↓
Encrypted Column

Each level protects the one below it.

Symmetric Encryption

Uses one key for both:

• Encryption
• Decryption

Advantages

• Fast
• Efficient
• Best for large datasets

Example

Encrypt → Key A
Decrypt → Key A

Asymmetric Encryption

Uses:

• Public key
• Private key

Advantages

• Strong security
• Digital signatures

Disadvantages

• Slower

Usually used to protect symmetric keys.

Certificates

Certificates often protect symmetric keys.

Example:

Certificate
↓
Protects Symmetric Key
↓
Encrypts Customer Data

Always Encrypted

Always Encrypted is one of the most important DP-800 topics.

Unlike traditional encryption:

SQL Server never sees the plaintext values.

Encryption occurs inside the client application.

Why Always Encrypted Exists

Imagine a database administrator with full access.

With normal encryption:

• DBA can decrypt data.

With Always Encrypted:

• DBA cannot read encrypted values.

Only authorized client applications possess the encryption keys.

How Always Encrypted Works

Application
↓
Encrypt value
↓
SQL Server stores ciphertext
↓
Application retrieves ciphertext
↓
Application decrypts

SQL Server never performs decryption.

Benefits

Protects against:

• Curious administrators
• Database theft
• Backup theft
• Cloud administrators
• Insider attacks

Key Components

Always Encrypted uses two key types.

Column Master Key (CMK)

Stored outside SQL Server.

Examples:

• Windows Certificate Store
• Azure Key Vault
• Hardware Security Module (HSM)

Purpose:

Protects Column Encryption Keys.

Column Encryption Key (CEK)

Stored inside SQL Server.

Purpose:

Encrypts actual column values.

Hierarchy:

CMK
↓
CEK
↓
Encrypted Data

Deterministic Encryption

Always produces the same ciphertext for identical values.

Example

"Florida"
↓
A91BCD
"Florida"
↓
A91BCD

Advantages

Supports:

• Equality searches
• Joins
• GROUP BY
• Indexes

Disadvantages

Repeated values are recognizable.

Randomized Encryption

Produces different ciphertext every time.

Example

Florida
↓
A91BCD
Florida
↓
XYZ123

Advantages

Maximum security.

Disadvantages

Cannot perform:

• Equality comparisons
• JOIN
• GROUP BY
• Index lookups

Deterministic vs Randomized

Creating a Column Master Key

Example:

CREATE COLUMN MASTER KEY CMK1
WITH
(
KEY_STORE_PROVIDER_NAME =
'MSSQL_CERTIFICATE_STORE',
KEY_PATH =
'CurrentUser/My/123456789'
);

Creating a Column Encryption Key

CREATE COLUMN ENCRYPTION KEY CEK1
WITH VALUES
(
COLUMN_MASTER_KEY = CMK1,
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = ...
);

Encrypting a Column

CREATE TABLE Customers
(
CustomerID INT,
SSN CHAR(11)
COLLATE Latin1_General_BIN2
ENCRYPTED WITH
(
COLUMN_ENCRYPTION_KEY = CEK1,
ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM =
'AEAD_AES_256_CBC_HMAC_SHA_256'
)
);

Secure Enclaves

Always Encrypted originally limited many SQL operations.

Secure Enclaves improve functionality by allowing protected computations within a secure hardware-based memory region.

Benefits:

• Richer comparisons
• Pattern matching
• Range queries
• In-place encryption
• Better performance

Limitations of Always Encrypted

Developers should understand these limitations.

Not all SQL operations are supported.

Some restrictions include:

• LIKE (without enclaves)
• Pattern matching
• Sorting randomized columns
• Range comparisons
• Certain aggregates
• Some conversions

Client Driver Requirements

Always Encrypted requires supported drivers.

Examples:

• Microsoft.Data.SqlClient
• .NET Framework
• ODBC Driver
• JDBC Driver

Client drivers perform:

• Encryption
• Decryption
• Key retrieval

Azure Key Vault Integration

A common enterprise deployment stores Column Master Keys inside Azure Key Vault.

Benefits:

• Centralized key management
• Hardware-backed security
• Automatic auditing
• Key rotation
• Separation of duties

Performance Considerations

Always Encrypted introduces overhead because:

• Client encrypts data
• Client decrypts data
• Keys must be managed
• Network payloads increase

However, it provides much stronger protection than standard encryption.

Best Practices

Microsoft recommends:

• Encrypt only sensitive columns.
• Store CMKs outside SQL Server.
• Use Azure Key Vault when possible.
• Use deterministic encryption only when querying is required.
• Use randomized encryption for maximum confidentiality.
• Rotate encryption keys regularly.
• Use TLS together with Always Encrypted.
• Monitor application performance after enabling encryption.
• Test query compatibility before production deployment.

DP-800 Exam Tips

Be prepared to distinguish:

• TDE vs Always Encrypted
• Column-Level Encryption vs Always Encrypted
• Deterministic vs Randomized encryption
• CMK vs CEK
• Encryption at rest vs in transit vs in use
• Azure Key Vault integration
• Secure Enclaves
• Encryption hierarchy
• Performance implications
• Client-side versus server-side encryption

Practice Exam Questions

Question 1

A company wants to ensure that database administrators cannot view customers’ Social Security numbers while still allowing applications to access the data. Which encryption technology should be implemented?

A. Transparent Data Encryption (TDE)

B. Dynamic Data Masking

C. Row-Level Security

D. Always Encrypted

Answer: D

Explanation: Always Encrypted performs encryption and decryption on the client side, preventing SQL Server and database administrators from viewing plaintext data.

Question 2

Which key encrypts the actual column data in Always Encrypted?

A. Column Encryption Key

B. Database Master Key

C. Service Master Key

D. Column Master Key

Answer: A

Explanation: The Column Encryption Key (CEK) encrypts the column values. The Column Master Key (CMK) protects the CEK.

Question 3

Which encryption type should you choose if users must frequently search by exact Social Security number?

A. Randomized encryption

B. Transparent Data Encryption

C. Deterministic encryption

D. Dynamic Data Masking

Answer: C

Explanation: Deterministic encryption produces the same ciphertext for identical values, enabling equality searches and index usage.

Question 4

Which SQL Server feature encrypts entire database files and backup files without requiring application changes?

A. Always Encrypted

B. Column-Level Encryption

C. Dynamic Data Masking

D. Transparent Data Encryption

Answer: D

Explanation: Transparent Data Encryption (TDE) encrypts database and backup files, protecting data at rest.

Question 5

Where is the Column Master Key typically stored?

A. Azure Storage Account

B. SQL Server system database

C. TempDB

D. Azure Key Vault or Windows Certificate Store

Answer: D

Explanation: Microsoft recommends storing Column Master Keys outside SQL Server, commonly in Azure Key Vault or the Windows Certificate Store.

Question 6

Which encryption method provides the highest confidentiality for sensitive columns?

A. Deterministic encryption

B. Randomized encryption

C. Transparent Data Encryption

D. TLS encryption

Answer: B

Explanation: Randomized encryption produces different ciphertext for identical values, making frequency analysis much more difficult.

Question 7

A developer wants to encrypt only the CreditCardNumber column while leaving the remainder of the table unchanged. Which approach is most appropriate?

A. Column-Level Encryption

B. Transparent Data Encryption

C. Database snapshots

D. Always On Availability Groups

Answer: A

Explanation: Column-level encryption targets individual columns rather than the entire database.

Question 8

Which SQL Server feature enhances Always Encrypted by allowing additional query operations on encrypted columns?

A. Secure Enclaves

B. Dynamic Data Masking

C. PolyBase

D. Stretch Database

Answer: A

Explanation: Secure Enclaves enable richer computations on encrypted data, including some range and pattern-matching operations.

Question 9

Which data state is protected by TLS encryption?

A. Data at rest

B. Data in transit

C. Data in use

D. Archived data

Answer: B

Explanation: TLS encrypts network communications between clients and SQL Server, protecting data while it is being transmitted.

Question 10

Why is Always Encrypted considered more secure than traditional column-level encryption?

A. It automatically compresses encrypted data.

B. It encrypts entire databases.

C. SQL Server never has access to plaintext values because encryption occurs on the client side.

D. It eliminates the need for encryption keys.

Answer: C

Explanation: Always Encrypted keeps encryption keys and plaintext data outside SQL Server, ensuring that even highly privileged users cannot view sensitive information.

Go to the DP-800 Exam Prep Hub main page