Welcome to the Azure Security Podcast, where we discuss topics relating to security, privacy, reliability, and compliance on the Microsoft Cloud Platform. Hey everybody, welcome to Episode 53. This week is a special episode. We're going to talk about a new feature that's coming in Azure SQL DB named Ledger. This week is myself, Michael and Sarah, and our guest this week is Peter Vanhove. We'll also have no news this week because it is a special episode.
Peter, thank you so much for joining us this week. Would you like to spend a moment and just give our listeners a background about yourself? Yeah. Hi. Good morning, good afternoon, wherever you are in the world listening to us. My name is Peter. I'm a Program Manager at Microsoft in the Data Platform Security Team.
So our team is responsible for all security features inside SQL products like SQL Server, Azure SQL DB, MI, and I'm the feature Program Manager of Ledger, the feature we're going to talk about today. Thanks for that, Peter. So tell us what is Ledger? What does it do? Why is it so exciting and why are we having a special episode on it? Yeah, sure. I can explain. Imagine that you're working at a financial or medical company and that you have very sensitive data inside your database.
How can you be 100 percent sure that nobody has actually tampered with your data? If I look at my previous life, I was a DBA so I literally had full control of all the databases. So if I wanted to, I could just log in into all these databases, tampered with the data, erase all my traces, and you're good to go. Nobody would ever have noticed that I have modified the data. So it's really crucial for these financial and medical companies that they can prove that the data can be trusted.
So currently for them, it's really hard to do. You can try to switch on auditing. That will help, of course. But then again, if you look at my role as a DBA, I could easily switch off the auditing, do my modification, then switch on auditing again. These companies are, like I said, not 100 percent sure that they can trust their data.
On the other side, if we look at multi-party companies or workflows, let's say, typically what these companies are doing, they are looking into blockchain technologies to guarantee the integrity of the data and the business logic in an untrusted environment. Thing with blockchain is that it's decentralized structure, and it represents significant challenge for these companies.
When it comes to real-world production workloads, it's completely decentralized, it's not really performant, and there are many scenarios where blockchain solutions is really an overkill, and it doesn't really justify the cost and the performance overhead of implementing a complete blockchain structure. So that's why we implemented Ledger to address these two problems. So to address or how can we show that or prove that our data that is inside the database is completely trusted.
So is Ledger a service or is it actually built into the product? I'm not a database guru like Michael, so I'm asking the noob questions. It's a feature. So it's not a new flavor of an Azure SQL database or SQL Server. No, it's just a new feature that is enabled in every service tier of Azure SQL DB. So yeah, you just need to let's say switch it on by creating what we call a letter table. I'll come back to that immediately.
So no, there's no extra service that needs to be enabled, and this feature is currently available in Azure SQL DB. So as Michael said, we're just launching the GA today, and the feature is also available in the new version, the box version of SQL Server, SQL Server 2022. Nice. Just because it's always something to get out of the way because people have budgets, but do we charge for it? Is there an extra charge? How does that work for that particular feature? No, it's completely free.
So you just enable the ledger tables. You might end up with some extra storage because we're keeping track of the history. Again, I'll talk about that a little bit later, but that will basically be the extra cost of the storage. You don't need to pay anything extra for this feature. And as I said, it's available in all different service tiers. Even in the basic service tier, the cheapest one, you are able to use the ledger feature there.
All right, Peter. So let's get into some of the nitty-gritty. So how does this thing work? I mean, you just mentioned before that you can create a table and say it's a ledger table. So what happens next? Yeah, so it's really about bringing the power of blockchain into the SQL database. So we're making the SQL or the data that is inside the ledger table tamper evidence by using cryptographic.
And when we talk about cryptographies, literally you should see it as the pattern of cryptographic that you see in blockchain nowadays. So how does it work? We have two different types of tables. We have an updatable ledger table and we have an append-only ledger table. Typically, updatable ledger tables are used by applications, of course, so they need to be able to do insert update delete statements. The other flavor that we have is append-only. And like I said, you can just append records.
So we only allow insert statements, we don't allow any update or delete statements whatsoever on these particular tables. So the user can actually choose between those two. Let's go for the updatable ledger table. So what happens is when you update a particular column in a row of a ledger table, what we're going to do is we're going to take the previous version of that row and we're going to put that in what we call a history table.
People that are familiar with SQL Server, they will say, yeah, hey, we already have that. It's called temporal tables. Well, that's true. And I can even tell you more on the ledger feature is built on top of the technology of temporal table, but it's more advanced. It's better because we have the cryptographic parts under the hood as well. So what we're doing, when we move that previous version of the row to the history table, we also going to add extra metadata to that.
So for example, the transaction ID, the user that has actually executed that transaction, the commit timestamp of that transaction. So everything is then stored in that history table. On top of that, when you create a ledger table, we're also going to create what we call a ledger view. And the ledger view gives you a chronicle overview of your database. So it shows you the current value of your records, but it also shows you the historical values of the record.
So we make your life a little bit easier by default creating a ledger view for you. So this is how we keep track of the historical values of your ledger table. Now, how is the data then actually protected? And it is protected in what we call a database ledger. So we're basically using a blockchain structure under the hood that is implemented in SQL Server or in Azure SQL DB. What does it mean?
So every transaction is hashed by using a SHA-256 hashing mechanism, similar what you see in blockchain nowadays. Now, what are we doing? Every row in a transaction is hashed by using a Merkle Tree data structure that creates a root hash. So for those who don't know what a Merkle Tree is, what we're using as the input for the hash function is the row information, like how many columns did we update, what were the columns, what were the column types, and so on.
So we use that information as input for the hash functions. And then we're going to hash these rows, and we're going to produce what we call a parent hash. And then these parent hashes are then hashed again into new hash and so on and so on, until ultimately we produce a root hash that is representing all the rows in that single transaction. That's the first step to what we're doing. And secondly, we batch all these transactions into blocks.
So what does that mean is like in Azure SQL DB, every 30 seconds, we're going to create a new block that will consist all the transactions that have occurred during that time period. And that block is then also cryptographically hashed and linked to the previous block, just like in the blockchain. So that's how we're building that blockchain technology, or that's how we built the blockchain, in fact.
At that point, once we have produced the block, also what we call a database digest, we're going to push out that block or the database digest out of SQL server. And we're going to push it into trusted storage, like for example, Azure Immutable Blob Storage, or we also currently have a new service now called Azure Confidential Ledger. So you can choose between both of them, what you prefer.
And once this is pushed out, it is stored in Immutable Storage, which means nobody can there actually tamper with that data. Now it becomes interesting, of course, because remember when I said, what is the problem that we're trying to solve? Well, we need to build trust and we need to have that. We need to make our customers that they're able to prove that the data can be trusted. So how can they prove this right now?
Because we're storing the database digest on a different location, what we can do next is we can run a stored procedure that is going to verify the database digest that are outside the database. And we're going to compare that in what we have inside in the database, which means what we're going to do is we're going to recalculate the hashes in real time in SQL. And we're going to match that with the hashes that we have on the Immutable Storage.
And if we have a match between those hashes, it means that we are 100% sure that nobody has actually tampered with the data. If we don't have a match, it means that, hey, somebody screwed up our database, somebody screwed up the data that is inside that particular table. So we are sure that the data cannot be trusted and we need to figure out what happens.
Then you just need to look up into the history tables, what was the transaction, and start your investigation, what exactly has happened with the data inside that ledger table. This means that it makes it really, really easy for the customers to prove that tampering hasn't occurred on the data and that your data can actually be trusted. So it's really, really powerful for our customers to have that and to prove that your data can be trusted to external altitudes, for example.
Or if you're working with supply chains, for example, where you have multi-parties, these verifications can also be run by those multi-parties to gain trust and to know, okay, this data can be trusted from all the parties. And that's an important part, right? Because the hashes have to be stored on something that is tamper resistant, right? I mean, you could store these things on a storage account, but there's really no verification that they've been tampered with.
I mean, even if you put them in a storage account with an RBAC control, a strong RBAC policy, the problem there is that the admins can still... If the RBAC policy says admin can access right over these hashes, a rogue admin can still write over the hashes. So we have to use mitigations that are basically cryptographic controls, or in the case of Blobstore, we can use immutable storage. Is that a fair comment? Yeah, that's correct. If you just use an ordinary storage account, you're right.
Everybody that has the appropriate permissions can go into these digest files. These are just JSON files that we produce with the hashes inside. But still, you can open these files and you can tamper with whatever you want in these files. So if you switch on the immutable setting on the Azure Storage account, it's just writing once and reading multiple times, but there's no way you can actually modify anything in the digest. So that is crucial.
Yeah, the nice thing about Merkle trees as well is that they're actually quite efficient when it comes to verification, which is a little bit of a fun fact for all you people and not crypto nerds. Yeah, so we also have the Azure Confidential Ledger, which is a new feature that can also be used for storage of the hashes. But at the end of the day, that is another service, right? Yeah. Storage is a service and ACL is a service as well. One of the examples you gave was a malicious DBA.
And I think that's just a fantastic scenario, right? So how do I protect against a malicious DBA? And this is really important, like a zero trust environment as well. If an attacker is on the network and they've completely compromised absolutely everything, is the data still secure from tampering? By data, I mean that the hashes. And that's why mutable storage and has a confidential ledger is just so critically important.
I mean, their threat models are designed specifically to mitigate a rogue DBA. But when you're now talking a couple of days ago, you also mentioned another example with a hardware vendor. Do you want to just go through that scenario? Yeah, we have a vendor. The company is called Lenovo. Probably everybody knows Lenovo. So they're producing laptops. And they are one of our customers that are already using the ledger feature or their supply chain.
So what they build is when they produce a laptop, they store the hardware information into a ledger table, like for example, the number of CPUs, the number of memory that is in that particular laptop, and so on. So all these specifications are stored in there. And once we, let's say, Michael, you order the laptop and we send the laptop to you and you receive the laptop, you open it up and then you need to register your laptop.
And basically what we're going to do is we're going to verify that the laptop that you've received is really what you have ordered. And to prove that is we're going to check based on the, or Lenovo is going to check based on the serial number of your laptop, and it's going to verify that into the ledger table. And if that is correct, so the number of CPUs, the number of memory, the type and so on.
If that is valid, we know that you have received the correct laptop that you've ordered because we know that the data in the ledger table can be trusted. And this is a real life example because sometimes when the laptop is delivered by a third party company or by a vendor, apparently it happened from time to time that instead of, let's say, if you have eight CPUs, you just get like four. And nobody would have ever known what has happened.
But by using or storing that information in the ledger table, we're sure that if these values don't match, that the end user or the end customer didn't get the correct laptop. And that's a really good example of how you can use ledger. So an interesting point here is, OK, so we've got this series of hashes. I can verify that the data has not been tampered with by verifying the hashes. But let's say there has been a tampering incident and the data has been tampered with.
How can we recover from that? There are two types of tampering. First, let me go back a little bit. So you gave the example of the malicious DBA, right? If he or she just tries to modify a record, that will be stored in the history table, right? That modification. But if we should run the verification, that would succeed, right? Because there has nothing really changed under the hood or there has nothing really changed in the database digest itself. So that's one way of tampering it.
And to get to figure that out, you just need to go into the history table and try to find out what exactly has happened. There is another possibility, of course, is that the DBA, if they have SQL skills and they know how to tamper, for example, immediately in the data file on disk or if we look at our platform, if we should have a cloud operator that is able to tamper with the data inside the data files itself, then, yeah, these changes would never appear in the history table.
But these changes or that type of tampering would be captured by the verification, right? Because then the hashes wouldn't match and then the verification would fail. Now, okay, let's say that you figured out that somebody actually has tampered with your data, so the verification fails. Indeed, a good question. How can you recover from this tampering? And you should look at two different scenarios.
You should look at it like, okay, the tampering, does it affect the data that is used in further transactions or the tampering didn't affect any further transactions? If you look at the first example, so it affected further transactions, the only option that you have is doing a point-in-time restore of your database to the latest backup that can be verified.
So, yeah, you will need to try an error to see, okay, if I do a restore until, let's say, yesterday and then I run the verification on that restored database, the verification still fails, okay, then we still need to go back in time. So, it's a manual process doing the point-in-time restore. And then what you can do, you can manually repair the table, the ledger stable, by reinserting the information, that's one option.
If you know what information that you need to insert, but that's a manual action. Or the other option is that you just accept that the transactions that occurred after the restore that they are lost. So, these are the two options that you have. Now, if we look at the other example where the tampering didn't affect the further transactions, what you can do is you can retrieve the correct table ledger from backup. So, what you need to do, you need to restore the database under an under name.
Then you need to figure out, okay, what was the previous value of that particular record. And then you can just overwrite the tampered data with the original data that you found back in the backup files. With that, you will correct the data, it will again, store a value in the history table, but that's okay. After that, yeah, the tampering will go away and, well, not the tampering will go away, but you will have the original value back again in the ledger table. So, nothing comes free.
So, what's the performance impact? My guess is it's probably some kind of impact on writes and updates. Probably not on reads, but I'll leave that to you. Yeah, that's right. Yeah, there is an impact. So, there is an overhead when you switch on or when you start using a ledger table. So, what we did, we did a bunch of tests with two different type of loads. So, one load that is just typically what we see, a random read and write access of an application.
And then another load what we did was one where we did a lot of updates, only single updates on a particular ledger table. And there is indeed an impact. Yeah, there is an overhead. Like with the regular loads, updates and inserts, we lose around 6% of performance. That's the consequence of having a ledger enabled. The thing is that, of course, if we look at the DML latency here, for inserts, it's pretty much okay because we don't need to keep track of historical data for insert statements.
The only thing we need to do is we need to compute the hash. So, that's pretty much okay. But if we look at delete operations, then we need to keep track of that in the history table. And on top of that, we also need to compute the hash there. So, that's some extra performance impact. Even for update operations, it's even more because then we need to add two records in the history table. So, one for the old version and then one for the new version.
So, we basically do a delete and then an insert again in the history table. So, yeah, it has an impact, but to be honest, the customers that I've been working with for proof of concepts, they are happy with the performance of the ledger tapes. They don't have any complaints at all there. Yeah, I think it's important, right?
Because when you're looking at the security benefit and the non-repudiation benefits that come with ledger, for certain environments, it completely outweighs the performance cost. When we know how to solve the performance cost problem, we throw some more hardware at the problem. But at the end of the day, there's a feature that you absolutely need. And I mentioned non-repudiation. That's incredibly important because there are some environments where you need strong cryptographic non-repudiation.
I mean, a log file, for one's of a better word, with just strong RBAC policies, still has very weak non-repudiation value because it can still be manipulated by potentially a trusted user. Something that uses cryptographic mechanisms to protect it and with strong write-ones, read-many storage, carries a lot more weight when it comes to mitigating repudiation threats.
And in fact, for those of you out there who are into threat modeling, you may be familiar with stride, which is spoofing, tampering, repudiation, information disclosure, denial of service, and elevation of privilege, well, the R in stride is repudiation. And this ledger technology is a fantastic arrow to having your quiver when it comes to mechanisms for mitigating repudiation threats. Historically, they're kind of pretty weak.
A lot of technologies don't support it very well, but this is one that supports exceedingly well out of the box. So this is, I'm really, really excited actually about ledger. So this is great to see. So Peter, one thing we ask all our guests is if you have one final thought you'd like to leave our listeners with, what would it be? Well, if you want to have cryptographic proof that your data can be trusted and that it has not been tampered with, just start looking at ledger.
It's easy to use and it's performance and it's just SQL. It's a feature in SQL server. So go ahead and play with it and let us know what you think of it. Okay, Peter, thank you so much for joining us this week. I really appreciate it. I know that with the launch of this, this is going to be pretty busy. So again, thank you very much for joining us and to all our listeners out there. We hope you found this really useful. Ledger is certainly a technology that I'm really excited about.
You can find us on Twitter at azuresecpod.com.