• Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Search
  • Register
  • Login
Netgate Discussion Forum
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Search
  • Register
  • Login

Can't connect to Microsoft SQL via OpenVPN

Scheduled Pinned Locked Moved OpenVPN
24 Posts 4 Posters 18.2k Views
Loading More Posts
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • M
    malt0r
    last edited by Sep 17, 2012, 8:45 AM

    Hello Everyone,

    I've got a pfSense 2.0.1 running. I've also running an OpenVPN Server on that box. My VPN Network is 5.5.0.0/24 and my local network is 192.168.1.0/24. I can access all SMb shares via VPN and HTTP and all other stuff bot not the Microsoft SQL Server (Port 1433).

    I've already added the following rules:

    TCP/UDP * * WAN1 address 443 (HTTPS) * none   OpenVPN Home-LAN wizard on the WAN interface

    • 5.5.0.0/24 * * * * none   OpenVPN LAN  on the LAN interface

              • none   OpenVPN Home-LAN wizard  on the OpenVPn Tab of the firewall rules.

    Can anybody help me with this issue please?

    Many thanks.

    1 Reply Last reply Reply Quote 0
    • J
      johnpoz LAYER 8 Global Moderator
      last edited by Sep 17, 2012, 12:59 PM

      Why in the world would you be using 5.5.0.0/24 as your vpn network for starters?

      inetnum:        5.4.0.0 - 5.7.255.255
      netname:        DE-E-PLUS-20120425
      descr:          E-Plus Mobilfunk GmbH

      What there is no private range you could use like say 10.5.5.0/24 ?

      Why would you think to use 5.5.0 ??

      An intelligent man is sometimes forced to be drunk to spend time with his fools
      If you get confused: Listen to the Music Play
      Please don't Chat/PM me for help, unless mod related
      SG-4860 24.11 | Lab VMs 2.7.2, 24.11

      1 Reply Last reply Reply Quote 0
      • M
        malt0r
        last edited by Sep 17, 2012, 1:48 PM

        I've changed my VPn network to 192.168.253.0/24 but it's still the same. I've also modyfied the rules.

        1 Reply Last reply Reply Quote 0
        • J
          johnpoz LAYER 8 Global Moderator
          last edited by Sep 17, 2012, 1:51 PM

          can you post your rules in a screenshot vs just the text, having a hard time understanding what is what there.

          You should not need any special rules on your lan interface or even your wan interface - only rules that would matter on your openvpn tab, maybe floating could be causing issues.  But I have everything open on my openvpn tab.. I have not found any services I could not connect to.

          sure you don't just have a host filewall on the sql box blocking the traffic - that would where I would look first.  Sniff on sql box, do you see the traffic hitting it?

          openvpnrules.png
          openvpnrules.png_thumb

          An intelligent man is sometimes forced to be drunk to spend time with his fools
          If you get confused: Listen to the Music Play
          Please don't Chat/PM me for help, unless mod related
          SG-4860 24.11 | Lab VMs 2.7.2, 24.11

          1 Reply Last reply Reply Quote 0
          • M
            malt0r
            last edited by Sep 17, 2012, 1:58 PM

            Please find attached screenshots of the LAN, WAN and OpenVPN rules.

            I've also done a packet capture:

            15:57:28.617356 IP 192.168.253.6.56705 > 192.168.1.245.1433: tcp 0
            15:57:28.619250 IP 192.168.1.245.1433 > 192.168.253.6.56705: tcp 0
            15:57:29.146403 IP 192.168.253.6.56705 > 192.168.1.245.1433: tcp 0
            15:57:29.146708 IP 192.168.1.245.1433 > 192.168.253.6.56705: tcp 0
            15:57:29.664995 IP 192.168.253.6.56705 > 192.168.1.245.1433: tcp 0
            15:57:29.665302 IP 192.168.1.245.1433 > 192.168.253.6.56705: tcp 0

            192.168.1.245 is the IP of the Microsoft SQL Server

            openvpn.png
            openvpn.png_thumb
            wan.png
            wan.png_thumb
            lan.png
            lan.png_thumb

            1 Reply Last reply Reply Quote 0
            • J
              johnpoz LAYER 8 Global Moderator
              last edited by Sep 17, 2012, 3:27 PM

              where did you do that capture?  On the pfsense box, clearly the traffic is going on the wire and looks like response.

              What is in your floating tab for rules?

              An intelligent man is sometimes forced to be drunk to spend time with his fools
              If you get confused: Listen to the Music Play
              Please don't Chat/PM me for help, unless mod related
              SG-4860 24.11 | Lab VMs 2.7.2, 24.11

              1 Reply Last reply Reply Quote 0
              • M
                malt0r
                last edited by Sep 17, 2012, 5:04 PM

                The capture was done on the pfsense Box. My Floating rules Tab is empty. Also there is nö Firewall on the SQL Server.

                1 Reply Last reply Reply Quote 0
                • J
                  johnpoz LAYER 8 Global Moderator
                  last edited by Sep 17, 2012, 6:04 PM

                  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?

                  An intelligent man is sometimes forced to be drunk to spend time with his fools
                  If you get confused: Listen to the Music Play
                  Please don't Chat/PM me for help, unless mod related
                  SG-4860 24.11 | Lab VMs 2.7.2, 24.11

                  1 Reply Last reply Reply Quote 0
                  • M
                    malt0r
                    last edited by Sep 17, 2012, 7:56 PM Sep 17, 2012, 7:54 PM

                    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?

                    1 Reply Last reply Reply Quote 0
                    • J
                      johnpoz LAYER 8 Global Moderator
                      last edited by Sep 17, 2012, 8:36 PM

                      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

                      An intelligent man is sometimes forced to be drunk to spend time with his fools
                      If you get confused: Listen to the Music Play
                      Please don't Chat/PM me for help, unless mod related
                      SG-4860 24.11 | Lab VMs 2.7.2, 24.11

                      1 Reply Last reply Reply Quote 0
                      • M
                        malt0r
                        last edited by Sep 17, 2012, 10:22 PM

                        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.

                        1 Reply Last reply Reply Quote 0
                        • C
                          cmb
                          last edited by Sep 17, 2012, 10:58 PM

                          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.

                          1 Reply Last reply Reply Quote 0
                          • M
                            malt0r
                            last edited by Sep 18, 2012, 6:48 AM

                            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.

                            ![client capture.png](/public/imported_attachments/1/client capture.png)
                            ![client capture.png_thumb](/public/imported_attachments/1/client capture.png_thumb)
                            ![sql capture.png](/public/imported_attachments/1/sql capture.png)
                            ![sql capture.png_thumb](/public/imported_attachments/1/sql capture.png_thumb)

                            1 Reply Last reply Reply Quote 0
                            • C
                              cmb
                              last edited by Sep 18, 2012, 6:51 AM

                              You have bidirectional traffic in the other capture, do the same one there again with full verbosity.

                              1 Reply Last reply Reply Quote 0
                              • M
                                malt0r
                                last edited by Sep 18, 2012, 8:04 AM Sep 18, 2012, 6:58 AM

                                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]
                                
                                1 Reply Last reply Reply Quote 0
                                • C
                                  cmb
                                  last edited by Sep 18, 2012, 8:08 AM

                                  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.

                                  1 Reply Last reply Reply Quote 0
                                  • M
                                    malt0r
                                    last edited by Sep 18, 2012, 10:15 AM

                                     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.

                                    1 Reply Last reply Reply Quote 0
                                    • M
                                      Metu69salemi
                                      last edited by Sep 18, 2012, 11:08 AM

                                      And this server has no software based firewall solution?
                                      I have feeling of two most possible problems here and solutions are easy:

                                      1. Check servers gateway-address that it's taking right gateway
                                      2. software based firewall blocks this openvpn connection, because it's coming from another subnet
                                      1 Reply Last reply Reply Quote 0
                                      • M
                                        malt0r
                                        last edited by Sep 18, 2012, 12:06 PM

                                        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)

                                        firewall.png
                                        firewall.png_thumb
                                        ![ip config.png](/public/imported_attachments/1/ip config.png)
                                        ![ip config.png_thumb](/public/imported_attachments/1/ip config.png_thumb)

                                        1 Reply Last reply Reply Quote 0
                                        • M
                                          Metu69salemi
                                          last edited by Sep 18, 2012, 4:36 PM

                                          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

                                          1 Reply Last reply Reply Quote 0
                                          20 out of 24
                                          • First post
                                            20/24
                                            Last post
                                          Copyright 2025 Rubicon Communications LLC (Netgate). All rights reserved.
                                            This community forum collects and processes your personal information.
                                            consent.not_received