Before we get started, this post assumes you have a passing knowledge of Threat Modeling and the ledger feature in SQL Server and Azure SQL Database.
If you are not familiar with either of these, consider reading my colleague and co-author’s thoughts on threat modeling: An annotated read of the Threat Modeling Manifesto « Simone On Security, and listen to Episode 53 of the Azure Security Podcast, where Sarah and I interview my colleague, Pieter Vanhove about ledger.
You can also read about how ledger is used in the real world in our new case study Microsoft Customer Story-Qode Health Solutions secures COVID-19 vaccination records with the ledger ....
Ok, back to the post.
It is common to use the STRIDE mnemonic when considering threats to the elements that make up a system, STRIDE is:
- Information Disclosure
- Denial of Service
- Elevation of Privilege
Think of STRIDE as a more fine-grained version of CIA (Confidentiality, Integrity, and Availability) but from an attacker’s perspective. The running joke, which I may well be guilty of starting, is we added Repudiation to STRIDE so it wouldn’t be DIETS. But, to set the record straight, ‘R’ has always been in STRIDE.
So, what is Repudiation, and why is it important and what role can ledger in SQL Server and Azure SQL Database play?
‘Repudiating’ something means to go back on, reneguing or denying a transaction.
This includes examples like:
- repudiating you sent something (repudiation of transmission),
- repudiating you received something (repudiation of receipt),
- repudiating it was you involved in the transaction (repudiation of origin)
Here’s a simple example.
- Paige wants to sell 100 shares of Contoso Corp for $10 each.
- She places her sell order with her online brokerage.
- A split second after she hits ‘SELL’, the stock price jumps to $13.
- A moment after that a sales confirmation comes back from the brokerage, Paige says she never made that sell order which could be repudiation of transmission (I never sent it) or even repudiation of origin (it wasn’t me.)
- At this point the brokerage must prove Paige sent that sell order, and the stock price at that point in time was $10.
This is where it gets interesting. How does the brokerage verify that it did receive the sell order from Paige?
The brokerage could go to the log files and verify that Paige made the sell order. Audit trails are critical for any system, of course, but how do you know if the logs are valid? What evidence is there that the log files have not been tampered with? What if Paige has an insider at the brokerage named Blake, and Paige asks him to scrub the log file? If Blake is an admin or has enough privilege, he could remove that line from the log, almost as if the transaction never occurred. Or perhaps Blake might change the 100 shares sold to 001 shares sold, reducing the potential impact on Paige.
Strong access control mechanisms on log files are not an adequate defense, because people with high enough privilege, such as Blake, can tamper with the files.
Sadly, because repudiation is not well understood, it is often ignored when building threat models or designing systems.
Ledger to the Rescue
This is where technologies like ledger in SQL Server or Azure SQL Database come in. It’s a new technology that offers the power of Blockchain in Azure SQL Database and SQL Server. We’re making the data in SQL verifiable using the same cryptographic patterns seen in Blockchain technology, while keeping the flexibility and performance of a traditional database. It is centrally managed while you can cryptographically attest to other parties, such as auditors or other business parties, that your data can be trusted and hasn't been tampered with.
Let’s say our brokerage system uses Azure SQL Database and critical transaction tables are configured as ledger tables.
In the scenario above, when Paige issues her sell order, a record of that transaction is written to a ledger table. Each transaction that the database executes is cryptographically hashed (SHA-256). Transactions are then cryptographically linked together, like a Blockchain. A hash representing transactions is periodically written to Immutable Blob Storage or Azure Confidential Ledger. We will focus on Azure Immutable Blob Storage for this post.
Immutable Storage has write-once-read-many (WORM) security guarantees. If you set a time-based retention policy, for example, 180 days, then data held in the storage account cannot be deleted or modified within 180 days. Even subscription owners or admins, Windows admins and SQL admins cannot delete or modify the data.
This last part is critical.
Even if Paige and Blake collude, and Blake is a SQL admin, Blake cannot change the transaction hash data in the immutable storage, so it is straightforward for an auditor with the VIEW LEDGER CONTENT permission to verify if Blake has deleted Paige’s transaction or changed her sell quantity from 100 to 1, or made any other change. The auditor will need to analyze the history table and verify if unusual transactions occurred.
Admittedly, a table tracking financial data is probably append-only anyway, but ledger can provide an extra level of assurance, as we will see in the next example.
Even if Blake has enough privilege and can get access to the database file on disk, and modify the data page in which the critical transaction elements are stored, the change is still detected by the ledger verification procedure. Note that because Azure SQL DB is a PaaS service, the data storage on-disk is not exposed, mitigating this threat further.
Ledger is a strong “repudiation of receipt” technology, and when you are building threat models, it is important you consider using tools like ledger in Azure SQL Database/SQL Server to mitigate such threats.
The diagram below shows the high-level view of how ledger functions, but you can read more by referring to the links at the end of this post.
There’re other aspects to consider, such as “repudiation of origin” and that requires technology such as strong authentication, but that’s a problem ledger in Azure SQL Database or SQL Server does not solve. One way to help solve repudiation of origin is strong authentication, such as that provided by Azure Active Directory using Multi-Factor authentication (MFA) and conditional access policies.
There’re other technologies you could also employ such as digital signing the transaction at the client, or for high-value transactions you could use a trusted third-party to countersign the transaction. Both are out of scope for this post focuses on what ledger can solve.
Another issue, which strong authentication by itself does not resolve is the ‘Confused Deputy’ problem. This is when a user logs in to a system, and malware on the device performs elevated actions. This is one of many reasons why comprehensive end-point protection, such as Microsoft Intune, is so important.
If you want to learn more about repudiation, I would highly recommend View of Non-repudiation in the digital environment | First Monday and if you can get a copy, chapter 9 of CORBA Security published by Addison-Wesley and written by Richard Soley is excellent. The book was published in 1999 and I have a copy on my bookshelf. Perhaps this ages me, however!
Finally, if you want to learn more about the security guarantees and mechanics of Azure Immutable Blob Storage, I recommend you read the SEC 17a-4(f) and CFTC 1.31(c)-(d) compliance assessment from Cohasset Associates, Inc. at https://aka.ms/AzureWormStorage.
For more information and to get started with ledger in Azure SQL Database, see:
Thanks to Panagiotis Antonopoulos, Andreas Wolter and Pieter Vanhove for their review, comments, and edits.