How to allow remote connections to SQL Server Express

Can't connect remotely to SQL Server? Watch Microsoft Certified IT Professional Jon Seigel show you how to allow remote connections to SQL Server Express and Developer edition instances without disabling the Windows Firewall.

While the configuration shown is based on installation defaults, a more complete reference is available on MSDN: http://msdn.microsoft.com/en-us/libra...

Blog post for this video:
http://voluntarydba.com/post/2013/09/...

Visit my channel for more database administration videos:
https://www.youtube.com/voluntarydba

Subscribe to get notified about my latest videos:
https://www.youtube.com/voluntarydba?...

Read additional content on my blog:
http://voluntarydba.com

Follow on Twitter:
https://twitter.com/voluntarydba

Like on Facebook:
https://facebook.com/voluntarydba

---

Closed Caption:

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!