>> Hi. On this episode of Data Exposed,
we'll dive deeper into Azure SQL Database's
new capability for private endpoints,
and actually, you'll get to take a look at it in the portal
and in things like NsLookup and PsPing.
Be sure to tune in on Data Exposed.
[MUSIC]
Hi. My name is Anna Hoffman
and welcome to this episode of Data Exposed.
In the first part of this video
which we encourage you to go watch,
we'll put a link for it in the comments,
Rohit covered what is private endpoint
which is a new feature for
Azure SQL Database among other services in Azure.
In this video, Rohit's actually going to dive into what this
looks like and how it compares to the public endpoint.
So Rohit, thanks for joining us again
and we're excited to see what you have to show us.
>> Thank you, Anna. Let me share my screen and
just walk everyone through that setup first.
In the previous video,
we talked about the simple scenario where you
have a SQL database and a client VM.
So I've set up the exact same thing here.
I have MySQL database in West US, and my client VM,
this is in East US.
Let me just open up the client VM here.
The client VM typically has two IP addresses.
This is important because this will set the context for us to
understand how connectivity changes
between public IP address and the private,
so just keep this in mind here,
40.117 is the public and the 10.9.1.4 is the private IP address.
For my demo here, what I'm going to do is first show you
how it looks like when you connect from the public IP address,
which means you're using the public endpoint,
then we'd like to go through the process
of setting up a private endpoint,
and I'll show you how the private IP connectivity looks like.
>> Cool.
>> Stay with me here
and let me go to my VM.
This is my client VM.
The first thing I'm going to do is
just try to verify. When I do an nslookup,
NsLookup is just checking what is
the DNS resolution for my server.
So this is my demo SQL Server.
Typically, when I get this result back like this,
this is telling me that it is going to
the public SQL gateway in the region.
Then these IP addresses,
this is a public IP address for the gateway,
and we have published this in our docs.
That is one quick way in which you
can check where your connection is going
to and whether it is using public connectivity to SQL database.
Next, we just run a PsPing command.
This is a Microsoft-enriched written tool
and all this does is tries to ping
that particular server on port 1433.
Again, we see it is resolving to
the public IP address of the SQL gateway.
So nothing magical so far.
Let me try to log in.
>> So here, you're using SQLCMD
which is pretty common tool
for connecting to Azure SQL database along with SQL Server.
Is this something you use for testing networking often?
>> Yes. When testing networking,
I prefer to do it old school, if you can say that.
Just stick to the command line because that gives you
the minimal functionality that you need
without taking any dependency on any tool.
>> Right. So that means [inaudible]
>> Yes. Now, what I wanted to show you here is,
I just logged in to the master database and just
did this dm_exec_connections [inaudible] and
I queried what is the client IP address that I'm coming in from.
You see here, it's showing me this 40.117 address
which is the public IP of the VM.
I need to whitelist this for the connection to work.
I have already done that ahead of time.
One last thing I wanted to show to really bring this home
is I've captured a network trace.
In this network trace,
first, the interesting piece is you'll
see two sets of IP addresses.
So you'll definitely see the source IP addresses for
our VM here, and the destination IP address is
the IP address of the SQL gateway in the region.
As you look at some interesting frames here,
you have a prelogin,
you have this TLS handshake going on,
and all this is happening while
you're interacting with the SQL gateway.
Then you will see
all these interactions with
the gateway will happen on port 1433,
so that's the first hop.
This is where you see the second hop when
you get redirected to the back end node.
You will see that the destination port here is 11031.
So if listeners go back and look at previous video,
we said that when you get redirected,
your destination port is in this 11,000 through 11,999 port range,
so that's what we are seeing here in the network trace.
This is just to bring the point home about
how public connectivity works.
Now, let's go look at how we use our feature.
So here, what I'm going to do is I have, as I said,
my Azure SQL database, this is in West US, and I'm going to create
a private endpoint in
the same virtual network as the VM in East US.
So the whole idea is
I take an IP address from the same private IP space
and I create that link between
that IP address to the SQL database,
which is in a different region.
How we do this is under the server.
You go to the private endpoint connections blade
and click on this Plus sign.
Again, it's going to ask you for three inputs.
Number 1, there is definitely what's
the name you want to give it and the region.
Let's see. I made a mistake.
I have my SQL resource group and I have my PE resource group.
This is two different resource groups
and two different regions,
so just a minor typo there.
Second thing is what type of resource I want to use.
So here, I'm going to pick my specific SQL Server here.
This is my Azure SQL Database server,
this is in the West US region,
and then I need to pick two other things
which is my VNet and subnet.
This is my VNet and subnet.
This is in East US,
which is the same where I have my client VM,
and this is the third piece.
I'm creating a new DNS zone that will do the translation.
We'll say, "Create".
>> While this is kicking off,
what we have is we have a SQL database server
and that's in the West US region?
>> Yes. There's a good one.
>> Yeah.
>> Let me go back and just
reinforce what we are doing in the demo here.
We have a SQL database server in the West US region
and my client VM is in the East US region.
For my client VM to connect to West US,
what I'm going to do is
I'm going to create a private endpoint in
the same resource group and in
the same VNet which contains my client VM.
So essentially, we are making sure that the client VM
talks to the SQL database via the private endpoint.
>> There's no reason that my private endpoint needs to be in
the same region as my SQL Server or SQL database, right?
>> Correct. That's a very good point that you bring up here, Anna,
which is all these three components;
the resource that you're creating the private link to versus
the private endpoint location
versus where your clients are connecting from.
These can be in three totally different regions,
they are not interdependent.
However, by at least keeping
the client VM and the private endpoint in the same region,
I cut down the need to set up
any more networking connectivity by using VNet peering.
This is just a simple scenario.
Our customers also use a hub and spoke scenario
where they have clients in different spoke VNets and
then you can have one specific VNet dedicated for
all your private endpoints and
just allow the traffic from the client to that VNet.
Let's take a pause for a minute also
because I think this takes a while to finish.
So that took a little bit
but the deployment succeeded.
Let's look at what actually got deployed.
It's probably easier to go back to the Dashboard here.
Go to the resource group.
You will see three new things that came up here.
Definitely the first new thing is this private endpoint.
So as we had talked about in the previous video,
all this is is a wrapper around this private IP address here.
Then you see how
this FQDN for your database is mapped to the private IP,
so this resource towards that mapping.
Second thing that got created
is the actual private IP address itself.
So this shows up as a NIC.
This NIC is associated with the private IP address,
I think you see it here.
Yeah, 10.9.0.4.
The third important thing,
and this is what the magic sauce is,
how do you associate that private IP address with the FQDN.
So this is the private DNS zone and this
is what makes sure that within the context of this VNet,
when I look up via demo SQL,
it actually will route the call to this private IP address.
So now that you've seen what resources got created,
let's go back to our VM and
see how this actually works in practice.
>> So this is the same VM and the same server?
>> The same. Nothing has changed. Yes. So let's
go rerun the exact same commands
that you saw me run the last time
and you'll see how things have changed here.
So I did an nslookup
and you immediately see there's a difference here.
I'm not going to the public SQL gateway in the region anymore.
In fact, the DNS resolution basically goes
to something called as .privatelink.database.windows.net,
and then it goes to 10.9.0.4.
So why is it doing that?
It's because this is what the DNS zone is set up to do.
Now, one thing that I want to point out here is
a lot of customers have tried to connect to this particular FQDN,
and that won't work because that is not listening for connections.
Rather, this is a DNS zone that just intercepts the call
to your FQDN and routes it
to this IP address. That's all it's doing.
>> Got you. This goes along with what you mentioned
I think in part one of the video,
that my connection string doesn't change,
I still just connect to it.
>> Exactly. Yes. The other thing that
we've heard from customers is,
"Hey, I created this private IP address.
Can I log in to the IP address itself?"
Again, the answer to that is
that's not supported because the whole idea here
is you don't need to change your connection string.
Let's see what the psping command does.
Here, what you'll see different from last time is, again,
it's going to 10.9.0.4,
which is my private IP address, and port 1433.
So I'm no longer going to the public IP address
which is the same thing we even saw last time.
>> So this is the same query that last time you ran it,
it returned the public IP address?
>> Absolutely. So as you see,
we didn't change anything on the client side.
We just set up this thing on the networking layer,
and now automatically, what happens is
as long as you are in the context of the VNet and you try to
connect to this particular server,
you are always going to be connecting via the private path.
Then this is reflected in the client net address,
1.4 is my IP address for the client.
Then one last thing I wanted to show to bring this home is
captured corresponding NetMon capture
on a similar setup on another machine.
What you see is
you only end up interacting with one IP address.
In the previous interaction,
you had to go to the SQL gateway
and then you had to go to the back end,
whereas here, we are only doing one IP address.
So that is the big security takeaway here,
your surface area is drastically reduced because you now have
just one IP address to protect and
it's always going to be on port 1433.
>> That's really cool.
>> Yeah. So that was a very high level of how this feature works.
I think the key things that I want people to take away
from this demo is three things.
One is, by going to the private tab,
you reduce your surface for data exfiltration
because you just have one IP address that you can connect.
Second thing is you don't need to change your connection strings.
So do not try to connect using privatelink.database.windows.net
or using the IP address
because you don't need to do that.
Your app needs to work exactly as it is today.
So that was my demo.
Hopefully, this helps people
understand this feature a little bit better
and helps them with
some of the FAQs that we've gotten around this so far.
>> Thanks, Rohit. For all our listeners,
thanks for joining us today.
We'll put some more information to links on how to get
this set up in your environment or to learn
more in the "Show information".
If you like this video or have any questions or comments,
please leave them in the comment section.
Tune in next time on Data Exposed.
[MUSIC]