Can't connect to Microsoft SQL via OpenVPN
-
No firewall - you sure?
MS SQL runs on windows OSes, every windows OS since what XP has had built in firewall. Turned on by default since what xp sp2?
Are you authing to the box correctly, so your accessing windows shares off the same box that is running SQL? From this vpn client?
-
I've manually turned of the Windows Firewall so I'm sure that no firewall is blocking the traffic. I can access SMB shares from that box, ping it, accessing the mysql server etc…. from every VPN client but not Microsoft SQL. Even if I try to telnet the port of the MS SQL Server I didn't get a reply.
Do I maybe have to create static rules and an interface for OpenVPN?
-
no you shouldn't
You sure SQL is listening on that port 1433, I know this is the default - and your accessing it directly via its IP and this port and this works on the lan? Doesn't sql use some kind of browser and dynamic ports?
Are you trying to connect to a named instance?
http://technet.microsoft.com/en-us/library/cc646023.aspx -
When I'm using LAn I can connect to the SQL Server. Even if i try to connect to it from my RemoteSite connected via IPSec I can access the server.
Yes, iT's a named instance.
-
A packet capture with a higher level of detail would be a bit more telling. The above shows the request is passed and it gets a response. Whether that's a SYN ACK or a RST in response though there's no way of telling from that.
-
I've done another capture with a windows tool on the SQL Server and one on the Client Side. I can see that the client is sending traffic to the SQL Server but it never arrives there so it seems that it's stuck at the pfsense box.
 -
You have bidirectional traffic in the other capture, do the same one there again with full verbosity.
-
08:56:05.792804 AF IPv4 (2), length 56: (tos 0x0, ttl 128, id 22691, offset 0, flags [DF], proto TCP (6), length 52) 192.168.253.6.49892 > 192.168.1.245.1433: Flags [s], cksum 0x1dcb (correct), seq 1317837410, win 8192, options [mss 1350,nop,wscale 2,nop,nop,sackOK], length 0 08:56:05.794406 AF IPv4 (2), length 44: (tos 0x0, ttl 127, id 24479, offset 0, flags [DF], proto TCP (6), length 40) 192.168.1.245.1433 > 192.168.253.6.49892: Flags [R.], cksum 0x7e16 (correct), seq 0, ack 1317837411, win 0, length 0 08:56:06.313799 AF IPv4 (2), length 56: (tos 0x0, ttl 128, id 22692, offset 0, flags [DF], proto TCP (6), length 52) 192.168.253.6.49892 > 192.168.1.245.1433: Flags [s], cksum 0x1dcb (correct), seq 1317837410, win 8192, options [mss 1350,nop,wscale 2,nop,nop,sackOK], length 0 08:56:06.314113 AF IPv4 (2), length 44: (tos 0x0, ttl 127, id 24480, offset 0, flags [DF], proto TCP (6), length 40) 192.168.1.245.1433 > 192.168.253.6.49892: Flags [R.], cksum 0x7e16 (correct), seq 0, ack 1, win 0, length 0 08:56:06.845949 AF IPv4 (2), length 52: (tos 0x0, ttl 128, id 22693, offset 0, flags [DF], proto TCP (6), length 48) 192.168.253.6.49892 > 192.168.1.245.1433: Flags [s], cksum 0x31d4 (correct), seq 1317837410, win 8192, options [mss 1350,nop,nop,sackOK], length 0 08:56:06.846406 AF IPv4 (2), length 44: (tos 0x0, ttl 127, id 24481, offset 0, flags [DF], proto TCP (6), length 40) 192.168.1.245.1433 > 192.168.253.6.49892: Flags [R.], cksum 0x7e16 (correct), seq 0, ack 1, win 0, length 0 Does that suit you?[/s][/s][/s] -
got botched by the forum formatting, fixed your post to quote it as code.
That's definitely more helpful. You're sending a SYN, and getting a RST back. That means something is actively rejecting your connection attempt. Unless you have a reject rule configured in pfSense that's blocking the traffic, it's coming from somewhere else. Either the server isn't listening on that port (that's what happens when you try to connect to a port that isn't open), or something is rejecting the connection. Your earlier packet capture seems to suggest that wouldn't be coming from the server, but it's entirely possible that capture is missing something that's actually there, not familiar enough with what you were using there to know for sure.
The last time I saw exactly what you were seeing there with a SYN responded to with a RST, just last week actually, it was because the MSSQL server wasn't listening on 1433. Check netstat -a on the server, see if it is actually listening.
-
Proto Lokale Adresse Remoteadresse Status TCP 0.0.0.0:80 SRV-2K8DB:0 ABHÖREN TCP 0.0.0.0:135 SRV-2K8DB:0 ABHÖREN TCP 0.0.0.0:443 SRV-2K8DB:0 ABHÖREN TCP 0.0.0.0:445 SRV-2K8DB:0 ABHÖREN TCP 0.0.0.0:1433 SRV-2K8DB:0 ABHÖREN TCP 0.0.0.0:3306 SRV-2K8DB:0 ABHÖREN TCP 0.0.0.0:3389 SRV-2K8DB:0 ABHÖREN TCP 0.0.0.0:6050 SRV-2K8DB:0 ABHÖREN TCP 0.0.0.0:10000 SRV-2K8DB:0 ABHÖREN TCP 0.0.0.0:41523 SRV-2K8DB:0 ABHÖREN TCP 0.0.0.0:47001 SRV-2K8DB:0 ABHÖREN TCP 0.0.0.0:49152 SRV-2K8DB:0 ABHÖREN TCP 0.0.0.0:49153 SRV-2K8DB:0 ABHÖREN TCP 0.0.0.0:49154 SRV-2K8DB:0 ABHÖREN TCP 0.0.0.0:49155 SRV-2K8DB:0 ABHÖREN TCP 0.0.0.0:49200 SRV-2K8DB:0 ABHÖREN TCP 0.0.0.0:49206 SRV-2K8DB:0 ABHÖREN TCP 0.0.0.0:49849 SRV-2K8DB:0 ABHÖREN TCP 0.0.0.0:58754 SRV-2K8DB:0 ABHÖREN TCP 0.0.0.0:59188 SRV-2K8DB:0 ABHÖREN TCP 127.0.0.1:49850 SRV-2K8DB:0 ABHÖREN TCP 127.0.0.1:58755 SRV-2K8DB:0 ABHÖREN TCP 127.0.0.1:59189 SRV-2K8DB:0 ABHÖREN TCP 192.168.1.245:139 SRV-2K8DB:0 ABHÖREN TCP 192.168.1.245:445 DE-EXT-MOB-005:65525 HERGESTE TCP 192.168.1.245:3389 DE-EXT-MOB-005:65475 HERGESTE TCP 192.168.1.245:52153 SRV-2K8AD:49155 HERGESTE TCP 192.168.1.245:52843 SRV-2K8AD:netbios-ssn WARTEND TCP 192.168.1.245:52844 SRV-2K8AD:netbios-ssn WARTEND TCP 192.168.1.245:52845 SRV-2K8AD:netbios-ssn WARTEND TCP 192.168.1.245:52848 SRV-2K8AD:epmap HERGESTE TCP 192.168.1.245:52849 SRV-2K8AD:49155 HERGESTE TCP 192.168.1.245:59188 SRV-David12-2:53009 HERGESTE TCP [::]:80 SRV-2K8DB:0 ABHÖREN TCP [::]:135 SRV-2K8DB:0 ABHÖREN TCP [::]:443 SRV-2K8DB:0 ABHÖREN TCP [::]:445 SRV-2K8DB:0 ABHÖREN TCP [::]:1433 SRV-2K8DB:0 ABHÖREN TCP [::]:3389 SRV-2K8DB:0 ABHÖREN TCP [::]:10000 SRV-2K8DB:0 ABHÖREN TCP [::]:47001 SRV-2K8DB:0 ABHÖREN TCP [::]:49152 SRV-2K8DB:0 ABHÖREN TCP [::]:49153 SRV-2K8DB:0 ABHÖREN TCP [::]:49154 SRV-2K8DB:0 ABHÖREN TCP [::]:49155 SRV-2K8DB:0 ABHÖREN TCP [::]:49200 SRV-2K8DB:0 ABHÖREN TCP [::]:49206 SRV-2K8DB:0 ABHÖREN TCP [::]:49849 SRV-2K8DB:0 ABHÖREN TCP [::]:58754 SRV-2K8DB:0 ABHÖREN TCP [::]:59188 SRV-2K8DB:0 ABHÖREN TCP [::1]:49850 SRV-2K8DB:0 ABHÖREN TCP [::1]:58755 SRV-2K8DB:0 ABHÖREN TCP [::1]:59189 SRV-2K8DB:0 ABHÖREN UDP 0.0.0.0:123 *:* UDP 0.0.0.0:500 *:* UDP 0.0.0.0:1434 *:* UDP 0.0.0.0:4500 *:* UDP 0.0.0.0:5355 *:* UDP 0.0.0.0:6050 *:* UDP 0.0.0.0:41524 *:* UDP 0.0.0.0:61245 *:* UDP 127.0.0.1:50306 *:* UDP 127.0.0.1:50823 *:* UDP 127.0.0.1:53350 *:* UDP 127.0.0.1:55764 *:* UDP 127.0.0.1:59267 *:* UDP 127.0.0.1:61244 *:* UDP 192.168.1.245:137 *:* UDP 192.168.1.245:138 *:* UDP [::]:123 *:* UDP [::]:500 *:* UDP [::]:1434 *:* UDP [::]:4500 *:* UDP [::]:5355 *:* UDP [fe80::a5ed:29ea:f1db:35ca%11]:546 *:*as you can see the SQL Server is listening on port 1433 on all IP's.
-
And this server has no software based firewall solution?
I have feeling of two most possible problems here and solutions are easy:- Check servers gateway-address that it's taking right gateway
- software based firewall blocks this openvpn connection, because it's coming from another subnet
-
as you can see in my attached screens the windows firewall is turned of and the gateway of the sevrer is the ip of the pfsense box (192.168.1.1)
 -
can you test that you route into this sql-database within your inhouse? Is there possibility, that this software just wont work from another subnet
-
So you are trying to connect to a named instance - unless this is the only instance running on the server it would not be on 1433 but a dynamic port. See the link I posted.
or a named instance that is the only instance running on the computer. (Named instances have special considerations. See Dynamic Ports later in this topic.)
By default, named instances (including SQL Server Express) use dynamic ports. That means that every time that the Database Engine starts, it identifies an available port and uses that port number. If the named instance is the only instance of the Database Engine installed, it will probably use TCP port 1433. If other instances of the Database Engine are installed, it will probably use a different TCP port. Because the port selected might change every time that the Database Engine is started, it is difficult to configure the firewall to enable access to the correct port number. Therefore, if a firewall is used, we recommend reconfiguring the Database Engine to use the same port number every time. This is called a fixed port or a static port. For more information, see Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager****
-
Hi All,
I've found the error. It was a misconfiguration of the SQL Server. I've now configured the Instance to run on a fixed Port and now everything is working fine. Thanks for your great help :)
-
Glad got it sorted.. Curious why it worked on ipsec tunnel? Unless you were connecting differently?
-
Don't know why it is working on the IPSec Tunnel but I was connecting the same way than via OpenVPN. Doesn't matter iT#s working now and I'm happy with that :)
