Accessing Oracle Database via a Cisco VPN (via LDAP) on Linux

Two proprietary pieces of software in one title! Stallman would be triggered… (and yes, the solution is horrible)

Published in

UCL API

11 min readFeb 6, 2017

Over the last couple of weeks I’ve been hard at work with the UCL API team to get Django on AWS talking to the Oracle Room Bookings Database at UCL. This has possibly been one of the worst programming experiences I have ever had. Period.

In theory this should all be fairly straightforward.

Here’s the Problem

We are currently working on the first stage of the UCL API project: Room Bookings data. This is all stored in an Oracle database within UCL’s Institutional Firewall (IFW), and therefore accessible only within the UCL campus or via VPN. Ideally we want to access all of this data from within Django hosted on an Amazon Web Services Elastic Computing Cloud (AWS EC2) instance using the full Object-Relation Mapping (ORM) functionality that Django provides so that we can code everything without writing specific, custom queries.

Oracle’s Database is all proprietary software, both on the server and the client sides. This means that if anything needs tweaking or a non-standard configuration is required, tricks are needed as the client cannot be tweaked directly.

At UCL we use the Cisco AnyConnect VPN appliance. This, too, is complex proprietary software but luckily it can be connected to using the handy open source OpenConnect project. We’ll see later how this can be harnessed.

To top all of this off, we are hosting all of our code on AWS’s London Data Centre, which is far from the UCL IFW…

The Theoretical Solution

This does not sound, in theory, desperately difficult to solve. Just run OpenConnect on an Amazon EC2 instance and connect to the Room Bookings Database normally from Django. Unfortunately, however, this does not work for some reason on AWS, and running OpenConnect as a tunnel for all outbound traffic totally breaks networking on the EC2 instance, even though this solution seems to work perfectly well in VMware Workstation locally. This is true even if we set up the Amazon Virtual Private Cloud to use an IP Address range that UCL does not use, such as 172.16.x.x.

The Actual Solution

F for respects…

The way I solved this in the end was using a couple of extra tools…

A disgracefully ugly diagram showing the setup I created for working with Oracle… *shudders*

OpenConnect

OpenConnect is an open source client for Cisco AnyConnect, which is the enterprise VPN appliance we have at UCL. Whilst it can be complex to set up, there’s an excellent guide available on UCL’s website that allows any Linux system to get OpenConnect working with UCL’s network. This gets our EC2 instance within the UCL IFW.

However, even though running this as root locally in a VMware instance seems to work fine with all proxying enabled, enabling it in EC2 completely messes up the Linux IP Stack and a lack of ability to bind to the instance’s assigned elastic IP. All external connections (e.g. via SSH) immediately fail. Connections from other VMs in the same Virtual Private Cloud seem to get through but the system is filled with lag and an inability to properly connect to the internet.

This leaves one solution: running OpenConnect as an unprivileged user and then piping the traffic we want through it. Cue ocproxy…

ocproxy

ocproxy is an awesome little tool that will bolt a SOCKSv5 proxy server right on top of OpenConnect. It also does not even require root access as it binds to non-standard ports.

SOCKS is a useful protocol as it can support pretty much any TCP traffic (which is important given that we do not know how the Oracle client talks to the internal database due to closed source libraries).

At this point we can start to use SQLcl, the command line interface to Oracle that is provided by Oracle themselves. This is because SQLcl is written in Java which actually has some fairly badly documented command line switches that allow you to force all connections via SOCKS. Unfortunately, however, we are not so lucky with the Oracle Instant Client which provides you only with a bunch of compiled native libraries…

redsocks

As we cannot control how the Instant Client communicates (e.g. we cannot force it to use SOCKS like we can with SQLcl), I made use of a tool called redsocks to utiliseiptables to bounce our Oracle TCP connections via the ocproxy SOCKS server, which in turn would send everything via the UCL VPN. This is pretty hacky, but actually worked surprisingly well.

iptables

The glue here is the iptables firewalling tool that comes built into most major Linux distributions. I was able to set up all TCP connections, except those to 172.16.x.x local addresses (the IP range we are using on our Virtual Private Cloud on EC2), to be sent via redsocks, via ocproxy, via OpenConnect, via UCL.

Oracle Instant Client

This is downloadable from Oracle’s website, and is just a bunch of native libraries. It is required for the Python cx_Oracle library that can plug into Django to provide database access.

cx_Oracle

cx_Oracle is an open source wrapper library for the Oracle Instant Client that allows Python code to get access to Oracle Databases pretty easily. It’s a requirement of Django’s Oracle support.

Guide

If you want to set up an abomination like this yourself, here’s how you’d do it! I am assuming that you are running on Ubuntu 64-bit. The most recent supported versions at this point in time are 16.10 and 16.04 LTS, so I’ll assume you are running on one of these Operating Systems.

Before doing anything you’ll want to download the following files, and place them into your Linux home directory. Do not unzip them for now!

Oracle Instant Basic Client (Linux x86–64)
http://download.oracle.com/otn/linux/instantclient/121020/instantclient-basic-linux.x64-12.1.0.2.0.zip
Oracle Instant Client SDK (Linux x86–64)
http://download.oracle.com/otn/linux/instantclient/121020/instantclient-sdk-linux.x64-12.1.0.2.0.zip
SQLcl
http://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.html

# Get the latest updates before we begin
sudo apt-get update
sudo apt-get -y dist-upgrade# Add the PPA for Git to get the latest version
sudo add-apt-repository -y ppa:git-core/ppa
sudo apt-get update# Install everything we need
sudo apt-get install git build-essential gettext curl unzip openjdk-8-jre-headless autoconf automake gnupg iptables liblz4-1 libevent-dev libssl-dev libp11-dev libpcre3 libpcre3-dev libxml2 libxml2-dev redsocks libproxy-dev zlib1g-dev libgnutls28-dev libgnutls30 libaio-dev libaio1 pkg-config python3 python3-virtualenv python3-pip execstack virtualenv# Download and compile the OpenConnect source code
wget -O - "ftp://ftp.infradead.org/pub/openconnect/openconnect-7.08.tar.gz" | tar zxvf -
pushd openconnect-7.08
sudo mkdir /etc/vpnc
sudo wget -O /etc/vpnc/vpnc-script "http://git.infradead.org/users/dwmw2/vpnc-scripts.git/blob_plain/HEAD:/vpnc-script"
sudo chmod +x /etc/vpnc/vpnc-script
./configure
make
sudo make install
popd# Download and compile the ocproxy source code
git clone https://github.com/cernekee/ocproxy
pushd ocproxy
./autogen.sh
./configure
make
popd# Download and compile the RedSocks source code
git clone https://github.com/darkk/redsocks
pushd redsocks
make
popd# Extract Oracle files (NOTE: Version numbers may differ for you!)
sudo mkdir /opt/oracle
sudo unzip -d/opt/oracle instantclient-basic-linux.x64-12.1.0.2.0.zip
sudo unzip -d/opt/oracle instantclient-sdk-linux.x64-12.1.0.2.0.zip
sudo unzip -d/opt/oracle sqlcl-4.2.0.16.355.0402-no-jre.zip
pushd /opt/oracle/instantclient_12_1
sudo ln -s libclntsh.so.12.1 libclntsh.so
popdexport ORACLE_HOME=/opt/oracle/instantclient_12_1# Update the library paths to find the Oracle Instant Client .so files
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME
sudo ldconfig

A note on Bash on Ubuntu on Windows

If you are doing this under Bash on Windows (or if you get executable stack errors later), you’ll need to execute the following additional commands:

sudo execstack -c /opt/oracle/instantclient_12_1/libociei.so
sudo execstack -c /opt/oracle/instantclient_12_1/libons.so
sudo execstack -c /opt/oracle/instantclient_12_1/libclntshcore.so.12.1
sudo execstack -c /opt/oracle/instantclient_12_1/liboramysql12.so
sudo execstack -c /opt/oracle/instantclient_12_1/libipc1.so
sudo execstack -c /opt/oracle/instantclient_12_1/libclntsh.so
sudo execstack -c /opt/oracle/instantclient_12_1/libnnz12.so
sudo execstack -c /opt/oracle/instantclient_12_1/libmql1.so
sudo execstack -c /opt/oracle/instantclient_12_1/libocijdbc12.so
sudo execstack -c /opt/oracle/instantclient_12_1/libocci.so.12.1
sudo execstack -c /opt/oracle/instantclient_12_1/libclntsh.so.12.1

Now you can carry on :)

CSD Wrapper Script

Many AnyConnect solutions (such as UCL’s) require a wrapper script to work. The one I use was lifted from UCL’s blog, and looks like this:

#!/bin/sh
#set -x

platform_version="x86x64"
device_type="Linux-x86"
device_uniqueid="AAAAAAA"

# delete the csdXXXXXX temp files so they don't start piling up
rm -f $1

exec curl \
--globoff \
--insecure \
--user-agent "AnyConnect Linux" \
--header "X-Transcend-Version: 1" \
--header "X-Aggregate-Auth: 1" \
--header "X-AnyConnect-Identifier-Platform: linux" \
--header "X-AnyConnect-Identifier-PlatformVersion: $platform_version" \
--header "X-AnyConnect-Identifier-DeviceType: $device_type" \
--header "X-AnyConnect-Identifier-Device-UniqueID: $uniqueid" \
--cookie "sdesktop=$CSD_TOKEN" \
--data-ascii @- "https://$CSD_HOSTNAME/+CSCOE+/sdesktop/scan.xml" <<END
endpoint.feature="failure";
endpoint.os.version="Linux";
END

Copy and paste that into a new file called csd_wrapper.sh and then run chmod +x ./csd_wrapper.sh in order to make it executable.

Starting OpenConnect

Now that you have the majority of the set up completed, it’s time to start the VPN connection. Run the following within the ocproxy folder to start the connection to AnyConnect:

openconnect vpn.yourdomain.tld --verbose --csd-wrapper=../csd_wrapper.sh --no-dtls --script-tun --script "./ocproxy -D 11080"

If all is well you should receive output that looks as follows:

Please enter your username and password.
Username:

If so, firstly enter your username and press enter. You’ll then be prompted for your password which you should also type in, followed by enter. If all is well you’ll get output that says something like Connected as x.x.x.x, using SSL where x.x.x.x is the internal IP address within your corporate network that the VPN has assigned you. You should also successfully have a SOCKS proxy set up too, via ocproxy.

You’ll notice that in this configuration OpenConnect is not daemonised (e.g. it runs in the foreground). If this is an issue for you then I recommend you get a multiplexing terminal client like tmux or, my personal favourite (a fantastic, feature-rich wrapper to tmux) byobu; it’s available in the standard Ubuntu repository.

Modifying and Running SQLcl

Running SQLcl is possible with almost no modification at this stage. Simply open up /opt/oracle/sqlcl/bin/sql and find the line that looks like this:

STD_ARGS="-Djava.awt.headless=true -Dapple.awt.UIElement=true -Xss10M -Djava.net.useSystemProxies=true"

Change it to look like the following:

STD_ARGS="-Djava.awt.headless=true -Dapple.awt.UIElement=true -Xss10M -Djava.net.useSystemProxies=false -Dhttp.proxyUser= -Dhttp.proxyPassword= -DsocksProxyHost=127.0.0.1 -DsocksProxyPort=11080 -Dhttp.proxyPort=11080"

Now running /opt/oracle/sqlcl/bin/sql should work just fine. In addition, if you need to use something obscure like TNS or LDAP, you can make use of this; the best way to do it is by using the LDAPCON environment variable like so:

export LDAPCON="jdbc:oracle:thin:@ldap://your.tns.server.yourdomain.tld:4032/#ENTRY#,cn=OracleContext,dc=yourdomain,dc=tld"

If SQLcl is all you need then you have succeeded in your mission to deal with the abomination that is Oracle! If not then do continue on to get RedSocks and Python set up :)

Setting up RedSocks

RedSocks should be downloaded and compiled from before, so just head into that folder and copy the following into a blankredsocks.conf file. Put this file inside the redsocks folder checked out from Git earlier in this guide.

base {
  log_info = on;
  log = "file:./redsocks.log";
  daemon = on;
  redirector = iptables;
  log_debug = on;
}
redsocks {
  local_ip = 0.0.0.0;
  local_port = 11081;
  ip = 127.0.0.1;
  port = 11080;
  type = socks5;
}

Now start up the RedSocks server by running

./redsocks -c ./redsocks.conf

Even though this will daemonise RedSocks, you’ll be able to monitor what it is doing in the background by using tail:

tail -f ./redsocks.log

Setting Up Python

In order to make use of the Instant Client, I recommend you set up cx_Oracle in a Python Virtual Environment (virtualenv/venv).

sudo pip3 install --upgrade pip
virtualenv --python=python3 venv
source venv/bin/activate
pip3 install cx-oracle

With any luck the above will successfully download, install and compile. If you have any issues, make sure you set up the ORACLE_HOME environment variable as shown in the first massive block of commands.

iptables (woop…woop….woop.)

It is a rule that every systems administrator everywhere must hate iptables for one reason or another.
~ Me, 2017

Here we’ll use the wonderful iptables utility to basically say the following for outgoing TCP connections:

If it’s a connection to my local network, do what would normally be done
If it’s a connection to my VPN server, again do what would normally be done (as you can’t proxy a VPN connection through a VPN connection that has not yet been established, hehe)
Otherwise, send it through the VPN

For point #1, find what your local IP Address range is. This is best done with the ifconfig command. When I run this on an AWS instance I get output like the following:

ubuntu@the-hostname-is-here$ ifconfig
eth0      Link encap:Ethernet  HWaddr 06:d8:78:84:86:cd
          inet addr:172.16.1.169  Bcast:172.16.1.255 Mask:255.255.255.0
          inet6 addr: fe80::4d8:78ff:fe84:86cd/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:9001  Metric:1
          RX packets:1162665 errors:0 dropped:0 overruns:0 frame:0
          TX packets:2037959 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:128023471 (128.0 MB)  TX bytes:510731857 (510.7 MB)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:65536  Metric:1
          RX packets:23005 errors:0 dropped:0 overruns:0 frame:0
          TX packets:23005 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1
          RX bytes:7611373 (7.6 MB)  TX bytes:7611373 (7.6 MB)

I have highlighed two important addresses. The first is the inet addr which is our IP Address. The second is the Mask which is our Subnet Mask, or essentially an instruction to the networking stack about what address range falls within our jurisdiction. The value I have here (255.255.255.0) is by far the most common for an internal network. Once you have these two values head to this page and enter your IP address into the first box and select your Subnet mask from the second. My example’s input looks like this:

Press the View Subnet button and you’ll get a page with a tab that looks like this:

Take a note of that circled code (it’ll be in the format a.b.c.d/e where a, b, c, d and e are numbers.

NB: the above rule assumes that your complete network is visible using this subnet. If you are on AWS and have multiple availability zones on different ranges, for example, or you have a LAN configuration with multiple ranges, (such as 192.168.0.0/24 and 192.168.1.0/24) you may have to change this to something more permissive like 192.168.0.0/16. If in doubt, consult your system administrator.

Now run the command nslookup vpn.yourdomain.tld where the second half is your VPN server. You should get an output like this:

Server:         172.16.0.2
Address:        172.16.0.2#53Non-authoritative answer:
Name:   vpn.yourdomain.tld
Address: 1.2.3.4

Take a note of that address, too.

Now we are ready to construct our iptables rules. Type the following commands:

sudo iptables -t nat -A OUTPUT -p tcp -j REDSOCKS
sudo iptables -t nat -A REDSOCKS -d 0.0.0.0/8 -j RETURN
sudo iptables -t nat -A REDSOCKS -d a.b.c.d/e -j RETURN
sudo iptables -t nat -A REDSOCKS -d 127.0.0.0/8 -j RETURN
sudo iptables -t nat -A REDSOCKS -d 169.254.0.0/16 -j RETURN
sudo iptables -t nat -A REDSOCKS -d 1.2.3.4 -j RETURN
sudo iptables -t nat -A REDSOCKS -o eth0 -p tcp -j DNAT --to-destination 127.0.0.1:11081

Note that you should change the bolded sections above to the values you derived earlier in this section.

Time to Test

If the above was all set up correctly, you should be able to connect to your database in code! I recommend you keep an eye on the RedSocks and OpenConnect log files iniitally to ensure traffic is being passed through the system. If certain websites are slow you may also wish to tweak the iptables rules to make them bypass the VPN.

If LDAP fails you at this stage (I had no luck with the Instant Client in Python, and it was ignoring my sqlnet.ora + ldap.ora files), you may need to use TNSPING, which is included with the Oracle full client on Windows, to fetch the direct connection string for the database server. Combine this with your username and password to connect to the database.

Let me know what you think and if this guide was helpful to you! :)

Sorry it’s so gross.

- Chris