Hi, everyone. In this demo, I'm going to show you how to connect remotely to a SQL
Server Express or Developer edition instance.
This scenario usually happens when you want one of your colleagues
to remotely connect to your machine. In order to allow access,
there are two levels of security that need to be changed. First, we have to allow
access through Windows,
and then we have to configure the SQL Server instance
to allow the protocol being requested. When you install SQL Server,
the edition you specify not only controls which features are available,
but also which protocols are available by default.
In order to allow access to SQL Express and Developer edition instances,
we have to enable TCP/IP which is not enabled by default.
After we've enabled TCP/IP in SQL Server,
we have to allow the appropriate ports through Windows Firewall.
Some advice on the internet says to disable Windows Firewall
entirely, but this is not a security best practice,
and it's not required at all. When you connect to a SQL Server instance,
there are two different types of instances that you can connect to.
First is the default instance, which is where you only specify the computer name
and nothing else. The second type
is a named instance where you not only specify the computer name,
but also the instance name. To connect to a default instance,
the only port you have to open is TCP port 1433.
To connect to a named instance,
there's a second service called the SQL Browser service,
which resolves the instance name to a port number.
The SQL Browser service uses
UDP port 1434. By default,
named instances use dynamic ports, which means
a new port number is assigned every time the service restarts.
This is why the SQL Browser service is necessary --
it's because the named instance service
does not expose a known port number to the outside directly.
As you can see,
I have two virtual machines running in Virtual Box. The SQL Server is running
on the one on the right,
and we'll be trying to connect from the one on the left. Both machines
are in an internal network, and while they're not in a domain,
will still connect using Windows Authentication.
As a demonstration, I'll show that I can connect locally
on the right side,
but I can't connect remotely on the left side.
The first step is to enable TCP/IP
on the SQL Server service. We do this by first opening SQL Server
Configuration Manager,
and then in the network configuration node,
we select the protocols item
for the instance of interest. Then we right click
on TCP/IP and click Enable.
We get a warning that the changes we just made
won't take effect until the next time the service is started,
so let's do that now.
We'll go to the services node,
right click on the database engine instance, and click Restart.
The service is now restarted
with TCP/IP enabled, but we still can't connect
until we configure the Windows Firewall.
We'll go into the Advanced Firewall configuration,
and then to the inbound rules.
To create an allow rule for the SQL Browser service,
we'll go to New Rule,
and for this, we'll want to choose a specific port number.
We'll select UDP port 1434.
We'll click Next all the way through,
and we'll give the rule a name,
and click Finish.
Next, we'll create an allow rule for the database engine instance itself.
We'll go to new rule,
this time we'll create a custom rule, and in the Services area,
we'll select the database engine instance service.
Again, we'll click
next all the way through, and give the rule a name,
and click Finish.
Now we have all the firewall rules
set up, so let's try to connect again.
And this time, it works.
Because the named instance uses dynamic ports by default,
I allowed the service itself and any ports it's using
through the firewall. Not only does this allow connection to the database engine
instance itself, but it also allows any other ports SQL Server may use
including things like database mirroring.
If this is inappropriate for your environment, you'll want to use more
restrictive policies,
but for most people this is OK. If you have any questions about this video,
or if you'd like to suggest topics for future videos, please leave me a comment
below.
Thanks for watching!