[MUSIC].
>> Hello everyone, and welcome.
My name is Colin Murphy, Product Marketing Manager at Microsoft.
I'm joined today by Rohit,
Program manager in the data platform team, correct?
>> Yes, I'm program manager and I mainly
look at connectivity for SQL database.
>> Then you're going to show us the demo today on how to
setup network rules for Azure SQL Database, correct?
>> Absolutely. So we will start with
the scenario that we left off with last time.
Which is the fact that let's assume you have a virtual machine and
you have a SQL database,
and you want to connect the two.
>> Okay.
>> So let's switch desktop,
and I'll take you through the process of how you do that.
>> Okay.
>> So [inaudible] the Azure Portal
here this is my virtual machine that's running.
This is my SQL database server.
When I go under Firewall Settings,
what I've done is I've whitelisted
the IP address of the virtual machine here.
Now with this when I log in to
the virtual machine and try to connect via Management Studio,
my connection goes on fine.
I can confirm that I'm indeed connecting from
that IP address by just running this query.
On Database. You can see I'm coming from the IP address of the VM.
So on nothing magical has happened.
>> Yeah.
>> This very routine scenario.
But what I'm going to show you next is how you
can restrict the scenario even further.
There are good reasons why people may not want to just do
this introduction because you're just opening up the IP address.
So there are additional measures that
ideal administrators can put
on the networking side of the virtual machine.
These are done at the network security group level.
By adding what is called as an outbound rule.
So when we add an outbound rule
essentially what we do is we restrict.
Where the outgoing traffic from
the virtual machine can communicate to.
>> Got you, okay.
>> There's again Gibbs,
the IT security people,
or the network security people.
Additional controls, they can put on the virtual machine so
that you're not open to communicating to anywhere in the world.
>> Correct, yeah. So for real-world applications,
this is typically what IT would do.
They would actually control the firewalls.
>> Absolutely.
>> The Network security of a mission-critical application.
So this is their domain,
where they will actually be setting these things up.
>> Yes because now I'm working with a plain vanilla VM.
So we have Outbound Internet connectivity.
But in real-world scenarios
that is not a privilege that you have everywhere.
So let me show you how we do that.
We say our source address is an IP address
and the IP address is basically this IP address,
which is the public IP of the virtual machine.
Source port ranges, I want to open up port 1433 and
port range 11,000-11,999. So remember.
>> This is sequel, yes.
>> So basically what we are saying is from
the virtual machine IP address we will
communicate to 1433 which is for our gateway machine.
That will redirect us to a port between 11,000 and
11,999 which is where our SQL database will reside.
So this is on the "Source side".
So now on the destination side what we
can do is use a concept called service tag.
This basically says since my database is in West US,
and it is Sql.WestUS.
So what that does is allow me outbound
to any SQL database in the West US region.
Then this can be star.
Our Protocol is, we want to communicate via TCP.
We won't allow.
We'll set it at low enough priority so
that it takes precedence over other rules,
and say allow SQL,
should be good enough, and we say "Add".
So all I'm doing here is I'm building an enhancement on
the previous scenario that I showed you
in the sense nothing has changed here am delivered to connect.
But now I can do more granular things at
the network security group level so I could
basically deny access to
certain regions or only allow specific ports, things like that.
>> Okay.
>> So this is one example of how the Network security component
of Azure networking can come
into play when connecting to SQL database.
>> Okay. So through this network security groups,
through these rules here I've allowed SQL and
anyone in that region to connect to that SQL database.
>> That's correct.
>> But that's in my subscription.
So it's not the world, it's not the Internet it's
just within my Azure subscription.
>> It's acting very more specific than that.
It's only from the source IP address of the VM.
>> Okay.
>> To any SQL database in West US.
>> Got you.
>> All right. So that was our first demo.
Now I'm going to switch out do a little bit of setup here.
So get rid of this.
>> This is typically definitely
what IT pros would use
for setting up their firewalls for their App Service.
>> Yes.
>> Then do I have to do this?
Is this something that we tell people they
have to go do this for when they are deploying SQL in Azure?
>> This is one way of securing access.
I mean you can do it at multiple levels.
People do it at network security group
as something that's concept at the VM level.
But there are other layers of networking,
you can have what is called as a network virtual appliance.
Which basically does backup inspections
for anything flowing inside your network.
You can also define custom routing rules that allow or
deny routing of packets between
certain parts which are your sub-nets within your Virtual Network.
So what I've shown you is just one level
of network security which is at the VM level.
This can be scaled up or
down at different levels within the network stack.
>> Great. So we've offer our also
tearing of Security here and lots of options.
>> We are just scratching the surface in terms of
what we allow on the network security side.
Okay. So great. So now what I'm going to
do is basically disconnect here.
Now let's go into our next demo and this is more
relevant to what we can do from the SQL database side.
So I'm in my firewall settings
here and note that there is nothing in the IP firewall.
So this is blank.
Now what I'm going to do is I'm going to
add what is called as a virtual network firewall rule.
Remember what it does is,
we start using the private IP address
of the VM to communicate with,
and not the public IP address.
For that, we need to do a slight tweak at the VM level.
>> We never want to use the public IP address.
>> Correct, yes.
That would be the preferred.
>> Opening our database to the World.
>> Absolutely.
>> Or potentially.
>> Yeah, and a lot of scenarios
customers don't have a public IP address for the VM.
So that's a very good security conscious,
customers will not even allow this.
So this becomes all the more important.
So here you see I have a private IP address ending in.4,
which comes from this VM Subnet two.
Right, so I'm going to click on that.
Go find my VM Subnet here.
So bear with me while I click through.
Here's a very interesting thing that I'm going to do now,
which is there's a feature called service endpoint.
I'm going to click and say Microsoft.SQL.
Let's read the text that comes here.
Basically says, "with service endpoint
the IP address will switch from
using public IPV4 to private IPV4."
So that's the key thing to note here,
as once we enable
a service endpoint for Microsoft.SQL on the Subnet,
then that Subnet immediately switches
from public to private IP addresses.
So at this point.
>> That's everything on that subnet?
>> Yes.
>> How do we care for planning just to make
sure if you got other services,
things running on that Subnet that they don't rely on a public IP.
>> Yes. So that's why usually it's
a good idea to usually have segmented my Subnets here.
So usually it's a good idea to keep
a Subnet only for a specific purpose.
So I have a Subnet for VMs,
I have a Subnet for My Apps and so on.
So once I do this,
and this is where probably will have to pause that demo a bit.
Because this takes a little bit of time to update here
because it's active switching
the Subnet traffic from public to private.
So let's wait for this to get done.
>> Great. So that just takes a couple of minutes to do that?
>> Yes. So now we have switched
the Subnet traffic of what we are using private IP.
Let me just go back here and show
you what happens when I tried to connect.
Go and reset this,
and now you get this very specific error message.
It says, cannot open server requested by login.
So this error message indicates that I'm trying to connect via
the private IP address but I'm not there
yet because I can't resolve it.
I'm trying from the Virtual Machine side
but my traffic is not going out.
So we'll now go and put the plumbing in for that traffic to go
out and that we do on the sequel server side.
So we'll go into the Firewall settings.
Again, note that we are not adding
anything in the IP address space,
rather we are clicking on this section at the bottom,
It says virtual networks.
We're going to add a new,
can take the default name that's fine.
Pick my Subscription.
Now here you need to be careful to pick the correct Subnet.
>> This is the Subnet now that's just going to
accept in private traffic?
>> Exactly.
>> Okay.
>> You'll see that it has a service endpoint that's enabled.
So that's the prerequisite for it to start accepting traffic.
>> So if you selected one that didn't have a service endpoint
is probably your red flagged to know that it's not going to work.
>> Yeah, you'll most more than likely run into
an error message that tells you
that you need to enable this feature.
So now I click "Okay" here.
It's basically going to
update its metadata on the SQL database side.
So you will see a new rule pop up
here in a minute or less than that.
Okay. So now you see that we have
a new Vnet firewall rule that is
basically allowing us access to this VM Subnet two.
It says the state is ready.
So now we'll see that
the address will have been
switched to using the private IP address.
So let me just run this again, this DMV query.
You can see this is now listing the client address as 10.1.0.4,
which is the private IP address of the VM.
So this is how by using
Vnet firewall rules we have basically switched
from using the public IP address
taken that out of the picture altogether.
>> Completely.
>> We're using the private IP address and this is
a feature that we want
more and more customers to know about and start using.
>> Okay.
>> Again, we are putting more investments
in this area so we are taking the story further.
So you'll see more announcements in the future in this area.
But for now what this gives you
is if you have virtual machines in a Subnet,
then you can restrict access from that Subnet
to SQL database using Vnet firewall rules.
>> Cool, it's already great.
>> So that was our demo.
>> Thank you very much. So thank you everyone for tuning in
on how to setup
some network firewall rules for Azure SQL database.
Give us a like and we'll see you next time. Thank you.
>> Thank you.
[MUSIC].