Study guide

11.1 Azure SQL Database Security & Always Encrypted

Microsoft Azure Security Technologies AZ-500

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Exam Overview & Azure Security Architecture Foundation

1.1 Azure Security Engineer Role & Exam Logistics 1.2 Microsoft Cybersecurity Reference Architecture & Zero Trust Principles

2 Chapter 2: Secure Identity and Access - Authentication, MFA & PIM

2.1 Microsoft Entra ID Security, Users, and Groups 2.2 Authentication Methods, MFA, and Conditional Access Policy Design 2.3 Microsoft Entra Privileged Identity Management (PIM)

3 Chapter 3: Secure Identity and Access - RBAC & Workload Identity

3.1 Azure RBAC, Custom Roles, and Governance 3.2 Workload Identities and Managed Identities 3.3 External Identities and Cross-Tenant Collaboration

4 Chapter 4: Microsoft Entra Application Access & Consent Governance

4.1 Enterprise Applications, OAuth Permission Grants & Consent Governance 4.2 App Registrations, Permission Scopes & Service Principal Credentials

5 Chapter 5: Secure Azure Networking - Boundary & Perimeter Protection

5.1 Virtual Network Security & Micro-segmentation 5.2 Azure Firewall and Firewall Manager 5.3 Web Application Firewall (WAF) & Azure Front Door

6 Chapter 6: Secure Azure Networking - Private Access & Monitoring

6.1 Private Connectivity with Private Endpoints & Private Link 6.2 Azure Bastion & Remote Management Security 6.3 Network Perimeter Protection & Flow Monitoring

7 Chapter 7: Network Architecture, Routing & Hybrid Connectivity

7.1 Azure Virtual Network Manager, Network Groups & User-Defined Routes 7.2 VPN Gateway, ExpressRoute Encryption & Virtual WAN Secured Hub 7.3 Network Integration for App Service, Functions, App Service Environment & SQL Managed Instance

8 Chapter 8: Public and Edge Access Protection

8.1 Azure Application Gateway Security, TLS Policy & Mutual Authentication 8.2 Azure Front Door, CDN Caching & TLS for App Service and API Management

9 Chapter 9: Compute, Container & Registry Security

9.1 Azure Virtual Machine Hardening & Host Security 9.2 Azure Key Vault Deep Dive & Secrets Lifecycle 9.3 Container & Azure Kubernetes Service (AKS) Security 9.4 Azure Container Instances, Container Apps & Registry Access Control

10 Chapter 10: Storage Security, Access Methods & Data Protection

10.1 Storage Account Security & Access Controls 10.2 Selecting and Configuring Access to Azure Files 10.3 Selecting and Configuring Access to Azure Blob Storage 10.4 Storage Data Protection: Soft Delete, Versioning, Point-in-Time Restore & Backup

11 Chapter 11: Data Platform & Database Security

11.1 Azure SQL Database Security & Always Encrypted 11.2 Database Auditing & Vulnerability Assessment 11.3 Data Discovery, Classification, & Microsoft Purview Integration

12 Chapter 12: Cloud Governance, Key Lifecycle & Asset Protection

12.1 Azure Policy for Security Governance: Definitions, Initiatives, Effects & Remediation 12.2 Key Vault Backup, Recovery & Automated Key Rotation 12.3 Implementing Security Controls to Protect Backups 12.4 Security Controls for Asset Management: Locks, Tags, Resource Graph & Azure Arc

13 Chapter 13: Microsoft Defender for Cloud - Posture & Workload Protection

13.1 Microsoft Defender for Cloud & Secure Score 13.2 Defender Cloud Workload Protection (CWP) Plans 13.3 Defender for Cloud Inventory, Compliance Standards & Custom Standards 13.4 Connecting AWS and GCP Environments to Microsoft Defender for Cloud

14 Chapter 14: External Attack Surface & Vulnerability Management

14.1 Microsoft Defender External Attack Surface Management (EASM) 14.2 Agentless Scanning & Microsoft Defender Vulnerability Management for Azure VMs 14.3 Defender for Cloud DevOps Security: GitHub, Azure DevOps & GitLab

15 Chapter 15: Security Monitoring, Microsoft Sentinel & Automation

15.1 Azure Monitor Data Collection Rules & Network Security Event Monitoring 15.2 Microsoft Sentinel Data Connectors, Content Hub & Normalization 15.3 Microsoft Sentinel Workspace & KQL Threat Hunting 15.4 Sentinel Incident Management & SOAR Automation

11.1 Azure SQL Database Security & Always Encrypted

Key Takeaways Explain This Topic AI

Server-level firewall rules apply to the logical SQL Server master and all databases, stored in the master database, whereas Database-level firewall rules apply only to specific databases, stored within individual databases to facilitate portable database migrations without granting server-wide access.

Microsoft Entra authentication for Azure SQL eliminates local SQL administrative passwords by integrating centralized Entra ID identities; enabling Entra-only authentication permanently disables legacy SQL server authentication logins (sa).

Transparent Data Encryption (TDE) provides real-time I/O encryption of database files, log files, and backups at rest using 256-bit AES, supported by Service-Managed Keys or Customer-Managed Keys (CMK) stored in Azure Key Vault or Key Vault Managed HSM (BYOK).

Always Encrypted protects sensitive column data (such as SSNs and credit card numbers) in transit and at rest by encrypting data client-side before passing it to the database driver; database administrators and hypervisors never see plaintext sensitive data.

Always Encrypted relies on Column Encryption Keys (CEK) to encrypt data values and Column Master Keys (CMK) stored in client certificate stores or Key Vault to encrypt CEKs; Always Encrypted with secure enclaves enables complex SQL computations (like pattern matching and range comparisons) inside server-side hardware-protected enclaves without exposing plaintext to host memory.

Last updated: August 2026

7.1 Azure SQL Database Security & Always Encrypted

Data stores in Microsoft Azure host an organization's most critical assets—customer personal data, financial records, proprietary IP, and healthcare telemetry. Securing database platforms requires a comprehensive defense-in-depth model spanning network perimeter isolation, centralized identity management, transparent storage encryption, and client-side cryptographic isolation. Azure SQL Database provides a rich suite of security controls designed to fulfill strict enterprise compliance requirements while protecting against modern insider and external threats.

Azure SQL Database Firewall Architecture

Network security for Azure SQL Database operates through firewall rules that prevent all unauthorized network access until explicitly permitted. Azure SQL provides two distinct firewall tiers: Server-level firewall rules and Database-level firewall rules . +-----------------------------------------------------------------------------------+

  • | AZURE SQL FIREWALL ARCHITECTURE |
  • +-----------------------------------------------------------------------------------+
  • | Client Request (IP: 203.0.113.15) |
  • | | |
  • | v |
  • | +-----------------------------------------------------------------------------+ |
  • | | SERVER-LEVEL FIREWALL (Stored in 'master' DB) | |
  • | | Evaluated first for all connection attempts to the logical server. | |
  • | +-----------------------------------------------------------------------------+ |
  • | | Pass (Matched server IP rule) |
  • | +------------------------------------+ |
  • | | Fail (No server rule match) | |
  • | v v |
  • | [Connect to DB] +-----------------------------------------+ |
  • | | DATABASE-LEVEL FIREWALL | |
  • | | (Stored inside target user DB) | |
  • | | Evaluated if server check fails. | |
  • | +-----------------------------------------+ |
  • | | Pass -> [Connect to DB] |
  • | | Fail -> [Access Denied] |
  • +-----------------------------------------------------------------------------------+
  • 1. Server-Level Firewall Rules

Server-level firewall rules enable connections to the entire logical Azure SQL Server, granting access to the master database and all user databases hosted on that logical server. Key attributes include:

Storage Location: Stored directly inside the master database ( sys.firewall_rules catalog view).

Configuration Interfaces: Created and managed via the Azure Portal, PowerShell, Azure CLI, REST API, or ARM/Bicep templates.

Scope & Applicability: Ideal for administrators and automated tools that require access across multiple databases on the same server.

Allow Azure Services Toggle: Includes a setting ("Allow Azure services and resources to access this server") that injects an internal rule (IP 0.0.0.0 ) allowing connections from any Azure resource. Exam Note: In secure enterprise environments, this toggle should be Disabled in favor of Virtual Network Private Endpoints or specific VNet Service Endpoints.

2. Database-Level Firewall Rules

Database-level firewall rules enable IP-restricted access to specific individual user databases without granting permissions to the entire server or other databases on the same host. Key attributes include:

Storage Location: Stored directly within individual user databases ( sys.database_firewall_rules view).

Configuration Interfaces: Configured exclusively using T-SQL commands ( sp_set_database_firewall_rule and sp_delete_database_firewall_rule ) executed on the target database.

Database Portability: Because rules reside inside the user database itself, when a database is copied, backed up, or geo-replicated to another server, the database-level firewall rules move with it, simplifying database migrations and tenant isolation.

Principle of Least Privilege: Prevents a client authorized for Database A from attempting logins against Database B on the same logical server.

Microsoft Entra Authentication & Entra-Only Mode

Traditional SQL authentication relies on local database usernames and passwords ( sa or custom SQL logins) stored inside the database. This legacy approach introduces credential management overhead, weak password risks, and lack of central identity governance.

Centralized Identity Integration

Integrating Microsoft Entra ID (formerly Azure Active Directory) authentication replaces local SQL accounts with centralized cloud identities:

Unified Identity Governance: Authenticates users, groups, and Azure Managed Identities via Entra ID tokens.

Multi-Factor Authentication (MFA) & Conditional Access: Enforces MFA, device compliance policies, and location-based access controls prior to database connection.

Managed Identity Authentication: Enables Azure services (such as App Services, Azure Functions, or VMs) to connect to Azure SQL without storing database passwords in application configuration files.

Microsoft Entra Administrative Assignment

Each Azure SQL Logical Server requires a designated Microsoft Entra Admin (a specific user or, preferably, an Entra Security Group). The Entra Admin acts as a high-privileged administrator capable of creating contained database users bound to Entra principals using syntax such as CREATE USER [sec-app-group] FROM EXTERNAL PROVIDER .

Entra-Only Authentication Mode

To enforce strict Zero Trust compliance, Azure SQL supports Microsoft Entra-only authentication mode :

Permanent Disabling of Local Logins: When Entra-only authentication is enabled on the logical server, all legacy SQL Server authentication logins (including the original server admin login created during deployment) are permanently disabled.

Rejection of SQL Password Authentication: Any connection string attempting to pass standard SQL credentials ( Server=...; User ID=sqladmin; Password=... ) is immediately rejected by the server gateway.

Mandatory Token-Based Access: All database connections must authenticate using Entra ID OAuth 2.0 access tokens, Entra interactive logins, or System/User-Assigned Managed Identities.

Transparent Data Encryption (TDE) & Key Management

Transparent Data Encryption (TDE) secures data at rest by performing real-time page-level I/O encryption and decryption of Azure SQL database files, log files ( .ldf ), and backup files ( .bak ) using 256-bit AES encryption algorithms.

TDE Mechanics & Architecture

Transparency: Encryption operates completely at the storage engine layer. Application queries, T-SQL code, and database schemas require zero modification.

Database Encryption Key (DEK): TDE encrypts database pages using a symmetric Database Encryption Key (DEK) stored in the database boot page.

TDE Protector: The DEK is encrypted using an asymmetric key known as the TDE Protector (or Key Encryption Key / KEK). +-----------------------------------------------------------------------------------+

  • | TRANSPARENT DATA ENCRYPTION (TDE) KEY FLOW |
  • +-----------------------------------------------------------------------------------+
  • | |
  • | +-----------------------------------------------------------------------------+ |
  • | | AZURE KEY VAULT / MANAGED HSM | |
  • | | Holds TDE Protector (RSA 2048/3072/4096 Customer-Managed Key / CMK) | |
  • | +-----------------------------------------------------------------------------+ |
  • | | |
  • | v Encrypts / Decrypts |
  • | +-----------------------------------------------------------------------------+ |
  • | | AZURE SQL DATABASE ENGINE | |
  • | | Database Encryption Key (DEK) -> Encrypts Data Pages & Log Files (.ldf) | |
  • | +-----------------------------------------------------------------------------+ |
  • | |
  • +-----------------------------------------------------------------------------------+
  • Service-Managed Keys vs. Customer-Managed Keys (BYOK)

Azure SQL supports two key management models for the TDE Protector:

Service-Managed Keys (Default): Azure automatically generates, manages, and rotates the TDE Protector certificate for each logical server. Requires zero administrative effort, but gives customers no direct control over key rotation schedules or revocation.

Customer-Managed Keys (CMK / Bring Your Own Key): The TDE Protector is stored in a customer-controlled Azure Key Vault or Azure Key Vault Managed HSM .

Key Vault Requirements: The Key Vault must have Soft-Delete and Purge Protection enabled to prevent catastrophic database lockouts caused by accidental key deletion.

Server Identity Access: The Azure SQL Logical Server accesses Key Vault using its System-Assigned or User-Assigned Managed Identity, requiring get , unwrapKey , and wrapKey permissions.

Key Revocation as Revocation of Access: Revoking Key Vault access or deleting the CMK causes Azure SQL to take the database offline within 30 minutes, rendering data completely unreadable.

Always Encrypted: Client-Side Column Encryption

While TDE encrypts database files on disk, data inside database memory (RAM) remains unencrypted during execution, leaving sensitive columns vulnerable to highly privileged users (such as Database Administrators, Cloud Infrastructure Admins, or compromised host OS processes). Always Encrypted solves this by providing client-side column-level encryption.

Fundamental Separation of Duties

Always Encrypted enforces a cryptographically backed separation between those who own the data (and hold the encryption keys) and those who manage the data (the database engine and cloud operators):

Client-Side Encryption: The application client driver encrypts sensitive data before sending it over the network to Azure SQL.

Ciphertext in Database: The database engine receives, stores, and processes ciphertext blobs. Plaintext data is never visible inside database engine memory, tempdb, or disk files.

Key Hierarchy: CEK vs. CMK

Always Encrypted utilizes a two-tier key hierarchy to isolate encryption operations:

Key Type Name Purpose & Storage Location

CEK Column Encryption Key Symmetric key (AES-256) used to encrypt actual data column values. Stored inside database metadata as encrypted ciphertext blobs.

CMK Column Master Key Key-protecting key used to encrypt the CEK. Stored in an external key store (Azure Key Vault, Windows Certificate Store, or Hardware Security Module). Never sent to Azure SQL.

Client Driver Decryption Workflow

The application sends a parameterized T-SQL query containing plaintext sensitive values to the Always Encrypted-enabled client driver (e.g., Microsoft.Data.SqlClient).

The driver contacts Azure Key Vault to retrieve the Column Master Key (CMK) and decrypts the encrypted Column Encryption Key (CEK).

The driver encrypts the sensitive parameter values using the decrypted CEK.

The driver sends the query containing ciphertext parameters to Azure SQL Database.

Upon receiving results, the driver decrypts the returned ciphertext column values locally using the decrypted CEK, presenting plaintext to the application.

Deterministic vs. Randomized Encryption

Always Encrypted supports two encryption algorithms for columns:

Deterministic Encryption: Always generates the exact same ciphertext value for a given plaintext string. Enables point lookups, equality joins, grouping, and indexing on encrypted columns. Trade-off: Allows unauthorized parties to infer information by analyzing patterns (e.g., identifying recurring ciphertext values for boolean or status fields).

Randomized Encryption: Uses a random initialization vector (salt), generating unique ciphertext each time the same plaintext is encrypted. Maximum cryptographic security, but prevents indexing, equality lookups, range queries, and joins on the column.

Always Encrypted with Secure Enclaves

Standard Always Encrypted severely limits database query capabilities because the SQL engine cannot parse or compare ciphertext values (e.g., pattern matching LIKE '%string%' or range queries WHERE Age > 30 fail). Always Encrypted with secure enclaves resolves this by introducing confidential computing hardware capabilities.

Hardware-Enforced Enclave Processing

A secure enclave is a hardware-protected memory region (such as Intel SGX or Virtualization-based Security / VBS enclaves ) inside the database server CPU host:

Trusted Execution Environment (TEE): The secure enclave acts as a isolated sandbox inside server RAM.

Delegated Query Execution: When a complex query (range scan, string comparison, sorting) is executed on encrypted columns, the client driver securely delegates decryption keys to the enclave.

In-Enclave Computation: The enclave decrypts data inside its hardware-isolated memory boundary, performs the SQL calculation, re-encrypts the results, and passes ciphertext back to the database engine.

Zero Exposure: Neither host operating system administrators, hypervisors, nor SQL Server system processes ( sqlservr.exe ) can inspect the memory inside the secure enclave.

TDE vs. Always Encrypted Matrix

  • Security Feature Transparent Data Encryption (TDE) Always Encrypted
  • Encryption Boundary Server/Storage engine layer (Disk/Backups) Client Application Driver layer (Client-Side)

Protection Target Data at rest ( .mdf , .ldf , .bak files) Sensitive column values in transit, at rest, & in memory

  • DBA Plaintext Access Yes (DBAs can query plaintext data) No (DBAs only see ciphertext blobs)
  • Key Location Service-Managed or Key Vault (TDE Protector) Azure Key Vault / Cert Store (Column Master Key)

Query Capabilities Full query & indexing support Restricted (Deterministic allows equality; Enclaves allow range/LIKE)

Application Changes None (Fully transparent) Requires driver updates & connection string flags

Loading diagram... Always Encrypted Client-Side Decryption & Query Workflow