Can't connect to local Sql Server while connected to a VPN

Question

We have a Sql Server in our office here that I connect to regularly. No problems.

We have a client now to whose VPN we need to connect using the Windows VPN software. When I connect to the VPN though, I can no longer connect to our local sql server.

I have made sure the VPN connection has the checkbox for "Use default gateway on remote network" is turned off for both IPv4 and IPv6, so I SHOULD be able to access my local LAN resources. I know that this at least partially working because I can RDP into our local servers.

For some reason though, I just cannot connect to our local Sql Server via Sql Server Manager or Sql Delta. I tried connecting via DNS name and IP address, both with and without ,1433 on the end.

By name without the port, I get the usual "network resource could not be found, make sure remote connections are enabled" message. When I try it by IP, or by name with the port, I get the following error:

Can anyone point me to how to fix this? I'm trying to get it set up so when we need to push changes, I can use Sql Delta to just generate change scripts and do it quickly, rather than having to backup/zip/copy/unzip/restore the whole database every time.

I'm using Windows 8. Thanks!

As requested, here is my route table...

C:\>route print
===========================================================================
Interface List
 35...........................FBS
 19...0c 60 76 37 fd 80 ......Microsoft Hosted Network Virtual Adapter
 13...00 26 b9 6f d3 84 ......NVIDIA nForce Networking Controller
 12...0c 60 76 37 fd 80 ......Dell Wireless 1510 Wireless-N WLAN Mini-Card
  1...........................Software Loopback Interface 1
 15...00 00 00 00 00 00 00 e0 Microsoft ISATAP Adapter
 16...00 00 00 00 00 00 00 e0 Teredo Tunneling Pseudo-Interface
 17...00 00 00 00 00 00 00 e0 Microsoft ISATAP Adapter #2
===========================================================================

IPv4 Route Table
===========================================================================
Active Routes:
Network Destination        Netmask          Gateway       Interface  Metric
          0.0.0.0          0.0.0.0      192.168.1.1    192.168.1.104     25
         10.0.0.0        255.0.0.0       10.3.82.40       10.3.82.42     26
       10.3.82.42  255.255.255.255         On-link        10.3.82.42    281
    66.134.25.226  255.255.255.255      192.168.1.1    192.168.1.104     26
        127.0.0.0        255.0.0.0         On-link         127.0.0.1    306
        127.0.0.1  255.255.255.255         On-link         127.0.0.1    306
  127.255.255.255  255.255.255.255         On-link         127.0.0.1    306
      192.168.1.0    255.255.255.0         On-link     192.168.1.104    281
    192.168.1.104  255.255.255.255         On-link     192.168.1.104    281
    192.168.1.255  255.255.255.255         On-link     192.168.1.104    281
        224.0.0.0        240.0.0.0         On-link         127.0.0.1    306
        224.0.0.0        240.0.0.0         On-link     192.168.1.104    281
        224.0.0.0        240.0.0.0         On-link        10.3.82.42    281
  255.255.255.255  255.255.255.255         On-link         127.0.0.1    306
  255.255.255.255  255.255.255.255         On-link     192.168.1.104    281
  255.255.255.255  255.255.255.255         On-link        10.3.82.42    281
===========================================================================
Persistent Routes:
  None

IPv6 Route Table
===========================================================================
Active Routes:
 If Metric Network Destination      Gateway
  1    306 ::1/128                  On-link
  1    306 ff00::/8                 On-link
===========================================================================
Persistent Routes:
  None

C:\>

I'm a developer, so software is really my thing. When it comes to any kind of more advanced networking, I'm a little out of my element, so I'm not sure what to make of most of that.

Answer 1

From your routing table it seems your main gateway is in the 192.168.1.0 net, and the remote network (the one made accessible by the VPN) is instead 10.0.0.0. But you also told us that pretty much all of your (local) servers are in the 10.0.0.0 range. From this it follows that there is a clash of Ip addresses: when connected thru the VPN, all packets to 10.0.0.0 are routed thru the VPN to the remote LAN, even those destined to your local sql server.

If the sql server is in the 10.3.82.0 network, which I believe is the remote LAN accessible thru the VPN (please correct if I am getting this wrong), there is nothing you can do. If instead the server is not in a 10.3.82.0 network, then the following commands, to be given after the establishment of the VPN conenction, will solve your problem:

route delete 10.0.0.0 
route ADD 10.3.82.0 MASK 255.255.255.0 10.3.82.40  
route ADD 10.0.0.0 MASK 255.0.0.0 192.168.1.1

There is an apparent conflict between these routes, but not to worry: the more restrictive rule always takes precedence on the larger rule, yielding the correct result.

Answer 2

Try to do the following:

1. Locate the .pbk file that contains the entry that you dial. In my case it is C:\Users\<user>\AppData\Roaming\Microsoft\Network\Connections\Pbk

2. Open the file in Notepad.

3. Locate the UseRasCredentials=1 entry

   

4. Change the 1 to 0 -> UseRasCredentials=0

   

5. Save it and close your Notepad

Just to add: I don't know how many VPN connections you have (I have one), but in case that you have more than one, you need to find all instances of UseRasCredentials=1 and change them to UseRasCredentials=0. Each VPN connection have the name in a form [VPN Connection name] at the beginning of its section