11.1 Azure SQL Database Security & Always Encrypted

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:

1. 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.
2. 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:

Client Driver Decryption Workflow

1. The application sends a parameterized T-SQL query containing plaintext sensitive values to the Always Encrypted-enabled client driver (e.g., Microsoft.Data.SqlClient).
2. The driver contacts Azure Key Vault to retrieve the Column Master Key (CMK) and decrypts the encrypted Column Encryption Key (CEK).
3. The driver encrypts the sensitive parameter values using the decrypted CEK.
4. The driver sends the query containing ciphertext parameters to Azure SQL Database.
5. 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