I am running SQL Server 2008 at the office on Windows Server 2003.
This server connects to the internet via a router. I am running SQL
Express 2008 on my laptop on Windows 7. When I am home, I connect
wirelessly to the internet via a router.

When both computers are in the office, there are no connectivity
issues. However, when I connect the two computers via VPN from my
home, my laptop can connect to the office's SQL, but the office con
only sometimes connect to my laptop's SQL.

For this troubleshooting period, I've turned off all firewalls. The
TCP ports are enabled, and the browser service is running on both
machines.

On my home router, I've enabled forwarding on ports 1433-1434 UDP to
my laptop's IP Address.

My questions:

1. Do I need to open the VPN port 1723 on my home router for remote
SQL connections over a VPN? I have the VPN port open on the office
router because that is where all the clients log on to via VPN, but do
I need to open it on my home router also? Or do I just need to make
sure the SQL ports are opened?

2. All SQL server instances on the machines are set to use dynamic
ports. Could this be why the office server can only sometimes connect
to my laptops's instance over VPN - because sometimes it's using 1433
or 1434 and sometimes it's not?

3. In order to reliably connect SQL server instances over a VPN
behind routers, must we use static port assignments? Or is there a
way to make the connectivity work with dynamic port assignments?

4. The reason I prefer dynamic port assignments on SQL Server is
because I don't want to hardcode everyone's IP addresses (we have
about 10 laptop users that need to replicate over VPN to SQL Server).
If I end up having to hardcode everyone's IP addresses to use static
ports, won't all these users have problems connecting to various WIFI
networks around town?

Sorry for all the questions, I'm trying my best to figure all this out
so we can get our users replicating.

Thanks for any feedback.

OK more information here:

I've been testing settings all morning, and just for troubleshooting
purposes, I un-enabled the port forwarding for ports 1433 and 1434 on
my home router, and I WAS able get SQL server at the office to connect
SQL Server on my laptop at home via VPN. How is this possible without
the ports forwarded on my home router?

I had tried this exact same scenario a week ago (had no ports
forwarded on my home router), and I was not able to connect. If I
could isolate reasons why I can or not connect at certain times, I'd
be able to get the whole office up and running. But right now the
connectivity issues seem random and I cannot make sense of them.

Hello

Ok... the port forwarding issue on your home router is just confusing your
problems.
You home "router" isnt really a router it is a NAT or Natural Address
Translator... your ip addresses are "re-written" by this thing.
you should not have to money with it at all.

#1... make sure your client settings on SQL server are set to TCP/IP
#2... Firewall... turn OFF any firewall or port blocking S/W you have on
your computer
#3.. establish the VPN to your office and verify you can ping the SQL
machine.

if this works and you still cannot connect, your problem is probably a
dynamic port issue on
your office network...

Let me know how it goes.

Chas Hyman

"dkimbrell" <dkimbrell (AT) gmail (DOT) com> wrote

Thanks for the response.

All firewalls are off. Client and server have TCP enabled. Both client and
server can ping each other.

So this would mean it is probably a dynamic port issue on the office network?

How would I begin diagnose/test/solve this problem? I'm supposed to have
this fixed by tonight!
Thanks!