Why Ledger, Always Encrypted, & AAD support in Azure SQL DB are vital for Zero Trust Architecture
Published May 01 2023 07:49 AM 4,569 Views


Zero Trust architecture has become increasingly important for cloud database deployments due to the evolving cyber threat landscape and the growing reliance on cloud services.


This security model operates on the principle of "never trust, always verify," effectively eliminating the traditional assumption that internal networks are inherently secure.


By requiring continuous authentication and authorization, Zero Trust architectures can mitigate risks associated with unauthorized access, data breaches, and compromised credentials.


In cloud database deployments, the shared responsibility model and the distributed nature of cloud services amplify the need for stringent security measures. Implementing a Zero Trust approach enhances visibility, control, and protection of sensitive data, while ensuring that access to resources is granted only to verified and authorized users.


This not only bolsters organizational security but also helps maintain regulatory compliance and fosters customer trust.

The Elements of Zero Trust

Depending on who you talk to, Zero Trust has a few pillars, Microsoft uses three pillars:


  • Verify Explicitly - constant authentication and validation of users and devices before granting access to resources. This approach assumes that no user, device, or network can be trusted by default, even if they are within an organization's perimeter.
  • Use least privilege access - limits users' access rights to only the minimum resources required to perform their tasks.
  • Assume Breach - presumes that an organization's network has already been compromised.

You can learn more here Zero Trust implementation guidance | Microsoft Learn.




All three of these are important, but I want to focus on the last, Assume Breach, because it is a fundamental shift in secure design.

Azure SQL DB and Assume Breach

Azure SQL Database and SQL Server , support three interesting technologies that address Assume Breach, they are:


  • Ledger
  • Always Encrypted
  • Azure Active Directory (AAD) Client Authentication

Let’s look at each of these through an Assume Breach lens.

Ledger Through an Assume Breach Lens

The cornerstone of ledger functionality in Azure SQL DB is Immutable Storage. Immutable Storage has an important security guarantee: no-one can delete or edit the data stored in Immutable Storage, and in the ledger scenario, database digest files are stored in Immutable Storage.


So, if you ask the question:

“In the event of a complete and utter server compromise, can the attacker change the digest data used to support ledger to repudiate transactions?”


The answer is “No!”


While the transaction digest data is held in a Write Once, Read Many (WORM) state, that data cannot be modified or deleted for a user-specified interval.


You can read more about Immutable Storage here Overview of immutable storage for blob data - Azure Storage | Microsoft Learn.

When using ledger, it is recommended you use a time-based retention policy rather than legal hold.

Always Encrypted Through an Assume Breach Lens

Always Encrypted has an important secure design element that mitigates Assume Breach scenarios; and that is the server does not have access to the data encryption keys. The AES data encryption keys are nowhere in the server’s memory. It’s not that they are hard to uncover, the keys are simply not there. Compare that with classic SQL column encryption, where the bulk encryption keys are in process memory. If an attacker can read process memory, there is a non-zero chance they could read the encryption keys.


So, for Always Encrypted if you ask the question:

“In the event of a complete and utter server compromise, can the attacker decrypt ciphertext encrypted using Always Encrypted?”


The answer again, is “No!” because the AES keys aren’t in memory.

Azure Active Directory (AAD) Client Authentication Through an Assume Breach Lens

Historically, database engines use their own authentication mechanisms, which means the engine must store the users’ credentials or derivative of the credential, such as a hash, salted hash or a salted and iterated hash, in the database file(s).

In the event of a complete server compromise, the attacker could get that data and start performing a Rainbow Table (https://freerainbowtables.com/) style attack against the credentials. Note that the addition of a salt and/or iterations helps mitigate Rainbow Table attacks, however.


But AAD client authentication is different because there is no credential or credential verifier maintained by Azure SQL DB, it delegates authentication to Azure Active Directory when configured to use AAD authentication. Also, when using AAD, you can take advantage of multi-factor authentication and Conditional Access. Learn more here Conditional Access - Azure SQL Database & SQL Managed Instance & Azure Synapse Analytics | Microsoft....


So, for AAD client authentication if you ask the question:

“In the event of a complete and utter server compromise, can the attacker harvest credentials from Azure SQL DB configured to use only AAD client authentication?”


Again, the answer is “No!” because SQL does not manage nor maintain any user credential when using AAD client authentication. Note that SQL does maintain some metadata about the user, but no credential or derivative of a credential.


Remember, this also applies to non-user clients, for example a Virtual Machine, or an Azure Function App configured to use Managed Identities. Again, AAD handles credential management and lifecycles, not Azure SQL DB.


As an aside, using Windows Active Directory with SQL Server also stores no user credentials or credential derivatives.


Zero Trust is no longer security “Markitecture”, it is a modern and important strategy to help protect networks.

These three technologies used by Azure SQL DB can help support your journey towards a more complete Zero Trust design, and for sensitive-data solutions, they are vital.


Thanks to Pieter Vanhove, Joachim Hammer, Tameika Lawrence, Mirek Sztajno, and Andreas Wolter for their review and valuable comments.


1 Comment
Version history
Last update:
‎May 01 2023 07:49 AM
Updated by: