>> You might have been hearing about blockchain and
ledger capabilities in Azure SQL Database and SQL Server 2022,
but now it's also available in Azure SQL Managed Instance.
Learn all about it this week on Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman and welcome to
this episode of Data Exposed.
Today, we have a product manager
from the Azure SQL Security Team joining us.
Yet again, we have Pieter joining us.
Pieter, thanks so much for joining us today.
>> Yeah. Sure. No problem.
Always glad to be here.
>> Yeah, and it's super exciting to have you back on the show.
You're always bringing some bleeding edge
or exciting new announcements.
I don't want to give it away,
but I want to just toss it to you and understand like,
what's new and what are we going to talk about today?
>> Yeah. We're going to talk about ledger again.
It's all about bringing the power of
blockchain to the Azure SQL and SQL Server.
Before we kick-off, just a bunch of questions first.
So how do we know that or how do you know as
a customer that your data
inside your database has not been tampered with?
Just to give you an example.
In my previous life,
I was a database consultant.
I always got enough permissions to
login to the databases of my customers.
I could easily tamper with the data,
I could run UPDATE statement,
even DELETE statements,
and nobody would have ever noticed what I have done.
The same for our customers.
How can they establish trust between their partners,
their stakeholders and so on?
Previously, we didn't have any technology or feature in
SQL or Azure SQL that could help our customers.
But as you know, Anna, we introduced
last year the ledger functionality.
Again, it's bringing the power of
blockchain with the simplicity of SQL.
What we're doing with ledger is we're making the data
inside SQL tamper evidence by using cryptographic technology.
When I say cryptographic,
we're really using blockchain technology under the covers.
Basically, we're capturing
all the transactions and we're going to
cryptographically link these data changes
to each other and make the data,
again, tamper evidence, but also verifiable.
What I mean by that is that an external party,
they can run the verification procedures
to really verify whether the data can be trusted.
What is easy for our customers that it is inside SQL.
So they know already SQL,
they still have the high performance and
the simplicity of a relational database.
It's just really, really easy to kick off and
start using ledger and provide data integrity.
Now, I mentioned that we had this already last year or since
last year in Azure SQL DB and SQL Server.
But now we've introduced also ledger in Managed Instance,
which is currently in public preview.
Everybody that wants to use this feature and
provide data integrity in Managed Instance,
it's there and you can start using it.
>> Awesome. That is great news.
Happy to announce it as part of the show.
For folks who are tuning in,
maybe they're just hearing about
blockchain in SQL and how we do that with ledger,
or maybe they've been ignoring
this functionality because it isn't an Azure SQL Managed Instance.
But now that it's across our whole Azure SQL suite,
can you give us a little refresher on what it is and how it works?
>> Sure. As I said,
it's there and it's enabled by default and we
provide our customers with what we call ledger tables.
We have two types of ledger tables.
We have updatable and append-only ledger tables.
Updatable ledger tables,
they are typically used by
applications because they need to do UPDATE,
and INSERT, and DELETE statements.
Append-only, we block UPDATE and DELETE statements,
so we will just allow INSERT statements.
The user, they have to create these and in this example,
I'm going to use an updatable ledger table.
Let's assume that somebody tries to
modify a row in an updatable ledger table.
What we're going to do is we're going to fetch
the initial version of that row and we're going to
copy that into a history table which has
exactly the same schema like the updatable ledger table.
But on top of that,
we're going to add extra metadata
like the commit time of a transaction,
the user that has executed the transaction so
that we have that historical information.
On top of that, our customers,
they also get the ledger view,
which really gives you a chronicle overview of
what is in your updatable ledger table
and also in your history table.
You have the current value,
but also the historical values.
Now, this is all how we keep track of what happens,
but where do we provide the data integrity?
That is done by what we call a database ledger.
All these transactions on updatable ledger table are hashed
together by using a Merkle tree mechanism under the recovers.
Then what we are going to so is these hashes are
linked to each other just like a blockchain technology.
Once we've provided that block,
that is something that can be done automatically every
30 seconds or it can also be generated manually by the customer.
They can choose. Now, once
we have generated the database digests,
we're going to offload that automatically to trusted storage.
What is trusted storage?
Still the same also in MI is Azure Confidential Ledger,
which is a new service that we're using,
or Azure storage account
with the immutability settings switched on.
Once we've offloaded that database digests,
now it becomes interesting, of course,
because an external party,
as I mentioned in the beginning,
they can now run a verification procedure.
What we're going to do is we're going to fetch
the database digests from the structured storage.
In real-time, we're going to recalculate
the hash inside the database.
Then we're going to compare these two hashes.
If we have a match,
then we have actually cryptographic proof
that the data has not been tampered with and can be fully trusted.
If we don't have a match, of course,
then you also have proved that your data cannot be
trusted and shouldn't be used any longer.
Append-only as I said,
we don't need to have the history table because we only
allow UPDATE and DELETE statements.
Let me quickly show how it works also in a Managed Instance.
First of all, let me switch to the portal.
Here I'm already connected to my Managed Instance,
which is called ledger MI and I've
created a database called ContosoHR.
The new experience exactly like in
Azure SQL Database is that into "Security" tab here,
we're going to have an extra option called "Ledger",
where the customers, where they can
configure the automatic digest storage.
So they can enable or disable it and they can choose between
an Azure storage account that they can select here or,
of course, Azure Confidential Ledger is also
another possibility that they can choose as the storage type.
People that already worked with
Azure SQL DB, this looks familiar.
This is exactly the same functionality,
but now in MI.
>> Nice.
>> Jumping to the management studio,
this is the same ContosoHR database that I have.
You can see I'm not lying.
I'm connected to my Managed Instance here.
I've already created a bunch of ledger table,
so a few append-only ledger tables like
audit events and ledger verifications.
But today we're going to work with
the employee's table which is updatable ledger table.
If I just do a simple SELECT statements,
then you can see a bunch of
records that contains a Social Security number,
first name, last name, and salary.
Apparently, Anna, looks like you're also part of this table.
What we're going to do to your records,
it's not 00 because we're going to add,
let's say $50,000 to your salary.
If we run this UPDATE statement,
we think nobody will notice it.
But of course, it's an updatable ledger table.
If we run this specific query where
we're going to use the ledger view
in combination with the new system table
called database ledger transactions,
then we can actually see what has happened.
We can see here that an UPDATE statement has occurred.
In the history table,
we're going to create two records.
A DELETE operation with the old version of the records,
which was your old salary,
and a new record,
which is an INSERT statement with the new salary.
This is the proof that we
have that somebody actually has tampered with the data.
To be 100 percent sure,
we can also run the verification stored
procedure in Managed Instance.
It's exactly the same syntax,
so the stored procedures called
sp_verify_database_ledger_from_digest_storage.
If we run this, we have a verification and here in the messages,
it says ledger verification successfully verified up to block 10.
Yeah, we don't have much blocks
because this is a demo purpose database.
But with that, we can or we now fully, sorry,
we know that our data that is inside the database can
be fully trusted because the verification was successful.
>> Wow, that's super cool.
I mean, unfortunately it means bad actors like myself in
this scenario can't give ourselves $50,000 raises.
But the good news is that's what enterprises,
what businesses would want.
This ability to catch things and bad actors like myself.
Super cool demo, super exciting to
see this land in Azure SQL Managed Instance.
As folks decide they want to get started with this,
are there any limitations that
they should be aware of when it comes to using
Azure SQL Managed Instance and ledger in
preview or any other tips you have for folks getting started?
>> Sure. No,
there are no limitations.
Everything is available already in public preview.
Well, almost everything.
As you have seen, updatable ledger tables,
append-only ledger tables are available.
The automatic digest storage is available.
There is a portal experience.
Customers can also use PowerShell commandlets for MI,
Azure CLI can be used as well.
We already even supports
specific Managed Instance capabilities like
copy database or GeoDr
is also already supported with ledger tables.
Even native restore,
so if you already should have
configures ledger tables and
automatic digest storage in an on-premises database.
When you restore that to Managed Instance,
we also already support that.
The updatable ledger tables that you had on-premises,
for example, will be available also in your Managed Instance.
The only thing that we do not support yet is a my link,
which is replicating your data from
on-premises database to a managed database.
That is something we currently do not support yet.
>> Awesome, cool. It seems like this is
good for folks to go ahead and get started with.
Pieter, thanks so much for coming on the show.
For our viewers, we are going to put a bunch of
links in the description for you to go learn more.
Go get started with this.
Let us know your feedback.
Any final comments, Pieter?
>> Yeah, please try it out.
It's a really, really cool feature.
If you have any issues, let us know.
We're still in public preview.
We can still change some stuff,
if we need to do that.
So yeah, please try it out.
>> Awesome. Thanks so much, Pieter.
To our viewers, thanks for tuning in.
Leave us a comment and let us know what you
think of this capability in Azure SQL Managed Instance,
and we hope to see you next time on Data Exposed.
[MUSIC]