Installing Capita’s SIMS FMS on a Citrix Metaframe server (and having it work)

Software

System Requirements:

  • Citrix
  • Capita’s SIMS
  • FMS for SIMS
  • Windows Server 2003 / Windows Server 2003 R2

The Problem:

Capita’s SIMS, or ‘Schools Information Management System’ is a widely used UK school management application made by the masters of conglomerate outsourcing the Capita group. The propaganda states that it is used by over 22,000 schools here in the UK…

FMS (Financial Management System) is a bursary module for SIMS providing financial support backed by the wider SIMS database sub-system. FMS makes use of its own database environment, separated from the main SIMS data store: should you attempt to install FMS (version less than 6.91) on a Citrix server you are going to run into a no longer working version of FMS.

FMS installation on the Citrix terminal server system renders a mal-configured users session, resulting in a DSN error.

SIMS configuration requires the presence of a User level DSN on the system (At the NTLM SAM level) in order for the client application to make a connection to the database server.

The problem has been diagnosed as three fold:

  1. The FMS installation process is not MSTSC/Citrix compatible and is unable to correctly configure the client system at run-time. The current assumption is that this is due to the use of a pre-windows/very early windows 2000 driver which is certified for NT4 (non-TSE) and not enterprise TS environments (Dated 1999).
  2. The user DSN database setting are not being registered into the default profile on the server and as such no user accounts are receiving the user configuration settings.
  3. The legacy database connectivity driver is unable to automatically register on the system, relying upon the installation process to perform all required integration into the ODBC environment. As the installer is failing, so is the driver registration. This means that the User DSN cannot be created on the TS without intervention.

Lovely problem, terrible system

As you all know, I like a challenge.

FMS makes use of the Borland Desktop Engine, or more accurately the Sybase SQL Anywhere 5.0 DBMS. The version compiled for use by SIMS FMS was compiled way back in 1999 when men were men, terminal server were never used and Windows 2000 was not exactly being used by anyone other than… me.

Here we are in 2007 and trying to push the FMS configuration data off of a standalone workstation and onto a dedicated Citrix server; it wasn’t going to play nicely was it!

FMS will install, but it will never enable you to connect to the database citing connectivity issues.

The Fix:

This problem is squarely due to Capita having never evolved their database components, it isn’t FMS causing the issue, it isn’t Citrix, Windows Server 2003 or SIMS. It’s SQL Anywhere 5.0.

I really would urge you as a School’s administrator to head to Capita at this point and perform a cost / benefit analysis on obtaining FMS 6.91 or higher, which will port your old databases across to a new MS SQL 2005 environment. I guess even Capita realised that they were on borrowed time!

There are two distinct problems that must be solved before you will be able to use FMS over Citrix:

  1. The installation of the SQL Anywhere 5.0 driver set is not designed to run across the Terminal Server Installation Mode. As a direct consequence, once the installation is complete you will be left with the FMS database binary data scattered around your servers hard drive, but it will not be in a state where it is able to do anything! Windows is left oblivious to its presence.
  2. FMS makes use of a Mandatory DNS connection hosted at the user level to initiate connections between the client (on the Citrix server) and the SIMS / FMS database (hopefully stored somewhere other than the Citrix server). The user level DNS is not installed as part of the Citrix install and must be constructed by hand in the required user accounts.

 

Fixing the Sybase SQL Anywhere 5.0 Driver

The failure here is down to the age of the database components being used. Sybase’s developers must have been using legacy design standards and/or cut some corners in the construction of their ODBC driver set because there is not a single component in version 5.0 which is capable of performing and self registration functions under Win32.

The long and short of it is that the entire system had to be pulled to pieces, analysed and a manual driver registration system generated.

You will be pleased to hear that I served my penance, did my good deed for society and the future generation and am offering it up to you here to download.

Download: FMS-Terminal-Server-Hack.zip (2KB)

 

The following steps require that the FMS installation be performed on the TS as far as is permissible before failure.

The steps assume that the default installation paths are used on all components of the database client system.

The following documentation assumes that future connection attempts are being made to the FMS database server using the same database name, conventions and settings as found at the time of writing.

Prerequisites

  1. Ensure that FMS is installed
  2. Ensure that the Sybase SQL Anywhere 5.0 driver components are located on the client at the following location:
    c:\SQLANY50\WIN32
    Note: The default installation for the client software is C:\SQLANY50\win32\. If you have installed the client applications to another location, you must edit the path in the hack file you downloaded above before registering it.
  3. Ensure that you have the DBclient.exe in the above path
  4. Ensure that you have FMS-Terminal-Server-Hack.reg from the zip file accompanying this document
  5. Make any edits to the fie that are required to tailor it to your system (see note above)
  6. Log into the terminal server with Administrative permissions to begin the process

Server configuration

Enter FMS-Terminal-Server-Hack.reg into the Windows registry.
This should be all that is required to configure the server. A reboot should not be necessary.

To validate the reconfiguration

  1. Start > Run > obdcad32.exe > ok
  2. User DSN tab
  3. Add
  4. Scroll to the bottom of the list and verify the presence of: Sybase SQL Anywhere 5.0 version 5.05.04.1980 date 10/08/1999

Client Configuration

The use of a mandatory User DSN means that the DSN must be configured on the client account, not the administrator account as it is not a global configuration setting.

The settings will be replicated to the user account profile.

 

Note: I cannot/will not provide screen shots of the process that follows in the interest of preserving client security. You will need to source the information from a working FMS client system from within your organisation.

 

To add the User DSN by hand:

  1. Start > Run > odbcad32.exe
  2. User DSN tab
  3. Add
  4. Select Sybase SQL Anywhere 5.0 from the bottom of the list, click Finish

Configure the Sybase DSN as follows

  • DSN: SIMS32
  • Description: General
  • User ID: <blank>
  • Password: <blank>
  • Server Name: <YOUR FMS INFO FROM A WORKING CLIENT>
  • Database Name: <YOUR FMS INFO FROM A WORKING CLIENT>
  • Database File: <YOUR FMS INFO FROM A WORKING CLIENT>
    Type: Custom
  • Translator Name: <No Translator>
  • Enable: Microsoft Applications (Keys in SQLStatistics)

Click on the OPTIONS button

  • Start Command: c:\sqlany50\win32\dbclient.exe -ta 600 -x NamedPipes,TCPIP
    Note: Modify the path above to match your Sybase SQL Anywhere 5.0 drive path
  • Database Switches: <blank>
  • Agent: Client
  • Autostop Database: Ticked
  • Isolation Level: <blank>
  • Describe Cursor Behaviour: If Required

Click Ok twice to save the DSN

Run FMS to prove that it works and and publish it into your Citrix environment as to your specific organisational needs.