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

20
2014-04
  • eidylon

    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:

    enter image description here

    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.

  • Answers
  • MariusMatutiae

    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.

  • Nikola Dimitrijevic

    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

      enter image description here

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

      enter image description here

    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


  • Related Question

    windows 7 - Rerouting local LAN and Internet traffic when in VPN
  • Domchi

    I'm connecting to a VPN which doesn't allow split tunneling and basically reroutes my Internet traffic through, which is slow. Additionally and more importantly, this also effectively removes my machine from local LAN.

    I'm looking for a way to modify routing table on Windows 7 to route Internet traffic and local LAN connections as usual, and restrict VPN traffic to 10.0.53.0 network, but although I know how to route delete and route add, I'm failing to understand what exactly I need to reroute.

    My network looks like this:

    • 192.168.192.0 - my local LAN
    • 192.168.192.1 - my router
    • 192.168.192.2 - my computer
    • 10.0.53.0 - VPN network
    • 10.0.53.1 - VPN gateway

    This are my routes when VPN is not connected (ipconfig + route print):

    Ethernet adapter Local Area Connection:
    
       Connection-specific DNS Suffix  . : lan
       Link-local IPv6 Address . . . . . : fe80::3449:3fc8:6133:b564%11
       IPv4 Address. . . . . . . . . . . : 192.168.192.2
       Subnet Mask . . . . . . . . . . . : 255.255.255.0
       Default Gateway . . . . . . . . . : 192.168.192.1
    
    Tunnel adapter isatap.lan:
    
       Media State . . . . . . . . . . . : Media disconnected
       Connection-specific DNS Suffix  . : lan
    
    Tunnel adapter Local Area Connection* 9:
    
       Connection-specific DNS Suffix  . :
       IPv6 Address. . . . . . . . . . . : 2001:0:5ef5:79fd:8fa:15c1:a65b:dce4
       Link-local IPv6 Address . . . . . : fe80::8fa:15c1:a65b:dce4%14
       Default Gateway . . . . . . . . . : ::
    
    ===========================================================================
    Interface List
     11...00 16 e6 dc 32 b6 ......Marvell Yukon 88E8052 PCI-E ASF Gigabit Ether
    ontroller
      1...........................Software Loopback Interface 1
     13...00 00 00 00 00 00 00 e0 Microsoft ISATAP Adapter
     14...00 00 00 00 00 00 00 e0 Teredo Tunneling Pseudo-Interface
    ===========================================================================
    
    IPv4 Route Table
    ===========================================================================
    Active Routes:
    Network Destination        Netmask          Gateway       Interface  Metric
              0.0.0.0          0.0.0.0    192.168.192.1    192.168.192.2     20
            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.192.0    255.255.255.0         On-link     192.168.192.2    276
        192.168.192.2  255.255.255.255         On-link     192.168.192.2    276
      192.168.192.255  255.255.255.255         On-link     192.168.192.2    276
            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.192.2    276
      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.192.2    276
    ===========================================================================
    Persistent Routes:
      None
    

    And this are my routes when VPN is connected (ipconfig + route print):

    Ethernet adapter Local Area Connection 5:
    
       Connection-specific DNS Suffix  . : emporion.hr
       Link-local IPv6 Address . . . . . : fe80::e127:bf06:eff3:f18e%26
       IPv4 Address. . . . . . . . . . . : 10.0.53.21
       Subnet Mask . . . . . . . . . . . : 255.255.255.0
       Default Gateway . . . . . . . . . : 10.0.53.1
    
    Ethernet adapter Local Area Connection:
    
       Connection-specific DNS Suffix  . : lan
       Link-local IPv6 Address . . . . . : fe80::3449:3fc8:6133:b564%11
       IPv4 Address. . . . . . . . . . . : 192.168.192.2
       Subnet Mask . . . . . . . . . . . : 255.255.255.0
       Default Gateway . . . . . . . . . : 192.168.192.1
    
    Tunnel adapter isatap.lan:
    
       Media State . . . . . . . . . . . : Media disconnected
       Connection-specific DNS Suffix  . : lan
    
    Tunnel adapter Local Area Connection* 9:
    
       Connection-specific DNS Suffix  . :
       IPv6 Address. . . . . . . . . . . : 2001:0:5ef5:79fd:8fa:15c1:a65b:dce4
       Link-local IPv6 Address . . . . . : fe80::8fa:15c1:a65b:dce4%14
       Default Gateway . . . . . . . . . : ::
    
    ===========================================================================
    Interface List
     26...00 05 9a 3c 78 00 ......Cisco Systems VPN Adapter for 64-bit Windows
     11...00 16 e6 dc 32 b6 ......Marvell Yukon 88E8052 PCI-E ASF Gigabit Ether
    ontroller
      1...........................Software Loopback Interface 1
     13...00 00 00 00 00 00 00 e0 Microsoft ISATAP Adapter
     14...00 00 00 00 00 00 00 e0 Teredo Tunneling Pseudo-Interface
    ===========================================================================
    
    IPv4 Route Table
    ===========================================================================
    Active Routes:
    Network Destination        Netmask          Gateway       Interface  Metric
              0.0.0.0          0.0.0.0    192.168.192.1    192.168.192.2     20
              0.0.0.0          0.0.0.0        10.0.53.1       10.0.53.22     21
            10.0.53.0    255.255.255.0         On-link        10.0.53.22    276
           10.0.53.22  255.255.255.255         On-link        10.0.53.22    276
          10.0.53.255  255.255.255.255         On-link        10.0.53.22    276
            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.192.0    255.255.255.0         On-link     192.168.192.2    276
        192.168.192.0    255.255.255.0        10.0.53.1       10.0.53.22    276
        192.168.192.1  255.255.255.255         On-link     192.168.192.2    100
        192.168.192.2  255.255.255.255         On-link     192.168.192.2    276
        192.168.192.2  255.255.255.255        10.0.53.1       10.0.53.22    276
      192.168.192.255  255.255.255.255         On-link     192.168.192.2    276
       213.147.99.115  255.255.255.255    192.168.192.1    192.168.192.2    100
            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.192.2    276
            224.0.0.0        240.0.0.0         On-link        10.0.53.22    276
      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.192.2    276
      255.255.255.255  255.255.255.255         On-link        10.0.53.22    276
    ===========================================================================
    Persistent Routes:
      Network Address          Netmask  Gateway Address  Metric
              0.0.0.0          0.0.0.0        10.0.53.1       1
    ===========================================================================
    

  • Related Answers
  • matrix154

    you problem is related to the version of the vpn client you use. If it es a cisco client, cisco has already fixed it with version 5.0.07.0410-k9 for 32Bit and 5.0.07.0440-k9 for 64Bit.

    Other case you have to do folowing:

    1. delete all staticaly added route (network or defaulte route)

      route DELETE 0.0.0.0

    2. change the metric of you local default route to the BEST "1"

      route ADD -p 0.0.0.0 MASK 0.0.0.0 192.168.192.1 METRIC 1

    3. Assure that your vpn server - i think this IP 213.147.99.115 - should be reachable over your local gateway

      route ADD -p 213.147.99.115 MASK 255.255.255.255 192.168.192.1 METRIC 1

    4. Assure the reachability of you local net because of this route in your output of "route print"

      192.168.192.0 255.255.255.0 On-link 192.168.192.2 276

      192.168.192.0 255.255.255.0 10.0.53.1 10.0.53.22 276

    with

    route CHANGE 192.168.192.0 MASK 255.255.255.0 192.168.192.1 METRIC 1

    1. When vpn is connected, change the gateway for the remote net to the IP address assigned to your cisco vpn client - in your ipconfig it's 10.0.53.22 - and metric to 10 (because < 276) to make sure that this route is valid.

      route CHANGE 10.0.53.0 MASK 255.255.255.0 10.0.53.22 METRIC 10

    if it failed delete the route first and add it again with "route ADD"

  • Lenne

    I just did this. By not allowing the vpn to take the the default gateway, i get all traffic to the remote network over vpn, and the rest the usual route through my router.

    On the vpn "nic", Network->ipv4->properties->advanced->ip-settings Remove check in "Use default gateway in remote network"

    (Or something like that, I my windows 8 is danish)

  • Randolf Richardson

    Check your Cisco VPN documentation for keywords like "default route" or "persistent route" in the hopes of finding an option to turn of the setting of the default route or gateway for VPN clients.

  • grawity
    1. Delete all routes that point to 10.0.53.1 as gateway.
    2. Add a route to 10.0.53.0 mask 255.255.255.0 via the same gateway.