Problem connecting to SQL Server through OpenVpn tunnel

[mitrix]

Hello! Clients can't connect to SQL Server using an instance name (sqlsrv\DEV for example)
OpenVPN server(version 2.2.1) is on a linux box(IP:10.0.100.1), clients are on Windows (XP & 7)(IP:10.0.102.0/24), SQL Server(IP:10.0.100.15) is in the same network with OpenVPN server.
An client to connect to an instance of sql server using instance name, is sending UDP packets on port 1434 to server (there is an SqlServer Browser service which is listening on this port). The problem is that these packets do not reach the server.

When I try to connect I listen on windows with windump and on linux with tcpdump:

windump -qn -i 2 host sqlsrv (2 is TAP interface)

Code: Select all

windump: listening on \Device\NPF_{3323E04C-470B-48A2-9ABA-C8875E107B49}
02:42:12.658175 IP 10.0.102.22.56032 > 10.0.100.15.1434: UDP, length 5
02:42:14.657714 IP 10.0.102.22.56033 > 10.0.100.15.1434: UDP, length 5
02:42:16.657657 IP 10.0.102.22.56034 > 10.0.100.15.1434: UDP, length 5

and in the same time on linux with `tcpdump -i tun0 udp` nothing appears..
But.. if I connect to that instance directly with port number (sqlserver, 20000) everything is ok, and also I can acces anything on work network (10.0.100.0/24) (windows shares, cvs, remote desktop, etc..)

Any firewall on windows is off.

server.conf

Code: Select all

local XX.XXX.XXX.XX
port 51194
proto udp
dev tun

ca keys/ca.crt
cert keys/server.crt
key keys/server.key
dh dh1024.pem

server 10.0.102.0 255.255.255.0

ifconfig-pool-persist ipp.txt
push "route 10.0.100.0 255.255.255.0"

push "dhcp-option DNS 10.0.100.10"
push "dhcp-option DOMAIN abc.group"

client-to-client

client-config-dir ccd

keepalive 10 20

user nobody
group nobody

persist-key
persist-tun

status openvpn-status.log
log         openvpn.log
verb 3
mute 20

crl-verify crl.pem

client.conf

Code: Select all

client
dev tun
proto udp
remote XX.XXX.XXX.XX 51194
ns-cert-type server

ca ca.crt
cert ClientHome.crt
key ClientHome.key

resolv-retry infinite

persist-key
persist-tun
verb 3

iptables -nvL

Code: Select all

Chain INPUT (policy ACCEPT 17M packets, 6407M bytes)
 pkts bytes target     prot opt in     out     source               destination
    4   200 DROP       tcp  --  eth0   *       0.0.0.0/0            0.0.0.0/0           tcp dpt:25
    0     0 DROP       tcp  --  eth0   *       0.0.0.0/0            0.0.0.0/0           tcp dpt:10000

Chain FORWARD (policy ACCEPT 15M packets, 11G bytes)
 pkts bytes target     prot opt in     out     source               destination

Chain OUTPUT (policy ACCEPT 26M packets, 34G bytes)
 pkts bytes target     prot opt in     out     source               destination

ifconfig

Code: Select all

eth0      Link encap:Ethernet  HWaddr 00:04:E2:33:9E:5D
          inet addr:XX.XXX.XXX.XX  Bcast:XX.XXX.XXX.XX  Mask:255.255.255.224
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:288514724 errors:2214 dropped:0 overruns:1914 frame:2217
          TX packets:262712101 errors:255 dropped:0 overruns:0 carrier:255
          collisions:0 txqueuelen:1000
          RX bytes:2117528692 (1.9 GiB)  TX bytes:2552925366 (2.3 GiB)
          Interrupt:16

eth1      Link encap:Ethernet  HWaddr 00:11:D8:8D:CD:8D
          inet addr:10.0.100.1  Bcast:10.0.100.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:194952709 errors:0 dropped:0 overruns:0 frame:0
          TX packets:228694195 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:4251645790 (3.9 GiB)  TX bytes:1978389015 (1.8 GiB)
          Interrupt:17

lo        Link encap:Local Loopback
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:647 errors:0 dropped:0 overruns:0 frame:0
          TX packets:647 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:46209 (45.1 KiB)  TX bytes:46209 (45.1 KiB)

tun0      Link encap:UNSPEC  HWaddr 00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00
          inet addr:10.0.102.1  P-t-P:10.0.102.2  Mask:255.255.255.255
          UP POINTOPOINT RUNNING NOARP MULTICAST  MTU:1500  Metric:1
          RX packets:3 errors:0 dropped:0 overruns:0 frame:0
          TX packets:3 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:100
          RX bytes:187 (187.0 b)  TX bytes:362 (362.0 b)

In OpenVPN logs everything is fine..

I tried for two weeks to solve this problem without success. Please help.

[janjust]

try pinging the SQL server first; does the SQL server know that packets with source address 10.102.x need to go back to the VPN server? can you add a route to this box to do that?

[mitrix]

Yes, SqlServer responds at ping from clients. The default gateway on SqlServer is 10.0.100.1.

[Mimiko]

Check the sql browser if it has no rules to respond only to packets with source from local lan only.

[mitrix]

No, I verified and SqlBrowser does not have settings to respond only to packets with source from local lan only.

For testing, on OpenVPN server box is installed poptop - if a windows client is connecting to this poptop, the connection to SqlServer can be made normaly with the instance name.

[Mimiko]

Now you are using poptop insteed of OpenVPN? If so and the task is accomplished, then its a better solution.

[janjust]

most likely this is a routing issue, or an MTU issue.

If poptop works and openvpn does not then check the routing tables on the client after either connects.

To verify whether it's an MTU issue add

Code: Select all

fragment 1300

to both client and server configs and restart the VPN connection.

[mitrix]

@Mimiko: No, I want to use OpenVPN not poptop. I used poptop only for testing.

@janjust: I tried with [fragment 1300] but with no luck.

Windows routes:

Code: Select all

Active Routes:
Network Destination        Netmask          Gateway       Interface  Metric
          0.0.0.0          0.0.0.0       10.0.101.1     10.0.101.101     20
       10.0.100.0    255.255.255.0       10.0.102.5       10.0.102.6     30
       10.0.101.0    255.255.255.0         On-link      10.0.101.101    276
     10.0.101.101  255.255.255.255         On-link      10.0.101.101    276
     10.0.101.255  255.255.255.255         On-link      10.0.101.101    276
       10.0.102.0    255.255.255.0       10.0.102.5       10.0.102.6     30
       10.0.102.4  255.255.255.252         On-link        10.0.102.6    286
       10.0.102.6  255.255.255.255         On-link        10.0.102.6    286
       10.0.102.7  255.255.255.255         On-link        10.0.102.6    286
        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
        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      10.0.101.101    276
        224.0.0.0        240.0.0.0         On-link        10.0.102.6    286
  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      10.0.101.101    276
  255.255.255.255  255.255.255.255         On-link        10.0.102.6    286
===========================================================================
Persistent Routes:
  None

Linux routes:

Code: Select all

Kernel IP routing table
Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
10.0.102.2      0.0.0.0         255.255.255.255 UH    0      0        0 tun0
XX.XXX.XXX.0    0.0.0.0         255.255.255.224 U     0      0        0 eth0
10.0.102.0      10.0.102.2      255.255.255.0   UG    0      0        0 tun0
10.0.100.0      0.0.0.0         255.255.255.0   U     0      0        0 eth1
0.0.0.0         XX.XXX.XXX.1    0.0.0.0         UG    0      0        0 eth0

Routes should be fine because I can access anything else.. i think..
I can not understand why those packets that appear on windump not reach the server (they should appear in tcpdump)

[Mimiko]

How about dumping packets with other port or protocol? Read this http://blogs.technet.com/b/nettracer/ar ... uster.aspx may be you get some hints.

[mitrix]

I've tried dumping with `tcpdump -i tun0` but still nothing.. it's good to know what I read about the sql server in cluster mode but is not my case.
Meanwhile I have made another test: If I make the connection to OpenVPN server from a computer with Linux, that I use as a router to my home computer, everything is ok - I can connect with the instance name to sql server.
Is it possible that this problem to be some kind of a bug in Windows OpenVPN version ? Is there anyone who can connect to a sql server with instance name through a OpenVpn tunnel from windows ?

[Mimiko]

As I understand, the sql instance name must resolve to some IP. Is it resolving?

[mitrix]

No, to connect I use sqlservername\instancename
sqlservername is resolving to the server IP (by DNS server) and is working
and instancename must be resolved to a port number by the SqlBrowser service that is installed on sqlservername, but the problem is SqlBrowser did not receive any queries

In this link sqlservername = snssql01 and instancename = DEV
[img=http://s1.postimage.org/cixs0103z/problem.png]

[Mimiko]

You said that using ip - the connection is established. Could you in server name input use IP\DEV ?
The name snssql01 is not resolved by DNS.

[mitrix]

I already tried with IP\DEV is not working; only if I enter IP, 20000 or sqlsrv, 20000 is working (20000 is the port number of sql instance)

[mitrix]

Problem solved in version 2.2.2.