WEBVTT

00:00:00.000 --> 00:00:11.610
[MUSIC].

00:00:11.610 --> 00:00:13.680
>> Hi, everyone, my name
is Colin Murphy.

00:00:13.680 --> 00:00:16.065
I'm a Product Marketing
Manager at Microsoft,

00:00:16.065 --> 00:00:17.865
and I'm here with Rohit to

00:00:17.865 --> 00:00:20.730
discuss connectivity
for Azure SQL database.

00:00:20.730 --> 00:00:24.200
So welcome, Rohit. So would
you mind just telling us

00:00:24.200 --> 00:00:28.700
just the basic concepts of how you
connect to Azure SQL database?

00:00:28.700 --> 00:00:31.250
>> Absolutely. Hello,
everyone, my name is Rohit.

00:00:31.250 --> 00:00:34.300
I'm a Program Manager in
the SQL database team.

00:00:34.300 --> 00:00:37.790
Today with Colin, we're
going to start and take you

00:00:37.790 --> 00:00:41.120
through how you connect
to Azure SQL database.

00:00:41.120 --> 00:00:43.375
So with that, let's get started.

00:00:43.375 --> 00:00:45.525
So first question.

00:00:45.525 --> 00:00:48.905
Let's understand a little bit
about our architecture before we

00:00:48.905 --> 00:00:52.370
go to the nitty-gritty of how
you establish a connection.

00:00:52.370 --> 00:00:55.265
So as a customer, what
you need to know is

00:00:55.265 --> 00:00:58.080
that we have a bunch of
SQL Gateways which are

00:00:58.080 --> 00:00:59.895
our front-end machines that taken

00:00:59.895 --> 00:01:02.060
incoming connections over
the Internet when you're

00:01:02.060 --> 00:01:06.825
connecting from on-premises
or outside Azure.

00:01:06.825 --> 00:01:08.710
>> These are just part
of that past service.

00:01:08.710 --> 00:01:10.470
You can just see if it's reckless.

00:01:10.470 --> 00:01:13.510
>> Yes. Then we have a bunch
of back-end machines where

00:01:13.510 --> 00:01:20.195
your actual SQL databases are
hosted and your data lives there.

00:01:20.195 --> 00:01:21.610
>> Okay.

00:01:21.710 --> 00:01:27.450
>> I'm trying to connect to
myserver.database.windows.net,

00:01:27.450 --> 00:01:31.435
that's the usual format
for all database servers.

00:01:31.435 --> 00:01:34.045
Typically, if you do
NsLookup on this,

00:01:34.045 --> 00:01:36.370
it will resolve to
this public IP address.

00:01:36.370 --> 00:01:38.680
The 104.42 IP is

00:01:38.680 --> 00:01:40.870
a public IP address and you're

00:01:40.870 --> 00:01:43.615
going to be connecting
to that on port 1433,

00:01:43.615 --> 00:01:46.055
which is where we're
listening for connections on.

00:01:46.055 --> 00:01:48.885
When you connect, one of

00:01:48.885 --> 00:01:52.380
the common things that
happens is, we basically,

00:01:52.380 --> 00:01:55.395
the SQL Gateway forwards
your connection on,

00:01:55.395 --> 00:01:57.770
so you have a sticky
connection going through

00:01:57.770 --> 00:02:00.545
the Gateway and connecting
to the back-end node.

00:02:00.545 --> 00:02:03.425
This is called a proxy
connection policy.

00:02:03.425 --> 00:02:05.570
So all the gateway is doing is it's

00:02:05.570 --> 00:02:09.110
a manual in the middle passing on
your connection to the back-end.

00:02:09.110 --> 00:02:10.670
>> That's good because
we are never exposing

00:02:10.670 --> 00:02:13.130
the actual IP of the SQL node.

00:02:13.130 --> 00:02:15.140
>> I got it. So that is how you

00:02:15.140 --> 00:02:17.330
connect when you're connecting
from outside of Azure.

00:02:17.330 --> 00:02:20.090
Now, when you connecting
from inside of Azure,

00:02:20.090 --> 00:02:23.915
say from a VM, we do a slightly
different way of connecting.

00:02:23.915 --> 00:02:27.170
So we connect to the
SQL Gateway and we get

00:02:27.170 --> 00:02:32.540
the actual location of your
SQL database in the back-end.

00:02:32.540 --> 00:02:35.810
So you'll see this 13.93 address.

00:02:35.810 --> 00:02:37.535
That's a private IP address.

00:02:37.535 --> 00:02:40.930
It is not something that users
can connect to directly,

00:02:40.930 --> 00:02:42.815
like, they don't know
about this IP address.

00:02:42.815 --> 00:02:43.325
>> Okay.

00:02:43.325 --> 00:02:46.385
>> Then to further randomize it,

00:02:46.385 --> 00:02:52.250
there's 11,000-11,999 port range.

00:02:52.250 --> 00:02:53.575
>> Okay.

00:02:53.575 --> 00:02:58.885
>> So SQL could be listening on
any of those within that range.

00:02:58.885 --> 00:03:02.010
That is where what
happens is this called

00:03:02.010 --> 00:03:06.560
the redirect method or redirection
is the connection policy,

00:03:06.560 --> 00:03:08.615
but we redirect you to that node.

00:03:08.615 --> 00:03:11.345
So you connect directly
to the SQL database,

00:03:11.345 --> 00:03:13.400
and the gateway don't come into

00:03:13.400 --> 00:03:17.040
the picture ever again
of that connection.

00:03:17.090 --> 00:03:18.525
>> Okay.

00:03:18.525 --> 00:03:20.900
>> So that's the basic architecture

00:03:20.900 --> 00:03:23.210
and you understand how to connect.

00:03:23.210 --> 00:03:24.920
Now, let me take you
through what happens

00:03:24.920 --> 00:03:27.410
when your connection actually
makes it to the Gateway.

00:03:27.410 --> 00:03:28.345
>> Okay.

00:03:28.345 --> 00:03:32.235
>> This is very, very basic
high-level stuff, nothing special,

00:03:32.235 --> 00:03:36.520
and this has been around for ages.

00:03:36.520 --> 00:03:40.375
So you have a client and you
have the Gateway machine,

00:03:40.375 --> 00:03:43.350
first thing is
the three-way handshake,

00:03:43.350 --> 00:03:47.835
standard PCP stuff, [inaudible]
communicating to server.

00:03:47.835 --> 00:03:50.720
Interesting thing, what
happens next is you have

00:03:50.720 --> 00:03:53.090
a prelogin package
that the client sends,

00:03:53.090 --> 00:03:55.840
and interesting features here is,

00:03:55.840 --> 00:03:57.900
we want you to,

00:03:57.900 --> 00:04:00.820
as a best practice, use encryption,

00:04:00.820 --> 00:04:03.425
which is set by putting

00:04:03.425 --> 00:04:07.490
encrypt equals true in the connection
string of your application.

00:04:07.490 --> 00:04:10.640
Other part we want you
to do as a best practice

00:04:10.640 --> 00:04:13.415
is TrustServerCertificate
equals false.

00:04:13.415 --> 00:04:16.940
So this forces the client to verify

00:04:16.940 --> 00:04:21.890
the certificate that you
get from the gateway node.

00:04:21.890 --> 00:04:23.580
So that way, you won't have

00:04:23.580 --> 00:04:28.215
any chance of spoofing or
man-in-the-middle attacks.

00:04:28.215 --> 00:04:30.105
>> Okay, that makes perfect sense.

00:04:30.105 --> 00:04:32.670
So these are the best practices and

00:04:32.670 --> 00:04:34.860
any developer connecting
SQL will find

00:04:34.860 --> 00:04:37.285
this on our docs pages
or wherever the place?

00:04:37.285 --> 00:04:40.080
>> Yeah. In fact, we
take it a step further.

00:04:40.080 --> 00:04:42.300
If you go in the portal and look at

00:04:42.300 --> 00:04:44.960
your SQL database and look
at the connection string,

00:04:44.960 --> 00:04:46.550
these are put in there.

00:04:46.550 --> 00:04:47.735
>> All these are the defaults?

00:04:47.735 --> 00:04:49.340
>> Yes. So you put them in

00:04:49.340 --> 00:04:51.320
there to make sure that
you are protected.

00:04:51.320 --> 00:04:52.075
>> Okay.

00:04:52.075 --> 00:04:53.940
>> Then from here on,

00:04:53.940 --> 00:04:55.110
the prelog and response,

00:04:55.110 --> 00:04:57.600
that is again part
of the TDS protocol,

00:04:57.600 --> 00:05:00.410
and then you have
this TLS handshake which is

00:05:00.410 --> 00:05:02.690
a long winded way of getting

00:05:02.690 --> 00:05:04.820
a secure channel between
the client and the server.

00:05:04.820 --> 00:05:05.320
>> Okay.

00:05:05.320 --> 00:05:07.220
>> So essentially, at
the end of this process,

00:05:07.220 --> 00:05:09.680
you are secure from
the protocol perspective,

00:05:09.680 --> 00:05:14.500
and then starts
the actual login packet which

00:05:14.500 --> 00:05:16.020
you'll see it as

00:05:16.020 --> 00:05:18.200
login selling packet if

00:05:18.200 --> 00:05:20.270
you would do via shock
or something like that.

00:05:20.270 --> 00:05:23.330
Then, based on what you are using,

00:05:23.330 --> 00:05:26.720
if you are connecting to
us from the proximal,

00:05:26.720 --> 00:05:29.215
you'll just get
the acknowledgment back.

00:05:29.215 --> 00:05:31.985
Otherwise, you'll get what is
called as the redirect token,

00:05:31.985 --> 00:05:33.485
which is a fancy way of saying,

00:05:33.485 --> 00:05:35.000
we will tell you the private IP

00:05:35.000 --> 00:05:36.895
address that you are
going to connect to.

00:05:36.895 --> 00:05:39.290
>> Wow, there's a lot of handshaking
going backwards [inaudible].

00:05:39.290 --> 00:05:39.980
>> Yes.

00:05:39.980 --> 00:05:41.690
>> Is that a long process or?

00:05:41.690 --> 00:05:44.075
>> No, it's a matter of milliseconds.

00:05:44.075 --> 00:05:45.170
>> Oh okay.

00:05:45.170 --> 00:05:48.530
>> Yeah, we keep it fast
even and we keep it secure.

00:05:48.530 --> 00:05:50.870
>> Okay, that's good.
Fast and secure.

00:05:50.870 --> 00:05:52.720
>> Yeah. So next,

00:05:52.720 --> 00:05:55.100
now that we know about
the architecture and we know

00:05:55.100 --> 00:05:58.460
about the basics of connectivity,

00:05:58.460 --> 00:06:00.395
let's talk about who can connect.

00:06:00.395 --> 00:06:03.055
So you want to put
some controls about,

00:06:03.055 --> 00:06:05.570
how do you actually filter who

00:06:05.570 --> 00:06:08.345
can connect to
your Azure SQL database.

00:06:08.345 --> 00:06:11.075
So this is, and I'll
show this in the demo.

00:06:11.075 --> 00:06:16.055
This is basically the firewall
blade folder in the Azure portal.

00:06:16.055 --> 00:06:17.900
So the first thing you'll notice here

00:06:17.900 --> 00:06:19.430
is this section up top that says,

00:06:19.430 --> 00:06:22.550
allow access to
Azure services, on or off.

00:06:22.550 --> 00:06:25.340
So this is a very broad category

00:06:25.340 --> 00:06:26.855
of permission that
you are giving here.

00:06:26.855 --> 00:06:28.420
It is basically saying,

00:06:28.420 --> 00:06:32.065
can any other servers that's
running within Azure,

00:06:32.065 --> 00:06:35.915
say like a web app
or another Azure VM,

00:06:35.915 --> 00:06:40.815
can it connect to your
SQL database server or not?

00:06:40.815 --> 00:06:42.420
>> As in anywhere in Azure?

00:06:42.420 --> 00:06:42.545
>> Yes.

00:06:42.545 --> 00:06:46.245
>> Was it set by a region
or zone or datacenter?

00:06:46.245 --> 00:06:47.205
>> It doesn't matter.

00:06:47.205 --> 00:06:47.940
>> It doesn't matter.

00:06:47.940 --> 00:06:49.910
>> Basically, what this does is,

00:06:49.910 --> 00:06:52.060
when you are running
services in Azure,

00:06:52.060 --> 00:06:57.605
each region or datacenter has
a set of fixed IP addresses.

00:06:57.605 --> 00:07:00.710
So we basically, this is a rule that

00:07:00.710 --> 00:07:03.560
says anything that's
coming to me from an idea.

00:07:03.560 --> 00:07:03.935
>> From inside Azure.

00:07:03.935 --> 00:07:04.205
>> Yeah.

00:07:04.205 --> 00:07:05.165
>> A trusted datacenter.

00:07:05.165 --> 00:07:05.675
>> Yes.

00:07:05.675 --> 00:07:07.460
>> So I'm going to accept? Got you.

00:07:07.460 --> 00:07:10.100
>> Now, of course, people
have reservations against

00:07:10.100 --> 00:07:12.875
this because this is
a little bit of a broader scope,

00:07:12.875 --> 00:07:15.215
but in some scenarios,

00:07:15.215 --> 00:07:16.280
you need to do this.

00:07:16.280 --> 00:07:18.785
For example, web app is one example.

00:07:18.785 --> 00:07:21.620
But gradually, we are
coming up with offerings to

00:07:21.620 --> 00:07:25.295
where you'll be able to put
this at off and come up.

00:07:25.295 --> 00:07:27.650
I'm going to show you
different ways in which-

00:07:27.650 --> 00:07:29.285
>> I am going to do
buy a service type

00:07:29.285 --> 00:07:30.845
or something like a
web app or something?

00:07:30.845 --> 00:07:31.465
>> Yes.

00:07:31.465 --> 00:07:32.985
>> Then just to clarify,

00:07:32.985 --> 00:07:36.120
when you say any service in Azure,

00:07:36.120 --> 00:07:38.160
it's within your own
description of course.

00:07:38.160 --> 00:07:38.925
>> Yes.

00:07:38.925 --> 00:07:41.115
>> Just to clarify that for people.

00:07:41.115 --> 00:07:44.420
>> Yeah. So next level of control

00:07:44.420 --> 00:07:47.525
that we offer is what's
IP-based firewall.

00:07:47.525 --> 00:07:55.080
So this is typically when you
provision Azure SQL Database server,

00:07:55.080 --> 00:07:58.080
the default list here resembling.

00:07:58.080 --> 00:08:01.430
So nobody can connect to it by

00:08:01.430 --> 00:08:04.820
using an IP address unless
they are in this list.

00:08:04.820 --> 00:08:05.580
>> Got you.

00:08:05.580 --> 00:08:07.305
>> So the first thing
you need to do is,

00:08:07.305 --> 00:08:09.195
when you go to the portal,

00:08:09.195 --> 00:08:12.125
it will show you the IP address
that you're coming from.

00:08:12.125 --> 00:08:14.690
So if I'm coming from my home IP,

00:08:14.690 --> 00:08:17.945
that's where it will show up in
this client IP address section,

00:08:17.945 --> 00:08:20.810
and I need to whitelist that
in order for me to be able

00:08:20.810 --> 00:08:23.755
to connect using
Management Studio for example.

00:08:23.755 --> 00:08:25.210
>> Okay. Pretty standard stuff,

00:08:25.210 --> 00:08:26.840
we've done it all by default.

00:08:26.840 --> 00:08:27.485
>> Exactly.

00:08:27.485 --> 00:08:28.450
>> Okay.

00:08:28.450 --> 00:08:30.890
>> Now, we have
a third interesting way of

00:08:30.890 --> 00:08:33.845
connecting which is called
VNet firewall rules.

00:08:33.845 --> 00:08:35.785
That's pretty much saying,

00:08:35.785 --> 00:08:37.985
allow all Azure VMs

00:08:37.985 --> 00:08:41.390
inside of a VNet to connect
to my SQL database.

00:08:41.390 --> 00:08:44.030
Now, there might be situations
where this is useful,

00:08:44.030 --> 00:08:46.370
say you have a legacy app
or your app, say,

00:08:46.370 --> 00:08:47.900
reporting services that's running in

00:08:47.900 --> 00:08:51.060
a VM and you want to
connect to SQL database,

00:08:51.060 --> 00:08:53.555
this is a granular way
of doing that to allow

00:08:53.555 --> 00:08:56.270
only a specific set of VMs that

00:08:56.270 --> 00:08:58.970
are within a sub-net to
connect to SQL database.

00:08:58.970 --> 00:08:59.405
>> Okay.

00:08:59.405 --> 00:09:01.565
>> So these are
the three ways in which we

00:09:01.565 --> 00:09:04.095
control who connects
to SQL database today.

00:09:04.095 --> 00:09:05.570
>> Yeah. I can see
that in various so far

00:09:05.570 --> 00:09:07.430
by moving legacy applications.

00:09:07.430 --> 00:09:07.940
>> Absolutely.

00:09:07.940 --> 00:09:10.595
>> We got VMs running
all within the one VNet.

00:09:10.595 --> 00:09:12.140
>> Yes. So next,

00:09:12.140 --> 00:09:14.105
I will take you through
details of each one.

00:09:14.105 --> 00:09:18.215
So let's look at how
the IP-based firewall works.

00:09:18.215 --> 00:09:23.655
So assume we have a server with
couple of databases DB1 and DB2,

00:09:23.655 --> 00:09:26.220
and here's an incoming connections.

00:09:26.220 --> 00:09:30.080
Of course, the incoming connection
could be proxy or redirect,

00:09:30.080 --> 00:09:31.535
it don't matter this point.

00:09:31.535 --> 00:09:34.100
The assumption is you gotten
past the gateway and you

00:09:34.100 --> 00:09:36.740
are actually coming at
the SQL engine level.

00:09:36.740 --> 00:09:39.050
The first thing that we're
going to do is we're going

00:09:39.050 --> 00:09:41.630
to check against
the database level firewalls.

00:09:41.630 --> 00:09:43.010
So this is stored in

00:09:43.010 --> 00:09:47.215
the master database and
sys.database_firewall_rules.

00:09:47.215 --> 00:09:49.590
It's a DMV, and all it contains

00:09:49.590 --> 00:09:52.160
is a range of IP addresses
that are allowed.

00:09:52.160 --> 00:09:55.100
So if you are in here,
then by default,

00:09:55.100 --> 00:09:58.100
you get our database scope
the access to

00:09:58.100 --> 00:10:01.790
whichever database
you want to connect.

00:10:01.790 --> 00:10:05.390
If you are not in
the database level firewall rules,

00:10:05.390 --> 00:10:08.735
then next check happens
is at the server level,

00:10:08.735 --> 00:10:11.180
which is slightly
different DMV called

00:10:11.180 --> 00:10:15.440
sys.firewall_rules and it's
again in the master database.

00:10:15.440 --> 00:10:17.420
If you have access here,

00:10:17.420 --> 00:10:19.160
then of course you have access at

00:10:19.160 --> 00:10:21.965
the server level so
once you are connected,

00:10:21.965 --> 00:10:24.635
you can in Management
Studio drop-down,

00:10:24.635 --> 00:10:26.725
choose both DB1 and DB2.

00:10:26.725 --> 00:10:29.635
>> Okay. So if I was
configuring using

00:10:29.635 --> 00:10:33.395
SSMS to configure
my Azure SQL database.

00:10:33.395 --> 00:10:37.680
If I say that firewall
rule in the server level,

00:10:37.680 --> 00:10:39.810
it could just give me
access to everything.

00:10:39.810 --> 00:10:41.130
>> Got it.

00:10:41.130 --> 00:10:45.165
>> So we should probably go to
Dev test but not in best practices.

00:10:45.165 --> 00:10:48.180
>> Yes. That's
a great question, Colin,

00:10:48.180 --> 00:10:53.075
because one of the things is that
why do we have these two levels?

00:10:53.075 --> 00:10:55.560
It's because in production,

00:10:55.560 --> 00:10:59.045
if you want to use what is
called as a contained user,

00:10:59.045 --> 00:11:01.595
then the best practice is,

00:11:01.595 --> 00:11:06.515
contained user only allows you to
set up a contained user for DB1.

00:11:06.515 --> 00:11:11.340
The whole idea of contained user
being I cannot access DB2.

00:11:11.410 --> 00:11:14.780
In combination with
the database firewall rules,

00:11:14.780 --> 00:11:20.555
I can also restrict the IP address
from where users can login.

00:11:20.555 --> 00:11:23.180
So essentially,
the database level firewall

00:11:23.180 --> 00:11:25.010
is a useful feature for depth data

00:11:25.010 --> 00:11:30.515
stand for managing the scope of
bad people can connect from.

00:11:30.515 --> 00:11:31.980
>> Okay.

00:11:32.260 --> 00:11:35.450
So again, just help me out here.

00:11:35.450 --> 00:11:37.430
Could you expand quite a few details.

00:11:37.430 --> 00:11:42.650
So on the firewall rules these
are inside the SQL server and

00:11:42.650 --> 00:11:44.750
as obviously then what we've
covered earlier on was

00:11:44.750 --> 00:11:48.215
the firewall rules for
the network within it.

00:11:48.215 --> 00:11:50.630
>> Yeah. So those are
two distinct concepts.

00:11:50.630 --> 00:11:52.220
So in the previous slide I showed

00:11:52.220 --> 00:11:54.155
you there's an IP address
based firewall.

00:11:54.155 --> 00:11:58.820
That is what this is and
the place where they

00:11:58.820 --> 00:12:03.995
are stored is inside of master
database within your SQL Server.

00:12:03.995 --> 00:12:07.050
They're exposed via the portal.

00:12:07.870 --> 00:12:11.660
Of course if you don't meet
any of these criteria then

00:12:11.660 --> 00:12:15.870
your connection will be rejected
which is what we spoke about.

00:12:16.090 --> 00:12:18.350
Now, let's look at

00:12:18.350 --> 00:12:21.875
the new interesting part here
which is the VNet firewall rules.

00:12:21.875 --> 00:12:25.235
So let me set up
the scenario for you.

00:12:25.235 --> 00:12:27.830
So let's assume you have
a virtual machine that's

00:12:27.830 --> 00:12:32.135
running say a Legacy app
on iOS or whatever.

00:12:32.135 --> 00:12:37.100
The virtual machine typically
has you can set up what's called

00:12:37.100 --> 00:12:42.260
as a public IP address for it
which is the 14.11 to address,

00:12:42.260 --> 00:12:45.200
and you can also have
a private IP address which

00:12:45.200 --> 00:12:48.305
comes from a particular subnet.

00:12:48.305 --> 00:12:51.020
So best practices
always make sure that

00:12:51.020 --> 00:12:53.600
your private IPs are
coming from subnets,

00:12:53.600 --> 00:12:55.490
helps in network segmentation

00:12:55.490 --> 00:12:57.920
and that's a networking
best practice.

00:12:57.920 --> 00:13:00.305
But I'll show you how it helps.

00:13:00.305 --> 00:13:02.990
>> You typically always
set those thing up or get

00:13:02.990 --> 00:13:05.270
a network IT pro to set those

00:13:05.270 --> 00:13:07.865
up before you go to
a different production.

00:13:07.865 --> 00:13:10.625
You want to make sure
you got enough IP.

00:13:10.625 --> 00:13:11.930
>> Yes, absolutely.

00:13:11.930 --> 00:13:13.985
It's a good point
that you bring up is

00:13:13.985 --> 00:13:16.985
we definitely want
separation of duties here,

00:13:16.985 --> 00:13:20.390
it's always good to plan
your network ahead of time.

00:13:20.390 --> 00:13:22.790
So you don't run out
of IP addresses and

00:13:22.790 --> 00:13:26.495
then you certainly don't
want a DBM messing

00:13:26.495 --> 00:13:32.030
around with setting these up and
getting the size wrong or because

00:13:32.030 --> 00:13:37.850
each of these it's easy
to miss things in here.

00:13:37.850 --> 00:13:40.280
>> Yeah, and once you've
set those ranges and

00:13:40.280 --> 00:13:42.995
started installing things
is you can't go backwards.

00:13:42.995 --> 00:13:47.000
>> Got it. So there's
the basic setup.

00:13:47.000 --> 00:13:50.930
I have a VM, it has a public
IP address and a private IP address.

00:13:50.930 --> 00:13:54.020
So let me take you through
option number one which is how

00:13:54.020 --> 00:13:57.350
do I connect to SQL database
using the public,

00:13:57.350 --> 00:14:01.190
we typically call this
the snatted IP address.

00:14:01.190 --> 00:14:04.130
Option number one is let's start from

00:14:04.130 --> 00:14:06.980
the VM side and we can define what is

00:14:06.980 --> 00:14:09.230
called as NSG rule which

00:14:09.230 --> 00:14:12.455
basically says I'm going
to allow outbound traffic.

00:14:12.455 --> 00:14:14.630
>> NSG, network security group?

00:14:14.630 --> 00:14:17.170
>> Network security group
which exist on

00:14:17.170 --> 00:14:21.120
the VM on the virtual machine.

00:14:21.120 --> 00:14:24.305
It allows you to define
inbound and outbound rules.

00:14:24.305 --> 00:14:28.865
So here we'll define an outbound
rule to connect to SQL database.

00:14:28.865 --> 00:14:31.880
The way we'll do that
is we'll say I need to

00:14:31.880 --> 00:14:35.225
connect to 1433 because
that's my initial.

00:14:35.225 --> 00:14:37.460
Remember initially you
need to connect to

00:14:37.460 --> 00:14:40.565
the gateway which is
investing on port 1433.

00:14:40.565 --> 00:14:43.700
Then, I need the 11,000 through

00:14:43.700 --> 00:14:48.170
11,999 range because I'm
going to use redirection.

00:14:48.170 --> 00:14:50.990
Then TCP is my protocol.

00:14:50.990 --> 00:14:54.425
My IP addresses the 40.112 address

00:14:54.425 --> 00:14:57.815
and way over on the right
the most interesting piece.

00:14:57.815 --> 00:15:01.355
I'm saying let this virtual machine

00:15:01.355 --> 00:15:04.415
connect to everything in SQL.WestUS.

00:15:04.415 --> 00:15:08.615
So that is again another networking
concept called a service tag.

00:15:08.615 --> 00:15:12.935
So what that means is I want
to connect to SQL database.

00:15:12.935 --> 00:15:15.410
I know my database is in WestUS,

00:15:15.410 --> 00:15:19.715
allow me just whitelist
traffic to the WestUS region.

00:15:19.715 --> 00:15:21.125
>> That's very good.

00:15:21.125 --> 00:15:24.455
So you could just let
me use that as a or

00:15:24.455 --> 00:15:29.795
just the uses to help with
just your latency and traffic.

00:15:29.795 --> 00:15:32.390
>> Yes, I mean
a bottom line here is this

00:15:32.390 --> 00:15:36.620
is a way of enabling the
connectivity from the network side.

00:15:36.620 --> 00:15:38.450
So typically your
network admin will do

00:15:38.450 --> 00:15:41.540
this and it just makes
management easier

00:15:41.540 --> 00:15:42.770
for them because when you say

00:15:42.770 --> 00:15:46.940
SQL.WestUS you don't have to
whitelist individual IP address.

00:15:46.940 --> 00:15:49.610
So it allows you to
connect to anything in

00:15:49.610 --> 00:15:53.180
any SQL database that
in the WestUS region.

00:15:53.180 --> 00:15:55.850
Now this also incurs

00:15:55.850 --> 00:15:57.845
an additional overhead
in the sense that

00:15:57.845 --> 00:15:59.990
once you are done with
the network setting,

00:15:59.990 --> 00:16:02.000
you need to come on
the SQL database side

00:16:02.000 --> 00:16:04.655
and again remember our
IP address firewall.

00:16:04.655 --> 00:16:06.845
So you need to add IP address here.

00:16:06.845 --> 00:16:08.975
So this is a two-step process.

00:16:08.975 --> 00:16:12.230
The downsides of this are one

00:16:12.230 --> 00:16:15.440
is you have to manage the IP address.

00:16:15.440 --> 00:16:19.520
So for example, anytime if you
chain the public IP address for

00:16:19.520 --> 00:16:22.280
the VM immediately
this connectivity will be

00:16:22.280 --> 00:16:25.400
broken because SQL won't
recognize that IP address.

00:16:25.400 --> 00:16:27.200
>> Yeah, because not [inaudible].

00:16:27.200 --> 00:16:30.740
>> Yes. The other thing
about this that

00:16:30.740 --> 00:16:33.140
some customers may not like is then

00:16:33.140 --> 00:16:36.155
a service tag is still
pretty wide open.

00:16:36.155 --> 00:16:37.805
It says allow me to connect to

00:16:37.805 --> 00:16:41.840
any SQL database in
the WestUS region.

00:16:41.840 --> 00:16:44.090
But of course, there are improvements

00:16:44.090 --> 00:16:46.130
coming on the network side
that will allow you to

00:16:46.130 --> 00:16:48.575
restrict that to a specific resource

00:16:48.575 --> 00:16:51.035
but those are in
progress at the moment.

00:16:51.035 --> 00:16:52.820
But this is what we have.

00:16:52.820 --> 00:16:58.775
So now let's flip the direction
from which we are coming here.

00:16:58.775 --> 00:17:02.720
You saw how we set up from
the VM side to SQL DB.

00:17:02.720 --> 00:17:05.240
The Vnet firewall rule is
something that you set

00:17:05.240 --> 00:17:07.400
a prom the SQL database side like

00:17:07.400 --> 00:17:11.330
this to say allow me to connect from

00:17:11.330 --> 00:17:17.135
my SQL database to
a particular subnet.

00:17:17.135 --> 00:17:21.005
So the beauty of it
is you do not need

00:17:21.005 --> 00:17:25.280
to whitelist any IP addresses
or do anything like that.

00:17:25.280 --> 00:17:28.460
All you're saying is
opening a path between

00:17:28.460 --> 00:17:29.960
your SQL database and

00:17:29.960 --> 00:17:33.620
a particular subnet and you're
using the private IP address.

00:17:33.620 --> 00:17:36.485
So there's that even
added benefit that

00:17:36.485 --> 00:17:42.455
everything is going through
the Azure backbone network wise.

00:17:42.455 --> 00:17:43.970
>> That's pretty cool.

00:17:43.970 --> 00:17:50.750
>> Other advantage of this is
no whitelisting required and yeah,

00:17:50.750 --> 00:17:52.625
that's pretty much it.

00:17:52.625 --> 00:17:56.420
>> Cool. So thank you for it.

00:17:56.420 --> 00:17:58.160
That's a really good explanation of

00:17:58.160 --> 00:18:01.220
the connectivity to
Azure SQL Database.

00:18:01.220 --> 00:18:03.780
Couple of other quick questions,

00:18:03.790 --> 00:18:06.680
is it just for Azure
SQL database like all of

00:18:06.680 --> 00:18:08.780
them the different
deployment options.

00:18:08.780 --> 00:18:11.855
I think we have a single elastic
goal was managed instance.

00:18:11.855 --> 00:18:12.290
>> Absolutely.

00:18:12.290 --> 00:18:15.360
>> Is it for all of them this
affected the options you explained?

00:18:15.360 --> 00:18:18.530
>> So when we talk about
Azure SQL database today,

00:18:18.530 --> 00:18:23.180
whether you look at a single
SQL databases, elastic pools,

00:18:23.180 --> 00:18:26.779
data warehouse and
even the open source databases,

00:18:26.779 --> 00:18:30.200
we share a common control
plane which means to

00:18:30.200 --> 00:18:34.640
say the SQL gateway that you
saw that component is common.

00:18:34.640 --> 00:18:37.820
So this connectivity applies to

00:18:37.820 --> 00:18:39.410
all of our offerings that are under

00:18:39.410 --> 00:18:41.525
the Azure SQL Database umbrella.

00:18:41.525 --> 00:18:44.690
>> That's fantastic. Well, thank you

00:18:44.690 --> 00:18:47.600
very much everyone for
tuning in and give us

00:18:47.600 --> 00:18:50.210
a like if you like
this type of content

00:18:50.210 --> 00:18:53.300
and subscribe to us and we'll
see you later on. Thank you.

00:18:53.300 --> 00:19:06.000
[MUSIC]