>> Learn about how we're combining the power of blockchain with
the simplicity of SQL this week on Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman and
welcome to this episode of Data Exposed.
Today I'm joined by Jason,
a Program Manager on the Azure SQL Security team.
Jason, thanks for joining us.
>> Hi Anna, happy to be here.
>> Happy to have you here.
Before we get started,
could you kick us off by just telling
us a little bit about what you do?
>> Sure. I'm a Program Manager on
the Azure SQL Security teams so that means my team is
responsible for ensuring all the security controls
that you need to secure your SQL estate are there and easy to use.
>> Awesome. Today we're going to be talking about
a very cool and new technology
that's in SQL now related to blockchain.
But before we get into that,
just because maybe not all of our viewers know about blockchain.
Can you tell us a little bit about blockchain and then also
the challenges with blockchain
that led the team to invest in this tech.
>> Absolutely. Blockchain is,
it's a nascent new technology.
There's a lot of buzzwords going on around it, crypto, NFTs, etc.
But enterprises specifically, have been
really excited about blockchain primarily
because of its immutability characteristics
where you can share data with
other parties that you may not trust very
well and ensure that that data has not been tampered with,
that it's truly immutable
and basically help with things like non-repudiation.
>> Awesome, okay, that makes sense.
I'm guessing we're going towards something that's
more Software as a Service or Platform as a Service.
Maybe even you can take advantage of
your existing Azure SQL deployments.
But can you just tell us now a little bit more
about Azure SQL Database ledger and what it actually is.
>> Absolutely. As we just talked about,
blockchain is really just spurred the interest of
enterprise specifically with that point of immutability,
and one of the core scenarios that they've been
seeing is the fact that today audits are really,
really hard to do and you have to have somebody
come in manually from an auditing firm and
inspect your security controls and
make sure that everything is set up such that your data is safe.
You can use a technology like
blockchain that enables digital trust,
where you can cryptographically prove to
an auditor that data has not been tampered with.
It's just super, super interesting.
A lot of companies have flocked to
blockchain to help solve that problem.
Now, one of the challenges though is that
fundamentally systems that are centralized,
so think of existing database systems
today where you want to digitize that trust.
Moving over to a blockchain is really,
not only hard to do, but it also is a lot of infrastructure.
Blockchain is called multi-party computing,
that means that every party that's going to participate in
the blockchain network has to host infrastructure and node,
if you will, to have a copy of that blockchain ledger.
it requires you to build a net new system.
we've had customers over the years
ask us during the blockchain journey we've gone on,
how do I enable this scenario for my databases?
It's a really good question because we really haven't had
an answer until now.
Azure SQL Database ledger is really about bringing
the power of blockchain into SQL Database.
Effectively, what we're doing is we're making
the data in SQL tamper-evident using cryptography.
When I say cryptography,
it's the same blockchain type patterns that you
would see in blockchain today, and I'll talk about that.
Now, in addition to that, we provide a way for
external parties that don't host that database to be able
to have a cryptographic proof that the data has not been tampered
with in that database or
interfacing with another party might be hosting.
That's really, really powerful and probably
the most important part is the fact that
this is a feature of Azure SQL Database.
It's not a new version of SQL or a new PaaS service.
It's effectively something that you'll be able to turn on for
your existing databases and then have that level of
tamper-evidence available for your applications.
Why don't we dive in real quick
and talk about really what we're doing here.
There's two types of functionality that we're
enabling in Azure SQL Database.
We call them ledger tables.
It's not a new table type.
It's a new property of tables that already exists.
So your existing tables effectively can be ledger tables.
We have two types,
and we'll talk about updateable ledger tables
first because this is
typically the pattern that we see with
Azure SQL Database applications today.
Let's assume that I'm doing an update to my database.
What happens with an updateable ledger table
is that when I've sent an update,
let's say I'm going to change a particular column value in a row.
That previous row version
has actually moved over to a history table.
Now those of you that are familiar with SQL are saying,
Well, that sounds like Temporal that we're in.
It's very similar to Temporal.
I should say it's actually built upon Temporal.
But there are some differences that will
come in here that you'll see in a second.
Now, in addition of moving
that historical row into this history table,
we also add some metadata to the table
itself to help with forensics and data lineage purposes.
For example, every transaction that comes in
is assigned a unique transaction ID.
So you know which transaction
actually initiated the changes in the database.
We also capture commit time and
other information that's useful for that forensic purposes.
Now, we also construct what's called a ledger view.
The Ledger view gives you a chronicle of your data over time.
it shows you that this is what the current value of your row is,
but hey, here's what the historical value is.
So rather than having to query
the updateable ledger table and the history table separately,
we construct that view to make it easier.
Now we've just talked about
the data lineage piece and capturing history.
We haven't talked about really the blockchain element here.
Data is protected in what we call the database ledger.
Think of this as a blockchain data structure
that's implemented in system tables in SQL.
Effectively, what happens is every transaction coming into
the system is also hashed using SHA-256 hashing.
This is very similar to what you'll see in a blockchain.
Every row in a transaction is hashed
using a Merkle tree data structure that creates a root hash.
Then we batch all of those transactions into blocks.
What that means is
every 30 seconds or so
a new block is created that will consist of all of
the transactions that have occurred during
that time and that block is
cryptographically hashed as well and link
to the previous blocks just like a blockchain.
Now, at that point,
once a block is produced,
that root hash representing that block is then pushed
outside of SQL and we push it into a trusted storage location.
A trusted storage location could be
Azure Immutable Blobs or another service
we announced recently called Azure Confidential Ledger,
which is a tamper-proof storage service,
or you can store it in
your own tamper-proof storage service if you want,
such as an on-premises warm device.
This is where it's interesting for other parties, auditors,
or other business parties that want to
verify that your data has not been tampered with.
They can run a stored procedure that effectively
references that database digest and trusted storage.
We then recalculate the hashes real-time inside a SQL,
and either the hashes match or they
don't match what was in the digest.
If they don't match, we know that a tampering event has occurred.
So it's pretty easy to figure out
whether there's been tampering or not and
in most cases there isn't tampering.
Providing that cryptographic proof
that tampering hasn't occurred, it's really powerful.
Now, we also have append-only ledger tables,
which are very similar.
The primary difference to updateable ledger tables
is we block updates and deletes at the API layer.
That means that a privileged user such as a DBA can't issue
an update or delete inside of Azure Data Studio as an example,
and modify data as well.
So both patterns are available.
>> Why don't we go ahead and jump into a demo here.
What I'm going to show you is I'm connected to
a database that I've already created a table.
In the table that I've created,
think of it as a simple banker scheming
scenario where I have a customer and their bank balance.
What you'll see here is that when I created
this T-SQL statement to create
this schema that I specified the ledger equals on argument.
It's a new argument that makes that table updateable ledger table.
Now, once I've created that table,
you'll see that actually what's been created are three objects.
The account balance table itself.
But then also that history table that I talked about
earlier and that ledger view that I told you about earlier.
Now what I want to do is I want to show you,
in addition to how you create these tables inside of T-SQL,
you can also enable the ledger functionality when
you create a SQL database inside of the Azure portal.
When I'm creating a new database,
effectively what happens here,
you'll see that in
the create database experience there's
a new tab called the Security tab,
and underneath here's an area called Ledger.
Now it's not configured by default because there are
some implicit decisions I have to make
specifically around that digest storage.
Where do I want to store those digests?
If I click on "Configure ledger",
you'll see that I have a couple of options here.
The first is I can have
the ability to enable
ledger functionality for this entire database.
What that means is if I check this box,
every table that I create in the future,
whether I specify ledger equals on or not in
my T-SQL will automatically be an updateable ledger table.
This ensures that nobody can create
tables in that database that are not ledger tracked.
Now you don't have to do that.
I could unclick that and I could still do it through
T-SQL but we're going to just do that here now.
Now, down below is the digest storage options.
You have the option to automatically
connect to Azure Storage or as you can see here,
Azure Confidential Ledger, which I mentioned is
a new service that provides [inaudible] storage.
During the lifecycle of this table,
I can't turn ledger off once I already have it on.
That's the security constraint.
We want to make sure that once
a table is ledger enabled, it always will be.
But I can't change my digest locations over time.
I could start with Azure Confidential Ledger,
but maybe later down the road I want to switch to
Azure Storage, so that's supported.
Now, once I've created that database,
you can go to the manage experience and under Security,
there's Ledger option here and you'll see
effectively the same experience.
Now one of the core differences here is the fact that this
checkbox that cannot uncheck once
I've enabled that ledger functionality.
Again, it's there for good for security reasons.
Again, I could change that storage location over time.
Now, I talked about earlier how to verify
the database and what actually is happening there.
We've built into the Azure portal a way to easily do that.
If I click on the "Verified database" command,
with the toolbar here,
we pre-populated T-SQL script
that will run the verification process.
I can copy this script,
I can run it in Query Editor but let's
go into Azure Data Studio and run it here.
I'm going to go ahead and paste this script in,
and we're going to go ahead and run it.
What we're effectively doing here is
we're pulling the locations where
digests have been stored because I have
a multiple locations and we're running the verification process.
What we'll see here is the results will provide
really two results inside
of the window here that
the success of the ledger verification itself,
but also the locations where the digest are stored.
Let's look at the digest location storage first.
This is important to inspect because theoretically an attacker
could point you to
a different storage location with forge digests,
so it's really important to protect those digests
because they are the root of trust for the database.
As you can see here, I've had
two locations that are configured over time.
This one is I'm currently using Azure Storage Blobs.
This true indicates that the current location.
This one here, Azure Confidential Ledger,
is where I previously had configured,
which is why it says false.
As you can see, running verification is really quick.
If I go into the messages window here you could see that
ledger verification ran successfully and
it tells you up to which block it referred
to inside of that verification process.
>> Awesome. Thanks Jason.
What I really like about this is how you're natively
building this experience into the Azure SQL experience.
In the portal during deployment,
you have these different blades.
After deployment in the portal you have
a whole ledger tab where you can
configure these things and learn what the T-SQL is.
I also really like that you're using T-SQL throughout.
Now, I don't have to go learn a new tool or learn a new language.
Once I understand the concepts,
I can go ahead and start taking advantage of this.
This is really cool, really promising.
I think a lot of people are going to be excited about this.
As we come back to scenarios overall,
like we looked at the tag,
what are some scenarios you see customers
using this for or that you think this can be used for?
That's a great question because it's really multifaceted.
Number 1, we see ledger as a fundamental security technology,
meaning any production data that you have in
your SQL database that you rely on
for your business, you really should protect,
and even if that's protecting from
your own internal DBAs and folks inside of your organization,
so protecting that data is important.
But in addition to that, as I alluded to earlier,
streamlinig the audit process.
Whether external or internal audits is really super important
being able to provide that cryptographic proof
that it has not been tampered glass.
That's one of the core ones.
Now in addition to that, this next one,
multi-party business processes.
This is an example where we've seen
over the years customers that have initially
gone to blockchain or tried to go to blockchain to
solve a data-sharing integrity problem.
But found that blockchain is just a lot to do.
Really what they needed is
that digital trust and they really
have fundamentally a centralized system.
We've seen customers that actually started down
the path of block chain and it's been hard,
and instead pivot over to something simpler using
Azure SQL Database Ledger to
solve that multi-party business process.
Then lastly, Azure SQL Database Ledger
is a great addition to an existing blockchain.
The reason being is blockchains are
notoriously challenging to be able
to query the data on the blockchain itself.
It's not a OLTP type system.
The blockchain itself has really wants to spend
its time validating blocks and committing to the ledger.
It's not optimized for high-throughput query.
A typical pattern that we've seen with
blockchains is that customers
will take the data that's on the chain
itself and then replicate it to what's called off-chain,
copying it to a database,
and then they do Power BI and analytics on that database itself.
But once you do that, you lose
the integrity that a blockchain
affords because it's now in a database.
Well, that Azure SQL database ledger connected to a block chain,
you keep that integrity from
the blockchain to the off-chain store itself.
It's very powerful for existing block chains as well.
>> I see very cool. Thank so much Jason.
This is really insightful.
Final question I have for you today is to ask
about some resources that you might
have that we can put in the description,
and then I also any advice
for folks who are just getting started with us.
>> Now, absolutely. As far as getting started with it,
really the resources are the key P. Number 1,
we don't announce blog,
and that's a good place to visit because it
showcases some of our customers
that are using the service of how they're using it.
Then of course, our documentation is very good.
We made sure to add some very easy tutorials.
In fact, the example that I just gave him, the banking schemas,
one of our tutorials to go through and just to learn
the relationship between the datable ledger table,
the view in the history table, etc.
Now for those of you that want to go a bit deeper
and understand the cryptography a bit more.
We've great white paper that's been produced and available that
goes into the depths of the system
itself and how that cryptography works.
Of course, if you want to watch
the Build Keynote that we had that's available for streaming.
Then lastly, I'd really like to
highlight one of Microsoft's close partners has done in terms of
building a set of resources and
sample applications that are using
Azure SQL database ledger to get
a practical example of how you could use it
for protecting health care records in that particular case.
>> Awesome, great. Well, again,
Jason, thanks so much for joining us today.
To our viewers we'll
put links to all of that image description
of this video, so check it out.
Go read more about this.
The white paper is super-interesting.
Jason, I want to thank you again so much for coming on
the show and to our viewers of Data Exposed.
Please like this video.
Leave us a comment and let us know what you think
of Azure SQL database ledger.
We hope to see you next time on Data Exposed.
[MUSIC]