1

I have a rather complex situation involving getting an external server outside a corporate network talking to a MSSQL server inside the corporate network. The purpose is to pull live database data into a cloud application for the client. I am working for the cloud application vendor. The cloud application is a "big data" analytics solution.

The client insists upon keeping their database within the walls of their own network, so moving the whole DB to the cloud is out.

The setup we have is:

Server 1) Ubuntu Virtual Server, sitting on AWS, running the cloud application. The cloud application is expecting to be told the IP address and port of a MSSQL server. We have root access to this.

Server 2) Ubuntu physical server, sitting inside the corporate network. We put this in to negotiate routing data to external cloud services. We have root access to this and can do what we like to it

Server 3) MSSQL database server, sitting inside the corporate network. This database server is used by a wide variety of client business applications so understandably the corporate IT department want us to leave this well alone. We do not have root access to this server. We have a read-only user account to connect to the database.

                         | (Corporate network)
(Cloud provider)         |
[Server 1] <=====(reverse SSH tunnel)===[Server 2]
                         |                  | (happily talking)
                         |              [Server 3 (DB)]

For various reasons, we cannot be given a public IP address for Server 2, nor can the corporate IT dept forward ports to Server 2. The only way we can actually speak to Server 2 from outside, which the Corporate IT dept found acceptable, is via reverse SSH tunnelling. We are allowed to create multiple reverse tunnels if we need.

The reverse tunnelling is working fine:

    Server 2)#   ssh -f -N -R 50022:localhost:22 reversessh@server_1.ip.address

And we can reach Server 2 from Server 1 with:

    Server 1)$   ssh my_user_on_server2@localhost -p 50022

Server 2 inside the network can see Server 3 and can happily connect to the MSSQL database server running on it. FWIW I'm been using unixodbc / FreeTDS to test talking to Server 3's MSSQL.

How could I get an MSSQL client on Server 1 talking to the database on Server 3?

What I've tried:

Bearing in mind we have full control over Server 2, I have tried to engineer port forwarding via UFW so packets hitting port 51433 on Server 2 get sent on to port 1433 Server 3. But even this doesn't seem to work (yes I've disabled then enabled UFW after adding the rules)

/etc/ufw/sysctl.conf

    net/ipv4/ip_forward=1

/etc/ufw/before.rules

    *nat
    :PREROUTING ACCEPT [0:0]
    -A PREROUTING -p tcp --dport 51433 -j DNAT --to-destination server_3.internal.ip.address:1433
    COMMIT

However, attempting to connect to the MSSQL database on Server 2 using localhost:51433 doesn't work.

My plan was then to set up another reverse SSH tunnel between Server 2 and Server 1:

    Server 2)#   ssh -f -N -R 1433:localhost:51433 reversessh@server_1.ip.address

And then point the MSSQL "client" (the cloud application) to localhost:1433 on Server 1.

So, bearing in mind we can only do Reverse SSH tunnelling to get traffic into our server on the inside...

How could I get an MSSQL client on Server 1 talking to the database on Server 3?

Is there another approach we could use, given the constraints of the corporate network?

Update

Working solution suggested by @dave_thompson_085:

    Server 2)#   ssh -f -N -R 51433:server3:1433 user@server1
  • I'm never too fond SSH tunnels as a "permanent" solution and would recommend instead of your creative approach simply have your client talk to their network administrator (maybe to set up a meeting where you attend as well) , explain their business requirements and get them to open up the required firewall ports (or set up a VPN or similar) rather than trying to circumvent the current network restrictions or alternatively have your client set up filtered replication to a second database and allow you acces to that. – HBruijn Aug 18 '16 at 15:59
  • If you can create arbitrary reverse tunnels (which ssh actualls calls forwardings) just do on server2 ssh -fNR 51433:server3:1433 user@server1 and have app on server1 connect to its localhost:51433. – dave_thompson_085 Aug 18 '16 at 16:03
  • @dave_thompson_085 that's an excellent suggestion! It works perfectly! Perhaps you'd like to submit it as an answer for me to accept? – michaeljtbrooks Aug 19 '16 at 8:16
  • @HBruijn agreed, it's not ideal. However the client laid off a lot of its IT staff a few years ago, including whoever was dealing with network admin. For any network / firewall jobs they have to hire him back in as a consultant. This requires budget approval separate to what's been approved for our SaaS, and I'm told this will take weeks. – michaeljtbrooks Aug 19 '16 at 8:16
  • 1
    An update to this... the reverse SSH tunnel has been operating stably for the last two years without significant downtime. – michaeljtbrooks Aug 27 '18 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.