>> Always Encrypted has been around for a while.
In this episode, you'll learn about Always Encrypted v1 and how
Always Encrypted has improved in v2 in SQL Server 2019.
Tune in to this episode of Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman.
Welcome to this episode of Data Exposed, MVP Edition.
Today, we're very lucky to be joined
by our security expert Mladen.
Mladen, thanks for being on the show.
Can you tell us a little bit more about yourself and what you do?
>> Sure. Thanks for having me.
My name is Mladen Prajdic.
I'm an MVP. I've been one for 11 years. I live in Slovenia.
I've been working with.NET and SQL Server
for the last 20 years now.
I've been involved in all kinds of projects,
from classic line of business to image processing
to a security stuff to optimization.
In my free time, I do
a well known add-in for SSMS called SSMS Tool Pack.
I'm also an organizer for a local SQLSaturday.
I go through all of conferences and give sessions
about any topic that interests me and that's pretty much it.
>> Awesome. We're excited to have you.
Seems like maybe not just security but lots
of topics are in your bandwagon.
Thanks again for joining us today.
Today, specifically, we're going to talk
about Always Encrypted in SQL Server 2019.
This is a really interesting topic and
I think I mentioned before the show,
like I'm excited to have you on and talk about this.
I think it would be good to start off maybe with you
telling us your thoughts on why
end-to-end encryption is useful and what
Always Encrypted or Always Encrypted with
Secure Enclaves brings to the table.
>> For me as a user,
end-to-end encryption is very important because I know I can be
sure that my data that I send to
the server cannot be accessed by anyone,
even by the administrators at the company I'm sending the data to.
Before Always Encrypted, this was very hard to do,
because all of the keys,
the passwords that would decrypt the data would
be stored on the company's servers.
With Always Encrypted, the key and the password is stored with me.
Yeah, but that's the basic,
the main improvement of the general encryption,
the way encryption is being done.
>> Awesome, cool. I like
that simple explanation but also very helpful and relevant.
Today we're going to go deeper into this topic,
and I know you have some content prepared to us.
I'm just going to pass it to you and let you teach us,
teach me because I don't know a ton about it.
How Always Encrypted works
and what are some of the things we need to know.
>> Okay, cool.
We'll take a look at a few slides and I won't be long.
The Always Encrypted in 2019 SQL Server,
I call it the searchable encryption
because that's basically what it is.
We'll go, first, we'll look
at how Always Encrypted version 1 works.
This was introduced in SQL Server 2016 and basically,
what it does is very simple.
You have a client which runs
on a modified or improved editor at the driver,
and you send the data that you
have unencrypted to the driver and the driver encrypts the data,
sends the data encrypted to a SQL Server
which that data gets stored or gets queried.
Once you get the result of your query,
the encrypted the data comes back from the server to the client,
and then the client decrypts that data,
and actual password or the key
for decryption is never sent to the SQL Server.
So even if you have sysadmins on SQL Server,
they cannot decrypt the data because they don't have the key,
they don't have the means to decrypt it.
The downside of this is that the data is encrypted on
the client as it is encrypted on the server,
meaning that you cannot do
any like searches or pattern matching or anything like that.
If you have a business case where this is important,
you cannot really use it that well.
This is where version 2 comes into light.
Version 2, which was introduced in 2019,
SQL Server 2019, introduced the concept of a Secure Enclave.
Basically, what this does is it
leaves a memory space on your SQL Server,
puts the encrypted data into
that memory space and decrypts it there,
makes the operations of pattern matching that you want to do there
and then returns the encrypted data back to the client.
Now, let's see one bit, actually.
Otherwise, it behaves the same as Always Encrypted version 1.
What is this Secure Enclave that we're talking about?
It's basically a virtualization-based security feature.
It's an isolated region of memory within
the address space of user-mode processes.
So basically anything that is not a [inaudible] mode process,
it's a user-mode process.
So SQL Server runs in a user-mode process.
Well, it is basically
an isolated region of memory in the SQL Server process.
It's controlled by the Windows hypervisor
and the data that is sent to
this Secure Enclave is only accessible by
code running in the enclave itself.
But the enclave itself has something called code integrity,
and it's basically a restricted instruction set.
It can do very little things,
but it can still do cryptographic computations.
This code is run in the enclave is also signed with a key
and checked for validity
and if it hasn't been tampered with or anything like that.
The version of Windows that you need for this to work
is either Windows Server 2019 or Windows 10, version 1809.
Now, Always Encrypted version 1 only needed SQL Server, that's it.
You didn't have any other dependencies.
For version 2, this changes.
You need something called a Host Guardian Service,
which basically it proves to the client, so to me,
as a client, that this VBS enclave,
the Secure Enclave of the SQL Server,
is in the correct state and it can be trusted.
This Host Guardian Service is on a separate computer.
That is, it should be admin'd by people
not admining your domain
or not have anything to do with SQL Server.
Now, the way it works,
the Always Encrypted, is you have two keys.
Basically, you have an enclave-enabled column master key
and a column encryption key.
The data in your column
is encrypted with the column encryption key.
>> The own encryption key is then
encrypted with the column master key.
But the password or the certificate for your column master key,
the way to decrypt
the column master key is not stored on the server.
It's stored with the client or in
some other secure location that you can access.
You need the 4.7.2. for this to work.
You can enable it in the connection string or in the application
itself via a property for SQL command.
If you put something
called attestation URL in your connection string,
version 2 of always encrypted will be used.
If you don't put this URL,
version 1 will be used.
This attestation URL is the URL to
your host guardian service machine.
This way, if the client knows that it has to contact,
this host guardian service machine,
and make it prove to the client that
the SQL server it connects to
has its VBS enclave in the correct state.
Now there are two ways
to encrypt data, deterministic and randomized.
Deterministic is, every time you encrypt the data,
the resulting ciphertext will be the same.
Randomized encryption means that every time you encrypt the data,
the resulting ciphertext will be different.
Randomized encryption is completely random as the name states.
Deterministic one, you can guess what the encrypted value
is if you have a cone that only has male,
female values or countries that you do
business with and if somebody knows
that you are doing business mostly with
the US or the UK or the EU is still that,
they can probabilistically guess that that data is
that country that you do most
[inaudible] with if you're encrypting it deterministically.
Because that's the value that is happening most in your data.
What you can do it with
v2 that you couldn't do in v1 is you can do in-place encryption.
Basically you have an existing table and you just say,
''Encrypt my data in the table''.
You can also re-encrypt the column data.
If you have key rotation,
if you want to change your column encryption keys,
if you want to change the actual key that
your column is encrypted with, you can do that.
Basically you just re-encrypt the column data.
You don't have to take the data out,
re-encrypt it and put the data back in like you had to do in v1.
You can change the decryption type.
You can go from deterministic to randomized or vice versa.
You can, of course, decrypt the data.
The best four, it supports all collations.
V1 didn't support all collations.
Now if you want to do rich computations,
pattern matching like searches, stuff like that,
that only works for BIN2 to collations because those are
binary collations and it's a matter of scope of work.
It's something that just hasn't been done yet.
I'm assuming this will be done moving forward.
How do we do this?
In-place encryption, you just say alter table, alter column,
and you say encrypt with the encryption type you want to do,
the encryption key you want to use in the algorithm,
which is just one, but it's enough.
That's it. These two statements will
encrypt my salary column and my SSN column.
I will have randomly encrypted data. It's very simple.
Compare that to v1 where you have to
take the data outside of the table,
encrypt it and then put it back in.
You had to change the scheme of the table to
fix that for binary data.
This is very much better.
It is a high improvement compared to v1.
>> Awesome. Quick question, sorry.
One of this is new to me, so I'm still learning.
It seems like it's fairly easy to use once it's set up.
What is the setup process like?
Because you mentioned there's now a new service that you have
to configure in order to use this always encrypted v2.
>> Here's the thing. I have
a whole day session about always encrypted v2.
On the setup, I spend about a third of it.
>> Okay, so it's not trivial.
>> It's not a trivial setup.
It needs just to set up
the host guardian service correctly for testing.
It's very simple to do,
you just follow the procedure.
But for actual production, it's not that simple.
You have to have different people validate the installations,
administration on these things.
But in the end,
it's just you follow the procedural steps and that's it.
For server-side, it's basically the same.
You just say, "I want to
register my SQL server
with the host guardian service for the certificate."
You copy that certificate over
and they know how to talk to each other this way.
Then in SSMS you say,
"This is my master key, " and that's it.
The main thing is that, the column master key
has to be enabled for secure enclaves.
If you don't do that, it will not work.
>> Okay, cool. That's good to know and
we'll definitely point our viewers to
some additional resources that you might share with us
after this so they can learn more if they're going to set this up.
>> I don't have a link. But if you
use your favorite search engine and you type in,
setting up always encrypted with secure enclaves,
it will be the first result.
The describe how to do it.
The Microsoft documentation on this is actually pretty good.
>> Good, that's a good idea.
>> I've been following. Interestingly, the same documentation
for the CTP version of SQL server 2019, it didn't work.
Because CTP, but for the production version,
it's fine. It works.
>> Wonderful. Sorry to derail you there. I was just curious.
>> Oh, it's fine.
I don't have much slides left.
Basically this is having to do
rich computations and pattern matching.
One thing to note though,
is that rich computations with
secure enclaves are not enabled by default.
You have to use a trace flag 127 to enable them.
Once the service parts,
you have to run this DBCC command.,
otherwise it won't work.
You have to just run it once.
Then when you do rich computations,
it's the same as when you
do normal pattern matching. That's the whole point.
This is what you want to do with encrypted data.
The way this works is your query is
basically sent from the client
to the SQL server with the encrypted data.
The SQL server knows it has to use
a secure enclave because it
has been proven by
the host guardian service that it can be trusted,
asks for your master key from the client.
The client sends it to the secure enclave directly and it
uses that to decrypt the data in this secure enclave.
Then it does this pattern matching
and then returns just the IDs of the rows.
It returns it to the SQL server.
Then SQL server returns those rows back to the client encrypted.
This has to be emphasized.
The data in the SQL server is never decrypted.
It's only decrypted in the secure enclave.
Interestingly though, if you want to
look profile or check with debug,
this secure enclave, it will fail
the secure memory space requirement.
It will show us, I think it shows as empty.
I'm not quite sure.
It will basically report to the user that something has failed,
if something is not secure.
What you can do for the nth,
this is a nice table of what is actually supported.
If your column is not enclave enabled,
and if it is enclave enabled,
and depending on the encryption type you have.
For randomized encryption with enclave enabled column,
you can basically do everything.
You can do in-place encryption,
you can do equality comparison,
you can do pattern matching, everything is supported.
If it's not enclave enabled,
the only thing you can actually do is do
equality comparison with deterministic encryption.
Because you cannot compare something with randomized encryption.
That's pretty much it.
This has been a very quick overview of always encrypted v2.
The point of all this was to somehow bring it closer to you,
to demystify it, and
to maybe start to make you go think about if you can,
somehow implement it in your environment.
>> Thanks Mladen. This has been really interesting.
I especially like your table that you showed at
the end that gives us that comparison.
For me at least and I'm sure for many of our viewers,
you did help in demystifying some of the things related to this,
as far as setting up enough goals,
as far as using your goals,
and as far as what you actually
get depending on what you're using.
If you had one tip for our viewers
as we close out that are just getting started with this,
what would you advise them?
>> For encryption or?
>> Yeah.
>> Okay. Don't be afraid of it.
Because, for me it seems that
encryption still has this negative meaning
that somehow if you need to encrypt your data,
you're doing something wrong.
But this is nonsense because
I always say that your use of security comes first choice.
>> Awesome.
>> With all the breaches happening today, encryption counts.
>> Now, definitely, it's very important.
We really appreciate you coming on
the show and sharing this insights with us.
We'll find some links and we'll share them with
the viewers in the description to this.
If you liked this video,
please go ahead and like this video,
subscribe to our channel.
We actually have some very exciting news in
our next episode of Data Exposed Live.
If you're watching this the day it airs,
tomorrow on Data Exposed Live,
we will be hopefully sharing
some exciting news around always encrypted.
Be sure to tune into that as well.
Thanks for viewing and we hope to see
you next time on Data Exposed.
>> [MUSIC]