Remote Access to The Raiser's Edge 7 via OpenSSH

Purpose

I was asked at my San Diego, CA-based company to provide access to The Raiser's Edge 7 (ASA) and The Financial Edge 7 (ASA) to our remote offices in Anaheim, CA and El Paso, TX. We run RE/FE on Win2k servers, and I discovered very quickly that remote access solutions for Windows are expensive, as in > $0. So I decided to take an old workstation nobody was using anymore and install an SSH tunnel on Linux. The final, working-quite-well solution uses the Debian stable distribution (currently called "woody", i.e. Debian GNU/Linux 3.0), ssh (which on Debian woody is OpenSSH, version 1:3.4p1-1), and some custom settings to the client's ODBC connection.

One side-effect we should mention right off the bat is that this same tool, SSH, can be used to share documents on the SSH server securely over the 'Net. With a little bit of work, we can also share documents on Windows machines through the same connection. More on this below.

Requirements

The basic requirement for a remote access solution is that it securely pass desirable traffic between the RE server and the RE client (I use RE throughout, but FE works the same way). Therefore, the solution must be able to: authenticate users, encrypt communication, and connect the two machines. We are somewhat fortunate as RE users, in that Blackbaud has seen fit to abtract their database connections through ODBC regardless of where the client and server sit in relation to each other on the network. This means that, if we can get an ODBC connection between the two machines, the RE/FE connection will follow without any further effort on our part.

Setup

It's up to you at which stage you want to begin testing these mechanisms on a remote Windows client. I found it easiest to set up a test box running RE on the local LAN like any other LAN user, then move that box outside the firewall and test the SSH setup. Only after this test box was running correctly did I make the effort to contact Anaheim and El Paso and set them up. YMMV. I will describe this process as I did it: locally, then nearly locally, then remote.

Configuration

We will first configure sshd (the server component). Then, we will install ssh on the clients and test connections. Finally, we will modify RE's ODBC connections to use the new tunnel. Test again. Accept accolades.

sshd

OpenSSH is a free (speech and beer) implementation of the SSH protocol suite of network connectivity tools. It consists primarily of a server component and a client component; we are only interested here in the server component, named sshd. It is modified via a flat configuration file. On Debian, you shouldn't have to change much. There are certain global settings which need to be made for security reasons:

PermitRootLogin no
PermitEmptyPasswords no

There are several ways to get sshd to run when your system boots; we haven't the space to go into them here. Debian woody should set it up with a script in /etc/init.d/ssh. Either reboot (shudder) or type /etc/init.d/ssh start. If this works, you should see a line mentioning /usr/sbin/sshd when you execute the command ps ax.

User Accounts

Before we proceed, we need to discuss authentication options. We are mixing Unix and Windows machines, here, so there is no "default" mapping between user accounts. By far the easiest solution to this, if you're not using the Linux box for other services, is to create a single Unix user, which all RE users will use to create their secure connection into your network. For example, you might type (as root, on the sshd server):

tuxville:/# addgroup bbusers
Adding group bbusers (102)...
Done.
tuxville:/# adduser --gid 102 bbuser
Adding user bbuser...
Adding new user bbuser (1002) with group bbusers.
Creating home directory /home/bbuser.
Copying files from /etc/skel
Enter new UNIX password: ********
Retype new UNIX password: ********
passwd: password updated successfully
Changing the user information for bbuser
Enter the new value, or press return for the default
        Full Name []: BlackBaud User
        Room Number []:
        Work Phone []:
        Home Phone []:
        Other []:
Is the information correct? [y/n] y
tuxville:/#

PuTTY

PuTTY is a free implementation of Telnet and SSH for Win32 platforms, along with an xterm terminal emulator, available at: http://www.chiark.greenend.org.uk/~sgtatham/putty/. You should install this on a Windows box on your local LAN (local to the RE server, that is). You can read the full documentation for PuTTY (and you might want to if this whole SSH thing is new to you). We are going to use it for the moment to test our sshd setup.

Run PuTTY on a Windows client on the local LAN before you get into the whole remote networking black hole. You should, at this point, connect by IP; put the IP address of the Linux machine in the "Host Name" textbox. Make sure PuTTY is set up to connect via SSH on port 22, since PuTTY can also be used for other protocols like Telnet (port 23). Open the connection and log on as the user you just created. You now have a remote terminal to your Linux box! If this step does not work, double-check the following components:

Tunnelling (Port Forwarding)

We now have a secure connection between a local Windows client and the Linux box. All communication, including the exchange of passwords, is encrypted via SSH. Neat. Now we are going to take advantage of a very powerful feature in SSH, the feature all the Unix gurus keep to themselves: port forwarding.

Port forwarding is a (relatively) simple concept. Now that we have a secure connection, we are going to take other, insecure communication channels and tunnel them through the secure channel. In practice, this generally is used to connect to services on the same machine that is serving SSH. For example, a large amount of end-user's handling of e-mail occurs over the POP3 protocol on port 110. This protocol is not encrypted; another machine on the netowrk can sniff (capture and inspect) POP3 traffic and quite easily pull out user names and passwords (I just did this the other day when I couldn't remember one of my own mail passwords). A reasonable and common use of SSH is to wrap this unencrypted traffic in a secure channel: clients connect to the SSH server, then connect on a non-standard port to their POP3 server.

However, we also have the opportunity to connect our client to any service to which the SSH server can itself connect, even if that service is running on another machine, and even if that machine is running Windows 2000 Server. In our case, we are going to take our Windows client's RE traffic and, tunnelling it through the Linux SSH server, connect to the Windows RE server. This is the sort of thing at which Linux excels.

The interesting part is that we need make no changes to our Linux server in order to achieve this: all configuration of the tunnels occurs on the client. This may be a benefit or detriment, depending on your point-of-view. On the down side, it means we need to configure each client separately, but we have to configure their ODBC settings anyway. It also means that other connections to other services provided on the LAN can be tunneled without explicit server settings; on the one hand, this makes customized clients easier, on the other hand, it means you might want to investigate denying traffic that you don't want tunnelled. Again, YMMV.

Configuration of the SSH client (PuTTY) involves creation of a forwarded port. In our case, we are going to take a local port (for example, localhost:5000) and connect it to the remote port of the RE server (in my case, 192.168.0.5:2638). Perhaps a diagram or two would be useful at this point. The first diagram show a remote connection with no tunnelling; the remote client connects to the RE server on the default Sybase ASA port of 2638:

Remote client connection with no tunnelling

This is extremely insecure, and that insecurity is what we are eliminating with the use of an SSH tunnel:

Remote client connection with SSH tunnelling

The RE client (actually, the ODBC connection that RE uses) needs to connect to an RE server. As you can see in the second diagram, we "fake out" the client to believe that that service is running, not on the other side of the Internet, but instead on our very own client on port 5000! Once we can do that, we inform PuTTY to grab that incoming traffic ("incoming" from our own machine) on port 5000; it then is instructed to pass such traffic through the SSH server on the other side of the Internet. Not only that, but it is configured to instruct the server to pass it on again, to a Windows machine on the same local network as the SSH server, a Windows machine which just happens to be serving Sybase connections on port 2638.

That seems like something which might be very complicated to set up, but it is in fact quite simple using PuTTY. Here's how (remember, this is done on the client machine):

Specifying forwarded port information in PuTTYAdding a forwarded port to PuTTY

ODBC Configuration

Finally, we must instruct RE to connect on local port 5000. Both The Raiser's Edge and The Financial Edge leave this decision up to ODBC: the Open DataBase Connectivity module. ODBC is a database abstraction layer, which allows RE and FE to code to a single ODBC API, and leave up to ODBC the gory details about whether your installation uses Oracle, Sybase, or MS SQL. Unfortunately, even though we use ODBC, the port specification is vendor-specific. Currently this document addresses only Sybase codes; if someone wants to work out Oracle code and shoot it over to me, I'd be glad to credit them. Once we move to RE 7.6 in the Fall of 2003, we'll migrate to MS SQL and update this document.

Sybase ODBC Configuration

Several settings ought to be made. In practice, I bundle these all up into a single Windows Registry file, which I then execute on each client. I'll show you the file now, then reference it as we deconstruct what it does. This is for Windows 2000, by the way. If you don't feel comfortable editing the Registry, don't!

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\RE7_1]
"Driver"="C:\\WINNT\\System32\\dbodbc7.dll"
"Description"="RE7 Network Client DataSource"
"DatabaseName"="DB_2"
"EngineName"="Blackbaud70"
"AutoStop"="NO"
"KeysInSQLStatistics"="YES"
"Integrated"="No"
"Debug"="NO"
"DisableMultiRowFetch"="YES"
"CommLinks"="TCPIP{DOBROADCAST=none;host=127.0.0.1;port=5000}"

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
"TcpMaxDataRetransmissions"=dword:0000000a

The first section (..\RE7_1) sets up an ODBC Data Source. This is the object which RE will use to connect to the Sybase database. Please make sure you understand these settings before arbitrarily committing them to your client! My system knows the RE database as "DB_2", but yours may be "DB_1" or something quite different. If in doubt, check your Blackbaud Management Console, or the ODBC settings of a standard LAN install, either in the Registry, or via Control Panel->Administrative Tools->ODBC. Your engine name may also differ drastically.

On a LAN, the "CommLinks" key typically contains only "TCPIP". It may even be blank in some situations. When we connect remotely, however, it is the field by which we instruct RE+ODBC to treat our database as if it were running on port 5000 on our own client machine. The "DOBROADCAST=none" parameter instructs the local Sybase driver to use only TCP to find and communicate with the RE server; in other words, don't use UDP.

As I found out rather expensively (it took two trips to Anaheim), case does matter in these parameters. Make sure the word 'none', for example, is lower-case (at least, don't mix case). The "port" value is that which you already specified in your PuTTY configuration. Your local host's IP address is 127.0.0.1. This is known as the loopback address on every machine since the Stone Age; if it doesn't work (i.e. you can't ping it), your machine is seriously broken.

The "TcpMaxDataRetransmissions" value is not part of the ODBC configuration per se; what it does is keep your SSH tunnel alive longer without activity. I found our clients' PuTTY sessions were freezing after a relatively short time. This lengthens that time. Remember, you will have no access to the RE server unless an SSH tunnel session is running in the background.

RE7 to GL

If you have an installation of RE which connects to FE, you'll need to perform the same magic, even if you do not install FE on this particular client. Example: you have RE Funds mapped to FE accounts and projects (via GL Distribution tables). You have a user in Sri Lanka who needs to enter Gifts in RE, but shouldn't have access to FE. You still (almost surely) need another ODBC connection to the FE database, so that RE can look up FE accounts when distributing/posting. Here's an example of mine:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\RE7_GL]
"Driver"="C:\\WINNT\\System32\\dbodbc7.dll"
"Description"="GLWin\\RE7 Client"
"DatabaseName"="DB_1"
"EngineName"="Blackbaud70"
"AutoStop"="No"
"KeysInSQLStatistics"="YES"
"Debug"="NO"
"DisableMultiRowFetch"="YES"
"CommLinks"="TCPIP{DOBROADCAST=none;host=127.0.0.1;port=5000}"

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
"TcpMaxDataRetransmissions"=dword:0000000a

Notice the port is the same, since for Sybase at least, both RE and FE are served from the same database engine, which only has one port. The databaseName parameter this time matches my Financial Edge database name.

I don't need to duplicate the TcpMaxDataRetransmissions value here, but I figured it couldn't hurt.

Testing

Fire up RE on the client. That ought to work. This can seem a complicated process if you're new to networking concepts. If it fails, check it all again. In many environments, you will need to contact additional personnel, such as system or network administrators, to help you open the ports between client and server(s), specifically port 22 inbound to your RE server.

Extra Goodies

You don't need to do or care about any of these next items; they're simply mentioned as nice side-effects of having implemented an SSH server.

As stated previously, PuTTY gives you a TTY (terminal access) to your Linux machine. You can use this as a secure replacement for telnet.

OpenSSH also provides for secure file transfer, usually via scp and/or sftp. PuTTY includes both. In addition, Windows clients may choose to look into WinSCP, a free SCP client which mimics either Windows Explorer or Norton Commander. It does not currently have tunneling capabilities (so you still need PuTTY for RE/FE), but since you are already running SSH now, it's a nice bonus. Who needs an expensive Windows VPN when you've got Linux?

Also as mentioned above, I use Samba and its winbind component to do two things:

  1. Look up and authenticate user names against Windows Active Directory. Winbind maps AD accounts to Unix accounts, passing authentication requests through to Active Directory. In this way, I can eliminate a separate set of Unix accounts for RE access.
  2. Auto-mount Windows shares to the directory structure on the Linux SSH box. This allows WinSCP users in remote locations to have drag-and-drop file transfer capabilities to both Unix and Windows files. When coupled with winbind, the permissions for remote Windows shares are also passed to Active Directory, again bypassing the need for separate Unix user accounts. If you're using Debian, this probably involves a kernel recompile, since mounting with smbd requires your kernel to handle SMB filesystems. Not for the Unix-newbie, but powerful if you can get it to work. I may write this process up in a separate document someday.

Concluding Remarks

  1. Help me make this better if you know how. That might be new code, new ideas, or simply an email which says, "I don't understand part X".
  2. Let me know if this document helped you. fumanchu@amor.org.
  3. The possibilities with SSH are different than with Terminal Services or VPN's. Each have their strengths and weaknesses. Take this as a base and run with it.
    1. Robert Brewer
      MIS
      Amor Ministries
      fumanchu@amor.org