WEBVTT
00:00:00.920 --> 00:00:02.610
Welcome to Microsoft Build 2017.
00:00:02.610 --> 00:00:04.620
My name is Tommy Mullaney.
00:00:04.620 --> 00:00:05.851
And I'm on
the engineering team for
00:00:05.851 --> 00:00:06.923
SQL Server here at Microsoft.
00:00:06.923 --> 00:00:10.596
In this video we're gonna talk
about securing your application
00:00:10.596 --> 00:00:12.508
data using SQL Server 2017.
00:00:12.508 --> 00:00:15.793
Now, if you follow the news
really at all over the last few
00:00:15.793 --> 00:00:18.532
years you don't need to
hear this from me, data
00:00:18.532 --> 00:00:22.430
security today is more important
than it ever has been before.
00:00:22.430 --> 00:00:25.380
The global cost of data breaches
is estimated to rise to
00:00:25.380 --> 00:00:28.400
more than $2 trillion
over the next few years.
00:00:28.400 --> 00:00:29.940
And organizations of all shapes,
00:00:29.940 --> 00:00:32.270
and sizes are going to
be impacted by this.
00:00:32.270 --> 00:00:34.360
And in fact, all the way
up to corporate leadership.
00:00:34.360 --> 00:00:37.057
Companies across the board
are doubling down on their data
00:00:37.057 --> 00:00:39.378
security precautions,
and security measures.
00:00:39.378 --> 00:00:42.540
At the same time data security
is still perceived by most as
00:00:42.540 --> 00:00:46.510
being a real burden, something
that's painful and difficult.
00:00:46.510 --> 00:00:48.950
And so, it's with this
context and this background
00:00:48.950 --> 00:00:51.410
that I want to leave you
with this message today.
00:00:51.410 --> 00:00:55.390
SQL Server 2017 offers
you a security toolset
00:00:55.390 --> 00:00:58.940
that enables you to fight these
trends and to build more secure
00:00:58.940 --> 00:01:01.970
applications more easily
than ever before.
00:01:01.970 --> 00:01:05.286
These security features are
built in, they're easy to use,
00:01:05.286 --> 00:01:08.730
they require a minimal, if any
changes to your application code
00:01:08.730 --> 00:01:11.410
and they work across any
programming language and
00:01:11.410 --> 00:01:13.580
any platform whether
that's Windows or
00:01:13.580 --> 00:01:16.004
Linux or Docker containers or
public cloud or
00:01:16.004 --> 00:01:18.845
private cloud, you name it,
it works everywhere.
00:01:18.845 --> 00:01:21.678
So, how do we think
about security for
00:01:21.678 --> 00:01:24.960
applications that
use SQL server?
00:01:24.960 --> 00:01:27.230
What do you think about
security is in terms of layers.
00:01:27.230 --> 00:01:28.240
And at the center of course,
00:01:28.240 --> 00:01:31.200
you have your data and then,
the first layer of security is
00:01:31.200 --> 00:01:34.130
all about how you protects
the data itself typically,
00:01:34.130 --> 00:01:35.590
by using some form
of encryption.
00:01:36.700 --> 00:01:38.991
The next layer of security
is about controlling access
00:01:38.991 --> 00:01:40.733
to the data,
through authentication and
00:01:40.733 --> 00:01:43.290
authorization, who has access
to which parts of your data?
00:01:43.290 --> 00:01:46.392
Then the outer layer of security
is about monitoring access and
00:01:46.392 --> 00:01:49.268
keeping track of who is doing
what against your data base.
00:01:49.268 --> 00:01:52.220
And so in this video, I want
to briefly run through each of
00:01:52.220 --> 00:01:54.250
these different layers
of security and
00:01:54.250 --> 00:01:57.261
show you how SQL Server provides
you with the tools you need
00:01:57.261 --> 00:02:00.560
to secure each of these
layers of your application.
00:02:00.560 --> 00:02:01.999
So let's start with
protecting data,
00:02:01.999 --> 00:02:03.096
the first layer of security.
00:02:03.096 --> 00:02:06.516
There are three different
encryption scenarios that I
00:02:06.516 --> 00:02:07.920
wanna touch on here.
00:02:07.920 --> 00:02:10.840
The first one is what we
call encryption at rest.
00:02:10.840 --> 00:02:14.179
And the idea here is, and
this is a very common compliance
00:02:14.179 --> 00:02:17.309
requirement, the idea is that
you want your data to be
00:02:17.309 --> 00:02:20.927
encrypted at rest so that if the
data files or the discs on which
00:02:20.927 --> 00:02:24.615
the data resides are stolen by
an attacker or some third party,
00:02:24.615 --> 00:02:28.608
all they're gonna have access to
is random gibberish ciphertext.
00:02:28.608 --> 00:02:31.893
And so SQL Server offers a
feature called transparent data
00:02:31.893 --> 00:02:34.172
encryption, that makes
it very easy for
00:02:34.172 --> 00:02:37.056
you to encrypt your entire
database at REST without
00:02:37.056 --> 00:02:40.200
requiring any changes to
your application code.
00:02:40.200 --> 00:02:42.320
Because the SQL Server Database
engine
00:02:42.320 --> 00:02:43.360
handles all the encryption and
00:02:43.360 --> 00:02:47.470
decryption as data is read
from and written to disk.
00:02:47.470 --> 00:02:49.100
Backup encryption
works similarly,
00:02:49.100 --> 00:02:52.670
in that it allows you to encrypt
data as you're taking backups.
00:02:52.670 --> 00:02:55.930
And cell level encryption
refers to the various T-SQL
00:02:55.930 --> 00:03:00.260
cryptographic functions that are
built into the T-SQL language,
00:03:00.260 --> 00:03:03.300
that enable you to granularly
encrypt individual cells or
00:03:03.300 --> 00:03:05.040
columns of data.
00:03:05.040 --> 00:03:06.231
So that's the encryption
at rest scenario.
00:03:06.231 --> 00:03:10.610
The next encryption scenario is
called encryption in transit.
00:03:10.610 --> 00:03:13.649
And the idea here is that you
want to encrypt communications
00:03:13.649 --> 00:03:16.629
between your client's
application and your SQL Server,
00:03:16.629 --> 00:03:19.484
so that a man in the middle
isn't able to intercept those
00:03:19.484 --> 00:03:23.180
communication and eavesdrop or
manipulate those packets.
00:03:23.180 --> 00:03:25.647
And there's a pretty standard
industry solution here and
00:03:25.647 --> 00:03:27.316
it's called the TLS
1.2 protocol.
00:03:27.316 --> 00:03:30.668
And I'd highly encourage you
to configure all of your
00:03:30.668 --> 00:03:34.760
applications to use TLS 1.2
when connecting to SQL server.
00:03:34.760 --> 00:03:37.789
In fact, this is something that
we do in our Microsoft Azure
00:03:37.789 --> 00:03:39.072
cloud across the board.
00:03:39.072 --> 00:03:42.540
So that's encryption in transit.
00:03:42.540 --> 00:03:44.250
The final encryption
scenario that I wanna
00:03:44.250 --> 00:03:47.000
talk about is one that I think,
personally is really cool,
00:03:47.000 --> 00:03:49.100
and that's called
client side encryption.
00:03:49.100 --> 00:03:51.110
And I want to dig into this
one just a little bit more,
00:03:51.110 --> 00:03:52.880
because it's so interesting.
00:03:52.880 --> 00:03:56.180
So the idea here is that I
need to be able to store
00:03:56.180 --> 00:04:00.570
really sensitive data like
some Social Security numbers,
00:04:00.570 --> 00:04:03.350
date of birth, address, or
other personally identifiable
00:04:03.350 --> 00:04:06.180
information, exactly the kinda
stuff that you don't
00:04:06.180 --> 00:04:08.560
want to get out in
the event of a data breach.
00:04:08.560 --> 00:04:11.510
So it's highly sensitive data
being stored inside SQL Server.
00:04:11.510 --> 00:04:12.820
But at the same time,
00:04:12.820 --> 00:04:15.940
I need to somehow prevent
my high privileged
00:04:15.940 --> 00:04:19.670
SQL Server sysadmins from being
able to access that data.
00:04:19.670 --> 00:04:23.180
So traditionally, this is kind
of a difficult problem to solve,
00:04:23.180 --> 00:04:25.500
because if you're
a SQL Server sysadmin,
00:04:25.500 --> 00:04:27.470
you kind of have
the keys to the kingdom.
00:04:27.470 --> 00:04:31.220
You can access anything that's
ont hat SQL server instance.
00:04:31.220 --> 00:04:33.810
So of course, the solution
is to encrypt the data
00:04:33.810 --> 00:04:36.090
before it even gets to
the SQL server instance,
00:04:36.090 --> 00:04:39.710
and this is where
SQL Server 2016 introduced
00:04:39.710 --> 00:04:42.650
a really cool new feature,
called always encrypted.
00:04:42.650 --> 00:04:46.170
And what it does is it encrypts
all of the data client side,
00:04:46.170 --> 00:04:49.490
using a master key that's
stored outside of SQL server,
00:04:49.490 --> 00:04:52.380
that only the application
has access to.
00:04:52.380 --> 00:04:54.860
So even though the data is
stored inside SQL Server in
00:04:54.860 --> 00:04:58.330
encrypted form, your SQL Server
admins don't have access to the
00:04:58.330 --> 00:05:01.460
key and therefore they cannot
access the plaintext data.
00:05:01.460 --> 00:05:03.990
What's particularly cool
about Always Encrypted
00:05:03.990 --> 00:05:07.683
is that our enhanced client
drivers like ODBC, or JDBC, or
00:05:07.683 --> 00:05:11.060
ADO.NET, the same drivers that
you're used to working with,
00:05:11.060 --> 00:05:13.720
these drivers handle all of the
encryption and decryption for
00:05:13.720 --> 00:05:16.610
you, so that your application
code doesn't have to make
00:05:16.610 --> 00:05:19.000
all sorts of sweeping changes
across your code base.
00:05:19.000 --> 00:05:20.826
The driver does all the grunt
work and the hard work for you.
00:05:20.826 --> 00:05:23.506
And this winds up being
a heck of a lot easier than
00:05:23.506 --> 00:05:26.655
the alternative options you
have, which are either buy
00:05:26.655 --> 00:05:30.139
a third party solution which is
expensive or write some custom
00:05:30.139 --> 00:05:33.750
application code which is
both risky and expensive.
00:05:33.750 --> 00:05:35.750
So, that's Always Encrypted
in a nutshell and
00:05:35.750 --> 00:05:37.650
I'm going to come back to
this because I wanna demo for
00:05:37.650 --> 00:05:40.290
you just how easy it is to get
started with Always Encrypted
00:05:40.290 --> 00:05:43.690
using a simple application,
okay.
00:05:43.690 --> 00:05:47.460
So, that's our first layer of
security about protecting data.
00:05:47.460 --> 00:05:49.110
Jumping back to our diagram,
the second
00:05:49.110 --> 00:05:52.400
layer of security is about
controlling access to data.
00:05:52.400 --> 00:05:54.990
And the way we think about that
with SQL server, is they're
00:05:54.990 --> 00:05:58.500
are kind of two buckets of
access control features.
00:05:58.500 --> 00:06:00.760
The first is what we
call database access, and
00:06:00.760 --> 00:06:03.340
these are features that
allow you to control
00:06:03.340 --> 00:06:05.510
who can access the data for
00:06:05.510 --> 00:06:08.130
people who can connect to
the database directly.
00:06:08.130 --> 00:06:09.790
And this is where we
have features like SQL
00:06:09.790 --> 00:06:12.710
authentication, active
directory authentication, and
00:06:12.710 --> 00:06:16.090
a fine-grained permission system
that allows you configure
00:06:16.090 --> 00:06:19.630
least privileges accounts for
your applications to run under.
00:06:19.630 --> 00:06:21.820
And this is one that I want to
highlight in particular from
00:06:21.820 --> 00:06:24.000
an application
security perspective.
00:06:24.000 --> 00:06:27.250
All of your applications should
connect to SQL Server specifying
00:06:27.250 --> 00:06:29.940
that account in the connection
string using a least privilege
00:06:29.940 --> 00:06:32.740
account, and the account has the
minimum permissions required for
00:06:32.740 --> 00:06:33.860
it to do its job.
00:06:33.860 --> 00:06:36.735
In that way, even if your
application code happens to have
00:06:36.735 --> 00:06:38.860
a SQL injection
vulnerability somewhere.
00:06:38.860 --> 00:06:40.990
The amount of damage
that an attacker can do
00:06:40.990 --> 00:06:43.290
is limited by the permissions
that have been granted
00:06:43.290 --> 00:06:46.140
to that application
service account, okay.
00:06:46.140 --> 00:06:48.590
So that's the database
access bucket of features.
00:06:48.590 --> 00:06:51.180
The other bucket of features
here is what we call application
00:06:51.180 --> 00:06:52.130
access features.
00:06:52.130 --> 00:06:52.970
And that's where we have some
00:06:52.970 --> 00:06:55.770
cool stuff that showed
up in SQL Server 2016.
00:06:55.770 --> 00:06:58.950
And the way these work is these
are features that are kind of
00:06:58.950 --> 00:07:01.710
programmability features that
let you define a policy within
00:07:01.710 --> 00:07:04.350
the database itself,
that controls what
00:07:04.350 --> 00:07:06.530
data's going to be returned
to the application and
00:07:06.530 --> 00:07:10.010
ultimately exposed to end
users of that application.
00:07:10.010 --> 00:07:12.792
And by centralizing that access
logic within the database
00:07:12.792 --> 00:07:15.980
itself, you minimize the risk of
error in your application code.
00:07:15.980 --> 00:07:17.950
You also simplify your
application code,
00:07:17.950 --> 00:07:20.466
so that you don't need to worry
about maybe you add a new
00:07:20.466 --> 00:07:23.320
application later that connects
to the same data source.
00:07:23.320 --> 00:07:25.900
You want to have the same
consistent access control policy
00:07:25.900 --> 00:07:27.080
always applied.
00:07:27.080 --> 00:07:30.800
So role level security is one
example of this type of feature,
00:07:30.800 --> 00:07:33.220
where you'd find a policy
within the database.
00:07:33.220 --> 00:07:36.855
That says only these rows should
be returned when this user is
00:07:36.855 --> 00:07:37.410
queried.
00:07:37.410 --> 00:07:40.130
Really useful for cases where
you have perhaps a multitenant
00:07:40.130 --> 00:07:43.410
table, or a table that stores
data from multiple users and
00:07:43.410 --> 00:07:46.810
you want to filter which rows
are returned based on the user's
00:07:46.810 --> 00:07:51.100
department or role or
something along those lines.
00:07:51.100 --> 00:07:54.030
Dynamic data masking similarly,
let's you define a policy
00:07:54.030 --> 00:07:57.660
within the database attached
to a column where you specify
00:07:57.660 --> 00:08:00.870
a masking function that will be
applied, so that whenever data
00:08:00.870 --> 00:08:03.370
is returned from that
column to the application,
00:08:03.370 --> 00:08:06.190
that masking function's applied
so that you only expose, for
00:08:06.190 --> 00:08:09.470
instance, the area code
of a phone number or
00:08:09.470 --> 00:08:12.220
the last few digits
of an account number.
00:08:12.220 --> 00:08:13.520
So these are useful features
00:08:13.520 --> 00:08:14.930
from a programmability
standpoint.
00:08:14.930 --> 00:08:17.400
They help you simplify
your application code.
00:08:17.400 --> 00:08:19.119
Okay, so
that's the access control layer,
00:08:19.119 --> 00:08:20.348
that second layer of security.
00:08:20.348 --> 00:08:24.190
The third layer of security is
what we call monitoring access.
00:08:24.190 --> 00:08:26.650
And the idea here is you
want to be able to track
00:08:26.650 --> 00:08:29.240
what activities are happening
against your database.
00:08:29.240 --> 00:08:31.875
And this is where SQL server
offers a feature called,
00:08:31.875 --> 00:08:33.470
fine-grained audit.
00:08:33.470 --> 00:08:36.200
And the idea here is you want
to be able to set up a policy
00:08:36.200 --> 00:08:38.660
within the database that says,
I want to track
00:08:38.660 --> 00:08:41.740
these users doing these
actions against these objects.
00:08:41.740 --> 00:08:43.480
And once you specify
this policy,
00:08:43.480 --> 00:08:45.950
the SQL Server database
engine will go ahead and
00:08:45.950 --> 00:08:48.880
log all activities according
to that policy, and
00:08:48.880 --> 00:08:51.680
write it all to a log file
that you can use later on.
00:08:51.680 --> 00:08:54.120
This is similarly to
transparent data encryption.
00:08:54.120 --> 00:08:58.210
This is a really common feature
popularly used for compliance
00:08:58.210 --> 00:09:01.270
requirements where you need
to have an audit trail, okay.
00:09:01.270 --> 00:09:05.480
So that in a nut shell is how
we think about security for
00:09:05.480 --> 00:09:08.050
SQL Server in terms of these
layers protecting data,
00:09:08.050 --> 00:09:10.746
controlling access, and
then monitoring access.
00:09:10.746 --> 00:09:14.272
And one of the things I wanna
highlight here that's really,
00:09:14.272 --> 00:09:17.530
for my opinion, really great is
that starting with SQL Server
00:09:17.530 --> 00:09:19.080
2016 Service Pack 1,
00:09:19.080 --> 00:09:21.930
which was released in November
2016, a bunch of these
00:09:21.930 --> 00:09:25.440
features that used to only be
available in Enterprise edition
00:09:25.440 --> 00:09:28.510
are now available across
all editions of SQL Server.
00:09:28.510 --> 00:09:31.320
And the idea here is that we
want to provide a consistent
00:09:31.320 --> 00:09:32.860
programming surface area, so
00:09:32.860 --> 00:09:35.310
that you can write your
applications that use and
00:09:35.310 --> 00:09:36.850
leverage these
security features.
00:09:36.850 --> 00:09:39.930
And not have to worry about
which edition of SQL Server
00:09:39.930 --> 00:09:41.910
is being used as the back end.
00:09:41.910 --> 00:09:44.712
So features like always
encrypted, row level security,
00:09:44.712 --> 00:09:47.170
dynamic data masking,
and fine-grained audit,
00:09:47.170 --> 00:09:50.142
these are available across all
editions of SQL Server ranging
00:09:50.142 --> 00:09:52.562
from Express all the way
up to Enterprise, okay.
00:09:52.562 --> 00:09:55.918
So that's how we think about
security with sequel server.
00:09:55.918 --> 00:09:58.540
What I wanna do now is I
want to jump into a demo and
00:09:58.540 --> 00:10:01.890
show you just how easy it is
to set up Always Encrypted
00:10:01.890 --> 00:10:04.040
to encrypt some sensitive
columns of data, so
00:10:04.040 --> 00:10:07.170
that even my database sysadmins
can't access the plaintext.
00:10:07.170 --> 00:10:09.690
And this is just gonna be one
example of how easy it is to set
00:10:09.690 --> 00:10:12.110
up these features, but I think
you'll find it illustrative.
00:10:12.110 --> 00:10:15.100
So with that, let's jump
out of PowerPoint here and
00:10:15.100 --> 00:10:18.504
switch on over to our
favorite Visual Studio code.
00:10:18.504 --> 00:10:22.705
So here what I've done
is I've downloaded
00:10:22.705 --> 00:10:26.555
a popular Open Source Django
project called Boot Camp.
00:10:26.555 --> 00:10:30.705
And it's basically an Open
Source enterprise social network
00:10:30.705 --> 00:10:31.695
application.
00:10:31.695 --> 00:10:34.705
And it's written in Python, uses
the Django web app framework.
00:10:34.705 --> 00:10:36.425
It's a framework that I
personally really like which is
00:10:36.425 --> 00:10:37.545
why I chose it.
00:10:37.545 --> 00:10:40.635
And what you'll see here is if
we jump on over into Settings,
00:10:41.740 --> 00:10:44.200
you'll see my database
connection string,
00:10:44.200 --> 00:10:47.990
where I'm using the ODBC
driver with PI-ODBC.
00:10:47.990 --> 00:10:52.250
And if we take a look at
the host, I'm connecting to
00:10:52.250 --> 00:10:56.340
a SQL server that happens to be
running on an Ubuntu machine.
00:10:56.340 --> 00:10:58.220
So I've got my application here,
Django,
00:10:58.220 --> 00:11:01.690
Python running on my
Windows 10 laptop.
00:11:01.690 --> 00:11:04.630
And it connects to a SQL Server
that's running on Ubuntu 16.04.
00:11:04.630 --> 00:11:08.010
I could configure this
really in anywhere I want.
00:11:08.010 --> 00:11:08.950
The operating system and
00:11:08.950 --> 00:11:10.720
the programming language
just don't really matter.
00:11:10.720 --> 00:11:12.613
But this will demonstrate
just how it works with one
00:11:12.613 --> 00:11:13.898
of these configurations, okay.
00:11:13.898 --> 00:11:16.133
So just to give you an idea
of what this looks like,
00:11:16.133 --> 00:11:17.370
let's go ahead and run it.
00:11:21.090 --> 00:11:23.010
We hit manage run server.
00:11:23.010 --> 00:11:25.860
We give it a moment to load,
okay, cool.
00:11:25.860 --> 00:11:27.996
So the application is running,
00:11:27.996 --> 00:11:32.274
let's jump on over into edge and
refresh our local host, okay.
00:11:32.274 --> 00:11:34.321
So this is what
Boot Camp looks like,
00:11:34.321 --> 00:11:37.694
it's a pretty standard social
networking looking site with
00:11:37.694 --> 00:11:40.089
a feed article network
that kind of stuff.
00:11:40.089 --> 00:11:42.670
There's a whole bunch
of functionality here.
00:11:42.670 --> 00:11:45.514
But what I wanna focus on is if
we jump on over into account
00:11:45.514 --> 00:11:48.064
settings, you'll see my
profile information.
00:11:48.064 --> 00:11:51.320
And this is information that's
perhaps a little sensitive.
00:11:51.320 --> 00:11:52.748
It's certainly okay for
00:11:52.748 --> 00:11:55.876
me to see it because it is
after all my information, but
00:11:55.876 --> 00:11:59.140
maybe we don't want our SQL
Server sysadmins to be able to
00:11:59.140 --> 00:12:02.880
access the job title information
or the location information for
00:12:02.880 --> 00:12:06.160
all of the different users
of this application.
00:12:06.160 --> 00:12:09.140
So this is a perfect used case
for always encrypted where we
00:12:09.140 --> 00:12:12.790
can use it to transparently
encrypt these values before
00:12:12.790 --> 00:12:15.910
they're sent to SQL Server and
then transparently decrypt them
00:12:15.910 --> 00:12:18.840
as they come back to our
application code, okay.
00:12:18.840 --> 00:12:22.477
So to enable always encrypted,
the easiest way is to jump on
00:12:22.477 --> 00:12:26.555
over into SQL Server Management
Studio where I've connected here
00:12:26.555 --> 00:12:29.855
to my Ubuntu SQL Server and
I have my Django database.
00:12:29.855 --> 00:12:31.976
Just to show you the operating
system that this system that
00:12:31.976 --> 00:12:33.758
this thing is running on
let me do a quick select,
00:12:40.703 --> 00:12:41.739
Run this guy and
00:12:41.739 --> 00:12:46.300
you can see that this is running
on Linux Ubuntu 16.04, okay.
00:12:46.300 --> 00:12:54.740
So the table that I wanna look
at is called off profile.
00:12:54.740 --> 00:12:55.620
And if we select from this,
00:12:55.620 --> 00:12:58.330
you'll see there's a location
and the job title information
00:12:58.330 --> 00:13:00.370
that I was just looking
at through the front-end.
00:13:00.370 --> 00:13:03.520
So this are the columns that we
want to go ahead and encrypt, so
00:13:03.520 --> 00:13:06.950
to do that lets hop over
into the object explorer.
00:13:06.950 --> 00:13:12.070
I'm gonna find that table and
here it is auth profile, and
00:13:12.070 --> 00:13:15.520
if I right-click on that,
and select Encrypt Columns,
00:13:15.520 --> 00:13:18.270
this is going to bring up
the Always Encrypted Wizard.
00:13:18.270 --> 00:13:21.700
Now, if I go ahead and
hit Next, now it prompts me
00:13:21.700 --> 00:13:24.290
to select which column
do I want to encrypt?
00:13:24.290 --> 00:13:27.380
I'm gonna choose the location
and job_title columns and
00:13:27.380 --> 00:13:28.595
now I have a choice.
00:13:28.595 --> 00:13:30.790
What type of encryption
do I want to use?
00:13:30.790 --> 00:13:33.410
Here, I'm going to choose
randomized encryption because
00:13:33.410 --> 00:13:35.300
that offers maximum security.
00:13:35.300 --> 00:13:37.630
The other option is called
deterministic encryption, and
00:13:37.630 --> 00:13:40.320
what's interesting about
deterministic encryption is
00:13:40.320 --> 00:13:42.920
that, whenever you give it, the
algorithm, the same plain text,
00:13:42.920 --> 00:13:45.060
you're always going to
get the same cipher text.
00:13:45.060 --> 00:13:47.120
And this allows you to do
certain types of operations
00:13:47.120 --> 00:13:52.300
like Lookups, equality joints or
indexes on these columns.
00:13:52.300 --> 00:13:53.330
But at the same time,
00:13:53.330 --> 00:13:55.815
if your columns only have a few
different possible values,
00:13:55.815 --> 00:13:58.770
sysadmin might be able to see
patterns in the ciphertext that
00:13:58.770 --> 00:14:00.580
reveal some kind of information.
00:14:00.580 --> 00:14:03.310
So we're gonna go to randomize
because I don't need to do any
00:14:03.310 --> 00:14:05.830
lookups on these columns here,
okay.
00:14:05.830 --> 00:14:09.340
So I hit next and
this is the interesting thing.
00:14:09.340 --> 00:14:11.370
We're gonna automatically
generate that master key,
00:14:11.370 --> 00:14:13.400
that I mentioned, that's gonna
be stored in the Windows
00:14:13.400 --> 00:14:15.540
certificate store where
my client application,
00:14:15.540 --> 00:14:17.090
this Django app, is running.
00:14:17.090 --> 00:14:19.612
It's not going to be stored on
the SQLl Server instance and in
00:14:19.612 --> 00:14:22.285
fact, someone with total control
over that SQL Server instance
00:14:22.285 --> 00:14:23.783
will never have
access to this key.
00:14:23.783 --> 00:14:26.758
And they'll never be able to
access that plaintext data
00:14:26.758 --> 00:14:30.173
because they won't be able to
decrypt it using this key, okay,
00:14:30.173 --> 00:14:31.140
let's hit next.
00:14:31.140 --> 00:14:34.190
It will say finish now or
run it.
00:14:34.190 --> 00:14:37.550
And so this wizard is gonna go
ahead and generate that master
00:14:37.550 --> 00:14:40.500
key, generate a symmetric key
to do encryption operations.
00:14:40.500 --> 00:14:41.690
And it's gonna go ahead and
00:14:41.690 --> 00:14:44.860
actually encrypt that column of
data or the two columns of data.
00:14:46.000 --> 00:14:51.370
Let it run and finish up here,
okay, there we go.
00:14:51.370 --> 00:14:52.270
So let's go ahead and
00:14:52.270 --> 00:14:55.080
close this, and just to show you
the results of what we just did.
00:14:55.080 --> 00:14:57.500
Let's go ahead and
run that same query again and
00:14:57.500 --> 00:14:59.910
what you'll see is that
now the location and
00:14:59.910 --> 00:15:02.630
job title fields are encrypted,
okay, cool.
00:15:02.630 --> 00:15:04.244
What about our
application though?
00:15:04.244 --> 00:15:08.020
If we switch back over to our
application and hit refresh,
00:15:08.020 --> 00:15:09.720
you'll see that there's
an error, there's a type error.
00:15:09.720 --> 00:15:12.480
And this is where there's one
small change that we need to
00:15:12.480 --> 00:15:15.020
make to our application
code to make this work.
00:15:15.020 --> 00:15:18.210
If we switch on over into
Visual Studio, I'm going to
00:15:18.210 --> 00:15:21.550
add one parameter to our
connection string that says
00:15:21.550 --> 00:15:25.320
Column Encryption is enabled and
what this does is it tells
00:15:25.320 --> 00:15:30.110
the ODBC driver to always be
encrypted enabled to be aware of
00:15:30.110 --> 00:15:33.130
columns that should be encrypted
and decrypted by the driver.
00:15:33.130 --> 00:15:35.300
So we'll go ahead and
do that, we'll hit save and
00:15:35.300 --> 00:15:37.320
we'll let Visual Studio
code go ahead and
00:15:37.320 --> 00:15:40.800
refresh our application and
if we switch back over to
00:15:40.800 --> 00:15:44.460
our application here we hit
refresh and there you go.
00:15:44.460 --> 00:15:46.280
Those columns, job title, and
00:15:46.280 --> 00:15:48.920
location are now being
transparently decrypted
00:15:48.920 --> 00:15:51.340
before their return to
our application code.
00:15:51.340 --> 00:15:53.920
We didn't have to make any
changes to the queries or
00:15:53.920 --> 00:15:55.770
anything like that,
it just worked.
00:15:55.770 --> 00:15:59.870
So in summary, SQL Server 2017
gives you an enterprise-grade
00:15:59.870 --> 00:16:02.890
security toolset to build
secure applications
00:16:02.890 --> 00:16:04.830
more easily than ever before.
00:16:04.830 --> 00:16:06.960
We walked through the different
layers of security that
00:16:06.960 --> 00:16:08.510
SQL Server helps you protect.
00:16:08.510 --> 00:16:10.908
Protecting data, controlling
access, and monitoring access.
00:16:10.908 --> 00:16:14.113
And we walked through all the
different security features that
00:16:14.113 --> 00:16:16.773
are available to you that
are built in, easy to use,
00:16:16.773 --> 00:16:20.230
require minimal if any changes
to your application code.
00:16:20.230 --> 00:16:23.690
And can run with any programming
language on any platform, and
00:16:23.690 --> 00:16:26.060
in any edition of SQL Server.
00:16:26.060 --> 00:16:26.986
So with that, what's next?
00:16:26.986 --> 00:16:29.234
If you're just getting
started with SQL Server,
00:16:29.234 --> 00:16:31.748
I'd highly encourage you to
take a look at our developer
00:16:31.748 --> 00:16:33.420
getting started tutorials.
00:16:33.420 --> 00:16:35.660
These tutorials will walk you
through in just a matter of
00:16:35.660 --> 00:16:38.160
minutes how to get started
building an application with
00:16:38.160 --> 00:16:41.190
SQL Server using your favorite
programming language.
00:16:41.190 --> 00:16:42.670
If you already have
a workload ready and
00:16:42.670 --> 00:16:45.200
want to get it going in
production on SQL Server 2017,
00:16:45.200 --> 00:16:49.290
I would encourage you to check
out our Early Adoption Program.
00:16:49.290 --> 00:16:52.400
Where you can sign up here, and
through this program you'll be
00:16:52.400 --> 00:16:55.750
able to work with someone on the
product team like myself, and
00:16:55.750 --> 00:16:58.150
we'll help you get up and
running with SQL Server 2017,
00:16:58.150 --> 00:17:00.918
and we'll offer you full
production support for
00:17:00.918 --> 00:17:01.454
your workload when it's ready.
00:17:02.480 --> 00:17:04.560
And if you're looking to
learn more about the security
00:17:04.560 --> 00:17:06.810
capabilities of SQL Server 2017,
I highly
00:17:06.810 --> 00:17:09.980
encourage you to check out our
SQL Server Security Center.
00:17:09.980 --> 00:17:13.610
This is kind of a hub that we've
put together, that stores all
00:17:13.610 --> 00:17:16.570
the different information,
getting started tutorials,
00:17:16.570 --> 00:17:19.680
documentation pages for all of
the different security features
00:17:19.680 --> 00:17:21.410
that I walk through
in this video today.
00:17:21.410 --> 00:17:23.850
And it's a perfect place to
start off with if you're looking
00:17:23.850 --> 00:17:26.330
to learn more about the things
we talked about in this video.
00:17:26.330 --> 00:17:28.470
So with that, thank you
very much for watching and
00:17:28.470 --> 00:17:30.360
I'll look forward to
hearing about your feedback
00:17:30.360 --> 00:17:33.744
on the security capabilities
of SQL Server 2017.